WEBVTT

1
00:00:01.320 --> 00:00:05.940
in this video we will continue looking
at querying the data in a sql lite

2
00:00:05.940 --> 00:00:11.009
database now we've seen a basic sql
queries using the Select statement in

3
00:00:11.009 --> 00:00:15.990
the previous video and it's time to look
at that in more detail now once we've

4
00:00:15.990 --> 00:00:19.260
used some more queries we're
going to look at how to store commonly

5
00:00:19.260 --> 00:00:24.240
used queries in what's called a view now the idea of views is common in most

6
00:00:24.240 --> 00:00:30.509
relational databases and also going to
introduce the sql join clause and show

7
00:00:30.509 --> 00:00:35.460
how that's used to link tables together
and that can result in quite complicated

8
00:00:35.460 --> 00:00:41.340
queries so we'll also use views as a way
to store a query so that we can reuse it

9
00:00:41.340 --> 00:00:46.440
to whatever we need to do our previous
contacts example only had a few records

10
00:00:46.440 --> 00:00:50.730
and using a database to store a handful
of row is probably overkill

11
00:00:51.270 --> 00:00:55.739
it's probably quickly to scan three rows
manual to find tim's phone number

12
00:00:55.739 --> 00:01:00.690
than it is to type in a query but once
you get a lot of rows though our database

13
00:01:00.690 --> 00:01:05.670
becomes really useful now to save typing
what I've done is I've created a database

14
00:01:05.670 --> 00:01:11.070
containing details of a music collection
so download the file music.zip from

15
00:01:11.070 --> 00:01:16.080
the resources section for this video and
you can see that I've downloaded that

16
00:01:16.080 --> 00:01:18.689
zip file onto my desktop

17
00:01:18.689 --> 00:01:22.409
once you've done that you want to
extract the database file which is music

18
00:01:22.409 --> 00:01:27.450
.DB and save it to a suitable location on
your computer's hard drive so I'm just

19
00:01:27.450 --> 00:01:30.720
going to double-click this which will
extract the file and you can see that

20
00:01:30.720 --> 00:01:34.799
i've got a file music.DB and
what I'm going to do is I'm going to

21
00:01:34.799 --> 00:01:39.210
change the directory to go to that folder which in this case is my desktop so

22
00:01:39.210 --> 00:01:45.420
that we can access that database so the
command for me is....

23
00:01:45.420 --> 00:01:52.320
....and you can do a similar thing
on linux to navigate to the folder and

24
00:01:52.320 --> 00:01:57.119
on Windows you can do a CD space and
just navigate to the folder typing in

25
00:01:57.119 --> 00:02:01.380
whatever the directory structures is and on
the windows you generally have to use

26
00:02:01.380 --> 00:02:06.119
backslashes instead of forward slashes
in any event moved to that folder on my case

27
00:02:06.119 --> 00:02:10.290
i can do now an LS and I can see that
I've got a couple of files in their

28
00:02:10.290 --> 00:02:12.350
music.db is the one I want

29
00:02:12.350 --> 00:02:17.480
and that command also work on linux and
windows you type dir to see the files

30
00:02:18.590 --> 00:02:23.630
alright so let's now go ahead and open
that file remember we've put the sql

31
00:02:23.630 --> 00:02:27.500
lite in the path so with a command
prompt or terminal session already

32
00:02:27.500 --> 00:02:30.980
opened as you can see I've got mine open
on the screen I've change into the

33
00:02:30.980 --> 00:02:40.190
directory and now going to type...now

34
00:02:40.190 --> 00:02:44.270
incidentally I've given the file a DB
extension but sql lite doesn't

35
00:02:44.270 --> 00:02:48.620
actually care how your name the database
file its usual to use something like .

36
00:02:48.620 --> 00:02:54.740
DB or . SQLite but it really doesn't
matter it is a good idea though to avoid

37
00:02:54.740 --> 00:03:00.110
using . SQL that's usually used to
indicate that the file contains as a

38
00:03:00.110 --> 00:03:05.630
sql script we will talk about sql scripts
a little later okay so we should now be

39
00:03:05.630 --> 00:03:08.750
running sql lite with the music
database loaded as I've got on the

