Lab 7: Songs

Write SQL queries to answer questions about a database of songs.

Accepting this Lab

1. Accept this lab via GitHub Classroom.
2. After about a minute, refresh the page and ensure you see “You’re ready to go!”.

Getting Started

Log into code.cs50.io, click on your terminal window, and execute cd by itself. You should find that your terminal window’s prompt resembles the below:

$

If you have not done so since beginning to work with SQL, next execute

update50

and click Rebuild now when prompted. After your codespace reloads, use

get50 songs

in order to download a directory called songs into your codespace.

Then execute

cd songs

in order to change into that directory. Your prompt should now resemble the below:

songs/ $

Execute ls by itself, and you should see 8 .sql files, as well as songs.db.

If you run into any trouble, follow these same steps again and see if you can determine where you went wrong!

Understanding

Provided to you is a file called songs.db, a SQLite database that stores data from Spotify about songs and their artists. This dataset contains the top 100 streamed songs on Spotify in 2018. In a terminal window, run sqlite3 songs.db so that you can begin executing queries on the database.

First, when sqlite3 prompts you to provide a query, type .schema and press enter. This will output the CREATE TABLE statements that were used to generate each of the tables in the database. By examining those statements, you can identify the columns present in each table.

Notice that every artist has an id and a name. Notice, too, that every song has a name, an artist_id (corresponding to the id of the artist of the song), as well as values for the danceability, energy, key, loudness, speechiness (presence of spoken words in a track), valence, tempo, and duration of the song (measured in milliseconds).

The challenge ahead of you is to write SQL queries to answer a variety of different questions by selecting data from one or more of these tables.

Implementation Details

For each of the following problems, you should write a single SQL query that outputs the results specified by each problem. Your response must take the form of a single SQL query, though you may nest other queries inside of your query. You should not assume anything about the ids of any particular songs or artists: your queries should be accurate even if the id of any particular song or person were different. Finally, each query should return only the data necessary to answer the question: if the problem only asks you to output the names of songs, for example, then your query should not also output each song’s tempo.

1. In 1.sql, write a SQL query to list the names of all songs in the database.
   • Your query should output a table with a single column for the name of each song.
2. In 2.sql, write a SQL query to list the names of all songs in increasing order of tempo.
   • Your query should output a table with a single column for the name of each song.
3. In 3.sql, write a SQL query to list the names of the top 5 longest songs, in descending order of length.
   • Your query should output a table with a single column for the name of each song.
4. In 4.sql, write a SQL query that lists the names of any songs that have danceability, energy, and valence greater than 0.75.
   • Your query should output a table with a single column for the name of each song.
5. In 5.sql, write a SQL query that returns the average energy of all the songs.
   • Your query should output a table with a single column and a single row containing the average energy.
6. In 6.sql, write a SQL query that lists the names of songs that are by Post Malone.
   • Your query should output a table with a single column for the name of each song.
   • You should not make any assumptions about what Post Malone’s artist_id is.
7. In 7.sql, write a SQL query that returns the average energy of songs that are by Drake.
   • Your query should output a table with a single column and a single row containing the average energy.
   • You should not make any assumptions about what Drake’s artist_id is.
8. In 8.sql, write a SQL query that lists the names of the songs that feature other artists.
   • Songs that feature other artists will include “feat.” in the name of the song.
   • Your query should output a table with a single column for the name of each song.

Hints

• See this SQL keywords reference for some SQL syntax that may be helpful!

Testing

Execute the below to evaluate the correctness of your code using check50.

check50 cs50/labs/2021/fall/songs

How to Submit

In your terminal, execute the below to submit your work.

submit50 songs

To confirm your submission, go to github.com/classroom50/songs-USERNAME where USERNAME is your GitHub username. You should see the code from your latest submission.

Labs are assessed only on whether you’ve submitted an honest attempt.

-- 1.sql 
-- All songs in the database.
SELECT name
FROM songs;

-- 2.sql 
-- All songs in increasing order of tempo.
SELECT name
FROM songs
ORDER BY tempo;

-- 3.sql 
-- The names of the top 5 longest songs, in descending order of length.
SELECT name
FROM songs 
ORDER BY duration_ms
DESC LIMIT 5;

-- 4.sql
-- The names of any songs that have danceability, energy, and valence greater than 0.75.
SELECT name 
FROM songs 
WHERE danceability > 0.75 AND energy > 0.75 AND valence > 0.75;

-- 5.sql
-- The average energy of all the songs.
SELECT AVG(energy)
FROM songs

-- 6.sql
-- The names of songs that are by Post Malone.
-- Using nested SELECTs
SELECT name
FROM songs
WHERE artist_id =
(
    SELECT id
    FROM artists
    WHERE name = 'Post Malone'
);

-- Using JOIN
SELECT songs.name
FROM songs
JOIN artists ON songs.artist_id = artists.id
WHERE artists.name = 'Post Malone';

-- 7.sql
-- The average energy of songs that are by Drake
-- Using nested SELECTs
SELECT AVG(energy) 
FROM songs 
WHERE artist_id = 
(
    SELECT id 
    FROM artists 
    WHERE name = 'Drake'
);

-- Using JOIN
SELECT AVG(songs.energy)
FROM songs
JOIN artists ON songs.artist_id = artists.id
WHERE artists.name = 'Drake';

-- 8.sql
-- The names of songs that feature other artists.
SELECT name 
FROM songs 
WHERE name LIKE '%feat.%';

Acknowledgements

Dataset from Kaggle.