bookstore.sql

SELECT isbn
FROM editions JOIN publishers ON (editions.publisher_id = publishers.id)
WHERE publishers.name='Random House'

SELECT isbn, book_id
FROM editions JOIN publishers ON (editions.publisher_id = publishers.id)
	JOIN books ON editions.book_id = books.id
WHERE publishers.name='Random House'

SELECT editions.isbn, editions.book_id, stock.stock, stock.retail
FROM editions JOIN publishers 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 > 0;

SELECT editions.isbn, editions.book_id, editions.type, stock.stock, stock.retail,
	CASE WHEN editions.type = 'h' THEN 'Hardcopy'
		 WHEN editions.type = 'p' THEN 'Paperback'
		 ELSE 'unknown'
		 END AS edition_type
FROM editions JOIN publishers 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 > 0;


SELECT books.title, editions.publication
FROM books JOIN editions ON books.id = editions.book_id

SELECT SUM(stock)
FROM stock

SELECT AVG(cost), AVG(retail), AVG(stock)
FROM stock

SELECT stock.stock, editions.book_id
FROM stock JOIN editions ON stock.isbn = editions.isbn
ORDER BY stock.stock DESC
LIMIT 1;


SELECT authors.id, COUNT(books.author_id), concat(authors.first_name, ' ', authors.last_name)
FROM authors JOIN books ON authors.id = books.author_id
GROUP BY authors.id;

SELECT authors.id, COUNT(books.author_id), concat(authors.first_name, ' ', authors.last_name)
FROM authors JOIN books ON authors.id = books.author_id
GROUP BY authors.id
ORDER BY COUNT(books.author_id) DESC;