40
00:03:08.750 --> 00:03:12.110
screen now so let's start by reviewing
the structure of the database

41
00:03:17.740 --> 00:03:22.060
so the mini challenge is to remember
what the appropriate sql lite command is

42
00:03:22.060 --> 00:03:26.410
to display the structure of the database
so type that in so you can actually see

43
00:03:26.410 --> 00:03:31.360
what the structure of this particular
databases so pause the video now and i

44
00:03:31.360 --> 00:03:33.340
will come back and I'll show you what it
is

45
00:03:33.340 --> 00:03:43.240
ok so the solution to the challenges is the
command to type is . schema....

46
00:03:43.240 --> 00:03:48.550
....and you can see that
gives us a list of all the tables and

47
00:03:48.550 --> 00:03:53.440
the sql source code that was used to
create them now you may have used the .

48
00:03:53.440 --> 00:03:57.520
dump as well that's a fine but there's
quite a lot of data in the table so you

49
00:03:57.520 --> 00:04:00.310
have to scroll up a long way to see the
structure of the tables if you did that

50
00:04:00.310 --> 00:04:04.990
so generally speaking . schema is a
better command to use here when we just

51
00:04:04.990 --> 00:04:09.160
interested in the tables rather than
their contents so I won't use the .dump

52
00:04:09.160 --> 00:04:12.520
but you could do that if you want to
now incidentally you're not too

53
00:04:12.520 --> 00:04:16.390
familiar with command lines you can
repeat previous commands using the up

54
00:04:16.390 --> 00:04:20.730
and down arrow keys to recall them but
with that said it doesn't always work it

55
00:04:20.730 --> 00:04:24.600
depends on your version because with a
mac I can't actually use an up arrow

56
00:04:24.600 --> 00:04:29.170
here but I can use it when i'm outside
of sql lite but sql lite for some

57
00:04:29.170 --> 00:04:33.040
reason is mapping my arrow keys and not
allowing me to actually use a previous

58
00:04:33.040 --> 00:04:35.350
command but if your on

59
00:04:35.350 --> 00:04:38.770
linux that will certainly work or will
certainly work on the arrow keys and

60
00:04:38.770 --> 00:04:43.810
it's also work on windows as well
basically how you normally do just press

61
00:04:43.810 --> 00:04:46.720
the up and down arrow keys to get to the
command you want

62
00:04:46.720 --> 00:04:49.480
and you can even use the left and right arrow keys to move around on the line if you

63
00:04:49.480 --> 00:04:54.600
need to to edit the command before
pressing enter again to executed now the

64
00:04:54.600 --> 00:04:58.060
left and right arrow keys may not work
if you're using ssh to connect on a

65
00:04:58.060 --> 00:05:00.880
remote computer if you're doing that
then you probably already know how to

66
00:05:00.880 --> 00:05:05.170
move around the terminal so i'll have to be
typing in the commands but just

67
00:05:05.170 --> 00:05:09.010
bear in mind that you can probably use
the up and down arrow keys to navigate

68
00:05:09.010 --> 00:05:11.980
to a command to save you having to typing it
multiple times

69
00:05:11.980 --> 00:05:15.700
alright so looking at the schema
command of the output on the screen

70
00:05:15.700 --> 00:05:21.310
there we can see that the database
contains three tables songs albums and

71
00:05:21.310 --> 00:05:25.930
artists now each table actually contains
an ID column which you can see there's

72
00:05:25.930 --> 00:05:31.360
the first field and have called that
_ID you don't have

73
00:05:31.360 --> 00:05:35.620
to call it that but some of the java
classes than android users to handle

74
00:05:35.620 --> 00:05:39.700
databases actually require an ID column
called and _ID so it's probably

75
00:05:39.700 --> 00:05:45.190
a good habit to get into to actually do
that but in fact that's the database is

76
00:05:45.190 --> 00:05:49.750
at the moment the _ID column
is just an integer field and we do have

77
00:05:49.750 --> 00:05:52.810
to update it manually but i'll be
changing that a little bit later in the

78
00:05:52.810 --> 00:05:57.280
course for now _ID
holds a number that uniquely identifies

