1 00:00:00,360 --> 00:00:00,960 Och. 2 00:00:01,380 --> 00:00:03,090 So problem three. 3 00:00:06,230 --> 00:00:12,070 It's very similar, but we're going to then substitute in for NULL under title. 4 00:00:12,080 --> 00:00:14,360 We want it to say missing and under grade. 5 00:00:14,360 --> 00:00:19,520 We want it to say zero so we can actually just use the same thing. 6 00:00:19,520 --> 00:00:20,930 So it's going to be select. 7 00:00:21,660 --> 00:00:23,090 I'll do this on separate lines. 8 00:00:23,150 --> 00:00:27,410 First name, comma title, comma grade. 9 00:00:31,630 --> 00:00:42,280 From students left join papers on student ID equals papers dot student ID. 10 00:00:42,400 --> 00:00:43,270 Quite a mouthful. 11 00:00:43,600 --> 00:00:49,390 However, when we're working so first name will be the same but title we're going to. 12 00:00:49,420 --> 00:00:53,950 If it's null, we're going to make it that the text or the string missing. 13 00:00:53,950 --> 00:01:04,420 And that's where our lovely if null function comes into hand or comes it's handy let's say so comma 14 00:01:04,750 --> 00:01:06,520 and we'll just replace it with missing. 15 00:01:06,520 --> 00:01:08,880 So remember, I'm beating a dead horse. 16 00:01:08,890 --> 00:01:15,210 Hopefully at this point, if null takes this if title is null, then it replaces it with missing. 17 00:01:15,220 --> 00:01:19,180 Otherwise it just keeps it as my first book report, my second book report and so on. 18 00:01:19,630 --> 00:01:20,920 So we'll start with that. 19 00:01:21,130 --> 00:01:26,020 You can see we get missing, but we still have null for grade and that's a simple fix. 20 00:01:26,020 --> 00:01:35,440 The same thing if no grade comma and we want zero, not text, not a string zero we want the number 21 00:01:35,440 --> 00:01:37,420 because this is an int over here. 22 00:01:38,980 --> 00:01:39,790 Perfect. 23 00:01:41,250 --> 00:01:42,150 And we're done. 24 00:01:42,210 --> 00:01:44,400 We get missing zero, missing zero. 25 00:01:44,400 --> 00:01:47,130 And then things that didn't have null are untouched. 26 00:01:48,450 --> 00:01:48,870 All right. 27 00:01:48,890 --> 00:01:49,680 Moving on. 28 00:01:51,440 --> 00:01:58,160 This one is similar in that we want to have data for students who don't have papers. 29 00:01:58,160 --> 00:01:59,300 So Raj and Lisa. 30 00:01:59,690 --> 00:02:01,760 So this is not going to be an inner join. 31 00:02:01,760 --> 00:02:05,540 We know that because if we did an inner join, we would only get these three. 32 00:02:06,320 --> 00:02:10,669 We wouldn't have anything on Raj and Lisa, our students who maybe aren't doing so hot, they're not 33 00:02:10,669 --> 00:02:11,570 turning things in. 34 00:02:12,020 --> 00:02:16,190 But what we want is only the first name and their average for all their papers. 35 00:02:16,310 --> 00:02:19,130 And if they didn't turn anything in, their average is zero. 36 00:02:20,630 --> 00:02:25,550 So with this really means is we need to group we need to group our students and we're going to start 37 00:02:25,550 --> 00:02:29,960 by selecting all of this and we'll get rid of our if. 38 00:02:29,960 --> 00:02:32,980 KNOWLES So let's just do title again. 39 00:02:32,990 --> 00:02:34,600 And we don't even need title, do we? 40 00:02:34,610 --> 00:02:35,930 We just want grade. 41 00:02:36,710 --> 00:02:37,700 We'll start with that. 42 00:02:39,080 --> 00:02:41,570 So if we do a left join, we get this. 43 00:02:41,570 --> 00:02:43,660 This is this is actually close, right? 44 00:02:43,670 --> 00:02:45,620 The only thing is we need to condense our data. 45 00:02:45,620 --> 00:02:47,600 We need to group these together. 46 00:02:47,720 --> 00:02:49,760 So what's the best way to group them? 47 00:02:50,630 --> 00:02:52,370 Well, we could use first name. 48 00:02:52,370 --> 00:02:53,450 That would absolutely work. 49 00:02:53,450 --> 00:02:54,350 They're unique. 50 00:02:55,130 --> 00:02:58,670 We could not use on the paper side of things. 51 00:02:58,670 --> 00:03:04,490 We could not do papers, dot, student ID because Raj and Lisa don't have paper set student ID, so 52 00:03:04,490 --> 00:03:07,250 that's null for them, which is problematic. 53 00:03:07,250 --> 00:03:09,740 We can't really dependably group by null. 54 00:03:09,740 --> 00:03:12,500 And also we don't want a group Raj and Lisa together. 55 00:03:12,500 --> 00: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 56 00:03:17,720 --> 00:03:20,480 that we're not seeing if we do Star. 57 00:03:22,940 --> 00:03:25,610 We can just group buy students ID. 58 00:03:26,390 --> 00:03:27,500 As you can see here. 59 00:03:28,340 --> 00:03:32,330 So what I was saying is that student ID doesn't work because these are null. 60 00:03:32,330 --> 00:03:35,540 And if we group, we don't want these together, we want them separate. 61 00:03:37,100 --> 00:03:38,690 So let's group by 62 00:03:41,360 --> 00:03:44,210 group by students. 63 00:03:44,210 --> 00:03:52,560 And we could get away with just saying ID because there is no ID on our papers table. 64 00:03:52,580 --> 00:03:52,760 Right? 65 00:03:52,820 --> 00:03:57,440 We didn't have ID, which we probably should have just for good practice, but we don't have it. 66 00:03:57,830 --> 00:04:03,740 But if we did have it, then we definitely need to be explicit and say students ID and it never hurts 67 00:04:03,740 --> 00:04:04,370 to do that. 68 00:04:04,670 --> 00:04:08,990 So we'll group my students ID and if we copy it and we paste. 69 00:04:10,610 --> 00:04:12,830 So we're getting close sort of. 70 00:04:12,830 --> 00:04:18,740 We have things grouped and remember that hides data from us because it's grouped behind this row. 71 00:04:19,459 --> 00:04:21,980 All two of Caleb's papers, two of Samantha's. 72 00:04:22,550 --> 00:04:24,590 But we're at least getting data grouped. 73 00:04:24,590 --> 00:04:28,610 So then the next thing is, rather than displaying grade here, we don't care about grade. 74 00:04:29,060 --> 00:04:34,670 What we want to display is the average of all the grades for those two. 75 00:04:35,330 --> 00:04:38,720 So Caleb has two grades 60 and something else. 76 00:04:38,720 --> 00:04:39,980 Samantha has two. 77 00:04:40,250 --> 00:04:41,210 Raj has. 78 00:04:41,210 --> 00:04:42,860 We'll have to deal with that in a little bit. 79 00:04:42,890 --> 00:04:43,920 Carlos two. 80 00:04:44,300 --> 00:04:45,250 Carlos has one. 81 00:04:45,260 --> 00:04:48,020 Lisa has no we'll deal with that in second as well. 82 00:04:48,140 --> 00:04:49,580 But let's see what we get now. 83 00:04:51,200 --> 00:04:56,900 So we get to averages 67.5, 96 and 89 three averages, excuse me. 84 00:04:56,900 --> 00:05:02,930 And then for our people who don't have papers, we end up with null and no simple solution. 85 00:05:03,230 --> 00:05:06,320 If they don't have any papers, we just use if no. 86 00:05:09,090 --> 00:05:14,190 If no average grade, then we just want to make it zero. 87 00:05:15,400 --> 00:05:17,620 Just like that before I deleted it. 88 00:05:18,370 --> 00:05:22,660 And now you can see we get Raj and Lisa have zeros. 89 00:05:23,530 --> 00:05:28,870 And if we check against what we had here, there's only one small change, which is we need to order 90 00:05:28,870 --> 00:05:36,160 them descending order by order by average. 91 00:05:36,160 --> 00:05:41,140 And actually, rather than doing average grade, let's just give this an alias, which is what I did 92 00:05:41,140 --> 00:05:41,470 over here. 93 00:05:41,470 --> 00:05:42,640 I just called it average. 94 00:05:45,070 --> 00:05:47,380 Now we can do order by average. 95 00:05:48,130 --> 00:05:48,910 Perfect. 96 00:05:49,900 --> 00:05:50,890 And there we go. 97 00:05:50,920 --> 00:05:52,540 Well, no, I lied. 98 00:05:52,920 --> 00:05:54,130 If I average descending. 99 00:05:54,130 --> 00:05:54,850 And now. 100 00:05:54,850 --> 00:05:55,540 There we go. 101 00:05:56,350 --> 00:05:57,130 Perfect. 102 00:05:57,910 --> 00:06:03,430 Moving on our final problem, same thing, although we're just now adding a passing status. 103 00:06:03,430 --> 00:06:10,030 So a new field that doesn't exist in either table and that says passing if the average grade is greater 104 00:06:10,030 --> 00:06:14,890 than 75 or equal to 75 and failing if it's less. 105 00:06:15,550 --> 00:06:19,120 So this is a great use for a case statement, so let's give it a shot. 106 00:06:19,750 --> 00:06:25,330 This one is very similar to the previous problem, so I'm just going to copy it, paste it and just 107 00:06:25,330 --> 00:06:25,810 rename it. 108 00:06:25,810 --> 00:06:26,710 Problem five. 109 00:06:27,460 --> 00:06:34,300 So all we need to do is add in another field, but this field involves a case statement. 110 00:06:34,300 --> 00:06:41,320 So what I like to do is always do my case and end together, case end and we'll call this passing. 111 00:06:41,680 --> 00:06:42,940 Is it passing status? 112 00:06:42,940 --> 00:06:43,690 There we go. 113 00:06:45,460 --> 00:06:46,960 So what do we fill in here? 114 00:06:46,990 --> 00:06:53,650 We're basically trying to say when, what, when average of grade is it? 115 00:06:53,680 --> 00:06:59,170 We'll copy that is greater than or equal to 75. 116 00:07:00,790 --> 00:07:02,590 Then what do we want? 117 00:07:03,070 --> 00:07:05,140 We want it to be passing. 118 00:07:06,510 --> 00:07:07,230 Perfect. 119 00:07:07,770 --> 00:07:11,790 Otherwise, we want it to be failing. 120 00:07:12,930 --> 00:07:14,100 And let's see what happens. 121 00:07:17,420 --> 00:07:17,900 Okay. 122 00:07:18,170 --> 00:07:19,880 So it seems to be working. 123 00:07:19,910 --> 00:07:20,870 Passing, passing. 124 00:07:20,870 --> 00:07:21,980 Failing, failing, failing. 125 00:07:22,700 --> 00:07:29,450 One thing to note, though, is that when we're doing average grade here for some of them, it's no 126 00:07:30,020 --> 00:07:30,830 remember that. 127 00:07:30,830 --> 00:07:37,760 And so it's important to realize if we work with NULL is no greater than or equal to 75. 128 00:07:39,670 --> 00:07:44,530 And the answer is no, which is bizarre, right. 129 00:07:45,460 --> 00:07:53,290 Versus if we had done is 65 greater than or equal to 75, we get zero or one true or false, but we're 130 00:07:53,290 --> 00:07:53,800 getting null. 131 00:07:53,800 --> 00:08:00,040 When you work with NULL, fortunately that is working to our advantage because when we get null here, 132 00:08:00,520 --> 00:08:05,020 it basically the case statement moves past it, ignores it and does the else. 133 00:08:05,320 --> 00:08:07,360 But it is important to understand that. 134 00:08:07,360 --> 00:08:18,970 So you could do something like this if you wanted to be extra careful when average grade is null, then 135 00:08:18,970 --> 00:08:19,810 failing. 136 00:08:19,810 --> 00:08:21,520 So that would catch null right away. 137 00:08:22,360 --> 00:08:28,990 Then we have OC when it's a number greater than 75, equal or greater than 75, then passing. 138 00:08:29,020 --> 00:08:34,000 Otherwise that means it's a number that's less than 75 and that means failing. 139 00:08:34,390 --> 00:08:37,059 So we won't see a change in our results here. 140 00:08:37,120 --> 00:08:37,630 Right? 141 00:08:37,780 --> 00:08:39,190 It looks exactly the same. 142 00:08:39,190 --> 00:08:43,630 Passing, passing, failing, failing, failing, passing, passing, failing, failing, failing. 143 00:08:43,630 --> 00:08:51,070 The difference is that we're not relying on this weird comparison between null because it is bizarre. 144 00:08:51,400 --> 00:08:54,970 It's no greater than or equal to one. 145 00:08:55,330 --> 00:08:56,620 And it tells us null. 146 00:08:56,650 --> 00:08:57,970 It's just it's useless. 147 00:08:57,970 --> 00:08:59,740 NULL is a weird special value. 148 00:08:59,740 --> 00:09:01,900 It has things like is null. 149 00:09:01,990 --> 00:09:04,480 So let's take advantage of that just to be safe. 150 00:09:05,110 --> 00:09:05,770 All right. 151 00:09:05,950 --> 00:09:06,940 Now we're done. 152 00:09:07,120 --> 00:09:08,740 Hopefully you enjoyed some of that. 153 00:09:09,520 --> 00:09:12,550 It's a little bit different than some of the exercises we've done in the past. 154 00:09:12,700 --> 00:09:15,580 It involves joints, but hopefully it's not too scary. 155 00:09:15,670 --> 00:09:17,230 And if it is, I'm sorry. 156 00:09:18,400 --> 00:09:18,620 Just. 157 00:09:18,640 --> 00:09:19,300 I'm sorry. 158 00:09:20,470 --> 00:09:21,160 Hang in there.