1
00:00:05,500 --> 00:00:09,540
alright so what we're going to do now is so
we're going to have a look at wild cards

2
00:00:09,540 --> 00:00:13,650
i'm going to bring up the text editor
that actually used in the last video

3
00:00:13,650 --> 00:00:18,150
because that's going to make it easy for
me to make these changes and just

4
00:00:18,150 --> 00:00:21,130
bring that up on the screen so you can see it

5
00:00:21,130 --> 00:00:23,650
I've got a particular song that
I like to hear in the collection

6
00:00:23,650 --> 00:00:29,010
but I can't remember exactly what it
was called nor who it's by the only

7
00:00:29,010 --> 00:00:33,010
thing I do know is it's got the word
doctor in the title though so how would

8
00:00:33,010 --> 00:00:38,050
I actually go about creating or retrieving
that information using sql code

9
00:00:38,730 --> 00:00:42,480
well now that i've pasted this previous
query into my text editor i can just

10
00:00:42,480 --> 00:00:46,360
come in here and just edit this where
clause and where it's got Doolittle i'm

11
00:00:46,360 --> 00:00:49,960
going to delete that out and leaving the
double quotes in there instead i'm going

12
00:00:49,960 --> 00:00:55,270
to type in percent the word doctor
without any additional spaces another

13
00:00:55,270 --> 00:01:00,690
percent so this should list all the
songs that contain the word doctor in

14
00:01:00,690 --> 00:01:05,280
their title and actually one other thing
i need to do i put where in its name

15
00:01:05,280 --> 00:01:06,570
and

16
00:01:06,570 --> 00:01:12,400
no longer will it be equal where it
needs to be here is like lets type that

17
00:01:12,400 --> 00:01:17,950
in so.....

18
00:01:17,950 --> 00:01:25,420
.....so now
if i copy that and paste it in i think

19
00:01:25,420 --> 00:01:29,470
the problem there is the double quotes
haven't been interpreted correctly by text

20
00:01:29,470 --> 00:01:32,830
edit these double quotes this is what
happens when you don't sort of use a

21
00:01:32,830 --> 00:01:37,120
proper text editor you get these
funny result so I'm going to fix

22
00:01:37,120 --> 00:01:45,040
that and see if that actually works a
bit better if it doesn't we're getting

23
00:01:45,040 --> 00:01:50,470
the same problem let's actually try
changing that to a single quote because

24
00:01:50,470 --> 00:01:53,500
the problem is the quote characters that are being typed here

25
00:01:53,500 --> 00:01:57,130
are actually invalid so actually what I will do is copy I've got this copied in

26
00:01:57,130 --> 00:02:01,150
another documents so going to copy
that i'll just try pasting that in there

27
00:02:01,150 --> 00:02:05,800
again notice how the double quotes this
time are correct this is what sometimes

28
00:02:05,800 --> 00:02:09,100
happens when you're using I mean not
using sort of a proper program editor

29
00:02:09,100 --> 00:02:12,720
can get these weird little characters
that are appearing that aren't the true

30
00:02:12,720 --> 00:02:14,970
double quotes and that's why we're
getting this error that couldn't be

31
00:02:14,970 --> 00:02:18,360
recognized because that quote there is
actually different to this one here even

32
00:02:18,360 --> 00:02:18,910
though

33
00:02:18,910 --> 00:02:23,920
very much the same let's just run this
and we'll go back and talk about it now

34
00:02:23,920 --> 00:02:25,060
that should work

35
00:02:25,060 --> 00:02:28,960
alright so we actually got to work this
time and you can see that we've got a

36
00:02:28,960 --> 00:02:34,270
result of all the songs that contain the
word doctor in them and just bring that

37
00:02:34,270 --> 00:02:39,040
query back on the screen again theirs two
things to note about the command and 3

38
00:02:39,040 --> 00:02:41,320
I guess if you count the fact that worked

39
00:02:41,320 --> 00:02:45,940
firstly we use the keyword like instead
of the equal symbol we want to match

40
00:02:45,940 --> 00:02:49,930
name that are like the text that we
typed in fact if I'd used equals

41
00:02:49,930 --> 00:02:54,280
instead of like I'd only have got back
the wishbone ash doctor song

42
00:02:54,280 --> 00:02:59,970
dr. the second thing is about that is
that the wild-card character in sql

43
00:02:59,970 --> 00:03:05,710
is the percent character now you may be
used to using a ? to match single

44
00:03:05,710 --> 00:03:11,590
characters or an astrix to match any
sequence but in sql you use the ? instead

45
00:03:11,590 --> 00:03:16,360
of an astrix to match a sequence of zero
or more characters and actually there

46
00:03:16,360 --> 00:03:21,580
was a third thing unlike equals which
performs a case-sensitive search like is

