1
00:00:04,980 --> 00:00:09,020
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,020 --> 00:00:13,130
i'm going to bring up the text editor
that actually used in the last video

3
00:00:13,130 --> 00:00:17,630
because that's going to make it easy for
me to make these changes and just

4
00:00:17,630 --> 00:00:20,610
bring that up on the screen so you can see it

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

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

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

8
00:00:32,490 --> 00:00:37,530
I actually go about creating or retrieving
that information using sql code

9
00:00:38,210 --> 00:00:41,960
well now that i've pasted this previous
query into my text editor i can just

10
00:00:41,960 --> 00:00:45,840
come in here and just edit this where
clause and where it's got Doolittle i'm

11
00:00:45,840 --> 00:00:49,440
going to delete that out and leaving the
double quotes in there instead i'm going

12
00:00:49,440 --> 00:00:54,750
to type in percent the word doctor
without any additional spaces another

13
00:00:54,750 --> 00:01:00,170
percent so this should list all the
songs that contain the word doctor in

14
00:01:00,170 --> 00:01:04,760
their title and actually one other thing
i need to do i put where in its name

15
00:01:04,760 --> 00:01:06,050
and

16
00:01:06,050 --> 00:01:11,880
no longer will it be equal where it
needs to be here is like lets type that

17
00:01:11,880 --> 00:01:17,430
in so.....

18
00:01:17,430 --> 00:01:24,900
.....so now
if i copy that and paste it in i think

19
00:01:24,900 --> 00:01:28,950
the problem there is the double quotes
haven't been interpreted correctly by text

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

21
00:01:32,310 --> 00:01:36,600
proper text editor you get these
funny result so I'm going to fix

22
00:01:36,600 --> 00:01:44,520
that and see if that actually works a
bit better if it doesn't we're getting

23
00:01:44,520 --> 00:01:49,950
the same problem let's actually try
changing that to a single quote because

24
00:01:49,950 --> 00:01:52,980
the problem is the quote characters that are being typed here

25
00:01:52,980 --> 00:01:56,610
are actually invalid so actually what I will do is copy I've got this copied in

26
00:01:56,610 --> 00:02:00,630
another documents so going to copy
that i'll just try pasting that in there

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

28
00:02:05,280 --> 00:02:08,580
happens when you're using I mean not
using sort of a proper program editor

29
00:02:08,580 --> 00:02:12,200
can get these weird little characters
that are appearing that aren't the true

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

31
00:02:14,450 --> 00:02:17,840
recognized because that quote there is
actually different to this one here even

32
00:02:17,840 --> 00:02:18,390
though

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

34
00:02:23,400 --> 00:02:24,540
that should work

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

36
00:02:28,440 --> 00:02:33,750
result of all the songs that contain the
word doctor in them and just bring that

37
00:02:33,750 --> 00:02:38,520
query back on the screen again theirs two
things to note about the command and 3

38
00:02:38,520 --> 00:02:40,800
I guess if you count the fact that worked

39
00:02:40,800 --> 00:02:45,420
firstly we use the keyword like instead
of the equal symbol we want to match

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

41
00:02:49,410 --> 00:02:53,760
instead of like I'd only have got back
the wishbone ash doctor song

42
00:02:53,760 --> 00:02:59,450
dr. the second thing is about that is
that the wild-card character in sql

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

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

45
00:03:11,070 --> 00:03:15,840
of an astrix to match a sequence of zero
or more characters and actually there

46
00:03:15,840 --> 00:03:21,060
was a third thing unlike equals which
performs a case-sensitive search like is

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

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

49
00:03:30,840 --> 00:03:36,000
any rows that have the word doctor in
the song's title column now bands

50
00:03:36,000 --> 00:03:39,660
sometimes change their names think of
prints or the sensational alex harvey

51
00:03:39,660 --> 00:03:44,190
band and this collection contains at
least one album by jefferson

52
00:03:44,190 --> 00:03:48,750
airplane which later became jefferson
starship which looks like another good

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

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

55
00:04:02,610 --> 00:04:09,600
we'll change that to like instead of the word
doctor we're gonna go with Jefferson this

56
00:04:09,600 --> 00:04:13,820
time I'm going to leave the percentage
of the start so it's Jefferson without

