ok so let's look a bit more at querying the data including how we can make sure
that we get the data back in a sensible order if as I mentioned ordering of rows
is undefined in a relational database so when we display all the artists records
actually come out in the same order each time i can do that again select....
.....so we get the same every time we do we get the same order and that's
because we have a primary key so the records will automatically be selected
based on the ordering of the primary key note that the actual order of the record
in the database is undefined and if we didn't have a primary key that will be coming
out in an undefined order now we can actually specify a different order in
our select statement by using an order by clause so we can type.....
....you can see now that the
records have appeared in alphabetical order and I can do exactly the same for
the album's.....and you notice right down the bottom
the two black beards Tea Party albums
heavens to Betsy and whip jamboree are out of order
that's because they start with lowercase letters now you can actually ignore case
by using the collate no case clause so we can do.....
....and you can see there are the ID 430 about 80% towards
the bottom of the screen which is whipped jamboree in that appears with
the other albums beginning with W in other words instead now ignoring case
when it's actually returning results now it is also possible to specify ascending
or descending order using the keys keywords asc desc or ASC or desc
respectively which stands for ascending or descending order
so we can do select....
....
.....that's fine but what if we want to group albums together
so that all the albums by each artist appear together well the order by clause
can actually contain more than one column so we can do something like
....
....and what that does it sorts first by artist ID and then by album
name so all the deep purple albums artists 196 you can see a group of them
here with the number 196 at the end of the list of data they appear together near the
end of the list you can see it also starting with burn this one up here
starting burn their and ending right down here with who do we think we are
remastered edition
ok time for another mini challenge
the challenges is to list all the songs so that songs from the same album appear
together in track order so that's the challenge have a go at doing that by
type in the sql code that necessary to achieve that pause the
video and when you're ready to see me type it in start the video again so pause
the video and I'll see you when you get back alright so to achieve that what we
need to do to list all the songs so that song from the same album appear together
in track order we type....
....there you go
so now the 11 songs from The Black Keys albums attack and release appear together
as you can see right at the end of the list and you can check that you wanted
to by typing.....
.....and then we could do something like....
....
....there you go you can see with a quick scan up to the list shows that the records
are grouped by album ID the last column and in track order within an album the
second column now having to run separate queries like that is a bit grubby though
let's see how to relate the tables together so that we can get a list of
songs that include the album the appear on as well as the artist that produce them
now to do this we need to use the SQL join clause that used to join tables
together now keeping data normalized so the tables only contain information
that relates to a single thing song album or artist in our example is a
fundamental part of relational databases and by doing that and then joining the
tables back together you get a great deal of flexibility in how you can query
and manipulate the data now remember that the songs table contains a column
holding the album ID and the album table has an artist ID field and these are
used to provide a link between the tables
don't worry about how those ids got into the tables at this stage we just
interested in using them to join the tables at the moment
so you can see here on screen how the album column in the songs table provides
a link to the album table the first 10 songs all belong to the album whose ID
is one tales of the crown and the next set of songs belong to the masquerade
ball
the artist column in the album's links to the artist table so those first two
albums are by axel rudi pell and the album crimes of passion is by pat
benatar and night flight is by band called budgie alright so with that said let's
actually join the tables in sql and see how this is going to look so I'm
going to do I'm actually just going to do a . quit and then I'm just going to
clear the screen and see notice how the up arrow is working from here it's just in
sql lite three for some reason it's not working maybe it was weird characters but
so I've gone back into the database again and just so starting off with a clean
slate so let's let's now use this select statement and add a joint clause to link
the songs and albums i'm going to do is type....
....
....press enter their so the first thing to note is that have specified which table the
columns are in when selecting them and probably what I should have done is explained that
while that select statement was on screen because of course now i can't
bring back or can i I can scroll up
so what I do is I just type it again and again you shouldn't have this scenario
should be able to do an up arrow and it should work but for some reason my Mac
is not doing what i wanted to do so albums....
....
alright so leave that on before I press enter this time getting back to that statement
the first thing to note is that i've specified which table the columns are in
when selecting them so track and title are in the song table and you notice how
I use songs . track and songs . title now name comes from the album's table so
specified that as albums . name if there's no ambiguity you can actually
leave off the table name so what I could have done I will just press this to see the
results again so i could have also written this as select track title name from
songs join album on song.albums
...so I could have done it that way if there's no ambiguity
with the names but it is a good habit to always specify the table
name especially in code now living in it off is a useful shortcut to save
typing when working interactively like this but i'd say always prefix the field
with the table name in your code now some albums have a sort of subtitle so
if the table was modified to include a title column then that query will no
longer work because it would know which table the title column should come from
and note though that we can't leave the table name off when using the ID column
so we just went back hear the end of it instead of putting albums.id if I
just put _ID there and press semicolon press enter we get error no
such song and and sorry no such column song . album now that was
actually different message that was because i accidentally type song their
so just going but I shoudl
be able to copy and paste i might do it that way it save a bit of time so
the original request was songs should be have been songs.album because of course songs is
the name of the table was going to show you as if I just type it like that without
actually putting the albums . before the ID and press enter now we get the error
that i wanted to show you the first time error ambiguous column name
_ID and that's because both tables have a column of that same name _ID
and then sql lite doesn't know which one you mean
so you need to specify it there and just going to copy that again paste it so i
would go back and make that songs and make that album I should say . _ID
and get our data back there are different types of joins the most common being an
inner join and join as I have used here is really a shorthand for inner join what
I'll do is I'll retrieve the full command that included the table names
then used include the word inner so i'm going to type....
....
....now
keep in mind that not all database systems will allow you to leave off the
word inner so it's worth always using it and i'll just run this to make sure it
works now looking at the result of that query we can see that the song just walk
in my shoes is from the album super lungs and permanent vacation is from an
album of the same name and so on so just paste this code back in again so again
this select statement follows the same pattern as we've been using up until now
instead of select from songs we're doing select from songs inner join albums
we then have to tell sql lite which columns are involved in the join which
is what the on part does it says to relate the rows and songs
to those in albums where the songs tables album column equals the album
tables ID column and if you really want to we can actually tact the order by
clause on the end of that if we want to sort the data so i could come to the end
here and I could type.....
.....that's actually
returned a heck of a lot of results as you can see there but it actually
went through and do it really quickly if I wanted to I could just scroll back up
and have a look at some of the other data that has been returned but you can see
there's a lot of data and sql lite has manipulated that and returned it very
quickly
alright so I'm going to finish the video here now we'll continue on working with
sql lite in the next video