-
Notifications
You must be signed in to change notification settings - Fork 0
/
Chinook _Project.sqbpro
110 lines (93 loc) · 4.61 KB
/
Chinook _Project.sqbpro
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
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
<?xml version="1.0" encoding="UTF-8"?><sqlb_project><db path="C:/Users/Yousef Amer/Desktop/my_SQL/database/chinook.db" readonly="0" foreign_keys="1" case_sensitive_like="0" temp_store="0" wal_autocheckpoint="1000" synchronous="2"/><attached/><window><main_tabs open="structure browser pragmas query" current="3"/></window><tab_structure><column_width id="0" width="300"/><column_width id="1" width="0"/><column_width id="2" width="125"/><column_width id="3" width="5283"/><column_width id="4" width="0"/><expanded_item id="0" parent="1"/><expanded_item id="1" parent="1"/><expanded_item id="2" parent="1"/><expanded_item id="3" parent="1"/></tab_structure><tab_browse><current_table name="4,5:mainAlbum"/><default_encoding codec=""/><browse_table_settings><table schema="main" name="Album" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_"><sort/><column_widths><column index="1" value="84"/><column index="2" value="300"/><column index="3" value="76"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table></browse_table_settings></tab_browse><tab_sql><sql name="SQL 1.sql">/*
We decieded to hold a concert.
but before we do, we must know Which music genre should we choose, Where to hold our concert and Which band should perform
*/
/*First SQL: Which music genre should we choose*/
/*
Questions answered with this SQL are the following:
Q1: Which music genre is most popular among our customers?
Q2: How many customers out of all our 59 customers listen to this genre with percentage?
*/
SELECT t1.Name, COUNT(*) AS "Number of listeners", COUNT(*)*100/59 AS "Percentage %"
FROM(
SELECT Customer.CustomerId, Genre.Name, COUNT(*)
FROM Customer
JOIN Invoice
ON Invoice.CustomerId = Customer.CustomerId
JOIN InvoiceLine
ON InvoiceLine.InvoiceId = Invoice.InvoiceId
JOIN Track
ON Track.TrackId = InvoiceLine.TrackId
JOIN Genre
ON Genre.GenreId = Track.GenreId
GROUP BY Customer.CustomerId, Genre.Name
ORDER BY Customer.CustomerId) as t1
GROUP BY 1
ORDER BY 2 DESC
/*
Based on this table we found out that 100% of our customers listen to Rock music, so we decided to told a Rock concert
*/</sql><sql name="SQL_2.sql">/*
Based on the last question (Q1), we decided to hold a rock concert, but In which country should we hold our concert?
Questions answered with this SQL are the following:
Q1: Top countries that consume rock music so we can hold a rock concert there?
*/
SELECT Customer.Country, SUM(InvoiceLine.UnitPrice) AS "Total Spend"
FROM Customer
JOIN Invoice
ON Invoice.CustomerId = Customer.CustomerId
JOIN InvoiceLine
ON InvoiceLine.InvoiceId = Invoice.InvoiceId
JOIN Track
ON Track.TrackId = InvoiceLine.TrackId
JOIN Genre
ON Genre.GenreId = Track.GenreId
WHERE Genre.Name = "Rock"
GROUP BY Customer.Country
ORDER BY 2 DESC
/*
Based on this table, we found out that customers from USA spend the most on rock music, so we decided to hold a rock concert there
*/</sql><sql name="SQL3.sql">/*
From the prvious two tabels we decieded to hold our rock concert in USA. but we haven't set a date for the concert yet
In this SQL we will determine the month when we should hold our rock concert
We want to choose the month with the lowest profit based on previous years (to increase the profit in that month).
*/
/*
This SQL will provide a tabel of total profits for each month.
*/
SELECT t1.month, sum(t1.UnitPrice) AS "Total Profit"
FROM(
SELECT *, strftime("%m",Invoice.InvoiceDate) month
FROM Invoice
JOIN InvoiceLine
ON InvoiceLine.InvoiceId = Invoice.InvoiceId
JOIN Track
ON Track.TrackId = InvoiceLine.TrackId
JOIN Genre
ON Genre.GenreId = track.GenreId
WHERE Genre.Name = "Rock") AS t1
GROUP BY 1
ORDER BY 1
/*
Based on this table, we find out that March is the lowest gross profit month, so we will hold our profitable concert in March to increase the profit
*/
</sql><sql name="SQL4.sql">/*
Finally, we would like to choose a rock band that will perform at our rock concert.
Of course, we'll pick the band with the highest grossing last year.
*/
SELECT Artist.Name as "Artist/Band Name", SUM(InvoiceLine.UnitPrice) AS "2013 Total Gross "
FROM Invoice
JOIN InvoiceLine
ON InvoiceLine.InvoiceId = Invoice.InvoiceId
JOIN Track
ON track.TrackId = InvoiceLine.TrackId
JOIN Album
ON Album.AlbumId = Track.AlbumId
JOIN Artist
ON Artist.ArtistId = Album.ArtistId
JOIN genre
ON Genre.GenreId = Track.GenreId
WHERE genre.Name = "Rock"
AND Invoice.InvoiceDate > "2012-12-31"
GROUP BY 1
ORDER BY 2 DESC
</sql><current_tab id="3"/></tab_sql></sqlb_project>