Skip to content

Instantly share code, notes, and snippets.

@kaiguogit

kaiguogit/ex.sql

Last active August 1, 2016 20:29
Show Gist options
  • Save kaiguogit/9fbed071483b749b623370691b8e5681 to your computer and use it in GitHub Desktop.
Save kaiguogit/9fbed071483b749b623370691b8e5681 to your computer and use it in GitHub Desktop.
Download ZIP
BookStore SQL Exercises
Raw
ex.sql
--Exercise 1
--Fetch ISBN of all book editions published by the publisher "Random House". You should have 3 results.
SELECT e.isbn
FROM editions AS e
JOIN publishers AS p ON (e.publisher_id = p.id)
WHERE p.name = 'Random House';
--Exercise 2
--Instead of just their ISBN number, fetch their Book Title as well. You should still have the same 3 results but with more information. But instead of just one column, we should have 2 columns in the result set.
SELECT e.isbn, b.title
FROM editions AS e
JOIN publishers AS p ON (e.publisher_id = p.id)
JOIN books AS b ON (e.book_id = b.id)
WHERE p.name = 'Random House';
--Exercise 3
--Also include their stock information (available stock and retail price for each book edition). You should still have the same 3 results but with more information. But instead of just 2 columns, we should have 4 columns in the result set.
SELECT e.isbn, b.title, s.stock, s.retail
FROM editions AS e
JOIN publishers AS p ON (e.publisher_id = p.id)
JOIN books AS b ON (e.book_id = b.id)
JOIN stock AS s ON (e.isbn = s.isbn)
WHERE p.name = 'Random House';
--Exercise 4
--Note how one of the of books has 0 stock. Modify the query to only return books that are in stock.
SELECT e.isbn, b.title, s.stock, s.retail
FROM editions AS e
JOIN publishers AS p ON (e.publisher_id = p.id)
JOIN books AS b ON (e.book_id = b.id)
JOIN stock AS s ON (e.isbn = s.isbn)
WHERE p.name = 'Random House' AND s.stock > 0;
--Exercise 5
--Editions has a column called "type". Include the print type but instead of just displaying "h" or "p" (the values in the column) output the human readable types ("hardcover" and "paperback" accordingly)
SELECT e.isbn, b.title, s.stock, s.retail,
CASE
WHEN e.type = 'h' THEN 'Hard Cover'
WHEN e.type = 'p' THEN 'Paper Back'
END AS Type
FROM editions AS e
JOIN publishers AS p ON (e.publisher_id = p.id)
JOIN books AS b ON (e.book_id = b.id)
JOIN stock AS s ON (e.isbn = s.isbn)
WHERE p.name = 'Random House' AND s.stock > 0;
--Exercise 6
--List all book titles along with their publication dates (column on the editions dates) That's 2 columns: "title" and "publication"
-- Important Notes:
-- Certain books (such as "Learning Python") don't have any editions but we still want to see those books in the results. They just shouldn't have a publication date associated with them.
-- Certain other books (such as "Dune") have multiple editions and they will be repeated multiple times in the result set.
SELECT b.title, e.publication AS "Publication Date"
FROM books AS b
LEFT JOIN editions AS e ON (b.id = e.book_id);
--Excercise 7
--What's the total inventory of books in this library (i.e. how many total copies are in stock)?
SELECT SUM(stock) FROM stock;
--Exercise 8
--What is the overall average cost and retail price for all books for sale? Return three columns "Average cost", "Average Retail" and "Average Profit"
-- SELECT ROUND(SUM(cost) / COUNT(isbn), 2) AS "Average Cost",
-- ROUND(SUM(retail) / COUNT(isbn),2) AS "Average Retail",
-- ROUND((SUM(retail) - SUM(cost)) / COUNT(isbn), 2) AS "Average Profit"
-- FROM stock;
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;
--Exercise 9
--Which book ID has the most pieces in stock?
-- Hints:
-- Use an aggregate function combined with LIMIT and ORDER BY clauses.
-- No need to join with the books table since we just want the ID which is already in the editions table.
SELECT editions.book_id
FROM editions
JOIN stock ON editions.isbn = stock.isbn
ORDER BY stock.stock
LIMIT 1;
-- Exercise 10
--List author ID along with the full name and the number of books they have written. Output 3 columns: "ID", "Full name" and "Number of Books"
SELECT a.id AS "ID",
(a.first_name || ' ' || a.last_name) AS "Full Name",
COUNT(b.id) AS "Number of Books"
FROM books AS b
JOIN authors AS a ON (b.author_id = a.id)
GROUP BY "Full Name", "ID";
-- Exercise 11
--Order the result set above by number of books so that authors with most number of books appear atop the list (descending order).
SELECT a.id AS "ID",
(a.first_name || ' ' || a.last_name) AS "Full Name",
COUNT(b.id) AS "Number of Books"
FROM books AS b
JOIN authors AS a ON (b.author_id = a.id)
GROUP BY "ID"
ORDER BY "Number of Books" DESC;
-- Exercise 12
-- List books that have both paperback and hardcover editions. That means at least one edition of the book in both formats.
SELECT b.title
FROM
(SELECT book_id, type
FROM editions
GROUP BY book_id, type) AS unique_type
JOIN books AS b ON unique_type.book_id = b.id
GROUP BY unique_type.book_id, b.title
HAVING COUNT(unique_type.type) >= 2
ORDER BY unique_type.book_id DESC;
-- Exercise 13
-- For each publisher, list their average book sale price, number of editions published.
SELECT p.name, COUNT(e.isbn) AS "Edition published", ROUND(SUM(s.retail) / COUNT(e.isbn), 2) AS "Average Price"
FROM editions AS e
JOIN publishers AS p ON e.publisher_id = p.id
JOIN books AS b ON e.book_id = b.id
JOIN stock AS s ON e.isbn = s.isbn
GROUP by p.name
ORDER BY p.name;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment