practice.sql

/* Exercise 1 */
SELECT isbn
  FROM publishers JOIN editions
    ON editions.publisher_id = publishers.id
  WHERE publishers.name = 'Random House';
/* Exercise 2 */
SELECT isbn, title
  FROM publishers JOIN editions
    ON editions.publisher_id = publishers.id JOIN books
    ON editions.book_id = books.id
  WHERE publishers.name = 'Random House';
/* Exercise 3 */
SELECT editions.isbn, title, stock.stock
  FROM publishers JOIN editions
    ON editions.publisher_id = publishers.id JOIN books
    ON editions.book_id = books.id JOIN stock
    ON editions.isbn = stock.isbn
  WHERE publishers.name = 'Random House';
/* Exercise 4 */
SELECT editions.isbn, title, stock.stock
  FROM publishers JOIN editions
    ON editions.publisher_id = publishers.id JOIN books
    ON editions.book_id = books.id JOIN stock
    ON editions.isbn = stock.isbn
  WHERE publishers.name = 'Random House' AND stock.stock >= 1;
/* Exercise 5 */
SELECT isbn, book_id, edition, publisher_id, publication,
  CASE WHEN type = 'h' THEN 'Hardcover'
    ELSE 'Paperback' END
  FROM editions;
/* Exercise 6 */
SELECT title, publication
  FROM books LEFT JOIN editions
    ON books.id = editions.book_id;
/* Exercise 7 */
SELECT SUM(stock.stock)
  FROM stock;
/* Exercise 8 */
SELECT AVG(stock.cost), AVG(stock.retail), AVG(stock.retail-stock.cost)
  FROM stock;
/* Exercise 9 */
SELECT book_id, stock
  FROM stock JOIN editions
    ON stock.isbn = editions.isbn
  ORDER BY stock DESC
  LIMIT 1;
/* Exercise 10 */
SELECT author_id, (first_name || ' ' || last_name) AS "Author Name", COUNT(books.id)
  FROM books JOIN authors
    ON books.author_id = authors.id
  GROUP BY author_id, first_name, last_name;
/* Exercise 11 */
SELECT author_id, (first_name || ' ' || last_name) AS "Author Name", COUNT(books.id)
  FROM books JOIN authors
    ON books.author_id = authors.id
  GROUP BY author_id, first_name, last_name
  ORDER BY count(books.id) DESC;
/* Exercise 12 */
SELECT title
  FROM books as b JOIN editions as e1
    ON b.id = e1.book_id AND e1.type='p' JOIN editions as e2
    ON b.id = e2.book_id AND e2.type='h';
/* Exercise 13 */
SELECT editions.publisher_id, AVG(stock.cost), COUNT(editions.book_id)
  FROM stock JOIN editions
    ON stock.isbn = editions.isbn
  GROUP BY publisher_id;