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


2
00:00:00,500 --> 00:00:02,430
SPEAKER: In this lab,
your task is going to be

3
00:00:02,430 --> 00:00:07,770
to write SQL queries that can answer
questions about a database of songs.

4
00:00:07,770 --> 00:00:12,750
As part of this lab, we'll give to
a sqlite database called songs.db,

5
00:00:12,750 --> 00:00:15,660
inside of which is
information taken from Spotify

6
00:00:15,660 --> 00:00:19,060
about various different
songs and their artists.

7
00:00:19,060 --> 00:00:21,210
Ultimately, the first
thing you'll want to do

8
00:00:21,210 --> 00:00:26,280
is to understand the structure or the
schema of the tables in this database.

9
00:00:26,280 --> 00:00:30,240
Inside songs.db, you'll
find a table called songs

10
00:00:30,240 --> 00:00:32,439
that has quite a few different columns.

11
00:00:32,439 --> 00:00:36,880
Every song has an ID, which is
an INTEGER, the name of the song,

12
00:00:36,880 --> 00:00:41,160
which is some TEXT, artist ID,
which is an INTEGER representing

13
00:00:41,160 --> 00:00:42,690
the artist of the song--

14
00:00:42,690 --> 00:00:44,530
we'll come back to that in a moment--

15
00:00:44,530 --> 00:00:46,720
and then other information
about the song as well--

16
00:00:46,720 --> 00:00:50,340
its danceability, its energy,
its key, its loudness,

17
00:00:50,340 --> 00:00:53,810
as well as other properties
of the song, too.

18
00:00:53,810 --> 00:00:56,150
Now, let's take a look at artist ID.

19
00:00:56,150 --> 00:00:59,510
To represent the various different
artists that songs can have,

20
00:00:59,510 --> 00:01:03,680
there's another table in this
database called the artists table.

21
00:01:03,680 --> 00:01:06,230
Every artist has an ID,
which is an INTEGER,

22
00:01:06,230 --> 00:01:09,470
and a name, which is just
going to be some TEXT.

23
00:01:09,470 --> 00:01:12,860
And now the artist ID
column of the songs table

24
00:01:12,860 --> 00:01:15,200
is going to be an
INTEGER that corresponds

25
00:01:15,200 --> 00:01:18,620
to the ID of one of these artists.

26
00:01:18,620 --> 00:01:22,100
So let's take a look at what this
database actually looks like.

27
00:01:22,100 --> 00:01:24,410
Once I've downloaded
the lab, in my terminal

28
00:01:24,410 --> 00:01:30,110
I can type sqlite3 songs.db to go
ahead and open up this database.

29
00:01:30,110 --> 00:01:34,460
And if I wanted to, I could type .schema
to get a sense for the schema of this

30
00:01:34,460 --> 00:01:35,300
database.

31
00:01:35,300 --> 00:01:37,610
I would see again, here's
CREATE TABLE songs,

32
00:01:37,610 --> 00:01:41,120
along with all the columns for songs,
as well as CREATE TABLE artists,

33
00:01:41,120 --> 00:01:43,340
to see all of those artists.

34
00:01:43,340 --> 00:01:46,500
So if I wanted to see what
the songs were, for example,

35
00:01:46,500 --> 00:01:51,110
I might write a query like
SELECT star FROM songs,

36
00:01:51,110 --> 00:01:53,330
to go ahead and select all of the songs.

37
00:01:53,330 --> 00:01:56,960
And I would see all the songs,
along with all of the columns

38
00:01:56,960 --> 00:01:59,600
that I have for each of
those individual rows.

39
00:01:59,600 --> 00:02:01,680
Maybe I didn't want
all of those columns.

40
00:02:01,680 --> 00:02:03,860
So I could filter down
my results as well.

41
00:02:03,860 --> 00:02:07,850
I could write a query
like SELECT name FROM song

42
00:02:07,850 --> 00:02:12,780
to just select the name
column from my songs table.

43
00:02:12,780 --> 00:02:18,000
If I do that, then I'll only see
the names of each of those songs.

44
00:02:18,000 --> 00:02:20,070
Ultimately, in this
lab, we're going to ask

45
00:02:20,070 --> 00:02:23,130
you eight different questions
about this database,

46
00:02:23,130 --> 00:02:25,320
and your task is going
to be to write queries

47
00:02:25,320 --> 00:02:27,690
to answer each of those questions.

48
00:02:27,690 --> 00:02:29,940
We've given you files in
which to put those answers.

49
00:02:29,940 --> 00:02:33,540
For example, 1.sql is where
you'll include your response

50
00:02:33,540 --> 00:02:36,600
to the first question,
writing a SQL query here

51
00:02:36,600 --> 00:02:39,730
that you can use to answer
a particular question.

52
00:02:39,730 --> 00:02:41,370
What might those prompts be?

53
00:02:41,370 --> 00:02:44,310
Well, for example, in
1.sql, you're going

54
00:02:44,310 --> 00:02:49,140
to write a SQL query to list the names
of all of the songs in the database.

55
00:02:49,140 --> 00:02:51,510
And that, in fact, is a query
that I've just shown you--

56
00:02:51,510 --> 00:02:54,030
SELECT name FROM songs.

57
00:02:54,030 --> 00:02:58,890
So inside of 1.sql, you would write
this query, SELECT name FROM songs,

58
00:02:58,890 --> 00:03:02,070
as a query that you could run
in order to get the names of all

59
00:03:02,070 --> 00:03:04,630
the songs in the database.

60
00:03:04,630 --> 00:03:07,030
Ultimately, the other
prompts are going to ask you

61
00:03:07,030 --> 00:03:09,340
for other information
about the database.

62
00:03:09,340 --> 00:03:11,800
Maybe not just looking
at the names of songs,

63
00:03:11,800 --> 00:03:14,950
but perhaps looking at
the artists of the songs,

64
00:03:14,950 --> 00:03:18,310
looking at the volume of those songs,
looking at their danceability, maybe

65
00:03:18,310 --> 00:03:20,980
other factors as well,
filtering and selecting

66
00:03:20,980 --> 00:03:24,850
and joining tables together as needed in
order to get the results you ultimately

67
00:03:24,850 --> 00:03:29,920
want to answer all of these questions
just based on a single database.

68
00:03:29,920 --> 00:03:33,390
My name is Brian, and this was Songs.

69
00:03:33,390 --> 00:03:34,000