WEBVTT

1
00:00:01.730 --> 00:00:05.779
alright so what we're going to do now is so
we're going to have a look at wild cards

2
00:00:05.779 --> 00:00:09.889
i'm going to bring up the text editor
that actually used in the last video

3
00:00:09.889 --> 00:00:14.389
because that's going to make it easy for
me to make these changes and just

4
00:00:14.389 --> 00:00:17.360
bring that up on the screen so you can see it

5
00:00:17.360 --> 00:00:19.880
I've got a particular song that
I like to hear in the collection

6
00:00:19.880 --> 00:00:25.249
but I can't remember exactly what it
was called nor who it's by the only

7
00:00:25.249 --> 00:00:29.240
thing I do know is it's got the word
doctor in the title though so how would

8
00:00:29.240 --> 00:00:34.280
I actually go about creating or retrieving
that information using sql code

9
00:00:34.969 --> 00:00:38.719
well now that i've pasted this previous
query into my text editor i can just

10
00:00:38.719 --> 00:00:42.590
come in here and just edit this where
clause and where it's got Doolittle i'm

11
00:00:42.590 --> 00:00:46.190
going to delete that out and leaving the
double quotes in there instead i'm going

12
00:00:46.190 --> 00:00:51.500
to type in percent the word doctor
without any additional spaces another

13
00:00:51.500 --> 00:00:56.929
percent so this should list all the
songs that contain the word doctor in

14
00:00:56.929 --> 00:01:01.519
their title and actually one other thing
i need to do i put where in its name

15
00:01:01.519 --> 00:01:02.809
and

16
00:01:02.809 --> 00:01:08.630
no longer will it be equal where it
needs to be here is like lets type that

17
00:01:08.630 --> 00:01:14.180
in so.....

18
00:01:14.180 --> 00:01:21.650
.....so now
if i copy that and paste it in i think

19
00:01:21.650 --> 00:01:25.700
the problem there is the double quotes
haven't been interpreted correctly by text

20
00:01:25.700 --> 00:01:29.060
edit these double quotes this is what
happens when you don't sort of use a

21
00:01:29.060 --> 00:01:33.350
proper text editor you get these
funny result so I'm going to fix

22
00:01:33.350 --> 00:01:41.270
that and see if that actually works a
bit better if it doesn't we're getting

23
00:01:41.270 --> 00:01:46.700
the same problem let's actually try
changing that to a single quote because

24
00:01:46.700 --> 00:01:49.730
the problem is the quote characters that are being typed here

25
00:01:49.730 --> 00:01:53.360
are actually invalid so actually what I will do is copy I've got this copied in

26
00:01:53.360 --> 00:01:57.380
another documents so going to copy
that i'll just try pasting that in there

27
00:01:57.380 --> 00:02:02.030
again notice how the double quotes this
time are correct this is what sometimes

28
00:02:02.030 --> 00:02:05.330
happens when you're using I mean not
using sort of a proper program editor

29
00:02:05.330 --> 00:02:08.959
can get these weird little characters
that are appearing that aren't the true

30
00:02:08.959 --> 00:02:11.209
double quotes and that's why we're
getting this error that couldn't be

31
00:02:11.209 --> 00:02:14.599
recognized because that quote there is
actually different to this one here even

32
00:02:14.599 --> 00:02:15.140
though

33
00:02:15.140 --> 00:02:20.150
very much the same let's just run this
and we'll go back and talk about it now

34
00:02:20.150 --> 00:02:21.290
that should work

35
00:02:21.290 --> 00:02:25.190
alright so we actually got to work this
time and you can see that we've got a

36
00:02:25.190 --> 00:02:30.500
result of all the songs that contain the
word doctor in them and just bring that

37
00:02:30.500 --> 00:02:35.270
query back on the screen again theirs two
things to note about the command and 3

38
00:02:35.270 --> 00:02:37.550
I guess if you count the fact that worked

39
00:02:37.550 --> 00:02:42.170
firstly we use the keyword like instead
of the equal symbol we want to match

40
00:02:42.170 --> 00:02:46.160
name that are like the text that we
typed in fact if I'd used equals

41
00:02:46.160 --> 00:02:50.510
instead of like I'd only have got back
the wishbone ash doctor song

42
00:02:50.510 --> 00:02:56.209
dr. the second thing is about that is
that the wild-card character in sql

43
00:02:56.209 --> 00:03:01.940
is the percent character now you may be
used to using a ? to match single

44
00:03:01.940 --> 00:03:07.820
characters or an astrix to match any
sequence but in sql you use the ? instead

45
00:03:07.820 --> 00:03:12.590
of an astrix to match a sequence of zero
or more characters and actually there

46
00:03:12.590 --> 00:03:17.810
was a third thing unlike equals which
performs a case-sensitive search like is

