w3d1

bookstore.sql

#1
select e.isbn from editions as e 
  join publishers as p on (p.id = e.publisher_id) 
  where p.name = 'Random House';

#2
select e.isbn, b.title from editions as e 
  join publishers as p on (p.id = e.publisher_id) 
  join books as b on (b.id = e.book_id)
  where p.name = 'Random House';

#3
select e.isbn, b.title, s.stock, s.retail from editions as e 
  join publishers as p on (p.id = e.publisher_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';

#4
select e.isbn, b.title, s.stock, s.retail from editions as e 
  join publishers as p on (p.id = e.publisher_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 s.stock <>0;

#5
select e.isbn, b.title, s.stock, s.retail, e.type, 
  case when e.type = 'p' then 'Paperback' 
    else 'Hardcover'
  end
  from editions as e 
  join publishers as p on (p.id = e.publisher_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 s.stock <>0;

#realised here using all lowercase is really difficult

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

#7
SELECT SUM (s.stock) FROM stock AS s;

#8
SELECT ROUND(AVG(s.cost), 2) 
  AS 'Average Cost', ROUND(AVG(s.retail),2) 
  AS 'Average Retail', ROUND(AVG(s.retail - s.cost), 2) 
  AS 'Average Profit'
FROM stock AS s;

#9
SELECT e.book_id FROM editions AS e 
  ORDER BY e.book_id DESC LIMIT 1;

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

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