-- Select all Rock artists

SELECT DISTINCT Artist.Name AS artist_name
FROM Artist
JOIN Album
    ON Artist.ArtistId = Album.ArtistId
JOIN Track
    ON Album.AlbumId = Track.AlbumId
JOIN Genre
    ON Track.GenreId = Genre.GenreId
WHERE Genre.Name = 'Rock'



-- All tracks that are part of at least 5 playlists, with additional track information and the number of playlists it is on

SELECT
    t.Name,
    t.Composer,
    t.Milliseconds,
    t.Bytes,
    t.UnitPrice,
    COUNT(*) AS playlist_count
FROM Track t
INNER JOIN PlaylistTrack pt ON t.TrackId = pt.TrackId
GROUP BY t.TrackId
HAVING playlist_count >= 5



-- Most popular playlist song

SELECT
    Track.Name AS track_name,
    COUNT(PlaylistTrack.TrackId) AS track_count
FROM Track
INNER JOIN PlaylistTrack
    ON Track.TrackId = PlaylistTrack.TrackId
GROUP BY Track.Name
ORDER BY track_count DESC
LIMIT 1



-- Top 5 most popular albums among the Danes

SELECT
    a.Title AS album_title,
    SUM(il.Quantity) AS album_sales
FROM InvoiceLine il
INNER JOIN Invoice i ON il.InvoiceId = i.InvoiceId
INNER JOIN Customer c ON i.CustomerId = c.CustomerId
INNER JOIN Track t ON il.TrackId = t.TrackId
INNER JOIN Album a ON t.AlbumId = a.AlbumId
WHERE c.Country = 'Denmark'
GROUP BY a.Title
ORDER BY album_sales DESC
LIMIT 5