All right.
So we inserted all of our data.
We have our schema ready and all of our tables created.
Now let's get down to business.
Let's do some stuff with it.
So I'm going to break every problem that I'm going to give you.
And there's quite a few here into its own video.
Hopefully we'll make them a bit more manageable and you won't want to go to sleep or drown yourself
after watching it.
So the first thing that you'll see here, the format of these is that I'm going to give you the results.
It's much easier just to give you a picture of them, rather than trying to describe in a bunch of text
what it actually means.
It's much easier just to show you.
So first thing I should say is that these results are truncated.
These are only for the first 15 or so.
I used a limit, but I want results for everything.
It's just too hard to fit them onto.
The screen would be very, very tiny.
But the key thing is that we have a title for a movie or excuse me, for a series and a corresponding
rating.
So we're going to need to join those tables together.
We need to join the series table with the reviews table, because right now they're not in the same
table.
We're storing the titles separately from the ratings.
So let's do this now.
We'll go over to Cloud nine and the first thing we'll do is just start simple.
We'll select Star.
And we'll just start from series, which we've already done.
Just like this.
One thing I should highlight before we go too far.
I did deliberately add some series that don't have any reviews so that nobody has rated them yet, and
that was deliberate so that we can work with that later.
However, in this example, I only want to see movies.
I keep saying that I only want to see titles of series that have been rated.
So we don't have any nulls in here, which is a bit of a hint.
So select star from series and on its own we get this.
Now what we want to do is join.
So if we do a separate select star from reviews.
We want to join.
Basically where this ID of the series is the same as the series ID.
So these first let's see, five here.
One, two, three, four, five.
Correspond to Archer, which is what we see here.
So to do that, we don't need to separate.
Select.
We want to do select star from series.
We'll do join with reviews on.
And where do we want.
We want to join on series.
Dot ID.
Equal to reviews.
Dot series.
Underscore ID.
Just to reiterate where this idea of the series actually it's going to be easier if I do it here.
Where this series ID is equal to this ID of the series table.
Okay.
And all we want are title and rating.
But we'll start with just getting everything.
And let's see if we're in business yet.
Well, it's a little hard to tell because we have this formatting issue where we have so much data,
things are being cascaded over.
But if we look closely, we can see we have Archer 8.0, Archer 7.5, Archer 8.5, and so on.
And if we scroll down, you can see that not every single TV show is here.
Like, you'll notice the IDs jump seven, eight, nine, General Hospital, ten halt and catch fire,
then goes up to 13.
So whatever 11 and 12 are, we don't have any reviews.
So that's good.
Not that we don't have reviews.
That's not good.
But they're not showing up, which is what we want.
So now we'll just whittle this down to title and rating, and that's all we want and I'll format it
a little bit nicer.
Here we go.
Hit Enter.
And now we get all of our data.
On the left side, we have title.
On the right side, we have corresponding ratings.
So hopefully what we just did there seemed relatively similar to what we did in the last section where
we're writing these joints.
What we'll build up to in this section is doing a double join where we have another joint statement
because we want to connect all three of our tables.
But for now we're working with one table or two tables at a time.
Okay.
So we're done with that one.
Let me just put a comment up here first.
Let me just do challenge one and now we'll move on.
Next video we'll pick up with challenge two.