47
00:03:21,580 --> 00:03:25,840
not case sensitive so you can use like
without a wild card if you want to

48
00:03:25,840 --> 00:03:31,360
perform a search without worrying about
the case so that where clause matches

49
00:03:31,360 --> 00:03:36,520
any rows that have the word doctor in
the song's title column now bands

50
00:03:36,520 --> 00:03:40,180
sometimes change their names think of
prints or the sensational alex harvey

51
00:03:40,180 --> 00:03:44,710
band and this collection contains at
least one album by jefferson

52
00:03:44,710 --> 00:03:49,270
airplane which later became jefferson
starship which looks like another good

53
00:03:49,270 --> 00:03:54,750
use for a wildcard search let's go ahead
and change the like there and where we

54
00:03:54,750 --> 00:04:03,130
got song title lets come back and change
that to artists . name and this time

55
00:04:03,130 --> 00:04:10,120
we'll change that to like instead of the word
doctor we're gonna go with Jefferson this

56
00:04:10,120 --> 00:04:14,340
time I'm going to leave the percentage
of the start so it's Jefferson without

57
00:04:14,340 --> 00:04:18,750
the double quotes and i think im gonna
get problems with those single quotes

58
00:04:18,750 --> 00:04:22,680
again so i'm going to copy this
off-screen again and I'm going to paste

59
00:04:22,680 --> 00:04:27,010
in here you can see those double quotes
are now fixed and incidentally if you're

60
00:04:27,010 --> 00:04:29,590
doing this with android studio as if
you're copying to and from Android

61
00:04:29,590 --> 00:04:30,820
studio or

62
00:04:30,820 --> 00:04:33,390
proper text editor then you wouldn't be
getting these weird little things i'm

63
00:04:33,390 --> 00:04:39,610
getting here now let's paste that in
just to see that it works okay and we'll

64
00:04:39,610 --> 00:04:43,170
just go back to look at the query again
you can see both on the screen now and

65
00:04:43,170 --> 00:04:46,990
the reason that i left off the initial
percent the one of the start of the word

66
00:04:46,990 --> 00:04:51,700
Jefferson is because I knew the band's
name started with Jefferson but the

67
00:04:51,700 --> 00:04:56,230
query would still work if I'd left it in
now sql also allows an underscore to

68
00:04:56,230 --> 00:04:59,230
match a single character if you need to
do that

69
00:04:59,890 --> 00:05:03,520
alright so that's all working fine and
once you know to use like and the

70
00:05:03,520 --> 00:05:07,080
percent character they shouldn't really
be anything surprising about wildcard

71
00:05:07,080 --> 00:05:11,710
searches even using the ability to copy
and paste between the terminal window or

72
00:05:11,710 --> 00:05:15,040
command prompt in a text editor though
it's still a bit tedious having to

73
00:05:15,040 --> 00:05:20,400
re-enter all those commands when all we
actually changed was the where clause

74
00:05:20,400 --> 00:05:24,300
now the major client server databases
have have one as known as stored procedures

75
00:05:24,300 --> 00:05:29,260
which are a way to store sql queries
amongst other things and execute them

76
00:05:29,260 --> 00:05:33,520
when you want often with parameters for
things like the text to search for they

77
00:05:33,520 --> 00:05:36,480
operate a little bit like functions or
methods that are stored in the database

78
00:05:36,480 --> 00:05:39,180
and can be reused when you want

79
00:05:39,180 --> 00:05:43,270
unfortunately though its sql lite
doesn't have stored procedures and is

80
00:05:43,270 --> 00:05:47,670
actually good reason for this and it's a
result of the fact that sql lite is

81
00:05:47,670 --> 00:05:51,180
intended to be embedded in programs so
normal

82
00:05:51,180 --> 00:05:55,230
normal client sql databases
have the database server running on a

83
00:05:55,230 --> 00:06:01,330
remote machine that you connect to in
order to access to data a stored procedure

84
00:06:01,330 --> 00:06:04,600
runs on the server so it's far more
efficient than trying to work with a

85
00:06:04,600 --> 00:06:09,640
large data set on a remote machine but
as sql lite is not client-server

86
00:06:09,640 --> 00:06:13,510
and everything is running on the same
machine anyway the advantages of using

87
00:06:13,510 --> 00:06:18,180
stored procedures really don't apply in
addition you don't generally use sql

88
00:06:18,180 --> 00:06:20,730
lite interactively like we're doing
here

89
00:06:20,730 --> 00:06:24,610
we're doing this because we develop
applications and need to try things out

90
00:06:24,610 --> 00:06:28,960
and get queries working etc but
ordinary users wouldn't normally

91
00:06:28,960 --> 00:06:32,790
interact with a sql database in this way it would all be done via the

92
00:06:32,790 --> 00:06:37,290
application itself the bottom line here
is the absence of stored procedures

