Skip to content

Instantly share code, notes, and snippets.

@ruxandrafed

ruxandrafed/ex_1.sql

Created September 14, 2015 22:24
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ruxandrafed/edc59e17348118a37f5c to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ruxandrafed/edc59e17348118a37f5c to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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'
Raw
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
Raw
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
Raw
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)
Raw
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
Raw
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'
Raw
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'
Raw
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
Raw
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
Raw
ex_6.sql
SELECT b.title, e.publication
FROM editions AS e
RIGHT JOIN books as b ON b.id=e.book_id
Raw
ex_7.sql
SELECT SUM(stock)
FROM stock
Raw
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
Raw
ex_9.sql
SELECT book_id FROM editions
WHERE isbn = (SELECT isbn FROM stock ORDER BY stock DESC LIMIT 1)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment