-
Notifications
You must be signed in to change notification settings - Fork 0
/
queries.txt
45 lines (42 loc) · 1.22 KB
/
queries.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
/*Query 1 - what are the top 5 bestsellers genres*/
SELECT g.Name AS Genre,
count(*) AS Number_of_sales
FROM Genre g
JOIN Track t ON g.GenreId = t.GenreId
JOIN InvoiceLine i ON t.TrackId = i.TrackId
GROUP BY g.Name
ORDER BY Number_of_sales DESC
LIMIT 5;
/*Query 2 - what are the top 5 genre in USA*/
SELECT g.Name,
count(*) AS Number
FROM Invoice i
JOIN InvoiceLine l ON i.InvoiceId = l.InvoiceId
JOIN Track t ON l.TrackId = t.TrackId
JOIN Genre g ON t.GenreId = g.GenreId
WHERE i.BillingCountry = 'USA'
GROUP BY g.Name
ORDER BY Number DESC
LIMIT 5;
/*Query 3 - what are the top 5 rock genre buying countries*/
SELECT i.BillingCountry AS Country,
COUNT(*) AS Rock_Invoice
FROM Invoice i
JOIN InvoiceLine l ON i.InvoiceId = l.InvoiceId
JOIN Track t ON l.TrackId = t.TrackId
JOIN Genre g ON t.GenreId = g.GenreId
WHERE g.Name = 'Rock'
GROUP BY i.BillingCountry
ORDER BY Rock_Invoice DESC
LIMIT 5;
/*Query 4 - who are the first 5 artists with the most rock tracks*/
SELECT s.Name AS Artist_Name,
COUNT(*) AS Rock_Tracks
FROM Artist s
JOIN Album a ON s.ArtistId = a.ArtistId
JOIN Track t ON a.AlbumId = t.AlbumId
JOIN Genre g ON t.GenreId = g.GenreId
WHERE g.Name = "Rock"
GROUP BY s.Name
ORDER BY Rock_Tracks DESC
LIMIT 5;