1
00:00:05,020 --> 00:00:09,640
in this video we will continue looking
at querying the data in a sql lite

2
00:00:09,640 --> 00:00:14,700
database now we've seen a basic sql
queries using the Select statement in

3
00:00:14,700 --> 00:00:19,690
the previous video and it's time to look
at that in more detail now once we've

4
00:00:19,690 --> 00:00:22,960
used some more queries we're
going to look at how to store commonly

5
00:00:22,960 --> 00:00:27,940
used queries in what's called a view now the idea of views is common in most

6
00:00:27,940 --> 00:00:34,200
relational databases and also going to
introduce the sql join clause and show

7
00:00:34,200 --> 00:00:39,160
how that's used to link tables together
and that can result in quite complicated

8
00:00:39,160 --> 00:00:45,040
queries so we'll also use views as a way
to store a query so that we can reuse it

9
00:00:45,040 --> 00:00:50,140
to whatever we need to do our previous
contacts example only had a few records

10
00:00:50,140 --> 00:00:54,430
and using a database to store a handful
of row is probably overkill

11
00:00:54,970 --> 00:00:59,430
it's probably quickly to scan three rows
manual to find tim's phone number

12
00:00:59,430 --> 00:01:04,390
than it is to type in a query but once
you get a lot of rows though our database

13
00:01:04,390 --> 00:01:09,370
becomes really useful now to save typing
what I've done is I've created a database

14
00:01:09,370 --> 00:01:14,770
containing details of a music collection
so download the file music.zip from

15
00:01:14,770 --> 00:01:19,780
the resources section for this video and
you can see that I've downloaded that

16
00:01:19,780 --> 00:01:22,380
zip file onto my desktop

17
00:01:22,380 --> 00:01:26,100
once you've done that you want to
extract the database file which is music

18
00:01:26,100 --> 00:01:31,150
.DB and save it to a suitable location on
your computer's hard drive so I'm just

19
00:01:31,150 --> 00:01:34,420
going to double-click this which will
extract the file and you can see that

20
00:01:34,420 --> 00:01:38,490
i've got a file music.DB and
what I'm going to do is I'm going to

21
00:01:38,490 --> 00:01:42,910
change the directory to go to that folder which in this case is my desktop so

22
00:01:42,910 --> 00:01:49,120
that we can access that database so the
command for me is....

23
00:01:49,120 --> 00:01:56,020
....and you can do a similar thing
on linux to navigate to the folder and

24
00:01:56,020 --> 00:02:00,810
on Windows you can do a CD space and
just navigate to the folder typing in

25
00:02:00,810 --> 00:02:05,080
whatever the directory structures is and on
the windows you generally have to use

26
00:02:05,080 --> 00:02:09,810
backslashes instead of forward slashes
in any event moved to that folder on my case

27
00:02:09,810 --> 00:02:13,990
i can do now an LS and I can see that
I've got a couple of files in their

28
00:02:13,990 --> 00:02:16,050
music.db is the one I want

29
00:02:16,050 --> 00:02:21,180
and that command also work on linux and
windows you type dir to see the files

30
00:02:22,290 --> 00:02:27,330
alright so let's now go ahead and open
that file remember we've put the sql

31
00:02:27,330 --> 00:02:31,200
lite in the path so with a command
prompt or terminal session already

32
00:02:31,200 --> 00:02:34,680
opened as you can see I've got mine open
on the screen I've change into the

33
00:02:34,680 --> 00:02:43,890
directory and now going to type...now

34
00:02:43,890 --> 00:02:47,970
incidentally I've given the file a DB
extension but sql lite doesn't

35
00:02:47,970 --> 00:02:52,320
actually care how your name the database
file its usual to use something like .

36
00:02:52,320 --> 00:02:58,440
DB or . SQLite but it really doesn't
matter it is a good idea though to avoid

37
00:02:58,440 --> 00:03:03,810
using . SQL that's usually used to
indicate that the file contains as a

38
00:03:03,810 --> 00:03:09,330
sql script we will talk about sql scripts
a little later okay so we should now be

39
00:03:09,330 --> 00:03:12,450
running sql lite with the music
database loaded as I've got on the

40
00:03:12,450 --> 00:03:15,810
screen now so let's start by reviewing
the structure of the database

41
00:03:21,440 --> 00:03:25,760
so the mini challenge is to remember
what the appropriate sql lite command is

42
00:03:25,760 --> 00:03:30,110
to display the structure of the database
so type that in so you can actually see

43
00:03:30,110 --> 00:03:35,060
what the structure of this particular
databases so pause the video now and i

44
00:03:35,060 --> 00:03:37,040
will come back and I'll show you what it
is

45
00:03:37,040 --> 00:03:46,940
ok so the solution to the challenges is the
command to type is . schema....

46
00:03:46,940 --> 00:03:52,250
....and you can see that
gives us a list of all the tables and

47
00:03:52,250 --> 00:03:57,140
the sql source code that was used to
create them now you may have used the .

48
00:03:57,140 --> 00:04:01,220
dump as well that's a fine but there's
quite a lot of data in the table so you

49
00:04:01,220 --> 00:04:04,010
have to scroll up a long way to see the
structure of the tables if you did that

50
00:04:04,010 --> 00:04:08,690
so generally speaking . schema is a
better command to use here when we just

51
00:04:08,690 --> 00:04:12,860
interested in the tables rather than
their contents so I won't use the .dump

52
00:04:12,860 --> 00:04:16,220
but you could do that if you want to
now incidentally you're not too

53
00:04:16,220 --> 00:04:20,089
familiar with command lines you can
repeat previous commands using the up

54
00:04:20,089 --> 00:04:24,430
and down arrow keys to recall them but
with that said it doesn't always work it

55
00:04:24,430 --> 00:04:28,300
depends on your version because with a
mac I can't actually use an up arrow

56
00:04:28,300 --> 00:04:32,870
here but I can use it when i'm outside
of sql lite but sql lite for some

57
00:04:32,870 --> 00:04:36,740
reason is mapping my arrow keys and not
allowing me to actually use a previous

58
00:04:36,740 --> 00:04:39,050
command but if your on

59
00:04:39,050 --> 00:04:42,470
linux that will certainly work or will
certainly work on the arrow keys and

60
00:04:42,470 --> 00:04:47,510
it's also work on windows as well
basically how you normally do just press

61
00:04:47,510 --> 00:04:50,420
the up and down arrow keys to get to the
command you want

62
00:04:50,420 --> 00:04:53,180
and you can even use the left and right arrow keys to move around on the line if you

63
00:04:53,180 --> 00:04:58,300
need to to edit the command before
pressing enter again to executed now the

64
00:04:58,300 --> 00:05:01,760
left and right arrow keys may not work
if you're using ssh to connect on a

65
00:05:01,760 --> 00:05:04,580
remote computer if you're doing that
then you probably already know how to

66
00:05:04,580 --> 00:05:08,870
move around the terminal so i'll have to be
typing in the commands but just

67
00:05:08,870 --> 00:05:12,710
bear in mind that you can probably use
the up and down arrow keys to navigate

68
00:05:12,710 --> 00:05:15,680
to a command to save you having to typing it
multiple times

69
00:05:15,680 --> 00:05:19,400
alright so looking at the schema
command of the output on the screen

70
00:05:19,400 --> 00:05:25,010
there we can see that the database
contains three tables songs albums and

71
00:05:25,010 --> 00:05:29,630
artists now each table actually contains
an ID column which you can see there's

72
00:05:29,630 --> 00:05:35,060
the first field and have called that
_ID you don't have

73
00:05:35,060 --> 00:05:39,320
to call it that but some of the java
classes than android users to handle

74
00:05:39,320 --> 00:05:43,400
databases actually require an ID column
called and _ID so it's probably

75
00:05:43,400 --> 00:05:48,890
a good habit to get into to actually do
that but in fact that's the database is

76
00:05:48,890 --> 00:05:53,450
at the moment the _ID column
is just an integer field and we do have

77
00:05:53,450 --> 00:05:56,510
to update it manually but i'll be
changing that a little bit later in the

