select * from pubs..titles

select title_id from pubs..titles
CROSS APPLY (SELECT AVG(Price) AS average FROM pubs..titles) AS D
WHERE price > average


select title_id from pubs..titles
CROSS APPLY (SELECT 10 AS average) AS D
WHERE price > average


SELECT TOP 2 title_id FROM pubs..titles


SELECT title_id, au_id FROM 
(SELECT TOP 2 title_id FROM pubs..titles) AS T
CROSS APPLY
(SELECT TOP 3 au_id FROM pubs..authors) AS A

SELECT title_id, au_id FROM 
(SELECT TOP 2 title_id FROM pubs..titles) AS T
JOIN
(SELECT TOP 3 au_id FROM pubs..authors) AS A
ON 1=1



SELECT title_id, au_id FROM 
(SELECT TOP 2 title_id FROM pubs..titles) AS T
CROSS APPLY
(SELECT TOP 0 au_id FROM pubs..authors) AS A



SELECT title_id, au_id FROM 
(SELECT TOP 2 title_id FROM pubs..titles) AS T
INNER JOIN
(SELECT TOP 3 au_id FROM pubs..authors) AS A
ON 1=1

SELECT title_id, au_id FROM 
(SELECT TOP 2 title_id FROM pubs..titles) AS T
RIGHT JOIN
(SELECT TOP 0 au_id FROM pubs..authors) AS A
ON 1=1


DECLARE @x xml
set @x = '<root><b>10</b></root>'
SELECT title_id, N.C.query('.') FROM pubs..titles
JOIN @x.nodes('//a') AS N(C)
ON N.C.exist('//a') = 1


SELECT title_id from pubs..titles AS T1
CROSS APPLY
(SELECT AVG(price) as average from pubs..titles) AS T2
WHERE T1.Price > T2.average


SELECT title_id from pubs..titles AS T1
WHERE (SELECT AVG(price) FROM pubs..titles) < T1.Price



DECLARE @x xml
set @x = '<root><b>10</b></root>'
SELECT title_id, N.C.query('.') FROM pubs..titles,
 @x.nodes('//b') AS N(C)
WHERE N.C.exist('//b') = 1

--DROP TABLE Custs

CREATE TABLE custs
(
id INT,
Name varchar(10)
)

INSERT INTO custs VALUES (1, 'Joe')
INSERT INTO custs VALUES (2, 'Jane')
INSERT INTO custs VALUES (3, 'Jack')
INSERT INTO custs VALUES (4, 'Joane')
INSERT INTO custs VALUES (5, 'Juan')
DECLARE @x xml
set @x = '<root>
<b id="1">10</b>
<b id="2">11</b>
<b id="1">12</b>
<b id="4">13</b>
<b id="1">14</b>
<b id="3">15</b>
</root>'
SELECT id, 
SUM(N.C.value('./text()[1]', 'int'))
--N.C.query('./text()')
 FROM custs
JOIN
@x.nodes('//b') AS N(C)
ON N.C.value('@id', 'int') = custs.id
GROUP BY id


SELECT title_id FROM pubs..titles
where price > (SELECT AVG(price) FROM pubs..titles)

SELECT T1.title_id, T1.price - T2.average AS DIFF FROM pubs..titles AS T1
CROSS APPLY (SELECT AVG(price) AS average FROM pubs..titles) AS T2

SELECT T1.title_id, T1.price - (SELECT avg(PRICE) from pubs..titles)
FROM pubs..titles AS T1



SELECT T1.title_id, T1.price - T2.p AS DIFF
 FROM pubs..titles AS T1
CROSS APPLY
(SELECT MAX(price) AS p FROM pubs..titles
WHERE T1.pub_id = pub_id
) AS T2





SELECT T1.title_id, T2.p AS pub_id
 FROM pubs..titles AS T1
OUTER APPLY
(SELECT TOP 1 title_id AS p FROM  pubs..titles
WHERE T1.pub_id = pub_id
) AS T2

SELECT T1.title_id, T2.p AS pub_id
 FROM pubs..titles AS T1
CROSS APPLY
(SELECT TOP 1 title_id AS p FROM  pubs..titles
WHERE T1.pub_id = pub_id
ORDER BY price
) AS T2


SELECT T.title, P.pub_name FROM
pubs..publishers P
CROSS APPLY
(SELECT TOP 1 title from pubs..titles
where P.pub_id = pub_id
ORDER BY Price
) AS T


select * from pubs..titles


SELECT T.title, P.pub_name FROM 
pubs..publishers P
OUTER APPLY
(SELECT TOP 1 title from pubs..titles
where P.pub_id = pub_id
ORDER BY Price
) AS T

select  COUNT(T.Title_id), P.pub_id from pubs..publishers AS P
JOIN pubs..titles T
ON T.pub_id = P.pub_id
GROUP BY P.pub_id

select count(*) from pubs..publishers
select count(*) from pubs..titles





SELECT T1.title_id, T1.price - 
(SELECT MAX(price) FROM pubs..titles 
WHERE pub_id = T1.pub_id) 
 AS DIFF FROM pubs..titles AS T1


