And I'm back here with a solution or multiple solutions to go over these joint problems.
So again, this was a one to many relationship students and papers.
And the first thing we're going to do is go over to cloud nine.
And I'm just going to work in the same database, but I'm going to make a new file just because it's
getting crazy.
And I'll call this one student papers SQL.
So we know we need to create two tables, create table students, and I'm just going to leave it empty
for now and then create table papers.
Also leave it empty.
Great.
So next, what do we put in them?
So we'll start with students.
Simple.
It's an ID, but we said it's a primary key integer, which means it should also auto increment unless
we want to do it manually, but we don't.
And then first name is a var char.
So we have ID int auto increment primary key and then we have first name which is just var char and
we'll do 100.
That's a good even nice number.
So that's it for students.
But then we have papers.
So papers has a title grade in student ID.
Let's start with just title grade student underscore ID.
Perfect.
And I'm noticing I capitalize this.
It doesn't really matter, but it's going to be annoying to remember that.
So I'm just going to put it back as lowercase ID.
So we've got title, grade and student ID.
Title is a bar chart.
We'll also do 100.
Grade is an int.
And then we have student ID and student ID is a foreign key referencing student's dot ID.
So it's just an integer.
And this sometimes trips people up that we just write student ID.
INT And then the next line is foreign key.
The field that is foreign key is student ID this right here and we need to say what it references.
So it's referencing student table, the ID field and we could also indent it like this because this
has to do with this.
And then if we wanted to, which you did not need to do.
But this is where we could add.
On Delete Cascade, which you may.
And we saw in the last video or two videos ago, it's not necessary here.
We're not deleting anything, but you could put that there.
Okay.
So the next step is now just to create them.
So before I go too far, I did catch this, I have an extra semicolon there, get rid of that.
But we'll create our students table and we'll create our papers table.
Everything looks good.
And if we do show tables, I'm still working in our same database, so I have customers and orders.
That's fine, but I have papers and students and we haven't done this a while.
How about we describe students?
Cool.
So we now have auto increment in their primary key and that's for students.
And what about papers?
If we do describe papers.
But you can see we have title and grade and then student ID.
So next up is to get our data in there.
And I said that you definitely didn't have to do this yourself, so I'm just going to put it in this
file so you have a solution file.
But it would be annoying to type this.
I totally I mean, I had to type it.
I totally get it.
So paste that in perfect.
So now we can do a select star from students and we should see our five students and do a select star
from papers and we see our five papers with student IDs.
Great.
So the first thing to do was to print this joint table where we have first name, title and grade,
nothing else, first name, title and grade.
Where and what's our joint condition here?
Well, basically where the student ID is equal to the student ID in the paper, and this we could accomplish
through an inner join.
That would be the easiest way.
We could also do it with the right join if you wanted to, which we'll take a look at.
But an inner joint is the simplest way and I'm going to do an explicit inner join.
So that means select.
And let's start with star from and let me put a number here.
Exercise one select star from.
I'll do it on a different line from students.
Join papers on student ID equals papers dot student ID and let's be explicit with inner join just like
that.
So if we take a look, you see, we have the right data, right?
We have Caleb and his two books, Samantha or two reports.
Samantha her two essays.
Carlos and his one essay.
And we have the grades.
Now we don't want ID and student ID, so we just need to get rid of that.
And the simplest way is just to.
Explicitly say what we want.
So grade, title and grade.
Now we do it and we're good to go.
There's one thing, and this is my fault for not mentioning in the instructions.
I did order this and totally fine if you didn't do it.
If you did catch it.
Congratulations on looking at that.
I didn't I forgot I did this late last night when I created these slides.
I'm recording it the next morning.
So this was ordered by grade.
So that's simple to just going to be order by grade.
And what order is it?
Well, it's descending.
Now we do it.
Now we have the correct answer.
The other one is correct too, I promise.
The only thing that I'll address here is that I mentioned that you could also do a write join.
And why is that?
Well, let's just verify, see if I'm crazy.
Nope.
It still works.
Good to know.
I'm just kidding.
But the reason that it works is because if we go back to our slides way back when we do an inner join
here between students and papers.
All of our papers have a student they belong to.
So basically this section is the same as this entire section here, the same as this.
If we pretend that left join doesn't exist.
Ignore that slide.
This intersection is the same as this because every paper has a student ID in it.
So you could also do a right join.
Nothing wrong with that.
But I think the inner join is easiest, although it's its easiest because it's just this is how I think
about joints most of the time.
But it's not any easier because it's just a single line or a single word that we're changing.
So I'll just write alt solution.
OC Moving on.
Print this.
So very similar, right?
Caleb, Samantha, their respective books.
But then we have Raj and Lisa who didn't write any reports or any papers and we have null in place.
So this is a left join.
And the way that we're going to do that, if we go back to our slides again, we're going to grab everything
from students and then any associated papers, but some of them don't have papers and those will then
be filled in with null.
So let's do this now.
Call this
let's call this problem two and we'll start with Select Star again and then we'll alter that from students.
And then we need to do a left join papers on students that ID equals papers dot student ID and we'll
leave it at that to start.
And you can see it's working.
We've got Caleb and his stuff.
Samantha, her essays or her papers.
Raj.
No, no, no.
Lisa.
No, no, no.
So we're getting every single student.
Caleb, Samantha, Raj, Carlos, Lisa.
And if they have a matching join condition in the papers table, then we also get their paper data.
But if they don't, we have.
No, the only thing left is to whittle it down to first name, title and grade.
Just like that.
Let's verify.
And is there any sorting here?
No.
Then we're done.