ex_1.sql

SELECT p.isbn FROM editions AS e
INNER JOIN publishers as p ON e.publisher_id=p.id
WHERE p.name = 'Random House'

ex_10.sql

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

ex_11.sql

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

ex_12.sql

SELECT b.title FROM editions AS e
FULL OUTER JOIN books AS b ON e.book_id = b.id
GROUP BY b.title, e.book_id
HAVING COUNT(e.book_id) = 2 And e.book_id IN 
	(SELECT e1.book_id 
	FROM editions e1 
	JOIN editions e2 ON e1.book_id = e2.book_id
	WHERE e1.type <> e2.type)

ex_13.sql

SELECT p.name AS "Publisher", 
ROUND(AVG(retail),2) AS "Average Book Sale Price", 
COUNT(edition) AS "Editions Published"
FROM publishers AS p
JOIN editions AS e ON p.id = e.publisher_id
JOIN stock AS s ON s.isbn = e.isbn
GROUP BY p.name

ex_2.sql

SELECT e.isbn, b.title FROM editions AS e
INNER JOIN publishers as p ON e.publisher_id=p.id
INNER JOIN books as b ON b.id=e.book_id 
WHERE p.name = 'Random House'

ex_3.sql

SELECT e.isbn, b.title, s.stock, s.retail FROM editions AS e
INNER JOIN publishers as p ON e.publisher_id=p.id
INNER JOIN books as b ON b.id=e.book_id
INNER JOIN stock as s ON s.isbn=e.isbn
WHERE p.name = 'Random House'

ex_4.sql

SELECT e.isbn, b.title, s.stock, s.retail FROM editions AS e
INNER JOIN publishers as p ON e.publisher_id=p.id
INNER JOIN books as b ON b.id=e.book_id
INNER JOIN stock as s ON s.isbn=e.isbn
WHERE p.name = 'Random House' AND s.stock > 0

ex_5.sql

SELECT e.isbn, b.title, s.stock, s.retail, CASE WHEN e.type = 'h' THEN 'hardcover'
     WHEN e.type = 'p' THEN 'paperback'
     END AS "type"
FROM editions AS e
INNER JOIN publishers as p ON e.publisher_id=p.id
INNER JOIN books as b ON b.id=e.book_id
INNER JOIN stock as s ON s.isbn=e.isbn
WHERE p.name = 'Random House' AND s.stock > 0

ex_6.sql

SELECT b.title, e.publication
FROM editions AS e
RIGHT JOIN books as b ON b.id=e.book_id

ex_7.sql

SELECT SUM(stock)
FROM stock

ex_8.sql

SELECT ROUND(AVG(cost),2) as "Average Cost", ROUND(AVG(retail),2) as "Average Retail", ROUND(AVG(retail) - AVG(cost),2) as "Average Profit"
FROM stock

ex_9.sql

SELECT book_id FROM editions
WHERE isbn = (SELECT isbn FROM stock ORDER BY stock DESC LIMIT 1)