alright so what we're going to do now is so we're going to have a look at wild cards
i'm going to bring up the text editor that actually used in the last video
because that's going to make it easy for me to make these changes and just
bring that up on the screen so you can see it
I've got a particular song that I like to hear in the collection
but I can't remember exactly what it was called nor who it's by the only
thing I do know is it's got the word doctor in the title though so how would
I actually go about creating or retrieving that information using sql code
well now that i've pasted this previous query into my text editor i can just
come in here and just edit this where clause and where it's got Doolittle i'm
going to delete that out and leaving the double quotes in there instead i'm going
to type in percent the word doctor without any additional spaces another
percent so this should list all the songs that contain the word doctor in
their title and actually one other thing i need to do i put where in its name
and
no longer will it be equal where it needs to be here is like lets type that
in so.....
.....so now if i copy that and paste it in i think
the problem there is the double quotes haven't been interpreted correctly by text
edit these double quotes this is what happens when you don't sort of use a
proper text editor you get these funny result so I'm going to fix
that and see if that actually works a bit better if it doesn't we're getting
the same problem let's actually try changing that to a single quote because
the problem is the quote characters that are being typed here
are actually invalid so actually what I will do is copy I've got this copied in
another documents so going to copy that i'll just try pasting that in there
again notice how the double quotes this time are correct this is what sometimes
happens when you're using I mean not using sort of a proper program editor
can get these weird little characters that are appearing that aren't the true
double quotes and that's why we're getting this error that couldn't be
recognized because that quote there is actually different to this one here even
though
very much the same let's just run this and we'll go back and talk about it now
that should work
alright so we actually got to work this time and you can see that we've got a
result of all the songs that contain the word doctor in them and just bring that
query back on the screen again theirs two things to note about the command and 3
I guess if you count the fact that worked
firstly we use the keyword like instead of the equal symbol we want to match
name that are like the text that we typed in fact if I'd used equals
instead of like I'd only have got back the wishbone ash doctor song
dr. the second thing is about that is that the wild-card character in sql
is the percent character now you may be used to using a ? to match single
characters or an astrix to match any sequence but in sql you use the ? instead
of an astrix to match a sequence of zero or more characters and actually there
was a third thing unlike equals which performs a case-sensitive search like is
not case sensitive so you can use like without a wild card if you want to
perform a search without worrying about the case so that where clause matches
any rows that have the word doctor in the song's title column now bands
sometimes change their names think of prints or the sensational alex harvey
band and this collection contains at least one album by jefferson
airplane which later became jefferson starship which looks like another good
use for a wildcard search let's go ahead and change the like there and where we
got song title lets come back and change that to artists . name and this time
we'll change that to like instead of the word doctor we're gonna go with Jefferson this
time I'm going to leave the percentage of the start so it's Jefferson without
the double quotes and i think im gonna get problems with those single quotes
again so i'm going to copy this off-screen again and I'm going to paste
in here you can see those double quotes are now fixed and incidentally if you're
doing this with android studio as if you're copying to and from Android
studio or
proper text editor then you wouldn't be getting these weird little things i'm
getting here now let's paste that in just to see that it works okay and we'll
just go back to look at the query again you can see both on the screen now and
the reason that i left off the initial percent the one of the start of the word
Jefferson is because I knew the band's name started with Jefferson but the
query would still work if I'd left it in now sql also allows an underscore to
match a single character if you need to do that
alright so that's all working fine and once you know to use like and the
percent character they shouldn't really be anything surprising about wildcard
searches even using the ability to copy and paste between the terminal window or
command prompt in a text editor though it's still a bit tedious having to
re-enter all those commands when all we actually changed was the where clause
now the major client server databases have have one as known as stored procedures
which are a way to store sql queries amongst other things and execute them
when you want often with parameters for things like the text to search for they
operate a little bit like functions or methods that are stored in the database
and can be reused when you want
unfortunately though its sql lite doesn't have stored procedures and is
actually good reason for this and it's a result of the fact that sql lite is
intended to be embedded in programs so normal
normal client sql databases have the database server running on a
remote machine that you connect to in order to access to data a stored procedure
runs on the server so it's far more efficient than trying to work with a
large data set on a remote machine but as sql lite is not client-server
and everything is running on the same machine anyway the advantages of using
stored procedures really don't apply in addition you don't generally use sql
lite interactively like we're doing here
we're doing this because we develop applications and need to try things out
and get queries working etc but ordinary users wouldn't normally
interact with a sql database in this way it would all be done via the
application itself the bottom line here is the absence of stored procedures
isn't really a drawback when you consider the way in which sql lite
is intended to be used but one thing that it does have
in common with the client server database systems is views so a good way
to think about view is as a virtual table
it doesn't really exist as a table but can be used as though it is one now you
can't modify data using a view at least not in sql lite so you can't
update delete or insert but you can query them just as if they were a table
and this will probably make more sense once we've seen a view in action
i'm going to create one based on the query we've been using a few times and
then talk some more about it so we come back here we start typing in sql lite
and what I might do is quit out of it clear the screen and start up again
so we are coming in with a clean slate so we created a view using the sql
command using the sql create view statement so....
....
...
....and that's now
created the view and we can see that this is now part of the database by
using the . schema command so...and you can see the entry on the
bottom shows us the that we've got the view called artist_list and the
commands to actually produce it at that point you could use that code if you
wanted to you could put that in your code your application or whatever you
wanted to do sort of the copy and paste
to use the view though its really quite simple you treat it just like you would
treat any other table so we can do a select star....
....and you can actually filter it also just like a table so
select....
....so we now effectively have
another table called artist_list that contains the data from three
related tables and I think that's incredibly cool and views are very very
useful things to have you can also create views on a single table and
perhaps to restrict the columns that are returned or the show the record in a
specified order without having to use the order by Clause every time now this
can be a good way to include security in your application the marketing
department of a bank for example may need to know the contact details of
customers so it can send out to mail shops or emails but they shouldn't have access to
customers security questions or account details so a view could be used to
provide them with the details they need while hiding the details that they
shouldn't be made commonly available or that shouldn't be made commonly
available now you also probably wouldn't want ordinary users seeing the link
columns in our tables they're interesting to us as developers but the
numbers at the end of this statement....
.....so the numbers there would just be confusing to other people and actually
the primary key field is also confusing so we can actually create
create a view that just returns the album names so going to something like.....
....
.....and
obviously at that point we are only getting the names now ideally I would have done a
case-insensitive ordering there because we once again got whipped jamboree in
heavens to Betsy out of order as far as most humans would be concerned right
down the bottom there now because a view doesn't actually exist in a way that a
table does we can actually delete the view and recreate it with the order by
Clause corrected so the command to actually delete the view would be drop
view type in the view name so album_list in this case and
incidentally you can also delete tables using the command drop table followed by
a table name but while deleting a view doesn't affect the data in the database
deleting a table obviously will and yes I've also done that by mistake as
well but now that I've actually deleted the view i can actually recreate it
going to paste the command in there to recreate it and noting that I've got
collate no case on the end there and I can do a select....
....and this time we've got things sorted in the right order you can see
here the lower case whipped jamboree is now where most humans would expected to
be sorted and that's just about everything we need to know to be able to
put a database in our program before i finish so there's just one more thing
I'd like I need to say about views now you may have noticed that when i
selected the jefferson starship albums from the view earlier i didn't have to
specify which name column I was searching on so I type....
....
...so that was the command I used if you haven't turned
headers on or you have to stop and start sql lite like i have used the command
to put them on again....
....so let's type that command again I'm just going to come up here and
drag it down and now I've done that you can see here its got name and a comma name
column 1 and title now because they were too named field in the Select
statement sql lite has renamed one of them so that the column name is a unique and
. schema will remind us of the command we used to create the views so if we type
schema again because of the clash sql lite automatically renamed the name
columns from the album's table to be name column 1 now not all database
systems do this and it's a good idea to explicitly named the columns when you
create the view if it's going to be named clash or potential name clash so
put that right what we need to do is drop the view and recreate it this time
giving the two name column a unique name so I'm going to just copy some
code here that both drops the view and recreate it again just to save a bit of
time so there's the code you can see that were initially dropping the view
and we're creating at this time
press ENTER that's been created and now when I do that command again
the Select command and incidentally I've used as after the artist and album
column names to provide a new name that the columns will be known as in the view
in case you're wondering what that was and if I paste this in and have a look at that
again we've now got artist and album track and title we no longer got name and name column 1
alright so that's the end of this introduction to databases in the sql
language in the next video I'm going to start going through show you how we can
use sql lite in our programs but before we do that theirs a little bit
of housekeeping that we're going to do in the next video I'm going to set you a
challenge and then after that challenge we're going to go through and start
putting this to use on an android application so see you in the next video