chinook-1.sql

chinook-1.sql

-- ------------------------
-- 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;