And I'm back here with a solution or multiple solutions to go over these joint problems.
So again this is a one to many relationship students and papers and the first thing we're going to do
is go over to cloud 9.
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.
Students papers as well.
OK so we know we need to create two tables create table students and we're going to leave it empty for
now and then create table papers also leave it empty.
Great.
So next what we put in so we'll start with students simple.
It's an idee that we said as a primary key integer which means it should also auto increment and thus
we want to do it manually but we don't.
And then first name is have our char.
So we have ID Ent. autoincrement primary key and then we have a 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 and student ID.
Let's start with just title grade student or score ID perfect.
And I'm noticing you know I capitalized 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.
This lower case ID.
So you've got title grade student the title is of our char.
So do 100 grade isn't it.
And then we have student I.D. and student ID is a foreign key referencing students dot ID.
So it's just an integer and this sometimes trips people up that we just right student ID in.
And then the next line is a foreign key the field that is a foreign key is student ID.
This right here.
And when you just say what it references.
So it's referencing Students table the ID field.
OK.
And we could also indented 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 reading anything but you could put that there.
OK.
The next step is now just to create them.
So before I go too far I did catch this.
I have an extra semi-colon 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.
I have customers and orders that's fine but I papers and students.
OK.
And we haven't done this a while.
How about we describe students.
So we now have autoincrement and their primary key and that's for students.
And what about papers we do describe papers where you can see we have title and grade and then student
ID.
OK.
So next step 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 first thing to do was to print this join 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 would be the easiest way we could also do with the right join if you want to which
will take a look at put an inner join 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 1 select star from let's do it on a different line from students.
Join papers on students ID schools papers.
Student ID.
And let's be explicit with our joint 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 SS Carlos and his one essay and we have the grades now.
We don't want I.D. 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 great title and great.
OK.
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.
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 too.
Just going to be order by grade.
And what order is it.
Well it's sending
Now we do it now we have the correct answer.
The other one is correct to a 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.
No 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 it right Turing.
Nothing wrong with that but I think the inner join us easiest although it's it's 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.
OK so I'll just write Allt solution.
OK.
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 no 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 no.
So let's do this now call this
let's call this problem too and start with select start 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 that 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 her papers Raj.
No no no.
LISA No no no.
So we're getting every single student Kalos Samantha Raj Carlos Lisa and if they have a matching 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 in grade
just like that.
Varify OK.
And is there any sorting here.
No.
And we're done.