SQL Structures

Question 1

FIND ALL

e.g. Find people who have read books from ALL publishers

Answer 1

SELECT first, last FROM People
WHERE NOT EXISTS (
SELECT pid FROM Publishers
EXCEPT
SELECT pid FROM Read R
JOIN Books B ON R.bid = B.bid
WHERE R.sin = P.sin
)

Question 2

FIND ABOVE AVG

e.g. find authors whose books are more expensive than the average book price

Answer 2

WITH avgAll AS (
SELECT AVG(price) AS avgPrice FROM Books ),

avgAuthor AS (
SELECT authorName, AVG(price) AS myAVG
FROM Authors A JOIN Books B ON A.aid = B.aid
GROUP BY A.aid authorName )

SELECT authorName FROM avgAuthor
WHERE myAvg > (SELECT avgPrice FROM avgAll)

Question 3

FIND AT LEAST 2 DIFF

e.g. find people who have read at least 2 diff books

Answer 3

SELECT P.first, P.last
FROM People P
JOIN Read r ON P.sin = r.sin
Join Read s ON r.sin = s.sin
WHERE r.bid != s.bid

Question 4

FIND AT LEAST X AMOUNT

e.g. find publishers located in a city with at least 50 people

Answer 4

WITH PubCount AS (
SELECT P.pid, pubName, COUNT(Pe.sin) AS cnt
FROM Publishers P
JOIN People Pe ON P.city = Pe.city
GROUP BY P.pid, pubName
)
SELECT pubName
FROM PubCount
WHERE cnt >= 50

Question 5

FIND MOST

e.g. find the book that has been read by the most people

Answer 5

WITH cnt AS (
SELECT title, COUNT(*) AS total
FROM Books B JOIN Read R ON B.bid = R.bid
GROUP BY B.bid, title
)

SELECT title, total
FROM cnt
WHERE total(SELECT MAX(total) FROM cnt)