1
00:00:00,000 --> 00:00:00,500


2
00:00:00,500 --> 00:00:03,120
BRIAN: Let's take a look at how
you might have solved "songs."

3
00:00:03,120 --> 00:00:06,510
In this lab, we gave you
eight questions to answer.

4
00:00:06,510 --> 00:00:09,240
And your task was to answer
each of those questions

5
00:00:09,240 --> 00:00:12,600
by writing a SQL query that you
could have run on a database

6
00:00:12,600 --> 00:00:15,080
to get access to that answer.

7
00:00:15,080 --> 00:00:17,320
So the first query we
asked you to write was

8
00:00:17,320 --> 00:00:22,450
to write a SQL query to list the names
of all of the songs in the database.

9
00:00:22,450 --> 00:00:24,550
And this was just a "select" query.

10
00:00:24,550 --> 00:00:28,120
You're going to select, and then
select takes as its first argument

11
00:00:28,120 --> 00:00:30,310
what columns you care about selecting.

12
00:00:30,310 --> 00:00:35,200
And so here I want to select the column
called "name" from the song's table.

13
00:00:35,200 --> 00:00:37,870
So select a name from
songs would have given us

14
00:00:37,870 --> 00:00:40,330
all of the names of
all of the songs that

15
00:00:40,330 --> 00:00:43,990
were stored inside of the songs table.

16
00:00:43,990 --> 00:00:47,020
Meanwhile, in 2.sql,
you were asked to write

17
00:00:47,020 --> 00:00:52,265
a SQL query to list the names of all of
the songs in increasing order of tempo.

18
00:00:52,265 --> 00:00:54,640
So the start of the query
would have been the same thing.

19
00:00:54,640 --> 00:00:57,490
We're still selecting
"name" from songs, but we

20
00:00:57,490 --> 00:01:00,380
want to put those songs
in a particular order.

21
00:01:00,380 --> 00:01:03,550
And for that, SQL has
an ORDER BY clause that

22
00:01:03,550 --> 00:01:08,660
lets us put rows in a particular order
according to the value of a column.

23
00:01:08,660 --> 00:01:14,050
So here we could say, select name
from songs, and then order by tempo.

24
00:01:14,050 --> 00:01:18,010
Where tempo here is the column on
which we want to order those songs.

25
00:01:18,010 --> 00:01:21,100
You could have explicitly
said ASC for ascending

26
00:01:21,100 --> 00:01:24,530
order, which is the default by SQL,
so you didn't need to include it.

27
00:01:24,530 --> 00:01:27,350
But if you had wanted to do
descending order, for example,

28
00:01:27,350 --> 00:01:29,380
that would have been DESC.

29
00:01:29,380 --> 00:01:31,880
But in this case, we just
asked for increasing order,

30
00:01:31,880 --> 00:01:33,550
which was the default by SQL.

31
00:01:33,550 --> 00:01:37,150
So just SELECT name FROM
songs, ORDER BY tempo

32
00:01:37,150 --> 00:01:40,000
would have allowed us to
query for all of the songs

33
00:01:40,000 --> 00:01:43,620
and then put them in
order by their tempo.

34
00:01:43,620 --> 00:01:46,770
In 3.sql, meanwhile,
your task was to write

35
00:01:46,770 --> 00:01:50,640
a SQL query that listed the
names of the top five longest

36
00:01:50,640 --> 00:01:54,130
songs in descending order of length.

37
00:01:54,130 --> 00:01:56,700
So there are really two
pieces to this query.

38
00:01:56,700 --> 00:02:00,570
The first thing is we need to make sure
they're in descending order of length.

39
00:02:00,570 --> 00:02:02,880
And the next thing we
need to do is to make sure

40
00:02:02,880 --> 00:02:06,810
that we only get back five results
instead of getting back all of the data

41
00:02:06,810 --> 00:02:08,259
from the table.

42
00:02:08,259 --> 00:02:11,030
So let's start with the
descending order of length.

43
00:02:11,030 --> 00:02:14,310
There's a column
duration_ms, which is going

44
00:02:14,310 --> 00:02:16,650
to represent the duration of any song.

45
00:02:16,650 --> 00:02:21,100
And so to organize the songs
in descending order of length,

46
00:02:21,100 --> 00:02:25,380
I could say, select name from
song, order by duration_ms,

47
00:02:25,380 --> 00:02:28,530
DESC for descending order.

48
00:02:28,530 --> 00:02:31,410
That's going to put all of
the rows in descending order.

