select * from authors CROSS APPLY publishers


select au_fname, au_lname, authors.state from authors
CROSS APPLY (
SELECT * from publishers where
publishers.state = authors.state
) A


select au_fname, au_lname, authors.state from authors
CROSS JOIN (
SELECT * from publishers where
publishers.state = authors.state
) A

select * from authors CROSS JOIN (select * from publishers) A
ON 1=1



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


CREATE 
FUNCTION LeastExpensiveNTitles(
@pub_id CHAR(4)
)
RETURNS TABLE
RETURN SELECT TOP 1 title FROM pubs..titles
WHERE pub_id = @pub_id
ORDER BY PRICE

SELECT T.title, P.pub_name FROM
pubs..publishers P
CROSS APPLY
LeastExpensiveNTitles(P.pub_id) AS T

CREATE TABLE Encryption
(
i int
)

with Override
AS
(
select * from titles
)
select * from override






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 au_fname, au_lname, authors.state from authors
JOIN (
SELECT * from publishers where
publishers.state = authors.state
) A
ON 1=1

select au_fname, au_lname, authors.state from authors
JOIN publishers on
publishers.state = authors.state



CREATE FUNCTION pubs(@state char(2))
RETURNS TABLE
RETURN SELECT * from publishers where state = @state



select * from authors JOIN pubs(authors.state)
ON 1=1




CREATE TABLE DetailAccount(
	id INT PRIMARY KEY,
	parent INT,
	balance FLOAT)
CREATE TABLE RollupAccount(
	id INT PRIMARY KEY,
	parent INT)
INSERT INTO DetailAccount VALUES (3001, 2001, 10)
INSERT INTO DetailAccount VALUES(4001, 3002, 12)
INSERT INTO DetailAccount VALUES(4002, 3002, 14)
INSERT INTO DetailAccount VALUES(3004, 2002, 17)
INSERT INTO DetailAccount VALUES(3005, 2002, 10)
INSERT INTO DetailAccount VALUES(3006, 2002, 25)
INSERT INTO DetailAccount VALUES(3007, 2003, 7)
INSERT INTO DetailAccount VALUES(3008, 2003, 9)

INSERT INTO RollupAccount VALUES(3002, 2001)
INSERT INTO RollupAccount VALUES(2001, 1000)
INSERT INTO RollupAccount VALUES(2002, 1000)
INSERT INTO RollupAccount VALUES(2003, 1000)
INSERT INTO RollupAccount VALUES(1000, NULL)


WITH Rollup
AS
(
SELECT id, parent FROM RollupAccount 
WHERE id = 1000
UNION ALL
SELECT R.id, R.parent FROM
RollupAccount AS R
JOIN
Rollup AS RP
ON R.parent = RP.ID
)
SELECT SUM(balance) AS Balance
FROM DetailAccount
JOIN
Rollup
ON DetailAccount.parent = Rollup.id
OPTION (MAXRECURSION 0)
