WEBVTT 00:00.480 --> 00:02.790 Get so Problem three 00:06.110 --> 00:11.260 very similar but we're going to then substitute in for a no. 00:11.280 --> 00:18.570 Under Title we want it to say missing an under grade you want it to say zero so we can actually just 00:18.570 --> 00:19.520 use the same thing. 00:19.520 --> 00:21.260 So it's going to be select. 00:21.690 --> 00:23.020 I'll do this on separate lines. 00:23.100 --> 00:27.140 First name comma title common grade 00:31.640 --> 00:40.840 from students left join papers on students that ID equals papers. 00:40.970 --> 00:43.440 Student ID mouthfull. 00:43.730 --> 00:47.990 However when we're working so first name will be the same title. 00:48.440 --> 00:53.880 We're going to if it's no we're going to make it that the text of the string missing. 00:54.140 --> 01:05.180 And that's where our lovely if no function comes into hand or comes in handy Let's say so comma and 01:05.180 --> 01:06.490 we'll just replace it with missing. 01:06.590 --> 01:13.820 So remember I'm beating a dead horse hopefully at this point if knoll takes this if title is no then 01:13.880 --> 01:15.240 it replaces it with missing. 01:15.290 --> 01:19.340 Otherwise it just keeps it as my first book report my second book before this one. 01:19.760 --> 01:21.170 So we'll start with that. 01:21.230 --> 01:24.640 You can see if we get missing we still have no for grade. 01:25.070 --> 01:28.250 That's a simple fix the same thing. 01:28.250 --> 01:34.460 If no grade comma and we want to zero not text not a string zero. 01:34.800 --> 01:35.390 You want the number. 01:35.390 --> 01:39.020 Because this is it over here. 01:39.020 --> 01:39.800 Perfect. 01:41.340 --> 01:47.720 And we're done yet missing zero missing zero and then things that didn't have no are untouched. 01:48.470 --> 01:48.840 All right. 01:48.840 --> 01:58.160 Moving on this one is similar in that we want to have data for students who don't have papers. 01:58.160 --> 01:59.490 So Roger and Lisa. 01:59.840 --> 02:01.810 So this is not going to be an inner join. 02:01.820 --> 02:06.220 We know that because if we did an inner join we would only get these three. 02:06.470 --> 02:10.880 We wouldn't have anything on Rush and Lisa are students who maybe aren't doing so hot they're not turning 02:10.880 --> 02:11.700 things in. 02:12.170 --> 02:17.600 But what we want is only the first name and their average for all their papers and if they didn't turn 02:17.600 --> 02:20.270 anything in their average is the. 02:20.780 --> 02:26.690 So this really means is we need a group Nici group of students and we're going to start by selecting 02:26.690 --> 02:33.170 all of this and we'll get rid of our if Noll's So let's just do the title again. 02:33.170 --> 02:36.610 We don't even need Title Duey we just want grade. 02:36.740 --> 02:38.970 We'll start with that. 02:39.030 --> 02:43.110 So if we do a left join K we get this this is this is actually close. 02:43.110 --> 02:43.820 Right. 02:43.850 --> 02:45.620 The only thing is we need to condense our data. 02:45.650 --> 02:47.610 We need to group these together. 02:47.900 --> 02:50.120 So what's the best way to group them. 02:50.750 --> 02:55.110 Well we could use first name that would absolutely work that are unique. 02:55.220 --> 03:02.600 We could not use on the paper side of things we could not do paper step student ID because Raj and Lisa 03:02.600 --> 03:07.250 don't have papers that student IDs so that's no for them which is problematic. 03:07.250 --> 03:09.760 We can't really dependably group by no. 03:09.920 --> 03:12.620 And also we don't want a group rise and they sit together. 03:12.620 --> 03:17.720 So we want to group from this side of the table and we could do names but there's also something else 03:17.720 --> 03:19.020 that we're not seeing. 03:19.490 --> 03:20.270 We do star 03:23.060 --> 03:28.430 we can just group by student ID so you can see here. 03:28.490 --> 03:32.310 So what I was saying is that student ID doesn't work because these are no. 03:32.450 --> 03:36.520 And if we group we don't want these together we want them separate. 03:37.220 --> 03:38.420 So let's group by 03:41.390 --> 03:52.460 group by students and we could get away with just saying ID because there is no ID on our papers table 03:52.560 --> 03:52.790 right. 03:52.790 --> 03:57.860 We didn't have ID which you probably should have just for good practice but we don't have it. 03:57.860 --> 04:02.620 But if we did have it then we definitely need to be explicit and say students. 04:02.650 --> 04:04.480 Heidi it never hurts to do that. 04:04.790 --> 04:05.420 So what group. 04:05.420 --> 04:09.430 Student ID and if we copy it and repaste. 04:10.230 --> 04:12.320 OK so we're getting close. 04:12.320 --> 04:19.490 Sort of we have things groups remember that hides data from us because it's groups behind this row. 04:19.640 --> 04:24.590 Two of Caleb's papers to Samantha's But we're at least getting data grouped. 04:24.710 --> 04:29.600 So then the next thing is rather than displaying grade here we don't care about grade what we want to 04:29.600 --> 04:34.830 display is the average of all the grades for those two. 04:35.330 --> 04:38.810 So Caleb has two grades 60 and something else. 04:38.840 --> 04:40.120 Samantha has two. 04:40.310 --> 04:42.920 Raj has have to deal with that and a little bit. 04:42.920 --> 04:45.290 Carlos Carlos has won. 04:45.290 --> 04:46.220 Lisa has no. 04:46.280 --> 04:48.050 We'll deal with that in a second as well. 04:48.270 --> 04:49.850 But let's see what we get now. 04:50.430 --> 04:54.240 OK so we get to averages sixty seven point five. 04:54.260 --> 05:01.130 96 in 1893 averages excuse me and then for our people who don't have papers we end up with no and no 05:01.790 --> 05:03.000 simple solution. 05:03.320 --> 05:06.100 If they don't have any papers we just use if no 05:09.050 --> 05:11.960 if no average grade. 05:12.380 --> 05:18.020 And we just want to make it zero just like that before we did it. 05:18.500 --> 05:25.160 And now you can see we get rajin to have zeros in if we check against what we had here. 05:25.160 --> 05:36.110 There's only one small change which is we need to order them descending order by quarter by average 05:36.140 --> 05:38.920 and actually rather than doing average grade. 05:39.110 --> 05:45.130 Let's just give this an alias which is what I did over here I just called it average. 05:45.140 --> 05:47.880 Now we can do order by average. 05:48.220 --> 05:49.830 Perfect. 05:49.990 --> 05:50.900 And there we go. 05:51.030 --> 05:52.770 Well no I lied. 05:52.870 --> 05:54.130 If I average descending. 05:54.250 --> 05:55.610 And now there you go. 05:55.930 --> 05:56.380 OK. 05:56.410 --> 05:57.960 Perfect. 05:57.970 --> 06:04.210 Moving on our final problem same thing although we're just now adding a passing status to a new field 06:04.210 --> 06:06.130 that doesn't exist in either table. 06:06.330 --> 06:13.860 And that says passing if the average grade is greater than 75 or equal to 75 and failing. 06:13.990 --> 06:15.260 If it's less. 06:15.670 --> 06:17.640 So this is a great use for a case statement. 06:17.650 --> 06:17.950 OK. 06:17.970 --> 06:19.870 So let's give it a shot. 06:19.870 --> 06:22.580 This one is very similar to the previous problems. 06:22.600 --> 06:27.030 I'm just going to copy paste it and just rename it problem 5. 06:27.610 --> 06:34.270 So all we need to do is add in another field but this field involves a case statement. 06:34.270 --> 06:36.610 So what I like to do is always do my case. 06:36.630 --> 06:43.060 And together case and we'll call this passing was a passing status. 06:43.060 --> 06:44.500 There we go. 06:44.770 --> 06:45.280 OK. 06:45.610 --> 06:47.130 So what do we fill in here. 06:47.140 --> 06:58.030 We're basically trying to say when what when average of grade will copy that is greater or equal to 06:58.120 --> 07:04.570 75 then what do we want we want it to be. 07:04.570 --> 07:12.030 Passing perfect otherwise you want it to be failing. 07:12.910 --> 07:14.080 And let's see what happens. 07:17.410 --> 07:18.020 OK. 07:18.350 --> 07:19.890 So seems to be working. 07:20.000 --> 07:22.280 Passing passing failing failing failing. 07:22.850 --> 07:30.020 One thing to note though is that when we're doing average grade here for some of them it's no. 07:30.050 --> 07:30.850 Remember that. 07:31.010 --> 07:39.710 And so it's important to realize we worked with no is no greater than or equal to 75. 07:39.790 --> 07:50.680 And the answer is no which is bizarre right versus if we had done is 65 greater than or equal to 75 07:51.040 --> 07:53.040 We get zero or one true or false. 07:53.050 --> 07:53.770 But we're getting no. 07:53.770 --> 07:54.390 When you worked with. 07:54.390 --> 07:55.010 No. 07:55.360 --> 08:02.220 Fortunately that is working to our advantage because when we get no here it basically the case statement 08:02.240 --> 08:05.190 moves past it ignores it and does the else. 08:05.320 --> 08:07.290 But it is important to understand that. 08:07.510 --> 08:18.730 So you could do something like this if you wanted to be extra careful when average grade is no then 08:19.010 --> 08:19.760 failing. 08:19.840 --> 08:22.450 So that would catch not right away. 08:22.450 --> 08:23.350 Then we have. 08:23.500 --> 08:24.090 OK. 08:24.220 --> 08:30.310 When it's a number greater than 75 equal or greater than 75 than passing otherwise that means it's a 08:30.310 --> 08:32.470 number that's less than 75. 08:32.790 --> 08:37.120 And that means failing so we won't see a change in our results here. 08:37.230 --> 08:37.820 Right. 08:37.870 --> 08:39.150 It looks exactly the same. 08:39.190 --> 08:43.580 Passing passing failing failing failing passing passing failing killing failing. 08:43.690 --> 08:48.830 The difference is that we're not relying on this weird comparison between. 08:48.820 --> 08:49.400 No. 08:49.780 --> 08:55.120 Because it is bizarre is no greater than or equal to one. 08:55.400 --> 08:56.640 And it tells us No. 08:56.770 --> 08:57.950 It's just it's useless. 08:58.120 --> 09:01.950 No it is a weird special value that has things like is no. 09:02.170 --> 09:05.130 So let's take advantage of that just to be safe. 09:05.140 --> 09:05.830 All right. 09:06.100 --> 09:07.160 Now we're done. 09:07.240 --> 09:09.400 Hopefully you enjoyed some of that. 09:09.670 --> 09:14.680 It's a little bit different than some of the exercises we've done in the past involves joins but hopefully 09:14.680 --> 09:15.690 it's not too scary. 09:15.840 --> 09:20.530 And if it is I'm sorry just I'm sorry. 09:20.530 --> 09:20.910 Hang in there.