All right, Let's write a solution for this joint exercise.
To start, we need to create our tables.
We'll start with students.
Very simple ID and first name.
So create table.
Students.
And we have an ID, which is an integer, but I also want it to be primary key and we'll add auto increment
as well.
And then we have first name which will just be var char.
I don't know, 50.
That should be safe for most people's first names to fit within 50 characters.
And then we have our second table papers, which has a title for paper and a grade that to number.
So create table papers.
And we have I guess I didn't say we need an ID, so we don't need to bother with that, but it's always
a good practice to put an ID in.
But that's okay.
Well, if title and grade title is a bar chart and in general it would be good to make first name not
null and title not null, but just for simplicity, simplicity sake.
I won't.
Then we have the grade, which is an integer.
And then finally we have our foreign key, which is student ID.
Now student ID is just an integer as well.
And then I'm going to say foreign key.
And what is the foreign key?
It is student.
ID.
What is it?
References.
It references the students table.
The ID column from students.
So that should set up the form key constraint for us.
I will go ahead and insert those two or rather create those two tables, and then I'm going to insert
this data that it's not really an exercise, it's just copy and pasting.
But I guess it helps you verify if things worked as well.
Let's select star from students.
That looks good.
Select star from papers.
Come on.
That looks good as well.
Next up, our first exercise using this data.
So we need to generate this result where we have first name, title and grade, where it's every student
that wrote a paper with the papers grade and title.
So this is an inner join.
This is going to be an inner join using students and papers.
So only two tables based upon the student ID and the foreign key student ID.
So let's try that.
Let's do a select I think it was first name and then title and then grade from students join.
Papers on and then we'll say where the papers dot student ID, if that's what we call this here, is
exactly equal to the student dot ID.
And it's there any sorting or anything else?
I don't think so.
So this should do it for us.
We get Caleb 60.
Caleb 75.
Samantha 94.
Samantha 98.
Carlos 89.
Looks good.
Same results now.
I'm an idiot.
There is a sort here which I didn't call out in the exercise, but there is a sort based upon the grade,
so we could easily add that in by just saying order by and then order by grade.
And we want descending.
The highest grade came first.
So if we try running this one.
Now we should get that same order 98 down to 60.
OC So that's an inner join.
We could have also written Inner space join if we prefer.
Then we have this other join.
In this case we have every single person, every student.
Right.
Caleb Samantha, We also have Raj and Lisa.
They don't have any corresponding papers so this would be an example of a left join.
If we started with students on the left.
Or it could be a right join.
If we had students on the right, we would do a right join.
We just want to take everything from the student's table and then any overlap from papers is great.
So I'm going to do a select first name.
Is it the same columns and the same order, first name, title and grade title grade from students.
And we'll do a left join papers on where papers dot student ID equal students ID make sure I have papers
there.
Okay, try that.
And this is looking good, right?
We have Caleb's papers, his grades, Samantha's papers, and the corresponding grades.
But then we also have Raj, who has no papers, Lisa, No papers, no grades.
So that's what we're supposed to get there.
Now we have the upgraded version where if we had NULL for a given title, we replace it with missing
and then we also replace the grade with zero.
So to do that, we're going to use if no, same thing here, first name, title grade, except I'm going
to write.
Let's do it on separate lines.
So it's easier to read.
We'll do a first name and then for title if null for title.
Remember the second value.
Here is what we want to replace title with.
If it is null, we'll say missing and try running that.
We should see missing.
Perfect.
And then we want to do zero four grade.
So the same approach if null for grade, replace it with the zero, the zero, replace it with zero.
And now we see zero over there.
Okay, that's looking good.
What do we have next?
We now need to calculate the average grade for all of our people, all of our students, and if they
don't have a grade, will replace it with zero.
Or if the average is null or something, we'll figure it out.
What we need to do to make this zero and then we're going to order them as well by that average.
So this is another example.
I want to do a left join because that's the only way I can find out that Roger and Lisa don't have grades
or they don't have papers.
If I only did an inner join, we would only get Samantha, Carlos and Caleb because they're the only
students who have written papers.
But I want to know about Raj and Lisa.
I want those null values and then I can replace them.
So we'll do a select.
All we're doing is first name and then grade.
But I'm going to average the grade, but we'll get there.
Select first name and grade from students and we'll do a left Join papers on students.
ID is equal to papers DOT Student ID And then the new addition here is a group by we're going to group
by first name.
I'm sorry if you can hear my dog whining in the background group by first name.
So now we take basically this table that we have here and regrouped by first name.
So Caleb has two, Samantha has two, Raj has none, Lisa has none.
Carlos has one.
And then we're going to do the average of grade instead of grade.
And let's see what that gives us.
Oh, I accidentally hit L at the end.
Let's try that again without the extra L.
All right, so we're close, but we want to make sure instead of No, we put zero there.
So remember, we can do if no of average.
Of the grade.
So if that is null, we'll just replace it with zero.
And I'm also just going to rename this to B average.
Looks good to me.
Run that.
Okay, that's looking good.
The final thing we need to do is sort or order by that average.
So let's do an order by average.
And we want descending, I believe, just looking at what we had here.
Yep.
Highest to lowest.
And there we are, 96, 89, 60, 70 500.
Looks good to me.
Perfect.
Our final piece now is to take that same query, but also add on this passing status that is based upon
whatever the average result was we're going to right passing or failing.
So we're going to use case copy this, and I'm just going to put these on separate lines to clean it
up a bit.
All right.
But from students here.
So we're going to have a case and I put the end before I forget.
And in here we're going to look at whether this right here.
So I'm just going to copy this when we have this.
If no average grade zero, this is going to spit out either a number like 50 or 90 or well, it's always
a number or zero.
But if that number is greater than 75 or equal to I guess it didn't specify, then we'll say.
Passing.
All right.
Else we'll say failing.
And let's see if that does it for us.
Let's try.
And it does.
I need to rename this, though.
This column name is quite long.
I called it passing status.
So let's do that.
As passing status.
And there we are.
So we see.
Samantha, Carlos, Caleb, Raj, Lisa.
Their grades sorted, highest to lowest and passing status.
The top two, Samantha and Carlos.
Pass.
Caleb with the 67 five.
Sorry.
He turned in papers, but he still failed.
They weren't high enough.
Raj and Lisa didn't turn anything in, so we had null values.
We replaced that with zero.
They fail.
And that's it for this exercise.
So hopefully you can see how a left joint helped us there.
Write an inner join is great if I want to know information about the overlap, but there's also information
in the lack of an overlap, meaning the people who did not turn in a paper.
We can find that out using a left join.
Or we could have done a right joint if we had students on the right.
They're interchangeable.
If you move what's on the left and what's on the right, it's the same concept.
All right.
That's joins.
Next up.
We've got a lot to learn about many to many relationships.