Och.
So problem three.
It's very similar, but we're going to then substitute in for NULL under title.
We want it to say missing and under grade.
We want it to say zero so we can actually just use the same thing.
So it's going to be select.
I'll do this on separate lines.
First name, comma title, comma grade.
From students left join papers on student ID equals papers dot student ID.
Quite a mouthful.
However, when we're working so first name will be the same but title we're going to.
If it's null, we're going to make it that the text or the string missing.
And that's where our lovely if null function comes into hand or comes it's handy let's say so comma
and we'll just replace it with missing.
So remember, I'm beating a dead horse.
Hopefully at this point, if null takes this if title is null, then it replaces it with missing.
Otherwise it just keeps it as my first book report, my second book report and so on.
So we'll start with that.
You can see we get missing, but we still have null for grade and that's a simple fix.
The same thing if no grade comma and we want zero, not text, not a string zero we want the number
because this is an int over here.
Perfect.
And we're done.
We get missing zero, missing zero.
And then things that didn't have null are untouched.
All right.
Moving on.
This one is similar in that we want to have data for students who don't have papers.
So Raj and Lisa.
So this is not going to be an inner join.
We know that because if we did an inner join, we would only get these three.
We wouldn't have anything on Raj and Lisa, our students who maybe aren't doing so hot, they're not
turning things in.
But what we want is only the first name and their average for all their papers.
And if they didn't turn anything in, their average is zero.
So with this really means is we need to group we need to group our students and we're going to start
by selecting all of this and we'll get rid of our if.
KNOWLES So let's just do title again.
And we don't even need title, do we?
We just want grade.
We'll start with that.
So if we do a left join, we get this.
This is this is actually close, right?
The only thing is we need to condense our data.
We need to group these together.
So what's the best way to group them?
Well, we could use first name.
That would absolutely work.
They're unique.
We could not use on the paper side of things.
We could not do papers, dot, student ID because Raj and Lisa don't have paper set student ID, so
that's null for them, which is problematic.
We can't really dependably group by null.
And also we don't want a group Raj and Lisa together.
So we want to group from this side of the table and we could do names, but there's also something else
that we're not seeing if we do Star.
We can just group buy students ID.
As you can see here.
So what I was saying is that student ID doesn't work because these are null.
And if we group, we don't want these together, we want them separate.
So let's group by
group by students.
And we could get away with just saying ID because there is no ID on our papers table.
Right?
We didn't have ID, which we probably should have just for good practice, but we don't have it.
But if we did have it, then we definitely need to be explicit and say students ID and it never hurts
to do that.
So we'll group my students ID and if we copy it and we paste.
So we're getting close sort of.
We have things grouped and remember that hides data from us because it's grouped behind this row.
All two of Caleb's papers, two of Samantha's.
But we're at least getting data grouped.
So then the next thing is, rather than displaying grade here, we don't care about grade.
What we want to display is the average of all the grades for those two.
So Caleb has two grades 60 and something else.
Samantha has two.
Raj has.
We'll have to deal with that in a little bit.
Carlos two.
Carlos has one.
Lisa has no we'll deal with that in second as well.
But let's see what we get now.
So we get to averages 67.5, 96 and 89 three averages, excuse me.
And then for our people who don't have papers, we end up with null and no simple solution.
If they don't have any papers, we just use if no.
If no average grade, then we just want to make it zero.
Just like that before I deleted it.
And now you can see we get Raj and Lisa have zeros.
And if we check against what we had here, there's only one small change, which is we need to order
them descending order by order by average.
And actually, rather than doing average grade, let's just give this an alias, which is what I did
over here.
I just called it average.
Now we can do order by average.
Perfect.
And there we go.
Well, no, I lied.
If I average descending.
And now.
There we go.
Perfect.
Moving on our final problem, same thing, although we're just now adding a passing status.
So a new field that doesn't exist in either table and that says passing if the average grade is greater
than 75 or equal to 75 and failing if it's less.
So this is a great use for a case statement, so let's give it a shot.
This one is very similar to the previous problem, so I'm just going to copy it, paste it and just
rename it.
Problem five.
So all we need to do is add in another field, but this field involves a case statement.
So what I like to do is always do my case and end together, case end and we'll call this passing.
Is it passing status?
There we go.
So what do we fill in here?
We're basically trying to say when, what, when average of grade is it?
We'll copy that is greater than or equal to 75.
Then what do we want?
We want it to be passing.
Perfect.
Otherwise, we want it to be failing.
And let's see what happens.
Okay.
So it seems to be working.
Passing, passing.
Failing, failing, failing.
One thing to note, though, is that when we're doing average grade here for some of them, it's no
remember that.
And so it's important to realize if we work with NULL is no greater than or equal to 75.
And the answer is no, which is bizarre, right.
Versus if we had done is 65 greater than or equal to 75, we get zero or one true or false, but we're
getting null.
When you work with NULL, fortunately that is working to our advantage because when we get null here,
it basically the case statement moves past it, ignores it and does the else.
But it is important to understand that.
So you could do something like this if you wanted to be extra careful when average grade is null, then
failing.
So that would catch null right away.
Then we have OC when it's a number greater than 75, equal or greater than 75, then passing.
Otherwise that means it's a number that's less than 75 and that means failing.
So we won't see a change in our results here.
Right?
It looks exactly the same.
Passing, passing, failing, failing, failing, passing, passing, failing, failing, failing.
The difference is that we're not relying on this weird comparison between null because it is bizarre.
It's no greater than or equal to one.
And it tells us null.
It's just it's useless.
NULL is a weird special value.
It has things like is null.
So let's take advantage of that just to be safe.
All right.
Now we're done.
Hopefully you enjoyed some of that.
It's a little bit different than some of the exercises we've done in the past.
It involves joints, but hopefully it's not too scary.
And if it is, I'm sorry.
Just.
I'm sorry.
Hang in there.