queries.sql

# List all customers (name + email), ordered alphabetically (no extra information)
# Should yield 59 results
SELECT first_name, last_name, email
FROM customers
ORDER BY last_name asc;

# List tracks (Name + Composer) of the Classical playlist
# Should yield 75 results
SELECT tracks.name, tracks.composer
FROM playlist_tracks
JOIN tracks ON tracks.id = playlist_tracks.track_id
JOIN playlists ON playlists.id = playlist_tracks.playlist_id
WHERE playlists.name = "Classical"

# List the 10 artists mostly listed in all playlists
# First result should be Iron Maiden with 560 occurrences
SELECT artists.name, COUNT(*) AS c
FROM tracks
JOIN playlist_tracks ON tracks.id = playlist_tracks.track_id
JOIN albums ON albums.id = tracks.album_id
JOIN artists ON artists.id = albums.artist_id
GROUP BY artists.name
ORDER BY c desc
LIMIT 10


# List the tracks which have been purchased at least twice, ordered by number of purchases
# Should yield 320 results, Steve Harris - The Trooper having 5 purchases being the top bought track
SELECT tracks.name, count(*) AS c
FROM tracks
JOIN invoice_lines on invoice_lines.track_id = tracks.id
GROUP BY tracks.name
HAVING (c >= 2)
ORDER by c desc