1
00:00:00,570 --> 00:00:03,930
All right, Let's write a solution for this joint exercise.

2
00:00:03,960 --> 00:00:06,180
To start, we need to create our tables.

3
00:00:06,210 --> 00:00:07,500
We'll start with students.

4
00:00:07,500 --> 00:00:10,170
Very simple ID and first name.

5
00:00:10,410 --> 00:00:12,630
So create table.

6
00:00:13,450 --> 00:00:14,650
Students.

7
00:00:15,280 --> 00:00:25,270
And we have an ID, which is an integer, but I also want it to be primary key and we'll add auto increment

8
00:00:25,270 --> 00:00:25,930
as well.

9
00:00:26,440 --> 00:00:31,540
And then we have first name which will just be var char.

10
00:00:31,990 --> 00:00:33,610
I don't know, 50.

11
00:00:34,720 --> 00:00:38,500
That should be safe for most people's first names to fit within 50 characters.

12
00:00:38,830 --> 00:00:45,220
And then we have our second table papers, which has a title for paper and a grade that to number.

13
00:00:45,220 --> 00:00:49,270
So create table papers.

14
00:00:50,560 --> 00:00:57,910
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

15
00:00:57,910 --> 00:00:59,610
a good practice to put an ID in.

16
00:00:59,710 --> 00:01:00,700
But that's okay.

17
00:01:00,700 --> 00:01:08,770
Well, if title and grade title is a bar chart and in general it would be good to make first name not

18
00:01:08,770 --> 00:01:12,490
null and title not null, but just for simplicity, simplicity sake.

19
00:01:12,490 --> 00:01:13,120
I won't.

20
00:01:13,120 --> 00:01:16,300
Then we have the grade, which is an integer.

21
00:01:16,750 --> 00:01:20,290
And then finally we have our foreign key, which is student ID.

22
00:01:20,650 --> 00:01:22,840
Now student ID is just an integer as well.

23
00:01:23,230 --> 00:01:26,080
And then I'm going to say foreign key.

24
00:01:26,260 --> 00:01:27,910
And what is the foreign key?

25
00:01:27,910 --> 00:01:29,740
It is student.

26
00:01:30,920 --> 00:01:31,730
ID.

27
00:01:32,240 --> 00:01:32,780
What is it?

28
00:01:32,780 --> 00:01:34,400
References.

29
00:01:34,910 --> 00:01:36,920
References.

30
00:01:37,160 --> 00:01:40,280
It references the students table.

31
00:01:40,700 --> 00:01:43,790
The ID column from students.

32
00:01:44,600 --> 00:01:47,090
So that should set up the form key constraint for us.

33
00:01:47,240 --> 00:01:53,360
I will go ahead and insert those two or rather create those two tables, and then I'm going to insert

34
00:01:53,360 --> 00:01:58,310
this data that it's not really an exercise, it's just copy and pasting.

35
00:01:58,940 --> 00:02:02,240
But I guess it helps you verify if things worked as well.

36
00:02:02,450 --> 00:02:05,420
Let's select star from students.

37
00:02:06,260 --> 00:02:07,110
That looks good.

38
00:02:07,130 --> 00:02:09,229
Select star from papers.

39
00:02:10,500 --> 00:02:11,310
Come on.

40
00:02:11,460 --> 00:02:12,630
That looks good as well.

41
00:02:14,220 --> 00:02:17,670
Next up, our first exercise using this data.

42
00:02:17,880 --> 00:02:25,020
So we need to generate this result where we have first name, title and grade, where it's every student

43
00:02:25,020 --> 00:02:28,830
that wrote a paper with the papers grade and title.

44
00:02:28,830 --> 00:02:30,300
So this is an inner join.

45
00:02:30,690 --> 00:02:34,470
This is going to be an inner join using students and papers.

46
00:02:34,470 --> 00:02:40,170
So only two tables based upon the student ID and the foreign key student ID.

47
00:02:40,200 --> 00:02:41,460
So let's try that.

48
00:02:41,670 --> 00:02:51,570
Let's do a select I think it was first name and then title and then grade from students join.

49
00:02:52,730 --> 00:03:02,210
Papers on and then we'll say where the papers dot student ID, if that's what we call this here, is

