-
Notifications
You must be signed in to change notification settings - Fork 0
/
Queries
54 lines (48 loc) · 1.13 KB
/
Queries
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
46
47
48
49
50
51
1)
SELECT g.Name Genre, COUNT(g.Name) NumTracks
FROM InvoiceLine il
JOIN Track t
ON il.TrackId = t.TrackId
JOIN Genre g
ON t.GenreId = g.GenreId
GROUP BY Genre
ORDER BY NumTracks DESC;
2)
SELECT i.BillingCountry Country, COUNT(g.Name) NumTracks
FROM Invoice i
JOIN InvoiceLine il
ON i.InvoiceId = il.InvoiceId
JOIN Track t
ON il.TrackId = t.TrackId
JOIN Genre g
ON t.GenreId = g.GenreId
WHERE g.Name = 'Rock'
GROUP BY Country
ORDER BY NumTracks DESC
3)
SELECT ar.Name Artist, a.Title AlbumTitle, SUM(il.Quantity*il.UnitPrice) Sales
FROM Artist ar
JOIN Album a
ON ar.ArtistID = a.ArtistId
JOIN Track t
ON a.AlbumiD = t.AlbumId
JOIN InvoiceLine il
ON t.TrackId = il.TrackId
JOIN Invoice i
ON il.InvoiceId = i.InvoiceId
WHERE i.InvoiceDate BETWEEN '2010-01-01' AND '2012-01-01'
GROUP BY AlbumTitle
ORDER BY Sales DESC
LIMIT 5
4)
SELECT mt.Name MediaTypeName, SUM(il.Quantity*il.UnitPrice) Sales
FROM MediaType mt
JOIN Track t
ON mt.MediaTypeId = t.MediaTypeId
JOIN InvoiceLine il
ON t.TrackId = il.TrackId
JOIN Invoice i
ON il.InvoiceId = i.InvoiceId
WHERE i.InvoiceDate BETWEEN '2010-01-01' AND '2013-01-01'
GROUP BY MediaTypeName
ORDER BY Sales DESC