CS50 Video Player
    • 🧁
      • 🍩
      • 🥝
      • 🍿
    • 0:00:00BRIAN: Let's take a look at how you might have solved "songs."
    • 0:00:03In this lab, we gave you eight questions to answer.
    • 0:00:06And your task was to answer each of those questions
    • 0:00:09by writing a SQL query that you could have run on a database
    • 0:00:12to get access to that answer.
    • 0:00:15So the first query we asked you to write was
    • 0:00:17to write a SQL query to list the names of all of the songs in the database.
    • 0:00:22And this was just a "select" query.
    • 0:00:24You're going to select, and then select takes as its first argument
    • 0:00:28what columns you care about selecting.
    • 0:00:30And so here I want to select the column called "name" from the song's table.
    • 0:00:35So select a name from songs would have given us
    • 0:00:37all of the names of all of the songs that
    • 0:00:40were stored inside of the songs table.
    • 0:00:43Meanwhile, in 2.sql, you were asked to write
    • 0:00:47a SQL query to list the names of all of the songs in increasing order of tempo.
    • 0:00:52So the start of the query would have been the same thing.
    • 0:00:54We're still selecting "name" from songs, but we
    • 0:00:57want to put those songs in a particular order.
    • 0:01:00And for that, SQL has an ORDER BY clause that
    • 0:01:03lets us put rows in a particular order according to the value of a column.
    • 0:01:08So here we could say, select name from songs, and then order by tempo.
    • 0:01:14Where tempo here is the column on which we want to order those songs.
    • 0:01:18You could have explicitly said ASC for ascending
    • 0:01:21order, which is the default by SQL, so you didn't need to include it.
    • 0:01:24But if you had wanted to do descending order, for example,
    • 0:01:27that would have been DESC.
    • 0:01:29But in this case, we just asked for increasing order,
    • 0:01:31which was the default by SQL.
    • 0:01:33So just SELECT name FROM songs, ORDER BY tempo
    • 0:01:37would have allowed us to query for all of the songs
    • 0:01:40and then put them in order by their tempo.
    • 0:01:43In 3.sql, meanwhile, your task was to write
    • 0:01:46a SQL query that listed the names of the top five longest
    • 0:01:50songs in descending order of length.
    • 0:01:54So there are really two pieces to this query.
    • 0:01:56The first thing is we need to make sure they're in descending order of length.
    • 0:02:00And the next thing we need to do is to make sure
    • 0:02:02that we only get back five results instead of getting back all of the data
    • 0:02:06from the table.
    • 0:02:08So let's start with the descending order of length.
    • 0:02:11There's a column duration_ms, which is going
    • 0:02:14to represent the duration of any song.
    • 0:02:16And so to organize the songs in descending order of length,
    • 0:02:21I could say, select name from song, order by duration_ms,
    • 0:02:25DESC for descending order.
    • 0:02:28That's going to put all of the rows in descending order.
    • 0:02:31But I only want the top five longest songs, not all of the songs,
    • 0:02:35in descending order.
    • 0:02:37And so here I can add another clause called
    • 0:02:39LIMIT that limits the number of rows that are
    • 0:02:43going to be returned by the SQL query.
    • 0:02:45By saying LIMIT 5, I'm limiting myself to just the five longest songs
    • 0:02:50that happen to be inside of this database.
    • 0:02:54Now in 4.sql, your task was to write a SQL query that
    • 0:02:59lists the names of any songs that have danceability, energy,
    • 0:03:03and valence greater than 0.75.
    • 0:03:07And so here I'm adding a condition, some sort of way
    • 0:03:10to filter down the results of my select query.
    • 0:03:13I don't want all of the songs, and I don't just
    • 0:03:15want the first some number of songs, but I want songs
    • 0:03:18that match a particular condition.
    • 0:03:21And here's where I can use a WHERE clause to specify a condition
    • 0:03:24and even join together multiple of those clauses
    • 0:03:27with Boolean expressions like AND.
    • 0:03:30So here I'm selecting name from songs where
    • 0:03:33--and this is where I introduce this condition-- where danceability
    • 0:03:37is greater than 75 AND energy is greater than 75
    • 0:03:42AND valence is greater than 0.75 in order
    • 0:03:45to make sure that all three of these things are going to be true.
    • 0:03:48And I will only be able to see names of songs that match that condition.
    • 0:03:53In 5.sql, you were asked to write a SQL query that returns
    • 0:03:57the average energy of all of the songs.
    • 0:04:00Well, the songs table has an energy column
    • 0:04:03for the energy of any individual song.
    • 0:04:05And so, to get the average energy, you needed to use a SQL function.
    • 0:04:09SQL has some built-in functions like count, and like average, and like sum
    • 0:04:14for performing various different operations.
    • 0:04:16And so, in this case, I needed to call the average function, which
    • 0:04:20in SQL is just AVG, to select the average value for the energy column
    • 0:04:25from my songs table.
    • 0:04:28In 6.sql, we ask you something a little bit more complicated.
    • 0:04:32We asked you to write a SQL query that lists the names of songs
    • 0:04:37that are by Post Malone.
    • 0:04:39Now ideally, we wanted to just select from the songs table
    • 0:04:43to get only the songs where the artist is Post Malone.
    • 0:04:47But there is no artist column in the songs table.
    • 0:04:50There is, however, an artist ID column in the songs table that corresponds
    • 0:04:54to some ID from the artists table.
    • 0:04:58So the first thing I might want to do is figure
    • 0:05:00out what Post Malone's artist ID is.
    • 0:05:03And to do that, I could have done something like SELECT ID
    • 0:05:06from artists where name equals Post Malone.
    • 0:05:11And that would have been a query that would tell me Post Malone's artist ID.
    • 0:05:14And then I could have used that ID in another query
    • 0:05:17to figure out all of the songs that are by Post Malone.
    • 0:05:21But in SQL, you can nest queries within each other.
    • 0:05:24Rather than separate this out into two distinct queries,
    • 0:05:28I could nest one within the other and say, select a name from songs
    • 0:05:32where artist ID is equal to whatever the result of this query is.
    • 0:05:38This query here will select Post Malone's artist ID.
    • 0:05:42And now I'm selecting all of the names of the songs
    • 0:05:45where artist ID is equal to Post Malone's artist ID.
    • 0:05:49The effect of that will be to list the names of songs that are by Post Malone.
    • 0:05:54Next, in 7.sql, we ask you to combine some of the ideas
    • 0:05:59from these past two queries.
    • 0:06:00Here we asked you to write a SQL query that
    • 0:06:03returns the average energy of songs, not all songs,
    • 0:06:07but only songs that are by Drake.
    • 0:06:09So here again, we wanted to use the average function
    • 0:06:12to get the average energy and select from the songs table.
    • 0:06:15But I needed to add a WHERE clause here to say
    • 0:06:18that I don't want the average energy of all songs
    • 0:06:21but only songs where the artist ID is equal to Drake's artist ID.
    • 0:06:26And here we could do another nested query
    • 0:06:28to get access to that information.
    • 0:06:30Selecting the ID from the artist table where
    • 0:06:34name is equal to Drake, which gives us Drake's artist ID, which we can then
    • 0:06:38use in this larger query to get the average energy of only those songs.
    • 0:06:44And finally, in 8.sql, we asked you to write
    • 0:06:47a SQL query that lists the names of songs that feature other artists.
    • 0:06:53And this was a little bit tricky, but the thing to realize is that songs that
    • 0:06:57feature other artists generally have in the name of the song the word feat,
    • 0:07:01F-E-A-T, and then a dot, meaning featuring some other artist.
    • 0:07:06And so how might you gone about querying this, well, here, rather than checking
    • 0:07:10if something is equal to something else, we
    • 0:07:12can use LIKE here to check, again, something
    • 0:07:14that potentially has wild cards.
    • 0:07:17Here I'm selecting the names of all songs
    • 0:07:19where the name is like this pattern, some wild card meaning
    • 0:07:23some potential characters, then feat dot, and then
    • 0:07:27another wild card meaning other characters could potentially follow it.
    • 0:07:32And this query will get us any song that has
    • 0:07:35feat dot somewhere inside of the name of that song, which
    • 0:07:39will then give us the names of all of the songs that feature other artists.
    • 0:07:43And so by writing these SQL queries, you could run them on the songs.DB database
    • 0:07:48to get answers to all of these questions and more.
    • 0:07:51My name is Brian, and this with songs.
  • CS50.ai
Shortcuts
Before using a shortcut, click at least once on the video itself (to give it "focus") after closing this window.
Play/Pause spacebar or k
Rewind 10 seconds left arrow or j
Fast forward 10 seconds right arrow or l
Previous frame (while paused) ,
Next frame (while paused) .
Decrease playback rate <
Increase playback rate >
Toggle captions on/off c
Toggle mute m
Toggle full screen f or double-click video