47
00:03:17.810 --> 00:03:22.070
not case sensitive so you can use like
without a wild card if you want to

48
00:03:22.070 --> 00:03:27.590
perform a search without worrying about
the case so that where clause matches

49
00:03:27.590 --> 00:03:32.750
any rows that have the word doctor in
the song's title column now bands

50
00:03:32.750 --> 00:03:36.410
sometimes change their names think of
prints or the sensational alex harvey

51
00:03:36.410 --> 00:03:40.940
band and this collection contains at
least one album by jefferson

52
00:03:40.940 --> 00:03:45.500
airplane which later became jefferson
starship which looks like another good

53
00:03:45.500 --> 00:03:50.989
use for a wildcard search let's go ahead
and change the like there and where we

54
00:03:50.989 --> 00:03:59.360
got song title lets come back and change
that to artists . name and this time

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

56
00:04:06.350 --> 00:04:10.579
time I'm going to leave the percentage
of the start so it's Jefferson without

57
00:04:10.579 --> 00:04:14.989
the double quotes and i think im gonna
get problems with those single quotes

58
00:04:14.989 --> 00:04:18.919
again so i'm going to copy this
off-screen again and I'm going to paste

59
00:04:18.919 --> 00:04:23.240
in here you can see those double quotes
are now fixed and incidentally if you're

60
00:04:23.240 --> 00:04:25.820
doing this with android studio as if
you're copying to and from Android

61
00:04:25.820 --> 00:04:27.050
studio or

62
00:04:27.050 --> 00:04:29.629
proper text editor then you wouldn't be
getting these weird little things i'm

63
00:04:29.629 --> 00:04:35.840
getting here now let's paste that in
just to see that it works okay and we'll

64
00:04:35.840 --> 00:04:39.409
just go back to look at the query again
you can see both on the screen now and

65
00:04:39.409 --> 00:04:43.220
the reason that i left off the initial
percent the one of the start of the word

66
00:04:43.220 --> 00:04:47.930
Jefferson is because I knew the band's
name started with Jefferson but the

67
00:04:47.930 --> 00:04:52.460
query would still work if I'd left it in
now sql also allows an underscore to

68
00:04:52.460 --> 00:04:55.460
match a single character if you need to
do that

69
00:04:56.120 --> 00:04:59.750
alright so that's all working fine and
once you know to use like and the

70
00:04:59.750 --> 00:05:03.319
percent character they shouldn't really
be anything surprising about wildcard

71
00:05:03.319 --> 00:05:07.940
searches even using the ability to copy
and paste between the terminal window or

72
00:05:07.940 --> 00:05:11.270
command prompt in a text editor though
it's still a bit tedious having to

73
00:05:11.270 --> 00:05:16.639
re-enter all those commands when all we
actually changed was the where clause

74
00:05:16.639 --> 00:05:20.539
now the major client server databases
have have one as known as stored procedures

75
00:05:20.539 --> 00:05:25.490
which are a way to store sql queries
amongst other things and execute them

76
00:05:25.490 --> 00:05:29.750
when you want often with parameters for
things like the text to search for they

77
00:05:29.750 --> 00:05:32.719
operate a little bit like functions or
methods that are stored in the database

78
00:05:32.719 --> 00:05:35.419
and can be reused when you want

79
00:05:35.419 --> 00:05:39.500
unfortunately though its sql lite
doesn't have stored procedures and is

80
00:05:39.500 --> 00:05:43.909
actually good reason for this and it's a
result of the fact that sql lite is

81
00:05:43.909 --> 00:05:47.419
intended to be embedded in programs so
normal

82
00:05:47.419 --> 00:05:51.469
normal client sql databases
have the database server running on a

83
00:05:51.469 --> 00:05:57.560
remote machine that you connect to in
order to access to data a stored procedure

84
00:05:57.560 --> 00:06:00.830
runs on the server so it's far more
efficient than trying to work with a

85
00:06:00.830 --> 00:06:05.870
large data set on a remote machine but
as sql lite is not client-server

86
00:06:05.870 --> 00:06:09.740
and everything is running on the same
machine anyway the advantages of using

87
00:06:09.740 --> 00:06:14.419
stored procedures really don't apply in
addition you don't generally use sql

88
00:06:14.419 --> 00:06:16.969
lite interactively like we're doing
here

89
00:06:16.969 --> 00:06:20.840
we're doing this because we develop
applications and need to try things out

90
00:06:20.840 --> 00:06:25.190
and get queries working etc but
ordinary users wouldn't normally

91
00:06:25.190 --> 00:06:29.029
interact with a sql database in this way it would all be done via the

92
00:06:29.029 --> 00:06:33.529
application itself the bottom line here
is the absence of stored procedures

