Created
October 11, 2019 00:44
-
-
Save ciardullo-apps/e04be8663e2327d309528ce228fc6c53 to your computer and use it in GitHub Desktop.
chinook-1.sql
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
-- ------------------------ | |
-- Demonstrates SQL statements in MySQL, such as | |
-- Subqueries | |
-- Aggregate functions | |
-- GROUP BY and HAVING | |
-- JOINS | |
-- | |
-- PM me for a link to see additional Chinook queries that demonstrate the following: | |
-- Advanced Subqueries | |
-- Advanced JOIN and GROUP BY | |
-- OUTER JOIN | |
-- Named subqueries | |
-- Correlated subqueries | |
-- CASE Statements | |
-- Window Functions | |
-- ROLLUP extension for GROUP BY | |
-- ------------------------ | |
-- Determine min, max, average track count for all albums | |
SELECT MIN(total_tracks), MAX(total_tracks), AVG(total_tracks) | |
FROM (SELECT AlbumId, COUNT(*) AS total_tracks FROM Track | |
GROUP BY AlbumId) a; | |
-- Determine min, max, average track count per invoice | |
SELECT MIN(total_tracks_per_invoice), MAX(total_tracks_per_invoice), AVG(total_tracks_per_invoice) | |
FROM (SELECT InvoiceId, COUNT(*) AS total_tracks_per_invoice FROM InvoiceLine | |
GROUP BY InvoiceId) a; | |
-- Get the count of the most tracks on any album | |
SELECT MAX(trackCount) | |
FROM ( | |
SELECT AlbumId, COUNT(*) AS trackCount | |
FROM Track t | |
GROUP BY AlbumId) trackCounts; | |
-- Get album id with the most tracks | |
SELECT AlbumId | |
FROM Track | |
GROUP BY AlbumId | |
HAVING COUNT(*) = (SELECT MAX(trackCount) | |
FROM ( | |
SELECT AlbumId, COUNT(*) AS trackCount | |
FROM Track t | |
GROUP BY AlbumId) trackCounts); | |
-- Get the album details of the album with the most tracks | |
SELECT * | |
FROM Album l JOIN Artist r ON l.ArtistId = r.ArtistId | |
WHERE AlbumId = | |
(SELECT AlbumId | |
FROM Track | |
GROUP BY AlbumId | |
HAVING COUNT(*) = (SELECT MAX(trackCount) | |
FROM ( | |
SELECT AlbumId, COUNT(*) AS trackCount | |
FROM Track t | |
GROUP BY AlbumId) trackCounts)); | |
-- Get the artist with the most albums | |
SELECT * FROM Artist | |
WHERE ArtistId = ( | |
SELECT ArtistId | |
FROM Album | |
GROUP BY ArtistId | |
HAVING COUNT(*) = | |
(SELECT MAX(albumCounts) | |
FROM | |
(SELECT COUNT(*) AS albumCounts | |
FROM Album | |
GROUP BY ArtistId) c)); | |
-- Get each artist and the count of unique tracks sold | |
SELECT r.ArtistId, COUNT(DISTINCT il.trackid) | |
FROM InvoiceLine il | |
JOIN Track t ON il.TrackId = t.Trackid | |
JOIN Album l ON t.AlbumId = l.AlbumId | |
JOIN Artist r ON l.ArtistId = r.ArtistId | |
GROUP BY r.ArtistId; | |
-- Get the artist having sold the most tracks | |
SELECT * FROM Artist WHERE ArtistId = | |
(SELECT r.ArtistId | |
FROM InvoiceLine il | |
JOIN Track t ON il.TrackId = t.Trackid | |
JOIN Album l ON t.AlbumId = l.AlbumId | |
JOIN Artist r ON l.ArtistId = r.ArtistId | |
GROUP BY r.ArtistId | |
HAVING COUNT(*) = | |
(SELECT MAX(trackCounts) | |
FROM | |
(SELECT r.ArtistId, COUNT(*) AS trackCounts | |
FROM InvoiceLine il | |
JOIN Track t ON il.TrackId = t.Trackid | |
JOIN Album l ON t.AlbumId = l.AlbumId | |
JOIN Artist r ON l.ArtistId = r.ArtistId | |
GROUP BY r.ArtistId) a)); | |
-- Get count of track sales for an individual artist | |
SELECT COUNT(il.TrackId) | |
FROM InvoiceLine il | |
JOIN Track t ON il.TrackId = t.TrackId | |
JOIN Album l ON t.AlbumId = l.AlbumId | |
JOIN Artist r ON l.ArtistId = r.Artistid | |
WHERE r.ArtistId = 90; | |
-- Get most sold track not possible, no track sold more than twice | |
SELECT Trackid, COUNT(*) | |
FROM InvoiceLine il | |
GROUP BY TrackId | |
HAVING COUNT(*) > 2; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment