Bookstore SQL Assignment

exercises.sql

# exercise 1
SELECT isbn
  FROM editions e JOIN publishers p ON (e.publisher_id = p.id)
  WHERE (p.name = 'Random House')

# exercise 2
FROM (editions e JOIN publishers p ON (e.publisher_id = p.id))
  INNER JOIN books b ON (b.id = e.book_id)
  WHERE (p.name = 'Random House')

# exercise 3
SELECT e.isbn, b.title, s.stock, s.retail
  FROM (editions e JOIN publishers p ON (e.publisher_id = p.id))
    JOIN books b ON (b.id = e.book_id)
    JOIN stock s ON (s.isbn = e.isbn)
    WHERE (p.name = 'Random House')

# exercise 4
SELECT e.isbn, b.title, s.stock, s.retail
  FROM (editions e JOIN publishers p ON (e.publisher_id = p.id))
    JOIN books b ON (b.id = e.book_id)
    JOIN stock s ON (s.isbn = e.isbn)
    WHERE (p.name = 'Random House')
    AND (s.stock != 0)

# exercise 5  <= it works!!
SELECT e.isbn, b.title, s.stock, s.retail,
  CASE WHEN e.type = 'p' THEN  'paperback'
         ELSE 'hardcover'
         END AS type
  FROM (editions e JOIN publishers p ON (e.publisher_id = p.id))
    JOIN books b ON (b.id = e.book_id)
    JOIN stock s ON (s.isbn = e.isbn)
    WHERE (p.name = 'Random House')
    AND (s.stock != 0);

# exercise 6
SELECT b.title, e.publication FROM books b LEFT JOIN editions e
  ON (b.id = e.book_id);

# exercise 7
SELECT SUM(s.stock) AS "Total Copies"
  FROM stock s;

# exercies 8
SELECT AVG(s.cost) AS "Average Price", AVG(s.retail) AS "Average Retail", AVG(s.retail - s.cost) AS "Average Profit"
  FROM stock s;

# exercise 9
SELECT e.book_id
  FROM stock s JOIN editions e ON  (s.isbn = e.isbn)
  ORDER BY s.stock DESC
  LIMIT 1;

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

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

# exercise 12 <= havent figured it out yet
 SELECT book_id FROM editions
   GROUP BY book_id;

# exercise 13