forked from data-tonye/Query-a-Digital-Music-Store-Database
-
Notifications
You must be signed in to change notification settings - Fork 0
/
SQL Queries.txt
50 lines (43 loc) · 1.27 KB
/
SQL 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
46
47
48
49
50
QUESTION 1 What Media Type did customers prefer buy?
SELECT mt.name, COUNT(DISTINCT c.customerid) Customers
FROM customer c
JOIN invoice i
ON c.customerid = i.customerid
JOIN invoiceline iv
ON i.invoiceid = iv.invoiceid
JOIN Track t
ON iv.trackid = t.trackid
JOIN MediaType mt
ON mt.mediatypeid = t.mediatypeid
GROUP BY mt.name
ORDER BY c.customerid DESC;
QUESTION 2 Number of Tracks Sold by Genre?
SELECT g.name, COUNT(t.trackid) AS No_Tracks_Sold
FROM genre g
JOIN Track t
ON g.genreid = t.genreid
JOIN InvoiceLine iv
ON iv.trackid = t.trackid
GROUP BY g.name
ORDER BY No_Tracks Sold DESC;
QUESTION 3 Total Spent Per Country Compared with Number of Tracks Sold?
SELECT i.billingcountry AS Country, SUM(iv.unitprice * iv.quantity) AS Sales, COUNT(t.trackid) AS No_Tracks
FROM Invoice i
JOIN InvoiceLine iv
ON i.invoiceid = iv.invoiceid
JOIN track t
ON t.trackid = iv.trackid
GROUP BY i.billingcountry
ORDER BY sales DESC;
QUESTION 4 Top 10 Albums based on sales?
SELECT a.albumid, a.title, ar.name AS Artist_Name, SUM(iv.unitprice * iv.quantity) AS Sales
FROM Artist ar
JOIN Album a
ON ar.artistid = a.artistid
JOIN Track t
ON a.albumid = t.albumid
JOIN InvoiceLine iv
ON iv.trackid = t.trackid
GROUP BY a.albumid
ORDER BY Sales DESC
LIMIT 10;