57
00:04:13,820 --> 00:04:18,230
the double quotes and i think im gonna
get problems with those single quotes

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

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

60
00:04:26,490 --> 00:04:29,070
doing this with android studio as if
you're copying to and from Android

61
00:04:29,070 --> 00:04:30,300
studio or

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

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

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

65
00:04:42,650 --> 00:04:46,470
the reason that i left off the initial
percent the one of the start of the word

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

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

68
00:04:55,710 --> 00:04:58,710
match a single character if you need to
do that

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

70
00:05:03,000 --> 00:05:06,560
percent character they shouldn't really
be anything surprising about wildcard

71
00:05:06,560 --> 00:05:11,190
searches even using the ability to copy
and paste between the terminal window or

72
00:05:11,190 --> 00:05:14,520
command prompt in a text editor though
it's still a bit tedious having to

73
00:05:14,520 --> 00:05:19,880
re-enter all those commands when all we
actually changed was the where clause

74
00:05:19,880 --> 00:05:23,780
now the major client server databases
have have one as known as stored procedures

75
00:05:23,780 --> 00:05:28,740
which are a way to store sql queries
amongst other things and execute them

76
00:05:28,740 --> 00:05:33,000
when you want often with parameters for
things like the text to search for they

77
00:05:33,000 --> 00:05:35,960
operate a little bit like functions or
methods that are stored in the database

78
00:05:35,960 --> 00:05:38,660
and can be reused when you want

79
00:05:38,660 --> 00:05:42,750
unfortunately though its sql lite
doesn't have stored procedures and is

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

81
00:05:47,150 --> 00:05:50,660
intended to be embedded in programs so
normal

82
00:05:50,660 --> 00:05:54,710
normal client sql databases
have the database server running on a

83
00:05:54,710 --> 00:06:00,810
remote machine that you connect to in
order to access to data a stored procedure

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

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

86
00:06:09,120 --> 00:06:12,990
and everything is running on the same
machine anyway the advantages of using

87
00:06:12,990 --> 00:06:17,660
stored procedures really don't apply in
addition you don't generally use sql

88
00:06:17,660 --> 00:06:20,210
lite interactively like we're doing
here

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

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

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

92
00:06:32,270 --> 00:06:36,770
application itself the bottom line here
is the absence of stored procedures

93
00:06:36,770 --> 00:06:40,130
isn't really a drawback when you
consider the way in which sql lite

94
00:06:40,130 --> 00:06:43,860
is intended to be used but one thing
that it does have

95
00:06:43,860 --> 00:06:48,270
in common with the client server
database systems is views so a good way

96
00:06:48,270 --> 00:06:51,590
to think about view is as a virtual
table

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

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

99
00:07:00,750 --> 00:07:05,780
update delete or insert but you can
query them just as if they were a table

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

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

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

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

104
00:07:22,940 --> 00:07:27,870
so we are coming in with a clean
slate so we created a view using the sql

105
00:07:27,870 --> 00:07:35,400
command using the sql create view
statement so....

106
00:07:35,400 --> 00:07:59,250
....

107
00:07:59,250 --> 00:08:23,030
...

108
00:08:23,030 --> 00:08:29,870
....and that's now

109
00:08:29,870 --> 00:08:33,650
created the view and we can see that
this is now part of the database by

110
00:08:33,650 --> 00:08:38,780
using the . schema command so...and you can see the entry on the

111
00:08:38,780 --> 00:08:43,640
bottom shows us the that we've got the
view called artist_list and the

112
00:08:43,640 --> 00:08:49,190
commands to actually produce it at that
point you could use that code if you

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

114
00:08:53,370 --> 00:08:56,620
wanted to do sort of the copy and paste

115
00:08:56,620 --> 00:08:59,830
to use the view though its really quite
simple you treat it just like you would

116
00:08:59,830 --> 00:09:07,180
treat any other table so we can do a
select star....

117
00:09:07,180 --> 00:09:15,970
....and you can actually
filter it also just like a table so

118
00:09:15,970 --> 00:09:25,990
select....

119
00:09:25,990 --> 00:09:33,760
....so we now effectively have

120
00:09:33,760 --> 00:09:38,020
another table called artist_list that contains the data from three

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

