bookstore_queries.sql

--Exercises:

--1

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

--2

SELECT books.title, editions.isbn 
  FROM books JOIN editions ON books.id = editions.book_id
  WHERE editions.publisher_id = 59;

--3

SELECT books.title, editions.isbn, stock.retail, stock.stock 
  FROM books JOIN editions ON books.id = editions.book_id
  JOIN stock ON editions.isbn = stock.isbn 
  WHERE editions.publisher_id = 59;

--4

SELECT books.title, editions.isbn, stock.retail, stock.stock 
  FROM books JOIN editions ON books.id = editions.book_id 
  JOIN stock ON editions.isbn = stock.isbn 
  WHERE editions.publisher_id = 59 
  AND stock.stock <> 0;

--5

SELECT books.title, editions.isbn, stock.retail, stock.stock, 
  CASE 
  WHEN editions.type = 'h' THEN 'hardcover'
  WHEN editions.type = 'p' THEN 'paperback'
  END AS binding
  FROM books JOIN editions ON books.id = editions.book_id 
  JOIN stock ON editions.isbn = stock.isbn 
  WHERE editions.publisher_id = 59 AND stock.stock <> 0;

--6

SELECT books.title, editions.publication
  FROM books LEFT JOIN editions ON books.id = editions.book_id;

--7

SELECT SUM(stock) FROM stock; --512

--8

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

--9

SELECT editions.book_id,  SUM(stock.stock) as total_stock
  FROM editions JOIN stock ON editions.isbn = stock.isbn
  GROUP BY editions.book_id
  ORDER BY total_stock DESC
  LIMIT 1;  -- id: 4513, stock: 166

--10

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

--11

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

--12
SELECT books.title FROM books 
  JOIN editions ON books.id = editions.book_id 
  WHERE editions.type = 'h' 
  INTERSECT (
  SELECT books.title FROM books 
  JOIN editions ON books.id = editions.book_id 
  WHERE editions.type = 'p'
  );

-- 13

SELECT publishers.name as publisher,
  AVG(stock.retail) as avg_retail,
  COUNT(editions.isbn) as editions_published
  FROM publishers 
  JOIN editions ON publishers.id = editions.publisher_id 
  JOIN stock ON editions.isbn = stock.isbn 
  GROUP BY publishers.id;