93
00:06:37,290 --> 00:06:40,650
isn't really a drawback when you
consider the way in which sql lite

94
00:06:40,650 --> 00:06:44,380
is intended to be used but one thing
that it does have

95
00:06:44,380 --> 00:06:48,790
in common with the client server
database systems is views so a good way

96
00:06:48,790 --> 00:06:52,110
to think about view is as a virtual
table

97
00:06:52,110 --> 00:06:56,580
it doesn't really exist as a table but
can be used as though it is one now you

98
00:06:56,580 --> 00:07:01,270
can't modify data using a view at
least not in sql lite so you can't

99
00:07:01,270 --> 00:07:06,300
update delete or insert but you can
query them just as if they were a table

100
00:07:06,300 --> 00:07:10,950
and this will probably make more sense
once we've seen a view in action

101
00:07:10,950 --> 00:07:14,380
i'm going to create one based on the
query we've been using a few times and

102
00:07:14,380 --> 00:07:18,580
then talk some more about it so we come
back here we start typing in sql lite

103
00:07:18,580 --> 00:07:23,460
and what I might do is quit out of
it clear the screen and start up again

104
00:07:23,460 --> 00:07:28,390
so we are coming in with a clean
slate so we created a view using the sql

105
00:07:28,390 --> 00:07:35,920
command using the sql create view
statement so....

106
00:07:35,920 --> 00:07:59,770
....

107
00:07:59,770 --> 00:08:23,550
...

108
00:08:23,550 --> 00:08:30,390
....and that's now

109
00:08:30,390 --> 00:08:34,169
created the view and we can see that
this is now part of the database by

110
00:08:34,169 --> 00:08:39,299
using the . schema command so...and you can see the entry on the

111
00:08:39,299 --> 00:08:44,159
bottom shows us the that we've got the
view called artist_list and the

112
00:08:44,159 --> 00:08:49,710
commands to actually produce it at that
point you could use that code if you

113
00:08:49,710 --> 00:08:53,890
wanted to you could put that in your
code your application or whatever you

114
00:08:53,890 --> 00:08:57,140
wanted to do sort of the copy and paste

115
00:08:57,140 --> 00:09:00,350
to use the view though its really quite
simple you treat it just like you would

116
00:09:00,350 --> 00:09:07,700
treat any other table so we can do a
select star....

117
00:09:07,700 --> 00:09:16,490
....and you can actually
filter it also just like a table so

118
00:09:16,490 --> 00:09:26,510
select....

119
00:09:26,510 --> 00:09:34,280
....so we now effectively have

120
00:09:34,280 --> 00:09:38,540
another table called artist_list that contains the data from three

121
00:09:38,540 --> 00:09:43,850
related tables and I think that's
incredibly cool and views are very very

122
00:09:43,850 --> 00:09:48,410
useful things to have you can also
create views on a single table and

123
00:09:48,410 --> 00:09:51,500
perhaps to restrict the columns that are
returned or the show the record in a

124
00:09:51,500 --> 00:09:56,480
specified order without having to use
the order by Clause every time now this

125
00:09:56,480 --> 00:10:00,500
can be a good way to include security in
your application the marketing

126
00:10:00,500 --> 00:10:04,720
department of a bank for example
may need to know the contact details of

127
00:10:04,720 --> 00:10:09,220
customers so it can send out to mail shops or
emails but they shouldn't have access to

128
00:10:09,220 --> 00:10:13,370
customers security questions or account
details so a view could be used to

129
00:10:13,370 --> 00:10:16,910
provide them with the details they need
while hiding the details that they

130
00:10:16,910 --> 00:10:20,470
shouldn't be made commonly available
or that shouldn't be made commonly

131
00:10:20,470 --> 00:10:24,920
available now you also probably wouldn't
want ordinary users seeing the link

132
00:10:24,920 --> 00:10:28,670
columns in our tables they're
interesting to us as developers but the

133
00:10:28,670 --> 00:10:36,020
numbers at the end of this
statement....

134
00:10:36,020 --> 00:10:43,370
.....so the numbers there would just be
confusing to other people and actually

135
00:10:43,370 --> 00:10:46,760
the primary key field is also
confusing so we can actually create

136
00:10:46,760 --> 00:10:50,600
create a view that just returns the
album names so going to something like.....

137
00:10:50,600 --> 00:11:02,510
....

138
00:11:04,090 --> 00:11:13,490
.....and

139
00:11:13,490 --> 00:11:17,930
obviously at that point we are only getting the
names now ideally I would have done a

140
00:11:17,930 --> 00:11:22,390
case-insensitive ordering there because
we once again got whipped jamboree in

141
00:11:22,390 --> 00:11:26,080
heavens to Betsy out of order as far as
most humans would be concerned right