50
00:03:02,210 --> 00:03:05,270
exactly equal to the student dot ID.

51
00:03:07,460 --> 00:03:10,190
And it's there any sorting or anything else?

52
00:03:10,190 --> 00:03:11,080
I don't think so.

53
00:03:11,090 --> 00:03:13,310
So this should do it for us.

54
00:03:13,700 --> 00:03:15,920
We get Caleb 60.

55
00:03:15,950 --> 00:03:17,190
Caleb 75.

56
00:03:17,210 --> 00:03:18,690
Samantha 94.

57
00:03:18,710 --> 00:03:20,100
Samantha 98.

58
00:03:20,120 --> 00:03:21,560
Carlos 89.

59
00:03:21,770 --> 00:03:22,700
Looks good.

60
00:03:22,730 --> 00:03:24,530
Same results now.

61
00:03:24,530 --> 00:03:25,070
I'm an idiot.

62
00:03:25,070 --> 00:03:30,590
There is a sort here which I didn't call out in the exercise, but there is a sort based upon the grade,

63
00:03:30,650 --> 00:03:37,130
so we could easily add that in by just saying order by and then order by grade.

64
00:03:37,970 --> 00:03:39,260
And we want descending.

65
00:03:39,260 --> 00:03:41,390
The highest grade came first.

66
00:03:41,870 --> 00:03:43,550
So if we try running this one.

67
00:03:44,290 --> 00:03:47,710
Now we should get that same order 98 down to 60.

68
00:03:48,040 --> 00:03:49,450
OC So that's an inner join.

69
00:03:49,480 --> 00:03:52,720
We could have also written Inner space join if we prefer.

70
00:03:53,230 --> 00:03:55,480
Then we have this other join.

71
00:03:55,540 --> 00:03:59,500
In this case we have every single person, every student.

72
00:03:59,500 --> 00:03:59,740
Right.

73
00:03:59,740 --> 00:04:02,950
Caleb Samantha, We also have Raj and Lisa.

74
00:04:02,950 --> 00:04:08,020
They don't have any corresponding papers so this would be an example of a left join.

75
00:04:08,020 --> 00:04:11,260
If we started with students on the left.

76
00:04:11,260 --> 00:04:12,820
Or it could be a right join.

77
00:04:12,820 --> 00:04:15,940
If we had students on the right, we would do a right join.

78
00:04:15,940 --> 00:04:22,180
We just want to take everything from the student's table and then any overlap from papers is great.

79
00:04:23,590 --> 00:04:27,760
So I'm going to do a select first name.

80
00:04:27,940 --> 00:04:35,770
Is it the same columns and the same order, first name, title and grade title grade from students.

81
00:04:35,770 --> 00:04:46,660
And we'll do a left join papers on where papers dot student ID equal students ID make sure I have papers

82
00:04:46,660 --> 00:04:47,350
there.

83
00:04:47,560 --> 00:04:48,640
Okay, try that.

84
00:04:49,030 --> 00:04:51,160
And this is looking good, right?

85
00:04:51,160 --> 00:04:56,200
We have Caleb's papers, his grades, Samantha's papers, and the corresponding grades.

86
00:04:56,200 --> 00:05:01,180
But then we also have Raj, who has no papers, Lisa, No papers, no grades.

87
00:05:02,260 --> 00:05:03,940
So that's what we're supposed to get there.

88
00:05:04,090 --> 00:05:12,340
Now we have the upgraded version where if we had NULL for a given title, we replace it with missing

89
00:05:12,340 --> 00:05:15,100
and then we also replace the grade with zero.

90
00:05:15,580 --> 00:05:22,630
So to do that, we're going to use if no, same thing here, first name, title grade, except I'm going

91
00:05:22,630 --> 00:05:23,140
to write.

92
00:05:23,140 --> 00:05:24,670
Let's do it on separate lines.

93
00:05:25,640 --> 00:05:27,200
So it's easier to read.

94
00:05:27,770 --> 00:05:33,980
We'll do a first name and then for title if null for title.

95
00:05:34,490 --> 00:05:35,890
Remember the second value.

96
00:05:35,900 --> 00:05:37,860
Here is what we want to replace title with.

97
00:05:37,880 --> 00:05:43,880
If it is null, we'll say missing and try running that.