49
00:02:31,410 --> 00:02:35,670
But I only want the top five
longest songs, not all of the songs,

50
00:02:35,670 --> 00:02:37,110
in descending order.

51
00:02:37,110 --> 00:02:39,640
And so here I can add
another clause called

52
00:02:39,640 --> 00:02:43,080
LIMIT that limits the
number of rows that are

53
00:02:43,080 --> 00:02:45,240
going to be returned by the SQL query.

54
00:02:45,240 --> 00:02:50,190
By saying LIMIT 5, I'm limiting
myself to just the five longest songs

55
00:02:50,190 --> 00:02:54,580
that happen to be
inside of this database.

56
00:02:54,580 --> 00:02:59,010
Now in 4.sql, your task was
to write a SQL query that

57
00:02:59,010 --> 00:03:03,180
lists the names of any songs
that have danceability, energy,

58
00:03:03,180 --> 00:03:07,380
and valence greater than 0.75.

59
00:03:07,380 --> 00:03:10,440
And so here I'm adding a
condition, some sort of way

60
00:03:10,440 --> 00:03:13,732
to filter down the results
of my select query.

61
00:03:13,732 --> 00:03:15,690
I don't want all of the
songs, and I don't just

62
00:03:15,690 --> 00:03:18,480
want the first some number
of songs, but I want songs

63
00:03:18,480 --> 00:03:21,000
that match a particular condition.

64
00:03:21,000 --> 00:03:24,330
And here's where I can use a WHERE
clause to specify a condition

65
00:03:24,330 --> 00:03:27,090
and even join together
multiple of those clauses

66
00:03:27,090 --> 00:03:30,070
with Boolean expressions like AND.

67
00:03:30,070 --> 00:03:33,690
So here I'm selecting
name from songs where

68
00:03:33,690 --> 00:03:37,650
--and this is where I introduce
this condition-- where danceability

69
00:03:37,650 --> 00:03:42,120
is greater than 75 AND
energy is greater than 75

70
00:03:42,120 --> 00:03:45,120
AND valence is greater
than 0.75 in order

71
00:03:45,120 --> 00:03:48,360
to make sure that all three of
these things are going to be true.

72
00:03:48,360 --> 00:03:53,520
And I will only be able to see names
of songs that match that condition.

73
00:03:53,520 --> 00:03:57,690
In 5.sql, you were asked to
write a SQL query that returns

74
00:03:57,690 --> 00:04:00,930
the average energy of all of the songs.

75
00:04:00,930 --> 00:04:03,150
Well, the songs table
has an energy column

76
00:04:03,150 --> 00:04:05,560
for the energy of any individual song.

77
00:04:05,560 --> 00:04:09,570
And so, to get the average energy,
you needed to use a SQL function.

78
00:04:09,570 --> 00:04:14,070
SQL has some built-in functions like
count, and like average, and like sum

79
00:04:14,070 --> 00:04:16,570
for performing various
different operations.

80
00:04:16,570 --> 00:04:20,070
And so, in this case, I needed to
call the average function, which

81
00:04:20,070 --> 00:04:25,530
in SQL is just AVG, to select the
average value for the energy column

82
00:04:25,530 --> 00:04:28,740
from my songs table.

83
00:04:28,740 --> 00:04:32,800
In 6.sql, we ask you something
a little bit more complicated.

84
00:04:32,800 --> 00:04:37,020
We asked you to write a SQL query
that lists the names of songs

85
00:04:37,020 --> 00:04:39,440
that are by Post Malone.

86
00:04:39,440 --> 00:04:43,070
Now ideally, we wanted to just
select from the songs table

87
00:04:43,070 --> 00:04:47,030
to get only the songs where
the artist is Post Malone.

88
00:04:47,030 --> 00:04:50,390
But there is no artist
column in the songs table.

89
00:04:50,390 --> 00:04:54,860
There is, however, an artist ID column
in the songs table that corresponds

90
00:04:54,860 --> 00:04:58,070
to some ID from the artists table.

91
00:04:58,070 --> 00:05:00,470
So the first thing I
might want to do is figure

92
00:05:00,470 --> 00:05:03,270
out what Post Malone's artist ID is.

93
00:05:03,270 --> 00:05:06,290
And to do that, I could have
done something like SELECT ID

94
00:05:06,290 --> 00:05:11,270
from artists where name
equals Post Malone.

