(bubby upbeat music)
(keyboard clicking)
Okay, 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 a row is undefined
in a relational data base.
So when we display all the artists' records,
they actually come out in the same order each time.
So I can do that again, select star
from artists, so I get the same,
every time we do it 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.
Now note that the actual order of the records in
the data base is undefined and if we didn't have
a primary key, they'd 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 it can talk select star from artists, order by, name.
Semicolon on the end as always.
And you can see now that the records
have a appeared in alphabetical order.
And I can do exactly the same for the albums,
select star from albums, order by, name.
You notice right down at 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 lower case letters.
Now you can actually ignore case by using the
collate no case clause.
So what you can do is select star from albums,
order by name, collate no case.
And you can see there the ID four thirty about
80 percent towards the bottom of the screen,
which is "Whip Jamboree" and that appears
with the other albums beginning with W.
In other words, it's now ignoring case when
it's actually returning results.
Now it also possible to specify ascending or
descending order using the keywords asc or desc,
or A S C or D E S C respectively, which stands
for ascending order, descending order.
So it can do, select star from albums,
order by name, collate no case, now we can talk
D E S C for descending order.
And 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 if we do something like select star from albums,
order by artist, comma name, collate no case.
And what that does, it sorts first by
artist ID, and then by album name.
So all the Deep Purple albums, artist 196,
and you can see a group of them here with
the number 196 at the end of the list of data,
now appear together near the end of the list.
You can see, so I started with Burn, this one up here,
starting with Burn there, and ending right down
here with Who Do We Think We Are remastered addition.
Okay, time for another mini challenge.
The challenge 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 typing in the SQL code
that's 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 to achieve that, what we need to do
to list all the songs so that songs from the
same album appear together in track order,
we talk select star, from songs,
and order by album, comma track.
Semicolon on the end, and there you go.
So now the 11 songs from the Black Keys album
Attack and Release, appear together as you can see
right at the end of the list.
And you can check that if you wanted to
by typing something like select star from albums,
where underscore ID equals four three nine,
and then we can do something like, select star
from artists, where underscore ID equals one three three.
And there you go.
You can see with a quick scan up the list shows
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, so let's see how to
relate the tables together so that we can
get a list of songs that include the album
they appear on, as well as the artist that produced them.
Now to do this, we need to use the S Q L JOIN clause,
that's used to join tables together.
Now, keeping data normalised so that tables
only contain information that relates to
a single theme, song, album, or artist
in our example, is a fundamental part of relational
data bases, 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.
Now don't worry about how those IDs got into
the tables at this stage, we're 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 song's table provides
the link to the album table.
The first ten songs all belong to the album
who's ID is one, Tales of the Crown,
and the next set of songs belong to
The Masquerade Ball.
The artist column in albums 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 Nightflight is by a 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 what I'm going to do, I'm actually just going
to do a dot quit, and then I'm just going to
clear the screen and notice how the up arrow
is working for me hear, it's just in SQL
light three for some reason, it's not working,
there's weird characters.
But I've gone back into the data base again,
and just sort of starting off with a clear slate.
So let's now use the select statement and add
a JOIN clause to link the songs and albums.
So what I'll do is, talk select, space,
songs dot track, comma, songs dot title,
comma, albums dot name from songs,
and here's the JOIN clause albums on songs
dot album, equal albums dot underscore ID.
Press enter there.
So the first thing to notice that I've
specified which table the columns
are in when selecting them.
And probably what I should have done is
explained that while that select stamp
was on screen because of course now I can't
bring it back, or can I, I can scroll up.
So what I'll do, is I'll just type it in again.
And again, you shouldn't have this scenario,
you should be able to go up arrow and it should
work, but for some reason my MAC is not doing
what I want it to do.
So albums, dot name, from songs, JOIN albums on
songs dot album, equals albums dot underscore ID.
Alright, so I'll leave that on before
I press enter this time.
So getting back to that statement, the first
thing to notice is that I've specified which
table the columns are in when selecting them.
So track and title are in the songs table,
and you notice how I use songs dot track,
and songs dot title.
Now name comes from the albums table, so I've
specified that as albums dot name.
Now if there's no ambiguity, you can actually
leave off the table name, so what I could have
done, I'll just press this to see the results again.
So I could have also written this as select
track title name from songs, JOIN albums on someday
albums, someday album I should say,
equals albums dot underscore ID.
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.
The leaving it off is a useful short cut to
save typing when working interactively like this,
but I'd say always prefix the fields with
a table name in your code.
Now some albums have a sort of subtitle,
so if the table is modified to include a title
column, then that query would no longer work
because it wouldn't know which table the
title column should come from.
And note though, we can't leave the table
name off when using the ID column.
So we just went back here at the end of it,
instead of putting albums then ID, if I just put
underscore ID there, semicolon, and then press
enter, we get error, no such column, song dot album.
Now that was actually a different message,
that was because I accidentally typed song in there
so I'm going to, should be able to copy and paste,
I'll do it that way, that might save a bit of time.
So, the original request was songs, should have been
songs, not album, because of course
songs is the name of the table.
What I was going to show you was if I just
type like that without actually putting the
albums dot before the ID, and press enter,
now we get the error that I wanted to show the first time.
Error, ambiguous column name, underscore ID.
And that's because both tables have a column
of that same name underscore ID, and SQL light
doesn't know which one you mean.
So you need to specify it there and I'm
just going to copy that again.
Paste it, so I'll go back and make that songs
and make that albums I should say, dot underscore ID.
And we get our data back.
Now there are different types of JOINs,
the most common being an inner JOIN and
JOINers I've used here is really a short hand
I've used for inner JOIN.
What I will do is I will retrieve the
full command that included the table names,
then use, then include the word inner.
So I'm going to type select songs dot track,
songs dot title, albums dot name, from songs,
inner JOIN, inner space JOIN that should be,
on, sorry inner JOIN space albums on songs dot
album, equals albums dot underscore ID.
Now keep in mind that not all data base
systems will allow you to leave off the work 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 we'll 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 JOINed albums.
We then have to tell SQL light which columns
are involved in the JOIN,
which is what the on part does.
It says to relate the rows in songs
to those in albums where the song's table's
album column equals the album table's ID column.
And if you really wanted to, we can actually
tack an order by clause on the end of that
if you want to sort the data.
So if you come to the end here,
and I could then type order by,
and name of the table, albums dot and then the
column we want, name then songs dot track, semicolon.
That's actually returned a heck of a lot of
results as you can see there, but it
actually went through and did it really quickly.
But if I wanted to, I could just scroll back up,
and have a look at some of the other
data that's been returned.
But you can see that there's a lot of data and
SQL light 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 light in the next video.