79
00:05:57.280 --> 00:06:03.550
the rows in the table so we can actually
check this out by typing.....

80
00:06:03.550 --> 00:06:11.590
....and you can see we got quite a few their we

81
00:06:11.590 --> 00:06:15.880
ended up with the total of 201 artists and
you can see the number on the left there

82
00:06:15.880 --> 00:06:21.460
to the left of the artist name is
uniquely identifying each one and the

83
00:06:21.460 --> 00:06:28.840
same is true we do a search for albums...you can see we've

84
00:06:28.840 --> 00:06:34.990
got a total 439 albums there and again
the id is unique for each album now

85
00:06:34.990 --> 00:06:40.090
the third column in the album's table is
the ID of the artists so that we can see

86
00:06:40.090 --> 00:06:46.510
that the last album that was created was
created by a artist 133 now if you read the

87
00:06:46.510 --> 00:06:49.870
screen very quickly when all the artists
scroll past you may remember that this

88
00:06:49.870 --> 00:06:54.070
was the black keys but we can actually
check that to confirm that by typing

89
00:06:54.070 --> 00:07:05.620
select.....and

90
00:07:05.620 --> 00:07:10.600
you can see that the variety 133 from
the artist table the name is black keys

91
00:07:10.600 --> 00:07:18.130
and finally the song so.....

92
00:07:18.130 --> 00:07:25.510
.....and you can see there's quite
a few songs here over 5,000 in fact once

93
00:07:25.510 --> 00:07:29.650
again each song has got a unique ID the
second number is the position of the

94
00:07:29.650 --> 00:07:34.480
song in its album and the final number
is the id of the album so permanent

95
00:07:34.480 --> 00:07:39.550
vacation which you can see the second to
last one there is the tenth track in

96
00:07:39.550 --> 00:07:42.550
album 367

97
00:07:44.160 --> 00:07:47.120
another mini challenge

98
00:07:47.120 --> 00:07:47.160
find the title of album 367 so type in
the sql command necessary to return
another mini challenge

99
00:07:47.160 --> 00:07:53.470
find the title of album 367 so type in
the sql command necessary to return

100
00:07:53.470 --> 00:07:58.340
that title of album 367 pause the
video now and figure that out and when

101
00:07:58.340 --> 00:08:02.210
you're ready to see me type in the
solution start the video pause the video

102
00:08:02.210 --> 00:08:05.210
now

103
00:08:06.440 --> 00:08:11.660
alright so how do we actually find the
title for album 367 we type select

104
00:08:11.660 --> 00:08:22.430
....

105
00:08:22.430 --> 00:08:27.910
....and we can see permanent vacation
so the album in other words is also

106
00:08:27.910 --> 00:08:34.130
called permanent vacation now we could
also use select star as theirs is only three

107
00:08:34.130 --> 00:08:40.330
columns in the album's table that's fine
as well so....

108
00:08:40.330 --> 00:08:47.570
....and that would
have given the same result and obviously

109
00:08:47.570 --> 00:08:49.730
it's returning the other two fields as
well

110
00:08:49.730 --> 00:08:54.560
now one thing I forgot to do was turn
headers on and it's not a big deal but

111
00:08:54.560 --> 00:08:58.700
it's helpful to see what the columns are
called so let's do that now . headers

112
00:08:58.700 --> 00:09:04.150
...see I can't use my up arrow

113
00:09:04.150 --> 00:09:07.270
normally I'd be able to press the up arrow
and get .headers to come back on the

114
00:09:07.270 --> 00:09:09.440
screen again then just type in the rest

115
00:09:09.440 --> 00:09:14.710
it's not letting me for some reason so
headers....and now if I did command

116
00:09:14.710 --> 00:09:24.320
again so.....you can see we've got the

117
00:09:24.320 --> 00:09:29.540
field names at the top as well as the actual
answer so the ID field can be used to

118
00:09:29.540 --> 00:09:34.730
relate the songs and albums tables so we
can easily see which album the song

119
00:09:34.730 --> 00:09:40.190
belongs to now having to perform two
queries to do that is a bit tedious but