95
00:05:11,270 --> 00:05:14,840
And that would have been a query that
would tell me Post Malone's artist ID.

96
00:05:14,840 --> 00:05:17,840
And then I could have used
that ID in another query

97
00:05:17,840 --> 00:05:21,740
to figure out all of the
songs that are by Post Malone.

98
00:05:21,740 --> 00:05:24,860
But in SQL, you can nest
queries within each other.

99
00:05:24,860 --> 00:05:28,250
Rather than separate this out
into two distinct queries,

100
00:05:28,250 --> 00:05:32,990
I could nest one within the other
and say, select a name from songs

101
00:05:32,990 --> 00:05:38,120
where artist ID is equal to whatever
the result of this query is.

102
00:05:38,120 --> 00:05:42,050
This query here will select
Post Malone's artist ID.

103
00:05:42,050 --> 00:05:45,050
And now I'm selecting all
of the names of the songs

104
00:05:45,050 --> 00:05:49,100
where artist ID is equal
to Post Malone's artist ID.

105
00:05:49,100 --> 00:05:54,530
The effect of that will be to list the
names of songs that are by Post Malone.

106
00:05:54,530 --> 00:05:59,060
Next, in 7.sql, we ask you
to combine some of the ideas

107
00:05:59,060 --> 00:06:00,830
from these past two queries.

108
00:06:00,830 --> 00:06:03,380
Here we asked you to
write a SQL query that

109
00:06:03,380 --> 00:06:07,040
returns the average energy
of songs, not all songs,

110
00:06:07,040 --> 00:06:09,500
but only songs that are by Drake.

111
00:06:09,500 --> 00:06:12,440
So here again, we wanted
to use the average function

112
00:06:12,440 --> 00:06:15,980
to get the average energy and
select from the songs table.

113
00:06:15,980 --> 00:06:18,890
But I needed to add a
WHERE clause here to say

114
00:06:18,890 --> 00:06:21,320
that I don't want the
average energy of all songs

115
00:06:21,320 --> 00:06:26,660
but only songs where the artist
ID is equal to Drake's artist ID.

116
00:06:26,660 --> 00:06:28,880
And here we could do
another nested query

117
00:06:28,880 --> 00:06:30,680
to get access to that information.

118
00:06:30,680 --> 00:06:34,430
Selecting the ID from
the artist table where

119
00:06:34,430 --> 00:06:38,210
name is equal to Drake, which gives us
Drake's artist ID, which we can then

120
00:06:38,210 --> 00:06:44,490
use in this larger query to get the
average energy of only those songs.

121
00:06:44,490 --> 00:06:47,700
And finally, in 8.sql,
we asked you to write

122
00:06:47,700 --> 00:06:53,100
a SQL query that lists the names of
songs that feature other artists.

123
00:06:53,100 --> 00:06:57,240
And this was a little bit tricky, but
the thing to realize is that songs that

124
00:06:57,240 --> 00:07:01,815
feature other artists generally have
in the name of the song the word feat,

125
00:07:01,815 --> 00:07:06,450
F-E-A-T, and then a dot, meaning
featuring some other artist.

126
00:07:06,450 --> 00:07:10,290
And so how might you gone about querying
this, well, here, rather than checking

127
00:07:10,290 --> 00:07:12,600
if something is equal
to something else, we

128
00:07:12,600 --> 00:07:14,940
can use LIKE here to
check, again, something

129
00:07:14,940 --> 00:07:17,160
that potentially has wild cards.

130
00:07:17,160 --> 00:07:19,680
Here I'm selecting
the names of all songs

131
00:07:19,680 --> 00:07:23,970
where the name is like this
pattern, some wild card meaning

132
00:07:23,970 --> 00:07:27,720
some potential characters,
then feat dot, and then

133
00:07:27,720 --> 00:07:32,220
another wild card meaning other
characters could potentially follow it.

134
00:07:32,220 --> 00:07:35,610
And this query will get
us any song that has

135
00:07:35,610 --> 00:07:39,570
feat dot somewhere inside of
the name of that song, which

136
00:07:39,570 --> 00:07:43,505
will then give us the names of all of
the songs that feature other artists.

137
00:07:43,505 --> 00:07:48,390
And so by writing these SQL queries, you
could run them on the songs.DB database

138
00:07:48,390 --> 00:07:51,730
to get answers to all of
these questions and more.

139
00:07:51,730 --> 00:07:55,160
My name is Brian, and this with songs.

140
00:07:55,160 --> 00:07:56,000