All righty.
Okey dokey.
Let's try writing some queries using our three tables.
So in this video, we're going to start simple.
I would like for us to write a query that results in this data.
Now, all of these that you see, all these screenshots only show a truncated version of the results.
I limited this, I think to 15 or so because if we had all of the results, it would just be the world's
tiniest text.
You wouldn't be able to see it.
So this is a subset.
I don't want you to limit it necessarily, but what I do want is to recreate the same columns, the
same relationship and information, but you don't need to limit it to 15 rows.
Okay, So on the left hand side, we have title.
On the right hand side, we have a rating.
So this is every review left for every title.
What is the title and what is the rating?
We don't have anything about who left it, what is their name, anything like that.
It's just title and rating side by side.
So somebody gave Archer an eight and then someone else did a 7.5.
Someone else had an 8.5, seven seven and an eight nine.
So how would we go about doing this?
We know that we need to do a join because we don't have these two pieces of information in the same
table.
So we have to join them and we'll start by just getting title.
We'll keep it simple.
We could do something like select title from series.
We know that we want that and that will just give us the titles.
Simple enough.
Okay, the next thing we need is rating.
But that's coming from a different table.
Of course, that is going to be coming from select rating from and then reviews.
There's all of the ratings.
But what we want to do is take let's just select staff from reviews, everything from reviews, and
we want to use the rating and match that row up to a series where the series ID matches the actual ID
from the series table.
In other words, take this 8.0 and we have a series idea of one.
We want to put that alongside the series that actually has the idea of one, and that is going to require
a join.
And we don't need to worry about a left or right join.
We want the overlap.
So it's as simple as select title from series and then join reviews on where we're doing that.
We're doing it where the Let's do series ID is the same as the reviews dot series ID.
Okay.
And instead of just selecting title, let's just select everything so you see what we're working with.
Looks good to every review that's been left.
We now have the rating the series ID in this case, one matches along with the series that has that
ID and we get the title and the release year in the genre, but all we want is title and rating.
So let's slim that down just like that.
Try running this.
And there we are.
We see every rating that's been made for every show that we have.
So every title alongside every rating, we're not doing an average.
We're not doing a some we're not involving users or reviewers.
It's just the score with the title and that's it.
That is a solution to this first challenge.
You could have written in or join.
You could have done a different order, started with reviews and then joined on series.
But at the end of the day, we want series and reviews joined where the series ID is the same as the
reviews series ID.