WEBVTT 00:00.120 --> 00:01.160 All right. 00:01.170 --> 00:07.570 So we made it through or at least I made it through hopefully still hanging in there over the years 00:07.780 --> 00:09.830 you're alive and you survived that section. 00:09.840 --> 00:11.920 I know it can be brutal. 00:11.940 --> 00:17.120 This is for me and for most teachers the hardest thing to teach. 00:17.460 --> 00:21.670 Not because it's technically that difficult but it's a big thing. 00:21.750 --> 00:25.890 We have to have multiple tables and have multiple tables so we have to create schema we have to insert 00:25.890 --> 00:31.890 the data then we have to you know show what the data looks like to understand it and then we can get 00:31.890 --> 00:37.560 started with the joint and you have to manipulate the data to change it just a lot of work and it's 00:37.560 --> 00:38.160 tough. 00:38.160 --> 00:44.020 It's tough to keep it engaging and frankly I don't think that that was terribly engaging. 00:44.640 --> 00:46.120 So I'm sorry. 00:46.620 --> 00:52.530 Hopefully you made it and I'm doing my best to make the rest of this a little bit more exciting because 00:53.070 --> 00:57.090 once we have these tools and are built into a lot easier to start doing case studies which we'll be 00:57.090 --> 01:00.270 doing are making it full application. 01:00.270 --> 01:08.590 We're getting close to end products actually making things rather than you know silly one off the examples. 01:08.920 --> 01:09.270 OK. 01:09.300 --> 01:10.930 So it's a long preamble. 01:11.220 --> 01:17.880 It's now your turn and this is where you're probably if you're confused if you're stuck with this joint 01:17.880 --> 01:18.180 stuff. 01:18.180 --> 01:22.760 This is hopefully where you'll be able to make some big connections to have some breakthroughs. 01:22.770 --> 01:27.870 So it's this is a little different than exercise if you've done the past there's only one schema on 01:27.860 --> 01:33.240 one table you'll be working with and there's not even that many questions but the first thing is that 01:33.270 --> 01:35.610 we're going to be working with students and papers. 01:35.610 --> 01:41.730 So I'm a teacher let's suppose I guess I am teacher but let's say I'm working in a classroom with high 01:41.730 --> 01:46.430 school students in an English class or literature class it's be very very simple data. 01:46.550 --> 01:52.110 I'm telling you what I'm looking for students and papers and for students will only worry about an ID 01:52.170 --> 01:54.730 and their first names so don't even care about the last name. 01:55.140 --> 01:59.850 Well let's make sure first name is unique and then we're going to have papers. 02:00.060 --> 02:06.810 And it's a one to many relationship one student can have multiple papers but a paper can only belong 02:06.810 --> 02:07.800 to one student. 02:08.160 --> 02:14.040 So these papers will have a title a grade A letter grade like her not a letter grade a numeric grade 02:14.040 --> 02:18.490 excuse me like 90 or 60 if they don't do particularly well. 02:18.750 --> 02:26.400 And then a foreign key student ID which is referencing a student ID OK. 02:26.610 --> 02:31.290 So that's the basic schema and I'd like for you to first go through and actually implement this. 02:31.410 --> 02:36.680 And don't worry about the data because in the next slide I'm giving you the data you can copy and paste 02:36.680 --> 02:37.420 it in. 02:37.860 --> 02:40.410 So this data is what we'll be working with. 02:40.410 --> 02:43.090 So we have one two three four five users. 02:43.270 --> 02:45.390 Students doing that. 02:45.390 --> 02:47.540 And then we have six. 02:47.700 --> 02:52.810 I can count five different papers but there's a couple important things. 02:52.830 --> 02:56.690 One is that a couple of students actually have multiple papers. 02:56.880 --> 03:03.700 So for instance if we take Caleb his ID is one that you can't see that here but he's the first one inserted. 03:03.780 --> 03:09.770 So his idea is one and he has my first book report which didn't get a great grade and 60 percent. 03:09.840 --> 03:16.680 My second book report and which only did marginally better 75 percent than we have Samantha who wrote 03:16.950 --> 03:18.360 Russian let through the ages. 03:18.480 --> 03:22.740 And we also have another paper by her to maintain the art of the essay. 03:24.090 --> 03:29.070 And then Raj looks like we don't have anything from Rush where his idea is three. 03:29.250 --> 03:34.770 I don't see anyone or any paper with the idea of three and we have Carlos who wrote or has in magical 03:34.770 --> 03:36.360 realism. 03:36.360 --> 03:40.290 And then we have Lisa who didn't submit anything either. 03:40.290 --> 03:45.000 So let's say the whole point of this is that it's the end of the year and I'm trying to figure out who 03:45.000 --> 03:47.880 submitted what papers what their average grades are. 03:48.000 --> 03:53.040 If I need to you know talk to somebody about they didn't turn anything in who's going to fail and so 03:53.040 --> 03:53.610 on. 03:53.610 --> 03:55.420 So here's a data we're working with. 03:55.590 --> 03:59.020 But the first thing you need to do is write the schema. 03:59.110 --> 04:07.710 So just again to reiterate primary key foreign key are far more integer integer. 04:08.220 --> 04:10.270 OK moving on. 04:10.270 --> 04:14.150 Once you have that done and you have your data in and play around with it. 04:14.680 --> 04:22.930 But I'd like you to print this first table so all that we have here is first the name of the student 04:23.680 --> 04:27.930 and their corresponding paper the title and the grade. 04:28.030 --> 04:31.620 Samantha and her two papers Carlos and his paper cart. 04:31.720 --> 04:35.490 Caleb and his two papers and the corresponding grades. 04:35.800 --> 04:37.030 So this is a joint. 04:37.480 --> 04:39.090 What type of join is it. 04:40.320 --> 04:46.260 And you only need these three things so I don't want to see I don't want to see student id just first 04:46.260 --> 04:47.980 name title and grade. 04:48.750 --> 04:51.980 And then once you've done that we have another joy. 04:52.020 --> 04:52.580 I'll give you a hint. 04:52.580 --> 04:56.660 Everything in the section in this exercise involves a joint. 04:56.820 --> 05:00.490 This one is different because we have the same basic stuff. 05:00.490 --> 05:08.020 Caleb in his two book reports Samantha her two papers rush though no title no grade. 05:08.040 --> 05:09.940 Raj didn't have a paper. 05:10.230 --> 05:12.600 Carlos Boreham and magical realism. 05:12.600 --> 05:13.410 And then Lisa. 05:13.470 --> 05:16.420 No no this is not a paper either. 05:16.650 --> 05:17.550 So print this out. 05:17.570 --> 05:28.020 It's a different joint and once you've done that move on similar but notice instead of having no and 05:28.030 --> 05:29.470 no. 05:29.470 --> 05:35.200 When a student didn't turn any papers that we now have missing and zero for their grade missing for 05:35.200 --> 05:36.960 title 0 for their grade. 05:37.510 --> 05:39.600 So it's the same thing here. 05:39.610 --> 05:42.540 We're just subbing in OK. 05:43.200 --> 05:44.940 Then we get to this. 05:44.940 --> 05:48.840 This is going to be an average grade next to every student. 05:48.840 --> 05:57.170 So we have Samantha and the average of her two papers is 96 Karlos the average of his one paper is 89. 05:57.360 --> 05:58.920 Caleb's average for two papers. 05:58.950 --> 06:00.970 Sixty seven point five. 06:01.020 --> 06:02.700 Raj has an average of zero. 06:02.740 --> 06:03.960 He didn't turn a paper in. 06:04.100 --> 06:06.390 And Lisa has an average of zero as well. 06:07.050 --> 06:12.570 So I won't give away too much about this but you can use the same joint you used here you just then 06:12.570 --> 06:13.970 need to condense it. 06:13.980 --> 06:17.270 We need less information you'll need to. 06:17.520 --> 06:19.660 Well that's I'll leave it at that. 06:20.280 --> 06:26.670 And then finally print this an extension of what this one was except there's a new column which is passing 06:26.670 --> 06:29.220 status and passing status. 06:29.250 --> 06:34.290 It just says if a student is passing or class or not and the threshold shouldn't I should have written 06:34.290 --> 06:34.870 this out. 06:34.890 --> 06:38.910 The threshold is if they get a 75 average in the class or higher. 06:39.390 --> 06:42.380 So Samantha passes Carlos passes. 06:42.390 --> 06:47.640 Caleb Unfortunately it's below 75 so he fails as you Raj and Lisa. 06:47.640 --> 06:48.300 All right. 06:48.300 --> 06:51.090 So there's a bunch of joints I need to write. 06:51.090 --> 06:55.650 First things first though get the data in there create the schema get the data in play around with it 06:55.920 --> 06:57.350 and then attempt these. 06:57.420 --> 06:58.620 How many five joints.