Advertisement
Guest User

Untitled

a guest
Sep 23rd, 2022
37
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 1.31 KB | None | 0 0
  1. -- Select all Rock artists
  2.  
  3. SELECT DISTINCT Artist.Name AS artist_name
  4. FROM Artist
  5. JOIN Album
  6.     ON Artist.ArtistId = Album.ArtistId
  7. JOIN Track
  8.     ON Album.AlbumId = Track.AlbumId
  9. JOIN Genre
  10.     ON Track.GenreId = Genre.GenreId
  11. WHERE Genre.Name = 'Rock'
  12.  
  13.  
  14.  
  15. -- All tracks that are part of at least 5 playlists, with additional track information and the number of playlists it is on
  16.  
  17. SELECT
  18.     t.Name,
  19.     t.Composer,
  20.     t.Milliseconds,
  21.     t.Bytes,
  22.     t.UnitPrice,
  23.     COUNT(*) AS playlist_count
  24. FROM Track t
  25. INNER JOIN PlaylistTrack pt ON t.TrackId = pt.TrackId
  26. GROUP BY t.TrackId
  27. HAVING playlist_count >= 5
  28.  
  29.  
  30.  
  31. -- Most popular playlist song
  32.  
  33. SELECT
  34.     Track.Name AS track_name,
  35.     COUNT(PlaylistTrack.TrackId) AS track_count
  36. FROM Track
  37. INNER JOIN PlaylistTrack
  38.     ON Track.TrackId = PlaylistTrack.TrackId
  39. GROUP BY Track.Name
  40. ORDER BY track_count DESC
  41. LIMIT 1
  42.  
  43.  
  44.  
  45. -- Top 5 most popular albums among the Danes
  46.  
  47. SELECT
  48.     a.Title AS album_title,
  49.     SUM(il.Quantity) AS album_sales
  50. FROM InvoiceLine il
  51. INNER JOIN Invoice i ON il.InvoiceId = i.InvoiceId
  52. INNER JOIN Customer c ON i.CustomerId = c.CustomerId
  53. INNER JOIN Track t ON il.TrackId = t.TrackId
  54. INNER JOIN Album a ON t.AlbumId = a.AlbumId
  55. WHERE c.Country = 'Denmark'
  56. GROUP BY a.Title
  57. ORDER BY album_sales DESC
  58. LIMIT 5
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement