Skip to content

Instantly share code, notes, and snippets.

@GrahamSM

GrahamSM/bookstore.sql

Last active May 9, 2016 20:00
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save GrahamSM/343c65d97a999780bed91a274ef41d81 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save GrahamSM/343c65d97a999780bed91a274ef41d81 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
bookstore.sql
SELECT title, name, stock.isbn, stock,
CASE WHEN type = 'h' THEN 'Hardcover edition'
WHEN type = 'p' THEN 'Paperback edition'
ELSE 'Unkown'
END AS cover
FROM editions JOIN publishers ON (editions.publisher_id = publishers.id)
JOIN books ON (editions.book_id = books.id)
JOIN stock ON (stock.isbn = editions.isbn)
WHERE (publishers.name = 'Random House') AND (stock.stock > 0);
SELECT title, publication
FROM books LEFT OUTER JOIN editions ON (books.id = editions.book_id);
SELECT sum(stock)
FROM stock;
SELECT avg(cost) AS avg_cost, avg(retail) AS avg_price, avg(retail-cost) AS avg_profit
FROM stock;
SELECT max(stock), editions.book_id
FROM stock JOIN editions ON (stock.isbn = editions.isbn)
GROUP BY editions.book_id
ORDER BY max(stock) desc
LIMIT 1;
SELECT authors.id AS "ID", (first_name || ' ' || last_name) AS "Full Name", count(books.author_id) AS "Number of Books"
FROM authors JOIN books ON (authors.id = books.author_id)
GROUP BY authors.id
ORDER BY count(books.author_id) desc;
SELECT title AS "Title", COUNT(type) AS "Number of Editions" FROM
editions INNER JOIN books
ON editions.book_id = books.id
GROUP BY title
HAVING COUNT(DISTINCT(type)) = 2;
SELECT p.name, AVG(s.retail), COUNT(e.edition) FROM publishers p JOIN editions e
ON (p.id = e.publisher_id) JOIN stock s
ON (e.isbn = s.isbn)
GROUP BY p.name
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment