1
00:00:04,570 --> 00:00:09,190
in this video we will continue looking
at querying the data in a sql lite

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

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

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

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

6
00:00:27,490 --> 00:00:33,750
relational databases and also going to
introduce the sql join clause and show

7
00:00:33,750 --> 00:00:38,710
how that's used to link tables together
and that can result in quite complicated

8
00:00:38,710 --> 00:00:44,590
queries so we'll also use views as a way
to store a query so that we can reuse it

9
00:00:44,590 --> 00:00:49,690
to whatever we need to do our previous
contacts example only had a few records

10
00:00:49,690 --> 00:00:53,980
and using a database to store a handful
of row is probably overkill

11
00:00:54,520 --> 00:00:58,980
it's probably quickly to scan three rows
manual to find tim's phone number

12
00:00:58,980 --> 00:01:03,940
than it is to type in a query but once
you get a lot of rows though our database

13
00:01:03,940 --> 00:01:08,920
becomes really useful now to save typing
what I've done is I've created a database

14
00:01:08,920 --> 00:01:14,320
containing details of a music collection
so download the file music.zip from

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

16
00:01:19,330 --> 00:01:21,930
zip file onto my desktop

17
00:01:21,930 --> 00:01:25,650
once you've done that you want to
extract the database file which is music

18
00:01:25,650 --> 00:01:30,700
.DB and save it to a suitable location on
your computer's hard drive so I'm just

19
00:01:30,700 --> 00:01:33,970
going to double-click this which will
extract the file and you can see that

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

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

22
00:01:42,460 --> 00:01:48,670
that we can access that database so the
command for me is....

23
00:01:48,670 --> 00:01:55,570
....and you can do a similar thing
on linux to navigate to the folder and

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

25
00:02:00,360 --> 00:02:04,630
whatever the directory structures is and on
the windows you generally have to use

26
00:02:04,630 --> 00:02:09,360
backslashes instead of forward slashes
in any event moved to that folder on my case

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

28
00:02:13,540 --> 00:02:15,600
music.db is the one I want

29
00:02:15,600 --> 00:02:20,730
and that command also work on linux and
windows you type dir to see the files

30
00:02:21,840 --> 00:02:26,880
alright so let's now go ahead and open
that file remember we've put the sql

31
00:02:26,880 --> 00:02:30,750
lite in the path so with a command
prompt or terminal session already

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

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

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

35
00:02:47,520 --> 00:02:51,870
actually care how your name the database
file its usual to use something like .

36
00:02:51,870 --> 00:02:57,990
DB or . SQLite but it really doesn't
matter it is a good idea though to avoid

37
00:02:57,990 --> 00:03:03,360
using . SQL that's usually used to
indicate that the file contains as a

38
00:03:03,360 --> 00:03:08,880
sql script we will talk about sql scripts
a little later okay so we should now be

39
00:03:08,880 --> 00:03:12,000
running sql lite with the music
database loaded as I've got on the

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

41
00:03:22,110 --> 00:03:26,430
so the mini challenge is to remember
what the appropriate sql lite command is

42
00:03:26,430 --> 00:03:30,780
to display the structure of the database
so type that in so you can actually see

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

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

45
00:03:37,710 --> 00:03:47,610
ok so the solution to the challenges is the
command to type is . schema....

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

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

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

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

50
00:04:04,680 --> 00:04:09,360
so generally speaking . schema is a
better command to use here when we just

51
00:04:09,360 --> 00:04:13,530
interested in the tables rather than
their contents so I won't use the .dump

52
00:04:13,530 --> 00:04:16,890
but you could do that if you want to
now incidentally you're not too

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

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

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

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

57
00:04:33,540 --> 00:04:37,410
reason is mapping my arrow keys and not
allowing me to actually use a previous

58
00:04:37,410 --> 00:04:39,720
command but if your on

59
00:04:39,720 --> 00:04:43,140
linux that will certainly work or will
certainly work on the arrow keys and

60
00:04:43,140 --> 00:04:48,180
it's also work on windows as well
basically how you normally do just press

61
00:04:48,180 --> 00:04:51,090
the up and down arrow keys to get to the
command you want

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

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

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

65
00:05:02,430 --> 00:05:05,250
remote computer if you're doing that
then you probably already know how to

66
00:05:05,250 --> 00:05:09,540
move around the terminal so i'll have to be
typing in the commands but just

67
00:05:09,540 --> 00:05:13,380
bear in mind that you can probably use
the up and down arrow keys to navigate

68
00:05:13,380 --> 00:05:16,350
to a command to save you having to typing it
multiple times

69
00:05:16,350 --> 00:05:20,070
alright so looking at the schema
command of the output on the screen

70
00:05:20,070 --> 00:05:25,680
there we can see that the database
contains three tables songs albums and

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

72
00:05:30,300 --> 00:05:35,730
the first field and have called that
_ID you don't have

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

74
00:05:39,990 --> 00:05:44,070
databases actually require an ID column
called and _ID so it's probably

75
00:05:44,070 --> 00:05:49,560
a good habit to get into to actually do
that but in fact that's the database is

76
00:05:49,560 --> 00:05:54,120
at the moment the _ID column
is just an integer field and we do have

77
00:05:54,120 --> 00:05:57,180
to update it manually but i'll be
changing that a little bit later in the

78
00:05:57,180 --> 00:06:01,650
course for now _ID
holds a number that uniquely identifies

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

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

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

82
00:06:20,250 --> 00:06:25,830
to the left of the artist name is
uniquely identifying each one and the

83
00:06:25,830 --> 00:06:33,210
same is true we do a search for albums...you can see we've

84
00:06:33,210 --> 00:06:39,360
got a total 439 albums there and again
the id is unique for each album now

85
00:06:39,360 --> 00:06:44,460
the third column in the album's table is
the ID of the artists so that we can see

86
00:06:44,460 --> 00:06:50,880
that the last album that was created was
created by a artist 133 now if you read the

87
00:06:50,880 --> 00:06:54,240
screen very quickly when all the artists
scroll past you may remember that this

88
00:06:54,240 --> 00:06:58,440
was the black keys but we can actually
check that to confirm that by typing

89
00:06:58,440 --> 00:07:09,990
select.....and

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

91
00:07:14,970 --> 00:07:22,500
and finally the song so.....

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

93
00:07:29,880 --> 00:07:34,020
again each song has got a unique ID the
second number is the position of the

94
00:07:34,020 --> 00:07:38,850
song in its album and the final number
is the id of the album so permanent

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

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

97
00:07:48,530 --> 00:07:51,530
another mini challenge

98
00:07:52,990 --> 00:07:59,340
find the title of album 367 so type in
the sql command necessary to return

99
00:07:59,340 --> 00:08:04,210
that title of album 367 pause the
video now and figure that out and when

100
00:08:04,210 --> 00:08:08,080
you're ready to see me type in the
solution start the video pause the video

101
00:08:08,080 --> 00:08:11,080
now

102
00:08:12,310 --> 00:08:17,530
alright so how do we actually find the
title for album 367 we type select

103
00:08:17,530 --> 00:08:28,300
....

104
00:08:28,300 --> 00:08:33,780
....and we can see permanent vacation
so the album in other words is also

105
00:08:33,780 --> 00:08:40,000
called permanent vacation now we could
also use select star as theirs is only three

106
00:08:40,000 --> 00:08:46,200
columns in the album's table that's fine
as well so....

107
00:08:46,200 --> 00:08:53,440
....and that would
have given the same result and obviously

108
00:08:53,440 --> 00:08:55,600
it's returning the other two fields as
well

109
00:08:55,600 --> 00:09:00,430
now one thing I forgot to do was turn
headers on and it's not a big deal but

110
00:09:00,430 --> 00:09:04,570
it's helpful to see what the columns are
called so let's do that now . headers

111
00:09:04,570 --> 00:09:10,020
...see I can't use my up arrow

112
00:09:10,020 --> 00:09:13,140
normally I'd be able to press the up arrow
and get .headers to come back on the

113
00:09:13,140 --> 00:09:15,310
screen again then just type in the rest

114
00:09:15,310 --> 00:09:20,580
it's not letting me for some reason so
headers....and now if I did command

115
00:09:20,580 --> 00:09:30,190
again so.....you can see we've got the

116
00:09:30,190 --> 00:09:35,410
field names at the top as well as the actual
answer so the ID field can be used to

117
00:09:35,410 --> 00:09:40,600
relate the songs and albums tables so we
can easily see which album the song

118
00:09:40,600 --> 00:09:46,060
belongs to now having to perform two
queries to do that is a bit tedious but

119
00:09:46,060 --> 00:09:49,020
I want to look a bit more at the
structure of the tables and do some more

120
00:09:49,020 --> 00:09:53,890
queering before we talk about how we can
join the tables together before moving

121
00:09:53,890 --> 00:09:57,250
on now I'm going to back up the database
in case i do something silly with my

122
00:09:57,250 --> 00:10:03,270
updates or deletes we're going to type
in....

123
00:10:03,840 --> 00:10:09,300
.....and you can see on my
desktop the file music-back up 1

124
00:10:09,300 --> 00:10:12,780
appeared and because it's on my desktop
you can see the file that gets created

125
00:10:12,780 --> 00:10:15,780
their so we can see that the file was
successfully backed up

126
00:10:16,560 --> 00:10:19,620
alright so let's have a look at the
table structures again because there's a

127
00:10:19,620 --> 00:10:23,190
couple of things in there that i didn't
mention in the previous video and type

128
00:10:23,190 --> 00:10:30,990
in . schema now the first thing is that
the ID column is set to be the primary

129
00:10:30,990 --> 00:10:37,110
key now a key in a table is an index
which provides a way to really speed up

130
00:10:37,110 --> 00:10:42,840
searches and joins on a column now when
columns are indexed they can be searched

131
00:10:42,840 --> 00:10:46,890
much faster than if they are not
basically index columns are sorted so

132
00:10:46,890 --> 00:10:50,700
that they can be searched through much
faster now one thing I should mention

133
00:10:50,700 --> 00:10:57,180
about relational databases is that the
ordering of the rows is undefined so in

134
00:10:57,180 --> 00:11:02,340
that respect they're very similar to java
maps or to set in fact relational

135
00:11:02,340 --> 00:11:07,500
database theory is heavily based on set
theory so by defining a key

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

137
00:11:11,070 --> 00:11:16,170
column or group of columns and searches
etc work far more efficiently as a

138
00:11:16,170 --> 00:11:22,050
result of doing that now they can be
lots of keys on a table but there can

139
00:11:22,050 --> 00:11:27,000
only be one primary key now usually this
is the ID column but if you don't have

140
00:11:27,000 --> 00:11:30,780
an ID column in your table then you can
choose another column to be the primary

141
00:11:30,780 --> 00:11:34,920
key if you want now one important thing
about the primary key though is that it

142
00:11:34,920 --> 00:11:36,330
must be unique

143
00:11:36,330 --> 00:11:43,230
let's try to add another artist using an
insert statement......

144
00:11:43,230 --> 00:11:52,200
....

145
00:11:52,200 --> 00:11:57,000
....and when we do that we should

146
00:11:57,000 --> 00:12:01,080
get an error you can see we've got an
error their unique constraint failed

147
00:12:01,080 --> 00:12:06,240
artists . _ID now personally
I'm not actually too unhappy that i

148
00:12:06,240 --> 00:12:10,530
could not add beoncy to my record
collection but it failed because there's

149
00:12:10,530 --> 00:12:16,410
already a record with a value 201 for its
primary key so we get an error we try to

150
00:12:16,410 --> 00:12:17,350
use that id

151
00:12:17,350 --> 00:12:22,300
again now keys don't have to be unique
and often you want to index a column

152
00:12:22,300 --> 00:12:25,720
that doesn't have a unique value it
doesn't have unique values a surname

153
00:12:25,720 --> 00:12:29,820
column in our context database for
example would benefit from being indexed

154
00:12:29,820 --> 00:12:32,940
but many people can have the same
surname

155
00:12:32,940 --> 00:12:37,810
so you can have keys that aren't unique
but the primary key must be unique

156
00:12:37,810 --> 00:12:45,060
so type in schema again now the other
thing to mention is the not null for the

157
00:12:45,060 --> 00:12:49,810
text fields the name column of the
artists and album tables is marked as

158
00:12:49,810 --> 00:12:55,500
not null and the title column of songs
is also not null and that means that the