78
00:05:56,510 --> 00:06:00,980
course for now _ID
holds a number that uniquely identifies

79
00:06:00,980 --> 00:06:07,250
the rows in the table so we can actually
check this out by typing.....

80
00:06:07,250 --> 00:06:15,290
....and you can see we got quite a few their we

81
00:06:15,290 --> 00:06:19,580
ended up with the total of 201 artists and
you can see the number on the left there

82
00:06:19,580 --> 00:06:25,160
to the left of the artist name is
uniquely identifying each one and the

83
00:06:25,160 --> 00:06:32,540
same is true we do a search for albums...you can see we've

84
00:06:32,540 --> 00:06:38,690
got a total 439 albums there and again
the id is unique for each album now

85
00:06:38,690 --> 00:06:43,790
the third column in the album's table is
the ID of the artists so that we can see

86
00:06:43,790 --> 00:06:50,210
that the last album that was created was
created by a artist 133 now if you read the

87
00:06:50,210 --> 00:06:53,570
screen very quickly when all the artists
scroll past you may remember that this

88
00:06:53,570 --> 00:06:57,770
was the black keys but we can actually
check that to confirm that by typing

89
00:06:57,770 --> 00:07:09,320
select.....and

90
00:07:09,320 --> 00:07:14,300
you can see that the variety 133 from
the artist table the name is black keys

91
00:07:14,300 --> 00:07:21,830
and finally the song so.....

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

93
00:07:29,210 --> 00:07:33,350
again each song has got a unique ID the
second number is the position of the

94
00:07:33,350 --> 00:07:38,180
song in its album and the final number
is the id of the album so permanent

95
00:07:38,180 --> 00:07:43,250
vacation which you can see the second to
last one there is the tenth track in

96
00:07:43,250 --> 00:07:46,250
album 367

97
00:07:47,860 --> 00:07:50,820
another mini challenge

98
00:07:50,820 --> 00:07:50,860
find the title of album 367 so type in
the sql command necessary to return
another mini challenge

99
00:07:50,860 --> 00:07:57,170
find the title of album 367 so type in
the sql command necessary to return

100
00:07:57,170 --> 00:08:02,040
that title of album 367 pause the
video now and figure that out and when

101
00:08:02,040 --> 00:08:05,910
you're ready to see me type in the
solution start the video pause the video

102
00:08:05,910 --> 00:08:08,910
now

103
00:08:10,140 --> 00:08:15,360
alright so how do we actually find the
title for album 367 we type select

104
00:08:15,360 --> 00:08:26,130
....

105
00:08:26,130 --> 00:08:31,610
....and we can see permanent vacation
so the album in other words is also

106
00:08:31,610 --> 00:08:37,830
called permanent vacation now we could
also use select star as theirs is only three

107
00:08:37,830 --> 00:08:44,030
columns in the album's table that's fine
as well so....

108
00:08:44,030 --> 00:08:51,270
....and that would
have given the same result and obviously

109
00:08:51,270 --> 00:08:53,430
it's returning the other two fields as
well

110
00:08:53,430 --> 00:08:58,260
now one thing I forgot to do was turn
headers on and it's not a big deal but

111
00:08:58,260 --> 00:09:02,400
it's helpful to see what the columns are
called so let's do that now . headers

112
00:09:02,400 --> 00:09:07,850
...see I can't use my up arrow

113
00:09:07,850 --> 00:09:10,970
normally I'd be able to press the up arrow
and get .headers to come back on the

114
00:09:10,970 --> 00:09:13,140
screen again then just type in the rest

115
00:09:13,140 --> 00:09:18,410
it's not letting me for some reason so
headers....and now if I did command

116
00:09:18,410 --> 00:09:28,020
again so.....you can see we've got the

117
00:09:28,020 --> 00:09:33,240
field names at the top as well as the actual
answer so the ID field can be used to

118
00:09:33,240 --> 00:09:38,430
relate the songs and albums tables so we
can easily see which album the song

119
00:09:38,430 --> 00:09:43,890
belongs to now having to perform two
queries to do that is a bit tedious but

