alright so as I mentioned at the end of the last video we finish the
introduction to databases in the sql language and what we're going to start
working on the next video is how we can use sql lite in java programs but
before we actually get to that video let's get through into a few more things
we're going to start with backing up the database again I'm going to type....
.....so what I suggest you do
now is experiment with the commands that we've used to search for different
groups of records from the three tables and also try creating queries that use
joins as well as they already joined view that we created and actually there's not
much point showing you how to back up the database if I don't show you how to get
back should you need to I know we have restored it previously but we're going
to restore this music one now database is restored from the backup as you saw
using the .restore command but if we restore right now you have no real
indication that it worked so let's actually trash some songs first so that
we can confirm that now most of the album's have fewer than 50 tracks so we
can delete all songs whos track number is less than 50 and that should leave us
with very few records so let's go ahead and do that i'm going to type....
....
....obviously we've got too much smaller list than what we had before and in
fact we're left with songs from only two albums and that is easy to see if we use
the view so if you look at the views so....
...you can see they're clearly there's only two now as you saw for the
condition in the where clause you can use less than greater than less than or
equal to and so on just like you'd expect
now sql uses less than or greater than or not equal to which makes
sense when you read it and avoid having to introduce another symbol other than
that the comparison operators are the same you'd normally expect in Java so we
can drop a song from the list
only selecting songs with a track number not equal to 71 so we could do that with
select....so you can see at that point
now recycled vinyl blues which was showing in the previous list is now no
longer showing now the other thing you can do which we haven't talked about is
you can include functions in the Select statement now i'm not going to go into a
range of functions available but one useful one is count so you can do....
....
....you can see we've got 24 entries for songs 439
records for albums and 202 for artists
alright so let's now restore the database and actually see how many records you've
got back so do . restore music back up 2 remember there's no semicolon because
it's a sql lite command because it starts with the . let's do the
equivalent commands now the select....
....
....so clearly
the restore worked alright so let's finish this video now with a challenge
for you to help get you started
so I got a number of things for you too
come up with the sql commands to return these the results that I'm asking
for so first one here number one select the titles of all the songs on the album
forbidden and second one repeat the previous query but this time display the
songs in track order now you may want to include the track number in the output
to verify that it worked okay number three display all songs for the band
deep purple number 4 rename the band mehitable to one kitten hope I pronounce
that right and note that this is an exception to the advice to always fully
qualify your column names set space artist.name won't work
you just need to specify name and you'll see that when you give that a go
number five check that the record was correctly renamed that you did in part
four
continuing on number six select the title of all the songs by aerosmith in
alphabetical order include only the title in the output number 7 replace the
column that you used in the previous answer with count title in parentheses
to get just a count of the number of the songs number of songs number 8
search the internet to find out how to get a list of the songs from step 6
without any duplicates number 9
search the internet again this time to find out how to get a count of the songs
without duplicates and hint it uses the same keyword as step 8
but the syntax may not be obvious and number ten repeat the previous query to
find the number of artists which obviously should be one and the number
of albums so that's it that's your challenge 10 things for you to have
a go at so pause the video and go away and see if you can figure those out and
when you're ready to see me come up with the answers start the video again so
pause the video now and i'll see you when you get back alright so how did you go
hopefully you managed to figure it out
let's have a go so start the first one and if you recall the first
challenge was select the titles of all the songs on the album forbidden to do
that we do.....
....
alright so that's all the title from the album forbidden number two was to
repeat the previous query but this time display the songs in track order and you
may want to include the track number in the output to verify that work ok so
let's type that up....
.....
...so we got the equivalent to the previous query
but this time sorted in track order and you can see we've got the track order on the
screen they're verifying that worked all right number three display all songs for
the band deep purple alright so....
....
....you can see we've got
quite a few entries there for deep purple
alternatively also you could have used the artists_list view that
would have been acceptable as well so that would have been much easier
actually just select....
....
alright continuing on now number 4 we want to rename the band mehitable
to one kitten and this was the one where I mention that there was
an exception to the advice to always fully qualify your column
names because set artist.name won't work
you just need to specify name in this scenario
so to do that we do update.....
....
......so now we can confirm that confirm the update working in otherword
select star.....and we can now
see that we've got an entry for that so that worked okay and checking it was ok
the records correctly rename which was actually challenge 5 just to be clear
alright so that's one we've just done so moving on now number 6 we have to
select the titles of all the songs by aerosmith in alphabetical order
including only the title in the output that's actually quite simple one so....
....
.....
....alright the next one we want is you want to replace the column that you used
in the previous answer with count title to get just a count of the number of the
songs instead of the actual titles that would be....
.....we should
get the answer 151 their 151 as you can see now note that
the in this particular case the order by Clause is redundant here because we're
doing a count we don't need that
however living in there won't caused the problems so if you have left it in there
that's fine
alright next we're going to a couple of the ones that require a bit of research
number 8 search the internet to find out how to get a list of the songs
from step 6 without any duplicates hopefully managed to find that so to do
that the command is select....
.....
....and you can see theirs no longer duplicates their
number 9 search the internet again to find out how to get a count of the songs without
duplicates and hint that i gave you was it uses the same keyword as step 8
but the syntax may not be obvious so to do that one.....
....
.....
....
.....because we're going on
the actual song title that's from artists_list where....
....o that should actually give us the same count before and 128 that's
better
alright so that was number nine and the last one was to repeat the previous
query to find the number of artists which obviously should be one and the
number of albums so the first one already type up there so we're just
going to copy and pasting again this is the one that I accidentally typed in the
wrong place
so that's going to give us the paste it in that's going to give us the number of
artists which obviously in this case should be 1 because we got our where
clause that is looking for aerosmith you can see that returns 1 then the last one the
number of albums by aerosmith effective we're going to select...
.....
....that gives us the answers 13 the number of unique albums by this artist
alright so that's actually it so we're actually done now with the sql lite
introduction and fiddling around and playing around a little bit with sql
lite so we can end the video here in the next one we're finally gonna get to the
stage of going back to android studio and we're going to actually start work
on our first sql lite android application see you in the next video