Bookstore_SQL.sql

-- Exercise 1

SELECT book_id 
  FROM editions
    WHERE publisher_id = 59;

-- Excercise 2

SELECT b.id, b.title, e.book_id 
  FROM books AS b, editions AS e 
    WHERE (e.publisher_id = 59) AND (b.id = e.book_id);

-- Exercise 3

SELECT DISTINCT b.id, b.title, s.retail, s.stock
  FROM books AS b, editions AS e, stock AS s
    WHERE (e.publisher_id = 59) AND (b.id = e.book_id);

-- Exercise 4

SELECT DISTINCT b.id, b.title, s.retail, s.stock
  FROM books AS b, editions AS e, stock AS s
    WHERE (e.publisher_id = 59) AND (b.id = e.book_id) AND (s.stock > 0);

-- Exercise 5

SELECT DISTINCT b.id, b.title, s.retail, s.stock, 
CASE WHEN e.type = 'h' THEN 'hardcover'
     WHEN e.type = 'p' THEN 'paperback'
     END AS book_type
  FROM books AS b, editions AS e, stock AS s
    WHERE (e.publisher_id = 59) AND (b.id = e.book_id) AND (s.stock > 0);

-- Exercise 6

SELECT b.title, e.publication
  FROM books AS b, editions AS e;

-- Exercise 7

SELECT SUM(stock) FROM stock;

-- Exercise 8

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

-- Exercise 9

SELECT e.isbn, s.stock
  FROM editions AS e, stock AS s
    WHERE (e.isbn = s.isbn)
      ORDER BY s.stock DESC
        LIMIT 1;

-- Exercise 10

SELECT a.id AS "Author ID", 
CONCAT(a.first_name || ' ' || a.last_name) AS "Full Name",
COUNT(b.title) AS "Number of Books"
  FROM authors AS a, books AS b
    WHERE (a.id = b.author_id)
      GROUP BY a.id;

-- Excersize 11

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