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.