Using SQL for a Bookstore DB

SQL exercise 10 - 11

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

SQL exercise 6

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

SQL exercise 7

SELECT COUNT(stock.stock) FROM stock

SQL exercise 8

SELECT ROUND(SUM(stock.retail)/COUNT(stock.stock), 0) AS Average_retail, 
	ROUND((SUM(stock.cost)/COUNT(stock.stock)),0) AS Average_cost,
	ROUND((SUM(stock.retail)/COUNT(stock.stock)),1) AS Average_Profit_per_Book
	
FROM stock 

SQL exercise 9

SELECT books.title, COUNT(stock.stock) FROM books 
	JOIN editions ON (books.id = editions.book_id) 
	LEFT JOIN stock ON (stock.isbn = editions.isbn)
	
	GROUP BY books.title
	ORDER BY COUNT(stock.stock) DESC

SQL exercises 1-5

SELECT editions.isbn, books.title,editions.publication, stock.stock , stock.retail,
 CASE WHEN type ='h' THEN 'hardcover'
 WHEN type = 'p' THEN 'paperback'
 END
FROM editions JOIN publishers ON (editions.publisher_id = publishers.id)
JOIN books ON (books.id = editions.book_id) JOIN stock ON (editions.isbn = stock.isbn)
WHERE publishers.name = 'Random House' AND stock.stock > 0