Optional Project: Music database

Project weight: 5 points

This is an optional project. Your grade will not go down regardless if you complete it or not, but it may go up. More precisely, the cumulative grade from the project reports will be computed in two ways:

  • as the average of all eight projects (including this one)

  • as an average of the first seven projects (excluding this one).

Then the higher of these two averages will be used toward the final course grade.

Objectives

Download the following SQLite file, which contains a database of music recordings:

music_data.sqlite

Here is a pdf file showing the structure of this database: tables it consists of and names of columns in each table.

Use SQL queries to solve exercises given below.

Notes.

  1. For illustration, each exercise shows the first five rows of the results you should obtain. Your answers should not be limited to just 5 results - show either all results or the number specified in the statement of an exercise.

  2. Your solutions must be obtained with SQL only, using the %%sql magic. You must not use pandas etc.

  3. This is a programming assignment. Your work will be graded primarily based on the results of the code, and also on the code documentation and report organization. You don’t need to include a narrative.

Exercise 1

Create a table with names of all tracks where the composer is Miles Davis. Track names should be listed in the alphabetical order.

[14]:

show_results("ex1")
Name Composer
'Round Midnight Miles Davis
Black Satin Miles Davis
Bye Bye Blackbird Miles Davis
Compulsion Miles Davis
E.S.P. Miles Davis

Exercise 2

Create a table that lists titles and composers for all tracks where the name of the composer includes “Clapton”. Order the table in the alphabetical order of track titles.

[15]:

show_results("ex2")
Name Composer
After Midnight Clapton/J. J. Cale
Badge Clapton/Harrison
Bell Bottom Blues Clapton
Cocaine Cale/Clapton
Crossroads Clapton/Robert Johnson Arr: Eric Clapton

Exercise 3

Create a table with the album title, the album artist, and the number of tracks in the album. Display 10 rows of this table with most tracks, ordered in the descending order of the number of tracks.

[16]:

show_results("ex3")
Title Artist NumTracks
Greatest Hits Lenny Kravitz 57
Minha Historia Chico Buarque 34
Unplugged Eric Clapton 30
Lost, Season 3 Lost 26
Lost, Season 1 Lost 25

Exercise 4

Create a table listing the album title, the name of the artist and the total length of the album in minutes for each album whose total length is more than 90 minutes. Order the table according to album lengths in the descending order.

Note. The tracks table lists lengths of tracks in milliseconds. One second is equal to 1000 milliseconds.

[17]:

show_results("ex4")
Title Artist Minutes
Lost, Season 3 Lost 1177
Battlestar Galactica (Classic), Season 1 Battlestar Galactica (Classic) 1170
Lost, Season 1 Lost 1080
Lost, Season 2 Lost 1054
Heroes, Season 1 Heroes 996

Exercise 5

Create a table listing AlbumId and the title of each album that has at least one track in the “Jazz” genre. Order album titles alphabetically.

[18]:

show_results("ex5")
AlbumId AlbumTitle
93 Blue Moods
38 Heart of the Night
157 Miles Ahead
204 Morning Dance
68 Outbreak

Exercise 6

Create a table which lists AlbumId, the album title and the price of 10 most expensive albums. The price of an album is the sum of prices of all its tracks. Order the list from the highest album price to the lowest.

[19]:

show_results("ex6")
AlbumId AlbumTitle Price
141 Greatest Hits 56.430000
229 Lost, Season 3 51.740000
251 The Office, Season 3 49.750000
230 Lost, Season 1 49.750000
253 Battlestar Galactica (Classic), Season 1 47.760000

Exercise 7

Create a table that lists AlbumId and the title of each album which consist of exactly 10 tracks. Order the table in the alphabetical order of album titles.

[20]:

show_results("ex7")
AlbumId AlbumTitle
127 BBC Sessions [Disc 2] [Live]
17 Black Sabbath Vol. 4 (Remaster)
97 Brave New World
191 Cesta Básica
116 Emergency On Planet Earth

Exercise 8

Create a table with two columns: one, called NumTracks listing the number of tracks in an album, and the second called NumAlbums which shows how many albums have such number of tracks. Order the table in the ascending order of NumTracks.

[21]:

show_results("ex8")
NumTracks NumAlbums
1 82
2 8
3 3
4 2
5 2

Exercise 9

Create a table which has one row for each album, listing the longest track of the album. The columns should be AlbumId, album title, TrackId, track title, and track time in milliseconds. Order the table according to track times in the descending order and display the first 10 rows.

[22]:

show_results("ex9")
AlbumId AlbumTitle TrackId TrackName Milliseconds
227 Battlestar Galactica, Season 3 2820 Occupation / Precipice 5286953
229 Lost, Season 3 3224 Through a Looking Glass 5088838
253 Battlestar Galactica (Classic), Season 1 3244 Greetings from Earth, Pt. 1 2960293
231 Lost, Season 2 2910 Dave 2825166
228 Heroes, Season 1 3223 How to Stop an Exploding Man 2687103

Exercise 10

Every track in the tracks table corresponds to one genre, but an album may consist of tracks of various genres.

Create a table showing AlbumId, the album title, and the number of distinct track genres for all albums which have more than one genre. Order the list in the alphabetical order of album titles.

[23]:

show_results("ex10")
AlbumId Title NumGenres
227 Battlestar Galactica, Season 3 3
141 Greatest Hits 3
228 Heroes, Season 1 2
261 LOST, Season 4 2
102 Live After Death 2