SQL Exercise

1) Random House ISBN

SELECT isbn FROM editions 
where publisher_id = 59

10) Book Count NOT DONE

SELECT authors.id, (authors.first_name || ' ' || authors.last_name) AS "Name", COUNT(authors.id) FROM authors 
join books on books.author_id = authors.id
GROUP BY authors.id

2) Title and ISBN

SELECT books.title, editions.isbn from books join editions
on books.id = editions.book_id
where editions.publisher_id = 59;

3) Title, ISBN, Retail, Stock

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

4) Stock > 0

SELECT books.title, editions.isbn, stock.retail, stock.stock from books 
join editions on books.id = editions.book_id 
join stock on stock.isbn = editions.isbn
where editions.publisher_id = 59 and stock.stock > 0;

5) Hardcover paperback

SELECT books.title, editions.isbn, stock.retail, stock.stock, 
CASE WHEN editions.type = 'h' then 'hardcover' 
WHEN editions.type = 'p' then 'paperback'
END AS type
FROM books 
JOIN editions on books.id = editions.book_id 
JOIN stock on stock.isbn = editions.isbn
WHERE editions.publisher_id = 59 and stock.stock > 0;

6) Title Publication

SELECT books.title, editions.publication 
FROM books 
join editions on books.id = editions.book_id

7) Stock Count

SELECT sum(stock.stock) from stock

8) Average Profit

SELECT round(avg(stock.retail),2) AS "Average Retail", round(avg(stock.cost),2) AS "Average Cost", round(avg(stock.retail),2) - round(avg(stock.cost),2) as "Average Profit" 
from stock 

9) Max Stock NOT DONE

SELECT editions.book_id, stock.stock FROM stock  
join editions on stock.isbn = editions.isbn
ORDER BY stock.stock DESC
LIMIT 1