alright so i'm actually quit out of the sql lite and I've started it up again I'm
just going to type this command in and you can just see that there's actually a
tremendous number of Records here that have appeared on the screen and
scrolling up as you can see there's tons of them
now depending on the speed of your Mac you might find it a lot slower to output
thease and you can actually use ctrl z to stop the listing and that also let you
see some record from the middle of the list rather than just the same view from
the end now you can do the same thing on windows with ctrl Z but there's
apparently a bug that causes ctrl z to quit sql lite as well so you might
have to start sql lite again with the sql lite 3 space music.DB to get back
into the database if you're on windows and you press ctrl z on linux you
might find as well that you should find ctrl z will stop the listing as
well
alright so let's just changed around a little bit and it might
actually look neater the other way around if we list the album name before
the song title so to do that we type....
....
....as you
can see that does look a little bit neater so you're free to return any
columns you want in any order you don't have to keep them in the same order that
they appear in the table nor in the order the actual table are joined either
alright time for another mini challenge
alright so the challenges is to produce a list of all the artists with their
albums in alphabetical order of artists name so go away and see if you can figure out
the sql code for that pause the video and when you're ready to see my solution
come back toward and i'll show you how to do it so pause the video now
alright so the way to solve that would be....
....
and because I can't use my up arrow i'm going to type in in again so...
....
....okay there you go that's the solution now what if you wanted to find out which
artist produced a song though now the songs table doesn't have any direct
links to artists and we just go back and look at the relationship between the
tables again it will help us to see how we can get the artist for a song alright
here's a representation just zoom in so you can see that little bit better so
if we have a look at the relationships between these tables again that's going
to help to see how we can get that artist for a particular song now although
we can't go directly to an artist from a song record we can find out which album
contains the song and from there it should be easy to find out who the
artist is and we do that by joining songs to albums and then albums to
artists let's have a go at typing the code for that
so we type.....
....
....so that's actually quite a lot of
statements you can see so it was good that sql lite allows us to split
over more than one line and doesn't try to execute the statement until it finds
an ending semicolon
so what I've done here is just chain the inner joins together so we have
songs inner join albums inner join artists now of course we have to specify
which columns to join on but hopefully the syntax as I've shown up there does make
sense and we'll just run this to make sure it works and you can see that we've got
the results that we're looking for the actual artist who produced the song so
the Select statement is pretty flexible we can include as many columns as we
need joining tables as we need them and then sort of as many columns as we need to
produce decent output and you can also nest select inside another select
statement but if you get to the stage of needing to do that then you really
getting into more advanced sql and that's above what we have time to cover
in this course
just keep in mind that the sql language is very powerful and really
quite simple considering what you can do with it now one thing that i haven't
mentioned so far is that the ordering of the clauses is important so you can't go
putting the order by Clause before the joins for example the order is strict
in that regard so the order that we've been doing things so far is actually
correct way to do it now if you want to include a where clause it has to go
before the order by Clause let's restrict the previous query to just the
album do little which has the id 19
alright so what I actually did was I actually copy and pasted off-screen so
you ignore those little dots and the greater than sign it's because i pasted
it all in the at the same time then it's actually come up with what would have
happened if we had press enter but the point is that what i've done is i put
the where clause you can see before the order by clause and I've got a semicolon
on the end so this should work when I press enter so we're only getting the
rows back for the album do little which again have the ID 19 that's where the
where clause came in now splitting the command over several lines like this
does make it easy to understand but it does make calling the command back a
little tricky
you have to do it line by line so the trick would be there if this up arrow was
working for you is to actually keep pressing the up arrow to call back the
first line in the statement the select line here then press enter then use the
up arrow to call back the next line and so on
I can't actually show you that because as I've outlined its not working
properly on the mac but what i can do is paste in part of the command like so
then what i'm going to do is add the claus again so...
....
....so as you can see the structure of the Select statement is
quite straightforward you specify the columns that you're interested in you
join any other tables that are needed filter the selection using a where
clause and finally you order the results now sometimes you have a rough idea of
what you want to find but you don't know what exactly
or perhaps you're interested in several rows have similar but not identical
names now the sql where clause can use wildcards to match on partial
strings to cope with these situations now I'm going to actually change sql
commands that span a few lines
in this next bit and as you've seen editing the statements from within the
sql lite show there's a bit fiddly a useful tip when working with the
sql lite shell is to keep a text editor handy and copy and paste between the two
windows and you also need to know to copy from that terminal command line if you
want to take the output from the . schema command and paste it into your
code going to digress slightly and show you how to do that so i can just come up
here and actually just copy these commands that I've type in here selected here
I do get a line at time I can just select the part you've seen
me doing this in the previous videos I can actually copy that and what i can do
is i can drag that down with my mouse and put into this line here and you can
see that's been added or I could have done that or I could have right-click it
and select and copy and paste which is also saw me do previously now on Windows
you can copy the selected text into the clipboard after you've selected by
pressing enter and on linux you need to click the right mouse button and choose
copy from the context menu that actually appears so it does depend on the
operating system as to how you go about copying that the bottom line is at that
point the text is now in the clipboard and you can paste it into the text
editor that you want to use and manipulate the sql statement on the
case of here what I'm going to do is I'm just going to copy all of this
here and i can do a copy here if I could do it that way and i can open
the text editor in my case I'm just going to open text edit but you can use
notepad or a linux editor as appropriate and I can just paste it in there and
notice that if we just zoom in there we might have to clean a little bit of this
up so we need to just delete this part here with those extra parts were added
by sql lite when enter was meant to be pressed and I just press enter the
relevant place to just to build up the clause like that and eventually I got the
thing working and ready to be manipulated and what do is I'm just
going to put this to the side so we can see both things at the same time for now
so you can see we're now ready to actually be able to work in both windows
so just gonna close that off and come back here and obviously i can just
select this here i can copy it and i can paste it directly in
because it's got a semicolon at the end I can press enter and i can get the
results that I want and I can go back to my text editor and have been
manipulating anything that i actually need to do so this is also useful when
you're working you're going to be working with Android code because you'll
be typing in a command interactively to make sure that it works then you'll be
taking this code which i've copied into my in this case and text edit which is a
standard text editor that comes with the mac but you might also be pasting that
into android studio into some java code as well so it's a good sort of thing to
know how to do because you'll be doing that that would normally be the sequence
of things you test to make sure that the queries the sql code that you're
typing is correct and valid in sql lite first and then once you sure that
it's working
that's when you copy the code and then put it back in Android studio in the
the relevant java file
alright so I'm going to finish the video here now in the next video we're going
to talk about the wild-card where I started telling you about the fact that
you can actually match on partial strings so we'll actually have a look at
how to do that in the next video