120
00:09:40.190 --> 00:09:43.150
I want to look a bit more at the
structure of the tables and do some more

121
00:09:43.150 --> 00:09:48.020
queering before we talk about how we can
join the tables together before moving

122
00:09:48.020 --> 00:09:51.380
on now I'm going to back up the database
in case i do something silly with my

123
00:09:51.380 --> 00:09:57.400
updates or deletes we're going to type
in....

124
00:09:57.970 --> 00:10:03.430
.....and you can see on my
desktop the file music-back up 1

125
00:10:03.430 --> 00:10:06.910
appeared and because it's on my desktop
you can see the file that gets created

126
00:10:06.910 --> 00:10:09.910
their so we can see that the file was
successfully backed up

127
00:10:10.690 --> 00:10:13.750
alright so let's have a look at the
table structures again because there's a

128
00:10:13.750 --> 00:10:17.320
couple of things in there that i didn't
mention in the previous video and type

129
00:10:17.320 --> 00:10:25.120
in . schema now the first thing is that
the ID column is set to be the primary

130
00:10:25.120 --> 00:10:31.240
key now a key in a table is an index
which provides a way to really speed up

131
00:10:31.240 --> 00:10:36.970
searches and joins on a column now when
columns are indexed they can be searched

132
00:10:36.970 --> 00:10:41.020
much faster than if they are not
basically index columns are sorted so

133
00:10:41.020 --> 00:10:44.830
that they can be searched through much
faster now one thing I should mention

134
00:10:44.830 --> 00:10:51.310
about relational databases is that the
ordering of the rows is undefined so in

135
00:10:51.310 --> 00:10:56.470
that respect they're very similar to java
maps or to set in fact relational

136
00:10:56.470 --> 00:11:01.630
database theory is heavily based on set
theory so by defining a key

137
00:11:02.200 --> 00:11:05.200
what you're doing is you're saying that
the data should be ordered on that

138
00:11:05.200 --> 00:11:10.300
column or group of columns and searches
etc work far more efficiently as a

139
00:11:10.300 --> 00:11:16.180
result of doing that now they can be
lots of keys on a table but there can

140
00:11:16.180 --> 00:11:21.130
only be one primary key now usually this
is the ID column but if you don't have

141
00:11:21.130 --> 00:11:24.910
an ID column in your table then you can
choose another column to be the primary

142
00:11:24.910 --> 00:11:29.050
key if you want now one important thing
about the primary key though is that it

143
00:11:29.050 --> 00:11:30.460
must be unique

144
00:11:30.460 --> 00:11:37.360
let's try to add another artist using an
insert statement......

145
00:11:37.360 --> 00:11:46.330
....

146
00:11:46.330 --> 00:11:51.130
....and when we do that we should

147
00:11:51.130 --> 00:11:55.210
get an error you can see we've got an
error their unique constraint failed

148
00:11:55.210 --> 00:12:00.370
artists . _ID now personally
I'm not actually too unhappy that i

149
00:12:00.370 --> 00:12:04.660
could not add beoncy to my record
collection but it failed because there's

150
00:12:04.660 --> 00:12:10.540
already a record with a value 201 for its
primary key so we get an error we try to

151
00:12:10.540 --> 00:12:11.480
use that id

152
00:12:11.480 --> 00:12:16.430
again now keys don't have to be unique
and often you want to index a column

153
00:12:16.430 --> 00:12:19.850
that doesn't have a unique value it
doesn't have unique values a surname

154
00:12:19.850 --> 00:12:23.950
column in our context database for
example would benefit from being indexed

155
00:12:23.950 --> 00:12:27.070
but many people can have the same
surname

156
00:12:27.070 --> 00:12:31.940
so you can have keys that aren't unique
but the primary key must be unique

157
00:12:31.940 --> 00:12:39.190
so type in schema again now the other
thing to mention is the not null for the

158
00:12:39.190 --> 00:12:43.940
text fields the name column of the
artists and album tables is marked as

159
00:12:43.940 --> 00:12:49.630
not null and the title column of songs
is also not null and that means that the

160
00:12:49.630 --> 00:12:53.650
columns must contain a value if you try
to leave them blank when inserting new