159
00:12:55,500 --> 00:12:59,520
columns must contain a value if you try
to leave them blank when inserting new

160
00:12:59,520 --> 00:13:03,730
record you'll actually get an error and
if you think about it in this case it

161
00:13:03,730 --> 00:13:07,480
really doesn't make much sense to store
an artist without a name in the same for

162
00:13:07,480 --> 00:13:12,690
an album so creating those columns as not null
ensures that all albums and artists have

163
00:13:12,690 --> 00:13:18,570
got a name and the same goes for song
titles now sometimes a null value does

164
00:13:18,570 --> 00:13:24,130
make sense a middle name column in a
contacts table may you know well often be

165
00:13:24,130 --> 00:13:29,190
null so it's fine in that situation to
allow nulls but when designing a tables

166
00:13:29,190 --> 00:13:33,390
have a think about the data and if it
wouldn't make sense to have a null value

167
00:13:33,390 --> 00:13:39,310
then use not null when creating the
column now the primary key column in our

168
00:13:39,310 --> 00:13:44,700
tables is automatically not null because
integer primary key columns in sql

169
00:13:44,700 --> 00:13:49,240
lite are treated in a special way and
we can see that by having another go at

170
00:13:49,240 --> 00:13:55,720
inserting Beyonce into the table so we
come back and type insert into artist's

171
00:13:55,720 --> 00:14:04,480
.....and this time
we just type....

172
00:14:04,480 --> 00:14:10,600
.....so this time we're

173
00:14:10,600 --> 00:14:16,140
not providing an ID as a result we must
explicitly specify the name column so

174
00:14:16,140 --> 00:14:20,880
that's sql lite knows which column we
want to have the value Beyonce so now i

175
00:14:20,880 --> 00:14:29,380
do a select...so now beyonce is appeared at the table

176
00:14:29,380 --> 00:14:30,570
right at the bottom

177
00:14:30,570 --> 00:14:35,940
and note how it's how she has been
automatically given the ID 202 an

178
00:14:35,940 --> 00:14:40,890
integer primary key column can't contain
null values and sql lite automatically

179
00:14:40,890 --> 00:14:46,110
generates a unique number for the column
if one isn't provided now this is

180
00:14:46,110 --> 00:14:50,100
slightly different from the behavior of
other databases other sql databases

181
00:14:50,100 --> 00:14:55,140
such as Microsoft sql server where you
have to specify autoincrement when

182
00:14:55,140 --> 00:14:59,430
creating the column if you want the
values to be automatically generated now

183
00:14:59,430 --> 00:15:02,850
there's a description of this behavior
and why you would normally use auto

184
00:15:02,850 --> 00:15:07,020
increment in sql lite databases in
the documentation so quickly take

185
00:15:07,020 --> 00:15:11,850
you to that page particularly we've got
some experience in other databases that

186
00:15:11,850 --> 00:15:17,490
will be good to know this if you're
going to be working with databases a lot

187
00:15:17,490 --> 00:15:21,180
it's worth reading that but really all
we need to know is that a sql lite

188
00:15:21,180 --> 00:15:24,690
will create the ids for us and we
don't have to worry about making sure

189
00:15:24,690 --> 00:15:28,290
that we don't reuse an integer ID in a
primary key field

190
00:15:29,160 --> 00:15:32,880
alright so that's all I'm going to say
about keys in this course database

191
00:15:32,880 --> 00:15:36,900
administration is a very complex topic
in its own right and the aim of this

192
00:15:36,900 --> 00:15:40,470
section is to give you the basics so
that's you can use databases to

193
00:15:40,470 --> 00:15:44,910
store your programs data if you are
going to be doing a lot of work with

194
00:15:44,910 --> 00:15:48,420
databases and you'll probably need to
know about keys and how they affect

195
00:15:48,420 --> 00:15:53,160
performance both positively and
negatively and stuff like that but we

196
00:15:53,160 --> 00:15:56,640
don't really need anymore for what
we're doing here so i'm going to end the

197
00:15:56,640 --> 00:16:01,350
video here now in the next video will
continue on with sql lite and we'll

198
00:16:01,350 --> 00:16:04,980
start looking at the order by Clause
i'll see you in the next video