120
00:09:43,890 --> 00:09:46,850
I want to look a bit more at the
structure of the tables and do some more

121
00:09:46,850 --> 00:09:51,720
queering before we talk about how we can
join the tables together before moving

122
00:09:51,720 --> 00:09:55,080
on now I'm going to back up the database
in case i do something silly with my

123
00:09:55,080 --> 00:10:01,100
updates or deletes we're going to type
in....

124
00:10:01,670 --> 00:10:07,130
.....and you can see on my
desktop the file music-back up 1

125
00:10:07,130 --> 00:10:10,610
appeared and because it's on my desktop
you can see the file that gets created

126
00:10:10,610 --> 00:10:13,610
their so we can see that the file was
successfully backed up

127
00:10:14,390 --> 00:10:17,450
alright so let's have a look at the
table structures again because there's a

128
00:10:17,450 --> 00:10:21,020
couple of things in there that i didn't
mention in the previous video and type

129
00:10:21,020 --> 00:10:28,820
in . schema now the first thing is that
the ID column is set to be the primary

130
00:10:28,820 --> 00:10:34,940
key now a key in a table is an index
which provides a way to really speed up

131
00:10:34,940 --> 00:10:40,670
searches and joins on a column now when
columns are indexed they can be searched

132
00:10:40,670 --> 00:10:44,720
much faster than if they are not
basically index columns are sorted so

133
00:10:44,720 --> 00:10:48,530
that they can be searched through much
faster now one thing I should mention

134
00:10:48,530 --> 00:10:55,010
about relational databases is that the
ordering of the rows is undefined so in

135
00:10:55,010 --> 00:11:00,170
that respect they're very similar to java
maps or to set in fact relational

136
00:11:00,170 --> 00:11:05,330
database theory is heavily based on set
theory so by defining a key

137
00:11:05,900 --> 00:11:08,900
what you're doing is you're saying that
the data should be ordered on that

138
00:11:08,900 --> 00:11:14,000
column or group of columns and searches
etc work far more efficiently as a

139
00:11:14,000 --> 00:11:19,880
result of doing that now they can be
lots of keys on a table but there can

140
00:11:19,880 --> 00:11:24,830
only be one primary key now usually this
is the ID column but if you don't have

141
00:11:24,830 --> 00:11:28,610
an ID column in your table then you can
choose another column to be the primary

142
00:11:28,610 --> 00:11:32,750
key if you want now one important thing
about the primary key though is that it

143
00:11:32,750 --> 00:11:34,160
must be unique

144
00:11:34,160 --> 00:11:41,060
let's try to add another artist using an
insert statement......

145
00:11:41,060 --> 00:11:50,030
....

146
00:11:50,030 --> 00:11:54,830
....and when we do that we should

147
00:11:54,830 --> 00:11:58,910
get an error you can see we've got an
error their unique constraint failed

148
00:11:58,910 --> 00:12:04,070
artists . _ID now personally
I'm not actually too unhappy that i

149
00:12:04,070 --> 00:12:08,360
could not add beoncy to my record
collection but it failed because there's

150
00:12:08,360 --> 00:12:14,240
already a record with a value 201 for its
primary key so we get an error we try to

151
00:12:14,240 --> 00:12:15,180
use that id

152
00:12:15,180 --> 00:12:20,130
again now keys don't have to be unique
and often you want to index a column

153
00:12:20,130 --> 00:12:23,550
that doesn't have a unique value it
doesn't have unique values a surname

154
00:12:23,550 --> 00:12:27,650
column in our context database for
example would benefit from being indexed

155
00:12:27,650 --> 00:12:30,770
but many people can have the same
surname

156
00:12:30,770 --> 00:12:35,640
so you can have keys that aren't unique
but the primary key must be unique

157
00:12:35,640 --> 00:12:42,890
so type in schema again now the other
thing to mention is the not null for the

158
00:12:42,890 --> 00:12:47,640
text fields the name column of the
artists and album tables is marked as

159
00:12:47,640 --> 00:12:53,330
not null and the title column of songs
is also not null and that means that the

160
00:12:53,330 --> 00:12:57,350
columns must contain a value if you try
to leave them blank when inserting new

