Created
July 6, 2016 03:45
-
-
Save FioFiyo/2c2f79ebe4319fd7243266fb6f2da3a9 to your computer and use it in GitHub Desktop.
Using SQL for a Bookstore DB
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT title, editions.publication | |
FROM books LEFT JOIN editions ON (books.id = editions.book_id) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT COUNT(stock.stock) FROM stock |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment