1
00:00:00,120 --> 00:00:00,870
All right.

2
00:00:00,990 --> 00:00:02,670
So we made it through.

3
00:00:02,730 --> 00:00:04,080
At least I made it through.

4
00:00:04,110 --> 00:00:07,020
Hopefully still hanging in there.

5
00:00:07,110 --> 00:00:09,730
Hopefully you're alive and you survived that section.

6
00:00:09,750 --> 00:00:11,370
I know it can be brutal.

7
00:00:11,790 --> 00:00:13,620
This is for me.

8
00:00:13,620 --> 00:00:17,040
And for most teachers, the hardest thing to teach.

9
00:00:17,400 --> 00:00:21,680
Not because it's technically that difficult, but it's a big thing.

10
00:00:21,690 --> 00:00:25,260
We have to have multiple tables and to have multiple tables, we have to create the schema.

11
00:00:25,260 --> 00:00:31,140
We have to insert the data, then we have to show what the data looks like to understand it.

12
00:00:31,140 --> 00:00:35,130
And then we can get started with the joint and you have to manipulate the data to change.

13
00:00:35,160 --> 00:00:38,130
It's just a lot of work and it's tough.

14
00:00:38,130 --> 00:00:39,690
It's tough to keep it engaging.

15
00:00:39,690 --> 00:00:43,680
And frankly, I don't think that that was terribly engaging.

16
00:00:44,460 --> 00:00:45,780
So I'm sorry.

17
00:00:46,590 --> 00:00:52,860
Hopefully you made it and I'm doing my best to make the rest of these a little bit more exciting because

18
00:00:52,860 --> 00:00:57,150
once we have these tools in our belt, it's a lot easier to start doing case studies, which we'll be

19
00:00:57,150 --> 00:01:00,000
doing or making a full application.

20
00:01:00,210 --> 00:01:07,290
We're getting close to end products to actually making things rather than, you know, silly one off

21
00:01:07,470 --> 00:01:08,460
little examples.

22
00:01:09,240 --> 00:01:10,890
So it's a long preamble.

23
00:01:11,160 --> 00:01:12,240
It's now your turn.

24
00:01:12,240 --> 00:01:18,540
And this is where you'll probably if you're confused, if you're stuck with this join stuff, this is

25
00:01:18,540 --> 00:01:22,590
hopefully where you'll be able to make some big connections, to have some breakthroughs.

26
00:01:22,590 --> 00:01:25,740
So this is a little different than exercises we've done in the past.

27
00:01:25,740 --> 00:01:31,320
There's only one schema, one table you'll be working with, and there's not even that many questions.

28
00:01:31,590 --> 00:01:35,490
But the first thing is that we're going to be working with students in papers.

29
00:01:35,490 --> 00:01:37,200
So I'm a teacher, let's suppose.

30
00:01:37,200 --> 00:01:43,230
I guess I am a teacher, but let's say I'm working in a classroom with high school students in an English

31
00:01:43,230 --> 00:01:44,520
class or a literature class.

32
00:01:44,520 --> 00:01:46,400
It's going to be very, very simple data.

33
00:01:46,540 --> 00:01:48,090
I'm telling you what I'm looking for.

34
00:01:48,120 --> 00:01:53,460
Students and papers and for students will only worry about an ID and their first name, so don't even

35
00:01:53,460 --> 00:01:54,510
care about the last name.

36
00:01:55,170 --> 00:02:01,950
Let's make sure first name is unique and then we're going to have papers and it's a one to many relationship.

37
00:02:02,670 --> 00:02:07,710
One student can have multiple papers, but a paper can only belong to one student.

38
00:02:08,070 --> 00:02:13,860
So these papers will have a title, a grade, a letter grade like or not a letter grade, a numeric

39
00:02:13,860 --> 00:02:14,100
grade.

40
00:02:14,100 --> 00:02:14,700
Excuse me?

41
00:02:14,700 --> 00:02:18,540
Like 90 or 60 if they don't do particularly well.

42
00:02:18,540 --> 00:02:24,810
And then a foreign key student ID which is referencing the student ID.

43
00:02:26,470 --> 00:02:27,820
So that's the basic schema.

44
00:02:27,820 --> 00:02:31,330
And I'd like for you to first go through and actually implement this.

45
00:02:31,330 --> 00:02:36,730
And don't worry about the data because in the next slide I'm giving you the data you can copy and paste

46
00:02:36,730 --> 00:02:37,240
it in.

47
00:02:37,720 --> 00:02:40,360
So this data is what we'll be working with.

48
00:02:40,360 --> 00:02:42,940
So we have one, two, three, four, five users.

49
00:02:43,300 --> 00:02:47,380
Students keep doing that and then we have six.

50
00:02:47,410 --> 00:02:52,660
Nope, I can't count five different papers, but there's a couple important things.

51
00:02:52,660 --> 00:02:56,760
One is that a couple of students actually have multiple papers.

52
00:02:56,770 --> 00:02:58,930
So, for instance, if we take Caleb.

53
00:02:59,800 --> 00:03:05,440
His ID is one, although you can't see that here, but he's the first one inserted, so his idea is

54
00:03:05,440 --> 00:03:05,740
one.

55
00:03:05,740 --> 00:03:11,200
And he has my first book report, which didn't get a great grade, 60% and my second book report, which

56
00:03:11,200 --> 00:03:13,690
only did marginally better, 75%.

57
00:03:14,620 --> 00:03:20,410
Then we have Samantha who wrote Russian Lit Through the Ages, and we also have another paper by her

58
00:03:20,440 --> 00:03:22,720
to Montaigne and the art of the essay.

59
00:03:23,950 --> 00:03:28,570
And then Raj looks like we don't have anything from Raj where his idea is.

60
00:03:28,570 --> 00:03:29,110
Three.

61
00:03:29,110 --> 00:03:31,790
I don't see anyone or any paper with ID of three.

62
00:03:31,810 --> 00:03:38,890
Then we have Carlos who wrote Borges and Magical Realism, and then we have Lisa who didn't submit anything

63
00:03:38,890 --> 00:03:39,430
either.

64
00:03:40,180 --> 00:03:44,740
So let's say in the whole point of this is that it's the end of the year and I'm trying to figure out

65
00:03:44,740 --> 00:03:47,590
who submitted what papers, what their average grades are.

66
00:03:47,920 --> 00:03:53,440
If I need to talk to somebody about they didn't turn anything in, who's going to fail and so on.

67
00:03:53,440 --> 00:03:57,940
So here's the data we're working with, but the first thing you need to do is write the schema.

68
00:03:58,990 --> 00:04:07,420
So just again, to reiterate, primary key, foreign key for cha cha cha integer integer.

69
00:04:09,130 --> 00:04:10,060
Moving on.

70
00:04:10,060 --> 00:04:13,960
Once you have that done and you have your data in, you can play around with it.

71
00:04:14,560 --> 00:04:17,470
But I'd like you to print this first table.

72
00:04:18,160 --> 00:04:26,320
So all that we have here is first the name of a student and their corresponding paper, the title and

73
00:04:26,320 --> 00:04:26,980
the grade.

74
00:04:27,970 --> 00:04:31,060
Samantha and her two papers, Carlos and his paper.

75
00:04:31,780 --> 00:04:35,380
Caleb and his two papers and then the corresponding grades.

76
00:04:35,620 --> 00:04:37,030
So this is a joint.

77
00:04:37,330 --> 00:04:39,040
What type of joint is it?

78
00:04:40,310 --> 00:04:41,960
And you only need these three things.

79
00:04:41,960 --> 00:04:47,750
So I don't want to see ID, I don't want to see student ID, just first name, title and grade.

80
00:04:48,680 --> 00:04:52,040
And then once you've done that, we have another join.

81
00:04:52,040 --> 00:04:52,640
I'll give you a hint.

82
00:04:52,640 --> 00:04:55,820
Everything in this section, in this exercise involves a joint.

83
00:04:56,720 --> 00:05:00,500
This one is different because we have the same basic stuff.

84
00:05:00,500 --> 00:05:02,210
Caleb and his two book reports.

85
00:05:02,210 --> 00:05:04,010
Samantha her two papers.

86
00:05:04,100 --> 00:05:07,970
Raj Though no title, no grade.

87
00:05:07,970 --> 00:05:09,320
Raj didn't have a paper.

88
00:05:09,920 --> 00:05:12,380
Carlos Borges And Magical Realism.

89
00:05:12,380 --> 00:05:13,400
And then Lisa.

90
00:05:13,430 --> 00:05:16,010
No, no, Lisa doesn't have a paper either.

91
00:05:16,580 --> 00:05:17,590
So print this out.

92
00:05:17,600 --> 00:05:18,740
It's a different joint.

93
00:05:19,630 --> 00:05:24,130
Then once you've done that, move on similar.

94
00:05:24,790 --> 00:05:31,460
But notice, instead of having null and null when a student didn't turn any papers in.

95
00:05:31,480 --> 00:05:36,910
We now have missing and zero for their grade missing for title zero for their grade.

96
00:05:37,390 --> 00:05:39,310
So it's the same thing here.

97
00:05:39,490 --> 00:05:40,690
We're just subbing in.

98
00:05:43,110 --> 00:05:44,400
Then we get to this.

99
00:05:44,790 --> 00:05:48,210
This is going to be an average grade next to every student.

100
00:05:48,690 --> 00:05:52,770
So we have Samantha and the average of her two papers is 96.

101
00:05:52,980 --> 00:05:57,090
Carlos The average of his one paper is 89.

102
00:05:57,210 --> 00:06:00,420
Caleb's average for two papers, 67.5.

103
00:06:00,900 --> 00:06:06,090
Raj has an average of zero because he didn't turn a paper in and Lisa has an average of zero as well.

104
00:06:06,900 --> 00:06:11,840
So I won't give away too much about this, but you can use the same John you used here.

105
00:06:11,850 --> 00:06:13,880
You just then need to condense it.

106
00:06:13,890 --> 00:06:15,660
We need less information.

107
00:06:16,020 --> 00:06:17,250
You'll need to.

108
00:06:17,430 --> 00:06:19,290
Well, that's I'll leave it at that.

109
00:06:20,160 --> 00:06:22,440
And then finally print this.

110
00:06:22,560 --> 00:06:28,230
An extension of what this one was, except there's a new column which is passing status and passing

111
00:06:28,230 --> 00:06:28,890
status.

112
00:06:29,190 --> 00:06:34,320
Just says if the student is passing our class or not in the threshold, I should I should have written

113
00:06:34,320 --> 00:06:34,810
this out.

114
00:06:34,830 --> 00:06:38,580
The threshold is if they get a 75 average in the class or higher.

115
00:06:39,300 --> 00:06:41,100
So Samantha passes.

116
00:06:41,100 --> 00:06:42,240
Carlos passes.

117
00:06:42,270 --> 00:06:44,400
Caleb, unfortunately, is below 75.

118
00:06:44,400 --> 00:06:47,040
So he fails, as do Raj and Lisa.

119
00:06:47,610 --> 00:06:50,640
All right, so there's a bunch of joints you need to write.

120
00:06:51,030 --> 00:06:52,080
First things first, though.

121
00:06:52,080 --> 00:06:56,700
Get the data in there, create the schema, get the data in, play around with it, and then attempt

122
00:06:56,700 --> 00:06:57,420
these.

123
00:06:57,420 --> 00:06:57,900
How many?

124
00:06:57,900 --> 00:06:58,800
Five joints.