98
00:05:45,270 --> 00:05:46,790
We should see missing.

99
00:05:46,800 --> 00:05:47,510
Perfect.

100
00:05:47,520 --> 00:05:50,070
And then we want to do zero four grade.

101
00:05:50,070 --> 00:05:59,160
So the same approach if null for grade, replace it with the zero, the zero, replace it with zero.

102
00:05:59,490 --> 00:06:01,200
And now we see zero over there.

103
00:06:01,590 --> 00:06:03,120
Okay, that's looking good.

104
00:06:04,310 --> 00:06:05,480
What do we have next?

105
00:06:05,510 --> 00:06:13,430
We now need to calculate the average grade for all of our people, all of our students, and if they

106
00:06:13,430 --> 00:06:15,980
don't have a grade, will replace it with zero.

107
00:06:16,400 --> 00:06:19,330
Or if the average is null or something, we'll figure it out.

108
00:06:19,340 --> 00:06:23,860
What we need to do to make this zero and then we're going to order them as well by that average.

109
00:06:23,870 --> 00:06:25,250
So this is another example.

110
00:06:25,250 --> 00:06:31,610
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

111
00:06:31,610 --> 00:06:32,840
or they don't have papers.

112
00:06:32,840 --> 00:06:38,540
If I only did an inner join, we would only get Samantha, Carlos and Caleb because they're the only

113
00:06:38,540 --> 00:06:40,340
students who have written papers.

114
00:06:40,430 --> 00:06:43,070
But I want to know about Raj and Lisa.

115
00:06:43,070 --> 00:06:45,890
I want those null values and then I can replace them.

116
00:06:46,430 --> 00:06:48,110
So we'll do a select.

117
00:06:48,320 --> 00:06:51,560
All we're doing is first name and then grade.

118
00:06:52,070 --> 00:06:54,860
But I'm going to average the grade, but we'll get there.

119
00:06:54,860 --> 00:07:03,620
Select first name and grade from students and we'll do a left Join papers on students.

120
00:07:04,130 --> 00:07:13,400
ID is equal to papers DOT Student ID And then the new addition here is a group by we're going to group

121
00:07:13,400 --> 00:07:14,750
by first name.

122
00:07:14,750 --> 00:07:19,610
I'm sorry if you can hear my dog whining in the background group by first name.

123
00:07:19,610 --> 00:07:25,700
So now we take basically this table that we have here and regrouped by first name.

124
00:07:25,730 --> 00:07:29,990
So Caleb has two, Samantha has two, Raj has none, Lisa has none.

125
00:07:29,990 --> 00:07:31,160
Carlos has one.

126
00:07:31,160 --> 00:07:36,800
And then we're going to do the average of grade instead of grade.

127
00:07:37,160 --> 00:07:38,720
And let's see what that gives us.

128
00:07:40,030 --> 00:07:42,190
Oh, I accidentally hit L at the end.

129
00:07:42,220 --> 00:07:44,470
Let's try that again without the extra L.

130
00:07:44,890 --> 00:07:49,960
All right, so we're close, but we want to make sure instead of No, we put zero there.

131
00:07:49,960 --> 00:07:53,950
So remember, we can do if no of average.

132
00:07:54,940 --> 00:07:56,020
Of the grade.

133
00:07:56,290 --> 00:07:59,770
So if that is null, we'll just replace it with zero.

134
00:07:59,770 --> 00:08:03,280
And I'm also just going to rename this to B average.

135
00:08:04,430 --> 00:08:05,690
Looks good to me.

136
00:08:06,410 --> 00:08:07,280
Run that.

137
00:08:09,340 --> 00:08:10,900
Okay, that's looking good.

138
00:08:10,990 --> 00:08:15,250
The final thing we need to do is sort or order by that average.

139
00:08:15,250 --> 00:08:18,970
So let's do an order by average.

140
00:08:18,970 --> 00:08:22,840
And we want descending, I believe, just looking at what we had here.

141
00:08:22,870 --> 00:08:23,530
Yep.

142
00:08:23,530 --> 00:08:24,760
Highest to lowest.

143
00:08:26,020 --> 00:08:29,860
And there we are, 96, 89, 60, 70 500.