161
00:12:53.650 --> 00:12:57.860
record you'll actually get an error and
if you think about it in this case it

162
00:12:57.860 --> 00:13:01.610
really doesn't make much sense to store
an artist without a name in the same for

163
00:13:01.610 --> 00:13:06.820
an album so creating those columns as not null
ensures that all albums and artists have

164
00:13:06.820 --> 00:13:12.700
got a name and the same goes for song
titles now sometimes a null value does

165
00:13:12.700 --> 00:13:18.260
make sense a middle name column in a
contacts table may you know well often be

166
00:13:18.260 --> 00:13:23.320
null so it's fine in that situation to
allow nulls but when designing a tables

167
00:13:23.320 --> 00:13:27.520
have a think about the data and if it
wouldn't make sense to have a null value

168
00:13:27.520 --> 00:13:33.440
then use not null when creating the
column now the primary key column in our

169
00:13:33.440 --> 00:13:38.830
tables is automatically not null because
integer primary key columns in sql

170
00:13:38.830 --> 00:13:43.370
lite are treated in a special way and
we can see that by having another go at

171
00:13:43.370 --> 00:13:49.850
inserting Beyonce into the table so we
come back and type insert into artist's

172
00:13:49.850 --> 00:13:58.610
.....and this time
we just type....

173
00:13:58.610 --> 00:14:04.730
.....so this time we're

174
00:14:04.730 --> 00:14:10.270
not providing an ID as a result we must
explicitly specify the name column so

175
00:14:10.270 --> 00:14:15.010
that's sql lite knows which column we
want to have the value Beyonce so now i

176
00:14:15.010 --> 00:14:23.510
do a select...so now beyonce is appeared at the table

177
00:14:23.510 --> 00:14:24.700
right at the bottom

178
00:14:24.700 --> 00:14:30.070
and note how it's how she has been
automatically given the ID 202 an

179
00:14:30.070 --> 00:14:35.020
integer primary key column can't contain
null values and sql lite automatically

180
00:14:35.020 --> 00:14:40.240
generates a unique number for the column
if one isn't provided now this is

181
00:14:40.240 --> 00:14:44.230
slightly different from the behavior of
other databases other sql databases

182
00:14:44.230 --> 00:14:49.270
such as Microsoft sql server where you
have to specify autoincrement when

183
00:14:49.270 --> 00:14:53.560
creating the column if you want the
values to be automatically generated now

184
00:14:53.560 --> 00:14:56.980
there's a description of this behavior
and why you would normally use auto

185
00:14:56.980 --> 00:15:01.150
increment in sql lite databases in
the documentation so quickly take

186
00:15:01.150 --> 00:15:05.980
you to that page particularly we've got
some experience in other databases that

187
00:15:05.980 --> 00:15:11.620
will be good to know this if you're
going to be working with databases a lot

188
00:15:11.620 --> 00:15:15.310
it's worth reading that but really all
we need to know is that a sql lite

189
00:15:15.310 --> 00:15:18.820
will create the ids for us and we
don't have to worry about making sure

190
00:15:18.820 --> 00:15:22.420
that we don't reuse an integer ID in a
primary key field

191
00:15:23.290 --> 00:15:27.010
alright so that's all I'm going to say
about keys in this course database

192
00:15:27.010 --> 00:15:31.030
administration is a very complex topic
in its own right and the aim of this

193
00:15:31.030 --> 00:15:34.600
section is to give you the basics so
that's you can use databases to

194
00:15:34.600 --> 00:15:39.040
store your programs data if you are
going to be doing a lot of work with

195
00:15:39.040 --> 00:15:42.550
databases and you'll probably need to
know about keys and how they affect

196
00:15:42.550 --> 00:15:47.290
performance both positively and
negatively and stuff like that but we

197
00:15:47.290 --> 00:15:50.770
don't really need anymore for what
we're doing here so i'm going to end the

198
00:15:50.770 --> 00:15:55.480
video here now in the next video will
continue on with sql lite and we'll

199
00:15:55.480 --> 00:15:59.110
start looking at the order by Clause
i'll see you in the next video