Bookstore SQL Assignment

bookstore.sql

#1
SELECT editions.isbn
FROM editions
INNER JOIN publishers
ON (editions.publisher_id = publishers.id)
WHERE (publishers.name = 'Random House');
#2
SELECT editions.isbn, books.title
FROM editions
INNER JOIN publishers
ON (editions.publisher_id = publishers.id)
INNER JOIN books
ON (editions.book_id = books.id)
WHERE (publishers.name = 'Random House');
#3
SELECT editions.isbn, books.title, stock.stock, stock.retail
FROM editions
INNER JOIN publishers
ON (editions.publisher_id = publishers.id)
INNER JOIN books
ON (editions.book_id = books.id)
INNER JOIN stock
ON (editions.isbn = stock.isbn)
WHERE (publishers.name = 'Random House');
#4
SELECT editions.isbn, books.title, stock.stock, stock.retail
FROM editions
INNER JOIN publishers
ON (editions.publisher_id = publishers.id)
INNER JOIN books
ON (editions.book_id = books.id)
INNER JOIN stock
ON (editions.isbn = stock.isbn)
WHERE (publishers.name = 'Random House' AND stock.stock > 0);
#5
SELECT isbn,
CASE WHEN type = 'h' THEN 'Hardcover'
ELSE 'Paperback'
END AS type
FROM editions;
#6
SELECT books.title, editions.publication
FROM books
LEFT OUTER JOIN editions
ON (books.id = editions.book_id);
#7
SELECT SUM(stock) from stock;
#8
SELECT AVG(cost) AS "Average Cost",
 AVG(retail) AS "Average Retail",
 AVG(retail - cost) AS "Average Profit" FROM stock;
#9
SELECT editions.book_id, stock.stock
FROM editions
INNER JOIN stock
ON (editions.isbn = stock.isbn)
ORDER BY stock.stock DESC;
#10
SELECT authors.id as "ID",
(authors.first_name || ' ' || authors.last_name) AS "Full Name",
COUNT(title) AS "Number of Books" 
FROM authors, books
WHERE books.author_id = authors.id
GROUP BY "ID", "Full Name";
#11
SELECT authors.id as "ID",
(authors.first_name || ' ' || authors.last_name) AS "Full Name",
COUNT(title) AS "Number of Books" 
FROM authors, books
WHERE books.author_id = authors.id
GROUP BY "ID", "Full Name"
ORDER BY "Number of Books" DESC;