161
00:12:57,350 --> 00:13:01,560
record you'll actually get an error and
if you think about it in this case it

162
00:13:01,560 --> 00:13:05,310
really doesn't make much sense to store
an artist without a name in the same for

163
00:13:05,310 --> 00:13:10,520
an album so creating those columns as not null
ensures that all albums and artists have

164
00:13:10,520 --> 00:13:16,400
got a name and the same goes for song
titles now sometimes a null value does

165
00:13:16,400 --> 00:13:21,960
make sense a middle name column in a
contacts table may you know well often be

166
00:13:21,960 --> 00:13:27,020
null so it's fine in that situation to
allow nulls but when designing a tables

167
00:13:27,020 --> 00:13:31,220
have a think about the data and if it
wouldn't make sense to have a null value

168
00:13:31,220 --> 00:13:37,140
then use not null when creating the
column now the primary key column in our

169
00:13:37,140 --> 00:13:42,530
tables is automatically not null because
integer primary key columns in sql

170
00:13:42,530 --> 00:13:47,070
lite are treated in a special way and
we can see that by having another go at

171
00:13:47,070 --> 00:13:53,550
inserting Beyonce into the table so we
come back and type insert into artist's

172
00:13:53,550 --> 00:14:02,310
.....and this time
we just type....

173
00:14:02,310 --> 00:14:08,430
.....so this time we're

174
00:14:08,430 --> 00:14:13,970
not providing an ID as a result we must
explicitly specify the name column so

175
00:14:13,970 --> 00:14:18,710
that's sql lite knows which column we
want to have the value Beyonce so now i

176
00:14:18,710 --> 00:14:27,210
do a select...so now beyonce is appeared at the table

177
00:14:27,210 --> 00:14:28,400
right at the bottom

178
00:14:28,400 --> 00:14:33,770
and note how it's how she has been
automatically given the ID 202 an

179
00:14:33,770 --> 00:14:38,720
integer primary key column can't contain
null values and sql lite automatically

180
00:14:38,720 --> 00:14:43,940
generates a unique number for the column
if one isn't provided now this is

181
00:14:43,940 --> 00:14:47,930
slightly different from the behavior of
other databases other sql databases

182
00:14:47,930 --> 00:14:52,970
such as Microsoft sql server where you
have to specify autoincrement when

183
00:14:52,970 --> 00:14:57,260
creating the column if you want the
values to be automatically generated now

184
00:14:57,260 --> 00:15:00,680
there's a description of this behavior
and why you would normally use auto

185
00:15:00,680 --> 00:15:04,850
increment in sql lite databases in
the documentation so quickly take

186
00:15:04,850 --> 00:15:09,680
you to that page particularly we've got
some experience in other databases that

187
00:15:09,680 --> 00:15:15,320
will be good to know this if you're
going to be working with databases a lot

188
00:15:15,320 --> 00:15:19,010
it's worth reading that but really all
we need to know is that a sql lite

189
00:15:19,010 --> 00:15:22,520
will create the ids for us and we
don't have to worry about making sure

190
00:15:22,520 --> 00:15:26,120
that we don't reuse an integer ID in a
primary key field

191
00:15:26,990 --> 00:15:30,710
alright so that's all I'm going to say
about keys in this course database

192
00:15:30,710 --> 00:15:34,730
administration is a very complex topic
in its own right and the aim of this

193
00:15:34,730 --> 00:15:38,300
section is to give you the basics so
that's you can use databases to

194
00:15:38,300 --> 00:15:42,740
store your programs data if you are
going to be doing a lot of work with

195
00:15:42,740 --> 00:15:46,250
databases and you'll probably need to
know about keys and how they affect

196
00:15:46,250 --> 00:15:50,990
performance both positively and
negatively and stuff like that but we

197
00:15:50,990 --> 00:15:54,470
don't really need anymore for what
we're doing here so i'm going to end the

198
00:15:54,470 --> 00:15:59,180
video here now in the next video will
continue on with sql lite and we'll

199
00:15:59,180 --> 00:16:02,810
start looking at the order by Clause
i'll see you in the next video