93
00:06:33.529 --> 00:06:36.889
isn't really a drawback when you
consider the way in which sql lite

94
00:06:36.889 --> 00:06:40.610
is intended to be used but one thing
that it does have

95
00:06:40.610 --> 00:06:45.020
in common with the client server
database systems is views so a good way

96
00:06:45.020 --> 00:06:48.349
to think about view is as a virtual
table

97
00:06:48.349 --> 00:06:52.819
it doesn't really exist as a table but
can be used as though it is one now you

98
00:06:52.819 --> 00:06:57.500
can't modify data using a view at
least not in sql lite so you can't

99
00:06:57.500 --> 00:07:02.539
update delete or insert but you can
query them just as if they were a table

100
00:07:02.539 --> 00:07:07.189
and this will probably make more sense
once we've seen a view in action

101
00:07:07.189 --> 00:07:10.610
i'm going to create one based on the
query we've been using a few times and

102
00:07:10.610 --> 00:07:14.810
then talk some more about it so we come
back here we start typing in sql lite

103
00:07:14.810 --> 00:07:19.699
and what I might do is quit out of
it clear the screen and start up again

104
00:07:19.699 --> 00:07:24.620
so we are coming in with a clean
slate so we created a view using the sql

105
00:07:24.620 --> 00:07:32.150
command using the sql create view
statement so....

106
00:07:32.150 --> 00:07:56.000
....

107
00:07:56.000 --> 00:08:19.789
...

108
00:08:19.789 --> 00:08:26.629
....and that's now

109
00:08:26.629 --> 00:08:30.409
created the view and we can see that
this is now part of the database by

110
00:08:30.409 --> 00:08:35.539
using the . schema command so...and you can see the entry on the

111
00:08:35.539 --> 00:08:40.399
bottom shows us the that we've got the
view called artist_list and the

112
00:08:40.399 --> 00:08:45.949
commands to actually produce it at that
point you could use that code if you

113
00:08:45.949 --> 00:08:50.120
wanted to you could put that in your
code your application or whatever you

114
00:08:50.120 --> 00:08:53.370
wanted to do sort of the copy and paste

115
00:08:53.370 --> 00:08:56.580
to use the view though its really quite
simple you treat it just like you would

116
00:08:56.580 --> 00:09:03.930
treat any other table so we can do a
select star....

117
00:09:03.930 --> 00:09:12.720
....and you can actually
filter it also just like a table so

118
00:09:12.720 --> 00:09:22.740
select....

119
00:09:22.740 --> 00:09:30.510
....so we now effectively have

120
00:09:30.510 --> 00:09:34.770
another table called artist_list that contains the data from three

121
00:09:34.770 --> 00:09:40.080
related tables and I think that's
incredibly cool and views are very very

122
00:09:40.080 --> 00:09:44.640
useful things to have you can also
create views on a single table and

123
00:09:44.640 --> 00:09:47.730
perhaps to restrict the columns that are
returned or the show the record in a

124
00:09:47.730 --> 00:09:52.710
specified order without having to use
the order by Clause every time now this

125
00:09:52.710 --> 00:09:56.730
can be a good way to include security in
your application the marketing

126
00:09:56.730 --> 00:10:00.959
department of a bank for example
may need to know the contact details of

127
00:10:00.959 --> 00:10:05.459
customers so it can send out to mail shops or
emails but they shouldn't have access to

128
00:10:05.459 --> 00:10:09.600
customers security questions or account
details so a view could be used to

129
00:10:09.600 --> 00:10:13.140
provide them with the details they need
while hiding the details that they

130
00:10:13.140 --> 00:10:16.709
shouldn't be made commonly available
or that shouldn't be made commonly

131
00:10:16.709 --> 00:10:21.150
available now you also probably wouldn't
want ordinary users seeing the link

132
00:10:21.150 --> 00:10:24.900
columns in our tables they're
interesting to us as developers but the

133
00:10:24.900 --> 00:10:32.250
numbers at the end of this
statement....

134
00:10:32.250 --> 00:10:39.600
.....so the numbers there would just be
confusing to other people and actually

135
00:10:39.600 --> 00:10:42.990
the primary key field is also
confusing so we can actually create

136
00:10:42.990 --> 00:10:46.830
create a view that just returns the
album names so going to something like.....

137
00:10:46.830 --> 00:10:58.740
....

138
00:11:00.329 --> 00:11:09.720
.....and

139
00:11:09.720 --> 00:11:14.160
obviously at that point we are only getting the
names now ideally I would have done a

140
00:11:14.160 --> 00:11:18.629
case-insensitive ordering there because
we once again got whipped jamboree in

141
00:11:18.629 --> 00:11:22.319
heavens to Betsy out of order as far as
most humans would be concerned right