122
00:09:43,330 --> 00:09:47,890
useful things to have you can also
create views on a single table and

123
00:09:47,890 --> 00:09:50,980
perhaps to restrict the columns that are
returned or the show the record in a

124
00:09:50,980 --> 00:09:55,960
specified order without having to use
the order by Clause every time now this

125
00:09:55,960 --> 00:09:59,980
can be a good way to include security in
your application the marketing

126
00:09:59,980 --> 00:10:04,200
department of a bank for example
may need to know the contact details of

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

128
00:10:08,700 --> 00:10:12,850
customers security questions or account
details so a view could be used to

129
00:10:12,850 --> 00:10:16,390
provide them with the details they need
while hiding the details that they

130
00:10:16,390 --> 00:10:19,950
shouldn't be made commonly available
or that shouldn't be made commonly

131
00:10:19,950 --> 00:10:24,400
available now you also probably wouldn't
want ordinary users seeing the link

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

133
00:10:28,150 --> 00:10:35,500
numbers at the end of this
statement....

134
00:10:35,500 --> 00:10:42,850
.....so the numbers there would just be
confusing to other people and actually

135
00:10:42,850 --> 00:10:46,240
the primary key field is also
confusing so we can actually create

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

137
00:10:50,080 --> 00:11:01,990
....

138
00:11:03,570 --> 00:11:12,970
.....and

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

140
00:11:17,410 --> 00:11:21,870
case-insensitive ordering there because
we once again got whipped jamboree in

141
00:11:21,870 --> 00:11:25,560
heavens to Betsy out of order as far as
most humans would be concerned right

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

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

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

145
00:11:39,000 --> 00:11:46,540
view type in the view name so album_list in this case and

146
00:11:46,540 --> 00:11:50,040
incidentally you can also delete tables
using the command drop table followed by

147
00:11:50,040 --> 00:11:53,790
a table name but while deleting a view
doesn't affect the data in the database

148
00:11:53,790 --> 00:11:58,950
deleting a table obviously will and
yes I've also done that by mistake as

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

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

151
00:12:07,500 --> 00:12:14,490
collate no case on the end there and I
can do a select....

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

153
00:12:19,380 --> 00:12:23,910
here the lower case whipped jamboree is now
where most humans would expected to

154
00:12:23,910 --> 00:12:27,990
be sorted and that's just about
everything we need to know to be able to

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

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

157
00:12:36,690 --> 00:12:40,410
selected the jefferson starship albums
from the view earlier i didn't have to

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

159
00:12:45,100 --> 00:13:02,310
....

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

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

162
00:13:11,910 --> 00:13:14,910
to put them on again....

163
00:13:15,840 --> 00:13:20,910
....so let's type that command
again I'm just going to come up here and

164
00:13:20,910 --> 00:13:30,510
drag it down and now I've done that you can
see here its got name and a comma name

165
00:13:30,510 --> 00:13:35,760
column 1 and title now because
they were too named field in the Select

166
00:13:35,760 --> 00:13:40,710
statement sql lite has renamed one of them
so that the column name is a unique and

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

168
00:13:44,400 --> 00:13:51,510
schema again because of the clash
sql lite automatically renamed the name

169
00:13:51,510 --> 00:13:56,970
columns from the album's table to be
name column 1 now not all database

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

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

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

173
00:14:09,510 --> 00:14:14,610
giving the two name column a unique
name so I'm going to just copy some

174
00:14:14,610 --> 00:14:18,870
code here that both drops the view and
recreate it again just to save a bit of

175
00:14:18,870 --> 00:14:24,690
time so there's the code you can see
that were initially dropping the view

176
00:14:24,690 --> 00:14:27,660
and we're creating at this time

177
00:14:27,660 --> 00:14:31,470
press ENTER that's been created and now when I do that command again

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

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

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

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

182
00:14:52,740 --> 00:14:57,900
alright so that's the end of this
introduction to databases in the sql

183
00:14:57,900 --> 00:15:02,730
language in the next video I'm going to
start going through show you how we can

184
00:15:02,730 --> 00:15:06,990
use sql lite in our programs but
before we do that theirs a little bit

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

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

187
00:15:13,050 --> 00:15:16,590
putting this to use on an android
application so see you in the next video