Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Bookstore SQL Assignment
-- double dash is comment in SQL
--################ Exercise 5 ################
SELECT e.isbn, b.title, s.stock, s.retail,
CASE e.type
WHEN 'p' THEN 'Paperback'
WHEN 'h' THEN 'Hardcover'
END AS cover
FROM editions AS e INNER JOIN publishers AS p ON (e.publisher_id = p.id)
INNER JOIN books AS b ON (e.book_id =b.id)
INNER JOIN stock AS s ON (e.isbn = s.isbn)
WHERE p.name = 'Random House' AND s.stock <> 0;
--################ Exercise 6 ################
SELECT b.title, e.publication
FROM books AS b LEFT OUTER JOIN editions AS e ON (e.book_id = b.id)
;
--################ Exercise 7 ################
SELECT SUM(s.stock) AS "Stock SUM" FROM stock as s;
--################ Exercise 8 ################
SELECT AVG(s.cost) AS "Average Cost", AVG(s.retail) AS "Average Retail", AVG(s.retail) - AVG(s.cost) AS "Average Profit"
FROM stock AS s
;
--################ Exercise 9 ################
SELECT b.id, s.stock AS ID
FROM stock AS s INNER JOIN editions AS e ON (e.isbn = s.isbn)
INNER JOIN books AS b ON (b.id = e.book_id)
ORDER BY s.stock DESC
LIMIT (1)
;
--################ Exercise 10 & 11 ################
SELECT a.id AS "Author ID", (a.first_name || ' ' || a.last_name ) AS "Full Name",COUNT(b.title) AS "Total of Books"
FROM authors AS a LEFT OUTER JOIN books AS b ON (b.author_id = a.id)
GROUP BY a.id
ORDER BY COUNT(b.title) DESC
;
--################ Exercise 12 ################
SELECT b.title AS "Books titles w/ Paperback and Hardcover"
FROM (SELECT e.book_id AS books_id, COUNT(DISTINCT e.type) AS num_types
FROM editions AS e GROUP BY e.book_id ) AS "f" INNER JOIN books AS b ON (f.books_id=b.id)
WHERE f.num_types = 2
;
-- Other possible solution!!!
SELECT b.title AS "Book titles w/ Paperback and Hardcover"
FROM books AS b
WHERE EXISTS(SELECT e.book_id FROM editions AS e WHERE e.book_id = b.id AND type = 'h')
AND EXISTS(SELECT e.book_id FROM editions AS e WHERE e.book_id = b.id AND type = 'p')
;
--################ Exercise 13 ################
SELECT p.name, AVG(s.retail), COUNT(e.publisher_id)
FROM publishers AS p LEFT OUTER JOIN editions AS e ON(e.publisher_id = p.id)
LEFT OUTER JOIN stock AS s ON(s.isbn = e.isbn)
GROUP BY p.name
ORDER BY COUNT(e.publisher_id) DESC
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment