Get so Problem three
very similar but we're going to then substitute in for a no.
Under Title we want it to say missing an under grade you 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 common grade
from students left join papers on students that ID equals papers.
Student ID mouthfull.
However when we're working so first name will be the same title.
We're going to if it's no we're going to make it that the text of the string missing.
And that's where our lovely if no function comes into hand or comes in 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 knoll takes this if title is no then
it replaces it with missing.
Otherwise it just keeps it as my first book report my second book before this one.
So we'll start with that.
You can see if we get missing we still have no for grade.
That's a simple fix the same thing.
If no grade comma and we want to zero not text not a string zero.
You want the number.
Because this is it over here.
Perfect.
And we're done yet missing zero missing zero and then things that didn't have no 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 Roger 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 Rush and Lisa are 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 the.
So this really means is we need a group Nici group of students and we're going to start by selecting
all of this and we'll get rid of our if Noll's So let's just do the title again.
We don't even need Title Duey we just want grade.
We'll start with that.
So if we do a left join K 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 that are unique.
We could not use on the paper side of things we could not do paper step student ID because Raj and Lisa
don't have papers that student IDs so that's no for them which is problematic.
We can't really dependably group by no.
And also we don't want a group rise and they sit 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.
We do star
we can just group by student ID so you can see here.
So what I was saying is that student ID doesn't work because these are no.
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 you 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.
Heidi it never hurts to do that.
So what group.
Student ID and if we copy it and repaste.
OK so we're getting close.
Sort of we have things groups remember that hides data from us because it's groups behind this row.
Two of Caleb's papers to 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 have to deal with that and a little bit.
Carlos Carlos has won.
Lisa has no.
We'll deal with that in a second as well.
But let's see what we get now.
OK so we get to averages sixty seven point five.
96 in 1893 averages excuse me and then for our people who don't have papers we end up with no and no
simple solution.
If they don't have any papers we just use if no
if no average grade.
And we just want to make it zero just like that before we did it.
And now you can see we get rajin to have zeros in if we check against what we had here.
There's only one small change which is we need to order them descending order by quarter 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 you go.
OK.
Perfect.
Moving on our final problem same thing although we're just now adding a passing status to 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.
OK.
So let's give it a shot.
This one is very similar to the previous problems.
I'm just going to copy paste it and just rename it problem 5.
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 together case and we'll call this passing was a passing status.
There we go.
OK.
So what do we fill in here.
We're basically trying to say when what when average of grade will copy that is greater or equal to
75 then what do we want we want it to be.
Passing perfect otherwise you want it to be failing.
And let's see what happens.
OK.
So 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 we worked with no 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 no.
When you worked with.
No.
Fortunately that is working to our advantage because when we get no 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 no then
failing.
So that would catch not right away.
Then we have.
OK.
When it's a number greater than 75 equal or greater than 75 than 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 killing failing.
The difference is that we're not relying on this weird comparison between.
No.
Because it is bizarre is no greater than or equal to one.
And it tells us No.
It's just it's useless.
No it is a weird special value that has things like is no.
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 involves joins but hopefully
it's not too scary.
And if it is I'm sorry just I'm sorry.
Hang in there.