Bookstore SQL Assignment

Select_statments.sql

-- 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
;