144
00:08:30,010 --> 00:08:31,210
Looks good to me.

145
00:08:31,360 --> 00:08:32,140
Perfect.

146
00:08:32,440 --> 00:08:39,909
Our final piece now is to take that same query, but also add on this passing status that is based upon

147
00:08:39,909 --> 00:08:44,200
whatever the average result was we're going to right passing or failing.

148
00:08:44,200 --> 00:08:50,170
So we're going to use case copy this, and I'm just going to put these on separate lines to clean it

149
00:08:50,170 --> 00:08:51,250
up a bit.

150
00:08:51,640 --> 00:08:52,390
All right.

151
00:08:52,390 --> 00:08:53,740
But from students here.

152
00:08:53,740 --> 00:08:57,610
So we're going to have a case and I put the end before I forget.

153
00:08:57,790 --> 00:09:02,770
And in here we're going to look at whether this right here.

154
00:09:02,880 --> 00:09:06,070
So I'm just going to copy this when we have this.

155
00:09:06,070 --> 00:09:12,820
If no average grade zero, this is going to spit out either a number like 50 or 90 or well, it's always

156
00:09:12,820 --> 00:09:14,020
a number or zero.

157
00:09:14,020 --> 00:09:21,910
But if that number is greater than 75 or equal to I guess it didn't specify, then we'll say.

158
00:09:22,880 --> 00:09:23,690
Passing.

159
00:09:24,770 --> 00:09:25,570
All right.

160
00:09:25,580 --> 00:09:27,800
Else we'll say failing.

161
00:09:28,430 --> 00:09:29,990
And let's see if that does it for us.

162
00:09:30,020 --> 00:09:30,920
Let's try.

163
00:09:32,060 --> 00:09:33,350
And it does.

164
00:09:33,860 --> 00:09:35,540
I need to rename this, though.

165
00:09:35,660 --> 00:09:37,540
This column name is quite long.

166
00:09:37,550 --> 00:09:39,560
I called it passing status.

167
00:09:39,560 --> 00:09:40,880
So let's do that.

168
00:09:41,690 --> 00:09:44,300
As passing status.

169
00:09:46,900 --> 00:09:47,680
And there we are.

170
00:09:47,890 --> 00:09:48,730
So we see.

171
00:09:48,730 --> 00:09:50,900
Samantha, Carlos, Caleb, Raj, Lisa.

172
00:09:50,920 --> 00:09:55,720
Their grades sorted, highest to lowest and passing status.

173
00:09:55,900 --> 00:09:57,940
The top two, Samantha and Carlos.

174
00:09:57,940 --> 00:09:58,330
Pass.

175
00:09:58,360 --> 00:09:59,920
Caleb with the 67 five.

176
00:09:59,950 --> 00:10:00,550
Sorry.

177
00:10:00,580 --> 00:10:02,740
He turned in papers, but he still failed.

178
00:10:02,770 --> 00:10:03,830
They weren't high enough.

179
00:10:03,850 --> 00:10:07,000
Raj and Lisa didn't turn anything in, so we had null values.

180
00:10:07,030 --> 00:10:08,560
We replaced that with zero.

181
00:10:08,710 --> 00:10:09,550
They fail.

182
00:10:09,880 --> 00:10:11,500
And that's it for this exercise.

183
00:10:11,500 --> 00:10:14,740
So hopefully you can see how a left joint helped us there.

184
00:10:14,740 --> 00:10:20,950
Write an inner join is great if I want to know information about the overlap, but there's also information

185
00:10:20,950 --> 00:10:26,200
in the lack of an overlap, meaning the people who did not turn in a paper.

186
00:10:26,200 --> 00:10:28,450
We can find that out using a left join.

187
00:10:28,450 --> 00:10:31,390
Or we could have done a right joint if we had students on the right.

188
00:10:31,990 --> 00:10:33,100
They're interchangeable.

189
00:10:33,130 --> 00:10:36,880
If you move what's on the left and what's on the right, it's the same concept.

190
00:10:37,000 --> 00:10:37,720
All right.

191
00:10:37,720 --> 00:10:38,740
That's joins.

192
00:10:38,740 --> 00:10:39,430
Next up.

193
00:10:39,430 --> 00:10:42,640
We've got a lot to learn about many to many relationships.

