queries.sql

# List all customers (name + email), ordered alphabetically (no extra information)
SELECT first_name, last_name, email
FROM customers
ORDER BY last_name ASC;
# List tracks (Name + Composer) of the Classical playlist
SELECT t.name, t.composer
FROM tracks t
JOIN playlist_tracks pt ON t.id = pt.track_id
JOIN playlists p ON p.id = pt.playlist_id
WHERE p.name = 'Classical';
# List the 10 artists mostly listed in all playlists
SELECT ar.id, ar.name, COUNT(pt.track_id) AS c
FROM artists ar
JOIN albums al ON ar.id = al.artist_id
JOIN tracks t ON al.id = t.album_id
JOIN playlist_tracks pt ON t.id = pt.track_id
GROUP BY ar.id
ORDER BY c DESC
LIMIT 10;
# List the tracks which have been purchased at least twice, ordered by number of purchases
SELECT t.id, t.name, COUNT(il.id) AS c
FROM tracks t
JOIN invoice_lines il ON t.id = il.track_id
GROUP BY t.id
HAVING c >= 2
ORDER BY c DESC;