142
00:11:22.319 --> 00:11:26.009
down the bottom there now because a view
doesn't actually exist in a way that a

143
00:11:26.009 --> 00:11:30.809
table does we can actually delete the
view and recreate it with the order by

144
00:11:30.809 --> 00:11:35.759
Clause corrected so the command to
actually delete the view would be drop

145
00:11:35.759 --> 00:11:43.290
view type in the view name so album_list in this case and

146
00:11:43.290 --> 00:11:46.799
incidentally you can also delete tables
using the command drop table followed by

147
00:11:46.799 --> 00:11:50.549
a table name but while deleting a view
doesn't affect the data in the database

148
00:11:50.549 --> 00:11:55.709
deleting a table obviously will and
yes I've also done that by mistake as

149
00:11:55.709 --> 00:12:00.059
well but now that I've actually deleted
the view i can actually recreate it

150
00:12:00.059 --> 00:12:04.259
going to paste the command in there to
recreate it and noting that I've got

151
00:12:04.259 --> 00:12:11.249
collate no case on the end there and I
can do a select....

152
00:12:11.249 --> 00:12:16.139
....and this time we've got
things sorted in the right order you can see

153
00:12:16.139 --> 00:12:20.669
here the lower case whipped jamboree is now
where most humans would expected to

154
00:12:20.669 --> 00:12:24.749
be sorted and that's just about
everything we need to know to be able to

155
00:12:24.749 --> 00:12:28.829
put a database in our program before i
finish so there's just one more thing

156
00:12:28.829 --> 00:12:33.449
I'd like I need to say about views now
you may have noticed that when i

157
00:12:33.449 --> 00:12:37.169
selected the jefferson starship albums
from the view earlier i didn't have to

158
00:12:37.169 --> 00:12:41.850
specify which name column I was searching
on so I type....

159
00:12:41.850 --> 00:12:59.069
....

160
00:12:59.069 --> 00:13:04.139
...so that was the
command I used if you haven't turned

161
00:13:04.139 --> 00:13:08.669
headers on or you have to stop and start
sql lite like i have used the command

162
00:13:08.669 --> 00:13:11.669
to put them on again....

163
00:13:12.590 --> 00:13:17.660
....so let's type that command
again I'm just going to come up here and

164
00:13:17.660 --> 00:13:27.260
drag it down and now I've done that you can
see here its got name and a comma name

165
00:13:27.260 --> 00:13:32.510
column 1 and title now because
they were too named field in the Select

166
00:13:32.510 --> 00:13:37.460
statement sql lite has renamed one of them
so that the column name is a unique and

167
00:13:37.460 --> 00:13:41.150
. schema will remind us of the command
we used to create the views so if we type

168
00:13:41.150 --> 00:13:48.260
schema again because of the clash
sql lite automatically renamed the name

169
00:13:48.260 --> 00:13:53.720
columns from the album's table to be
name column 1 now not all database

170
00:13:53.720 --> 00:13:57.860
systems do this and it's a good idea to
explicitly named the columns when you

171
00:13:57.860 --> 00:14:02.180
create the view if it's going to be
named clash or potential name clash so

172
00:14:02.180 --> 00:14:06.260
put that right what we need to do is
drop the view and recreate it this time

173
00:14:06.260 --> 00:14:11.360
giving the two name column a unique
name so I'm going to just copy some

174
00:14:11.360 --> 00:14:15.620
code here that both drops the view and
recreate it again just to save a bit of

175
00:14:15.620 --> 00:14:21.440
time so there's the code you can see
that were initially dropping the view

176
00:14:21.440 --> 00:14:24.410
and we're creating at this time

177
00:14:24.410 --> 00:14:28.220
press ENTER that's been created and now when I do that command again

178
00:14:28.220 --> 00:14:33.830
the Select command and incidentally I've
used as after the artist and album

179
00:14:33.830 --> 00:14:37.910
column names to provide a new name that
the columns will be known as in the view

180
00:14:37.910 --> 00:14:42.170
in case you're wondering what that was
and if I paste this in and have a look at that

181
00:14:42.170 --> 00:14:49.490
again we've now got artist and album track
and title we no longer got name and name column 1

182
00:14:49.490 --> 00:14:54.650
alright so that's the end of this
introduction to databases in the sql

183
00:14:54.650 --> 00:14:59.480
language in the next video I'm going to
start going through show you how we can

184
00:14:59.480 --> 00:15:03.740
use sql lite in our programs but
before we do that theirs a little bit

185
00:15:03.740 --> 00:15:06.860
of housekeeping that we're going to do
in the next video I'm going to set you a

186
00:15:06.860 --> 00:15:09.800
challenge and then after that challenge
we're going to go through and start

187
00:15:09.800 --> 00:15:13.340
putting this to use on an android
application so see you in the next video