Bookstore SQL

bookstore.sql

#NUMBER 1

SELECT e.isbn FROM editions AS e 
JOIN publishers AS p ON e.publisher_id = p.id 
WHERE p.name = 'Random House';

#NUMBER 2 NUMBER 3 NUMBER 4, NUMBER 5

SELECT b.title, e.isbn, s.stock, s.retail,
  CASE 
  WHEN type = 'h' THEN 'hardcover'
  WHEN type = 'p' THEN 'paperback'
  END AS type
FROM editions AS e JOIN publishers AS p ON e.publisher_id = p.id 
JOIN books AS b ON b.id = e.book_id
JOIN stock AS s ON s.isbn = e.isbn
WHERE p.name = 'Random House' AND stock != 0;

# NUMBER 6

SELECT b.title, e.publication
FROM books AS b LEFT OUTER JOIN editions AS e ON b.id = e.book_id;

# NUMBER 7

SELECT SUM(stock) FROM stock;

# NUMBER 8

SELECT AVG(cost) AS "Average Cost", AVG(retail) AS "Average Retail", AVG(retail-cost) AS "Average Profit"
FROM stock;

# NUMBER 9

SELECT e.book_id, s.stock FROM editions AS e
JOIN stock AS s ON s.isbn = e.isbn
ORDER BY stock DESC
LIMIT 1;

# NUMBER 10, 11

SELECT (a.id) AS "Author ID", COUNT(b.author_id) AS "Number of Books",
(a.last_name || ' '|| a.first_name) AS "Author's Full Name"
FROM authors AS a JOIN books AS b ON b.author_id = a.id
GROUP BY a.id
ORDER BY COUNT(b.author_id) DESC;