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

2
00:00:00,600 --> 00:00:05,770
It's time to get some practice with joints and foreign keys and all that fun stuff.

3
00:00:05,790 --> 00:00:11,220
So for this exercise, I would like you to start by creating this schema, right?

4
00:00:11,220 --> 00:00:12,390
These two tables.

5
00:00:12,480 --> 00:00:13,470
Relatively simple.

6
00:00:13,470 --> 00:00:15,570
We have students and papers.

7
00:00:15,720 --> 00:00:19,980
So we're a professor or a teacher and we have students.

8
00:00:19,980 --> 00:00:26,040
They write different papers and each student has multiple papers, potentially a midterm paper, a final

9
00:00:26,040 --> 00:00:27,850
paper essay, whatever.

10
00:00:27,870 --> 00:00:31,800
So the way I'd like it to work is that a student has an ID and a first name.

11
00:00:31,800 --> 00:00:32,729
Just keep it simple.

12
00:00:32,729 --> 00:00:34,890
We don't need the last name and other stuff.

13
00:00:35,250 --> 00:00:43,110
And then papers have a title like Russian Lit through the Ages, my second book report.

14
00:00:43,110 --> 00:00:46,710
And then they also have a grade that we assign to them.

15
00:00:46,710 --> 00:00:51,660
Out of 160 would be bad, 98 would be very good.

16
00:00:51,990 --> 00:00:59,460
And then finally we have the student ID, which is a foreign key referencing students ID, and this

17
00:00:59,460 --> 00:01:02,340
should be a primary key auto increment, all that stuff.

18
00:01:02,520 --> 00:01:09,720
So start by writing the two tables and then once you've done that, just copy and paste this information,

19
00:01:09,720 --> 00:01:10,950
these inserts.

20
00:01:10,950 --> 00:01:15,030
I'd like you to insert these into students so that we have the same data to work with.

21
00:01:15,030 --> 00:01:16,500
Just some sample data.

22
00:01:17,060 --> 00:01:23,510
And then once you've done that, I would like for you to print this Joined Together table that has the

23
00:01:23,510 --> 00:01:27,560
first name of every student who wrote a corresponding essay.

24
00:01:27,560 --> 00:01:28,790
So what their essay was.

25
00:01:28,790 --> 00:01:32,570
Samantha has two essays and the grade next to it.

26
00:01:32,570 --> 00:01:34,220
So just a simple join.

27
00:01:34,430 --> 00:01:41,420
You can figure out which one would work here, but it's the overlap between essays and papers.

28
00:01:42,100 --> 00:01:44,810
Then we have this one, which is a little different.

29
00:01:44,830 --> 00:01:46,090
It's hard to notice.

30
00:01:46,570 --> 00:01:52,210
We still have first name, title and grade, but we have these null values down here and here as well.

31
00:01:52,390 --> 00:01:55,330
So what join would result in this?

32
00:01:55,990 --> 00:01:59,500
And then after that, we've got a variation.

33
00:01:59,620 --> 00:02:06,490
Notice that the text becomes missing down here instead of null, and over here instead of null, we

34
00:02:06,490 --> 00:02:07,780
have zero.

35
00:02:07,810 --> 00:02:12,350
So this will need to be some sort of join along with if null.

36
00:02:12,370 --> 00:02:13,390
Just as a hint.

37
00:02:14,170 --> 00:02:14,770
All right.

38
00:02:14,770 --> 00:02:22,780
And then after that, print this here, which is an average of all of the grades each student received

39
00:02:22,780 --> 00:02:23,940
on their papers.

40
00:02:23,950 --> 00:02:27,820
And for some of the students, they don't have papers, Roge and Lisa.

41
00:02:28,030 --> 00:02:30,460
So their average is zero.

42
00:02:30,490 --> 00:02:31,930
They are slackers.

43
00:02:33,050 --> 00:02:37,880
Next up, print, whether it's a passing grade or not.

44
00:02:38,090 --> 00:02:40,550
Based off of the average for each student.

45
00:02:40,550 --> 00:02:43,180
So it's the same data from this example.

46
00:02:43,190 --> 00:02:51,050
But now we have this extra conditional use case here, a case statement that is either passing or failing

47
00:02:51,050 --> 00:02:53,450
and the threshold should be 75.

48
00:02:53,450 --> 00:02:56,510
So if the average is above 75, it's a passing grade.

49
00:02:56,510 --> 00:02:58,310
Otherwise it's a failing grade.

50
00:02:58,400 --> 00:03:01,280
So try it out and I'll be back in the next video with a solution.