livecode.sql

Le Wagon - Schema Design & SQL - Livecode

-- forgot to save the first one. sorry about that

SELECT t.name, t.composer
FROM tracks t
JOIN playlist_tracks pt ON pt.track_id = t.id
JOIN playlists p ON pt.playlist_id = p.id
WHERE p.name = 'Classical'
LIMIT 10;

SELECT a.name, COUNT(*) AS count
FROM artists a 
JOIN albums ON albums.artist_id = a.id
JOIN tracks t ON t.album_id = albums.id
JOIN playlist_tracks pt ON pt.track_id = t.id
GROUP BY a.name
ORDER BY count DESC
LIMIT 10;

SELECT t.name, artists.name, COUNT(*) AS purchases
FROM tracks t
JOIN albums ON t.album_id = albums.id
JOIN artists ON albums.artist_id = artists.id
JOIN invoice_lines il ON il.track_id = t.id
GROUP BY t.id
HAVING purchases >= 2
ORDER BY purchases DESC
LIMIT 10;