142
00:11:26,080 --> 00:11:29,770
down the bottom there now because a view
doesn't actually exist in a way that a

143
00:11:29,770 --> 00:11:34,570
table does we can actually delete the
view and recreate it with the order by

144
00:11:34,570 --> 00:11:39,520
Clause corrected so the command to
actually delete the view would be drop

145
00:11:39,520 --> 00:11:47,060
view type in the view name so album_list in this case and

146
00:11:47,060 --> 00:11:50,560
incidentally you can also delete tables
using the command drop table followed by

147
00:11:50,560 --> 00:11:54,310
a table name but while deleting a view
doesn't affect the data in the database

148
00:11:54,310 --> 00:11:59,470
deleting a table obviously will and
yes I've also done that by mistake as

149
00:11:59,470 --> 00:12:03,820
well but now that I've actually deleted
the view i can actually recreate it

150
00:12:03,820 --> 00:12:08,020
going to paste the command in there to
recreate it and noting that I've got

151
00:12:08,020 --> 00:12:15,010
collate no case on the end there and I
can do a select....

152
00:12:15,010 --> 00:12:19,900
....and this time we've got
things sorted in the right order you can see

153
00:12:19,900 --> 00:12:24,430
here the lower case whipped jamboree is now
where most humans would expected to

154
00:12:24,430 --> 00:12:28,510
be sorted and that's just about
everything we need to know to be able to

155
00:12:28,510 --> 00:12:32,590
put a database in our program before i
finish so there's just one more thing

156
00:12:32,590 --> 00:12:37,210
I'd like I need to say about views now
you may have noticed that when i

157
00:12:37,210 --> 00:12:40,930
selected the jefferson starship albums
from the view earlier i didn't have to

158
00:12:40,930 --> 00:12:45,620
specify which name column I was searching
on so I type....

159
00:12:45,620 --> 00:13:02,830
....

160
00:13:02,830 --> 00:13:07,900
...so that was the
command I used if you haven't turned

161
00:13:07,900 --> 00:13:12,430
headers on or you have to stop and start
sql lite like i have used the command

162
00:13:12,430 --> 00:13:15,430
to put them on again....

163
00:13:16,360 --> 00:13:21,430
....so let's type that command
again I'm just going to come up here and

164
00:13:21,430 --> 00:13:31,030
drag it down and now I've done that you can
see here its got name and a comma name

165
00:13:31,030 --> 00:13:36,280
column 1 and title now because
they were too named field in the Select

166
00:13:36,280 --> 00:13:41,230
statement sql lite has renamed one of them
so that the column name is a unique and

167
00:13:41,230 --> 00:13:44,920
. schema will remind us of the command
we used to create the views so if we type

168
00:13:44,920 --> 00:13:52,030
schema again because of the clash
sql lite automatically renamed the name

169
00:13:52,030 --> 00:13:57,490
columns from the album's table to be
name column 1 now not all database

170
00:13:57,490 --> 00:14:01,630
systems do this and it's a good idea to
explicitly named the columns when you

171
00:14:01,630 --> 00:14:05,950
create the view if it's going to be
named clash or potential name clash so

172
00:14:05,950 --> 00:14:10,030
put that right what we need to do is
drop the view and recreate it this time

173
00:14:10,030 --> 00:14:15,130
giving the two name column a unique
name so I'm going to just copy some

174
00:14:15,130 --> 00:14:19,390
code here that both drops the view and
recreate it again just to save a bit of

175
00:14:19,390 --> 00:14:25,210
time so there's the code you can see
that were initially dropping the view

176
00:14:25,210 --> 00:14:28,180
and we're creating at this time

177
00:14:28,180 --> 00:14:31,990
press ENTER that's been created and now when I do that command again

178
00:14:31,990 --> 00:14:37,600
the Select command and incidentally I've
used as after the artist and album

179
00:14:37,600 --> 00:14:41,680
column names to provide a new name that
the columns will be known as in the view

180
00:14:41,680 --> 00:14:45,940
in case you're wondering what that was
and if I paste this in and have a look at that

181
00:14:45,940 --> 00:14:53,260
again we've now got artist and album track
and title we no longer got name and name column 1

182
00:14:53,260 --> 00:14:58,420
alright so that's the end of this
introduction to databases in the sql

183
00:14:58,420 --> 00:15:03,250
language in the next video I'm going to
start going through show you how we can

184
00:15:03,250 --> 00:15:07,510
use sql lite in our programs but
before we do that theirs a little bit

185
00:15:07,510 --> 00:15:10,630
of housekeeping that we're going to do
in the next video I'm going to set you a

186
00:15:10,630 --> 00:15:13,570
challenge and then after that challenge
we're going to go through and start

187
00:15:13,570 --> 00:15:17,110
putting this to use on an android
application so see you in the next video