BRIAN: Let's take a look at how you might have solved "songs." In this lab, we gave you eight questions to answer. And your task was to answer each of those questions by writing a SQL query that you could have run on a database to get access to that answer. 

So the first query we asked you to write was to write a SQL query to list the names of all of the songs in the database. And this was just a "select" query. You're going to select, and then select takes as its first argument what columns you care about selecting. And so here I want to select the column called "name" from the song's table. So select a name from songs would have given us all of the names of all of the songs that were stored inside of the songs table. 

Meanwhile, in 2.sql, you were asked to write a SQL query to list the names of all of the songs in increasing order of tempo. So the start of the query would have been the same thing. We're still selecting "name" from songs, but we want to put those songs in a particular order. And for that, SQL has an ORDER BY clause that lets us put rows in a particular order according to the value of a column. 

So here we could say, select name from songs, and then order by tempo. Where tempo here is the column on which we want to order those songs. You could have explicitly said ASC for ascending order, which is the default by SQL, so you didn't need to include it. 

But if you had wanted to do descending order, for example, that would have been DESC. But in this case, we just asked for increasing order, which was the default by SQL. So just SELECT name FROM songs, ORDER BY tempo would have allowed us to query for all of the songs and then put them in order by their tempo. 

In 3.sql, meanwhile, your task was to write a SQL query that listed the names of the top five longest songs in descending order of length. So there are really two pieces to this query. The first thing is we need to make sure they're in descending order of length. And the next thing we need to do is to make sure that we only get back five results instead of getting back all of the data from the table. 

So let's start with the descending order of length. There's a column duration_ms, which is going to represent the duration of any song. And so to organize the songs in descending order of length, I could say, select name from song, order by duration_ms, DESC for descending order. That's going to put all of the rows in descending order. 

But I only want the top five longest songs, not all of the songs, in descending order. And so here I can add another clause called LIMIT that limits the number of rows that are going to be returned by the SQL query. By saying LIMIT 5, I'm limiting myself to just the five longest songs that happen to be inside of this database. 

Now in 4.sql, your task was to write a SQL query that lists the names of any songs that have danceability, energy, and valence greater than 0.75. And so here I'm adding a condition, some sort of way to filter down the results of my select query. I don't want all of the songs, and I don't just want the first some number of songs, but I want songs that match a particular condition. 

And here's where I can use a WHERE clause to specify a condition and even join together multiple of those clauses with Boolean expressions like AND. So here I'm selecting name from songs where --and this is where I introduce this condition-- where danceability is greater than 75 AND energy is greater than 75 AND valence is greater than 0.75 in order to make sure that all three of these things are going to be true. And I will only be able to see names of songs that match that condition. 

In 5.sql, you were asked to write a SQL query that returns the average energy of all of the songs. Well, the songs table has an energy column for the energy of any individual song. And so, to get the average energy, you needed to use a SQL function. SQL has some built-in functions like count, and like average, and like sum for performing various different operations. 

And so, in this case, I needed to call the average function, which in SQL is just AVG, to select the average value for the energy column from my songs table. In 6.sql, we ask you something a little bit more complicated. We asked you to write a SQL query that lists the names of songs that are by Post Malone. Now ideally, we wanted to just select from the songs table to get only the songs where the artist is Post Malone. But there is no artist column in the songs table. There is, however, an artist ID column in the songs table that corresponds to some ID from the artists table. 

So the first thing I might want to do is figure out what Post Malone's artist ID is. And to do that, I could have done something like SELECT ID from artists where name equals Post Malone. And that would have been a query that would tell me Post Malone's artist ID. And then I could have used that ID in another query to figure out all of the songs that are by Post Malone. 

But in SQL, you can nest queries within each other. Rather than separate this out into two distinct queries, I could nest one within the other and say, select a name from songs where artist ID is equal to whatever the result of this query is. This query here will select Post Malone's artist ID. And now I'm selecting all of the names of the songs where artist ID is equal to Post Malone's artist ID. The effect of that will be to list the names of songs that are by Post Malone. 

Next, in 7.sql, we ask you to combine some of the ideas from these past two queries. Here we asked you to write a SQL query that returns the average energy of songs, not all songs, but only songs that are by Drake. So here again, we wanted to use the average function to get the average energy and select from the songs table. But I needed to add a WHERE clause here to say that I don't want the average energy of all songs but only songs where the artist ID is equal to Drake's artist ID. 

And here we could do another nested query to get access to that information. Selecting the ID from the artist table where name is equal to Drake, which gives us Drake's artist ID, which we can then use in this larger query to get the average energy of only those songs. And finally, in 8.sql, we asked you to write a SQL query that lists the names of songs that feature other artists. 

And this was a little bit tricky, but the thing to realize is that songs that feature other artists generally have in the name of the song the word feat, F-E-A-T, and then a dot, meaning featuring some other artist. And so how might you gone about querying this, well, here, rather than checking if something is equal to something else, we can use LIKE here to check, again, something that potentially has wild cards. 

Here I'm selecting the names of all songs where the name is like this pattern, some wild card meaning some potential characters, then feat dot, and then another wild card meaning other characters could potentially follow it. And this query will get us any song that has feat dot somewhere inside of the name of that song, which will then give us the names of all of the songs that feature other artists. And so by writing these SQL queries, you could run them on the songs.DB database to get answers to all of these questions and more. My name is Brian, and this with songs.