Skip to content

Instantly share code, notes, and snippets.

@McTano
Created June 7, 2016 16:40
Show Gist options
  • Save McTano/7ced6fa6a0c2596289db91d5265e0cb2 to your computer and use it in GitHub Desktop.
Save McTano/7ced6fa6a0c2596289db91d5265e0cb2 to your computer and use it in GitHub Desktop.
--Exercises:
--1
SELECT editions.isbn FROM publishers JOIN editions
ON publishers.id = editions.publisher_id
WHERE publishers.name = 'Random House';
--2
SELECT books.title, editions.isbn
FROM books JOIN editions ON books.id = editions.book_id
WHERE editions.publisher_id = 59;
--3
SELECT books.title, editions.isbn, stock.retail, stock.stock
FROM books JOIN editions ON books.id = editions.book_id
JOIN stock ON editions.isbn = stock.isbn
WHERE editions.publisher_id = 59;
--4
SELECT books.title, editions.isbn, stock.retail, stock.stock
FROM books JOIN editions ON books.id = editions.book_id
JOIN stock ON editions.isbn = stock.isbn
WHERE editions.publisher_id = 59
AND stock.stock <> 0;
--5
SELECT books.title, editions.isbn, stock.retail, stock.stock,
CASE
WHEN editions.type = 'h' THEN 'hardcover'
WHEN editions.type = 'p' THEN 'paperback'
END AS binding
FROM books JOIN editions ON books.id = editions.book_id
JOIN stock ON editions.isbn = stock.isbn
WHERE editions.publisher_id = 59 AND stock.stock <> 0;
--6
SELECT books.title, editions.publication
FROM books LEFT JOIN editions ON books.id = editions.book_id;
--7
SELECT SUM(stock) FROM stock; --512
--8
SELECT AVG(cost) as "Average Cost",
AVG(retail) as "Average Retail",
AVG(cost - retail) as "Average Profit"
FROM stock;
--9
SELECT editions.book_id, SUM(stock.stock) as total_stock
FROM editions JOIN stock ON editions.isbn = stock.isbn
GROUP BY editions.book_id
ORDER BY total_stock DESC
LIMIT 1; -- id: 4513, stock: 166
--10
SELECT authors.id as "ID",
(authors.first_name || ' ' || authors.last_name) as "Full Name",
COUNT(books.id) as "Number of Books"
FROM authors JOIN books ON authors.id = books.author_id
GROUP BY authors.id
ORDER BY COUNT(books);
--11
SELECT authors.id as "ID",
(authors.first_name || ' ' || authors.last_name) as "Full Name",
COUNT(books.id) as "Number of Books"
FROM authors JOIN books ON authors.id = books.author_id
GROUP BY authors.id
ORDER BY COUNT(books) DESC;
--12
SELECT books.title FROM books
JOIN editions ON books.id = editions.book_id
WHERE editions.type = 'h'
INTERSECT (
SELECT books.title FROM books
JOIN editions ON books.id = editions.book_id
WHERE editions.type = 'p'
);
-- 13
SELECT publishers.name as publisher,
AVG(stock.retail) as avg_retail,
COUNT(editions.isbn) as editions_published
FROM publishers
JOIN editions ON publishers.id = editions.publisher_id
JOIN stock ON editions.isbn = stock.isbn
GROUP BY publishers.id;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment