WEBVTT 00:00.150 --> 00:06.190 And I'm back here with a solution or multiple solutions to go over these joint problems. 00:06.480 --> 00:11.430 So again this is a one to many relationship students and papers and the first thing we're going to do 00:11.430 --> 00:14.260 is go over to cloud 9. 00:14.550 --> 00:19.080 And I'm just going to work in the same database but I'm going to make a new file just because it's getting 00:19.080 --> 00:21.220 crazy and I'll call this one. 00:21.270 --> 00:24.220 Students papers as well. 00:24.810 --> 00:30.540 OK so we know we need to create two tables create table students and we're going to leave it empty for 00:30.540 --> 00:37.260 now and then create table papers also leave it empty. 00:37.260 --> 00:38.750 Great. 00:38.820 --> 00:44.000 So next what we put in so we'll start with students simple. 00:44.020 --> 00:49.960 It's an idee that we said as a primary key integer which means it should also auto increment and thus 00:49.970 --> 00:51.640 we want to do it manually but we don't. 00:51.800 --> 00:53.830 And then first name is have our char. 00:54.290 --> 01:06.380 So we have ID Ent. autoincrement primary key and then we have a first name which is just var char and 01:06.380 --> 01:07.230 we'll do 100. 01:07.250 --> 01:10.250 That's a good even nice number. 01:10.250 --> 01:11.640 So that's it for students. 01:11.970 --> 01:17.030 But then we have papers so papers has a title grade and student ID. 01:17.030 --> 01:25.710 Let's start with just title grade student or score ID perfect. 01:25.910 --> 01:27.960 And I'm noticing you know I capitalized this. 01:28.180 --> 01:32.210 It doesn't really matter but it's going to be annoying to remember that so I'm just going to put it 01:32.210 --> 01:32.590 back. 01:32.610 --> 01:34.890 This lower case ID. 01:34.910 --> 01:38.210 So you've got title grade student the title is of our char. 01:38.630 --> 01:43.100 So do 100 grade isn't it. 01:43.190 --> 01:51.770 And then we have student I.D. and student ID is a foreign key referencing students dot ID. 01:51.770 --> 01:58.210 So it's just an integer and this sometimes trips people up that we just right student ID in. 01:58.370 --> 02:07.330 And then the next line is a foreign key the field that is a foreign key is student ID. 02:07.370 --> 02:08.760 This right here. 02:09.190 --> 02:11.360 And when you just say what it references. 02:11.360 --> 02:16.370 So it's referencing Students table the ID field. 02:16.870 --> 02:17.130 OK. 02:17.150 --> 02:23.450 And we could also indented like this because this has to do with this. 02:23.780 --> 02:32.390 And then if we wanted to which you did not need to do but this is where we could add on delete cascade 02:33.530 --> 02:37.000 which you may and we saw in the last video or two videos ago. 02:37.250 --> 02:40.970 It's not necessary here we're not reading anything but you could put that there. 02:41.360 --> 02:41.750 OK. 02:41.780 --> 02:43.830 The next step is now just to create them. 02:44.210 --> 02:47.030 So before I go too far I did catch this. 02:47.030 --> 02:48.890 I have an extra semi-colon there. 02:48.890 --> 02:54.620 Get rid of that but we'll create our students table and we'll create our papers table. 02:54.620 --> 02:59.940 Everything looks good and if we do show tables I'm still working in our same database. 02:59.960 --> 03:03.400 I have customers and orders that's fine but I papers and students. 03:03.790 --> 03:04.540 OK. 03:05.000 --> 03:06.860 And we haven't done this a while. 03:06.870 --> 03:10.990 How about we describe students. 03:11.260 --> 03:15.640 So we now have autoincrement and their primary key and that's for students. 03:15.880 --> 03:23.500 And what about papers we do describe papers where you can see we have title and grade and then student 03:23.500 --> 03:24.280 ID. 03:24.760 --> 03:25.320 OK. 03:25.550 --> 03:28.140 So next step is to get our data in there. 03:28.390 --> 03:31.300 And I said that you definitely didn't have to do this yourself. 03:31.510 --> 03:37.000 So I'm just going to put it in this file so you have a solution file. 03:37.240 --> 03:38.680 But it would be annoying to type this. 03:38.690 --> 03:41.130 I totally I mean I had to type it. 03:41.380 --> 03:42.300 I totally get it. 03:42.310 --> 03:43.790 So paste that in. 03:43.870 --> 03:44.350 Perfect. 03:44.350 --> 03:51.850 So now we can do a select star from students and we should see our five students and do a select star 03:53.320 --> 03:59.380 from papers and we see our five papers with student IDs. 03:59.380 --> 04:00.450 Great. 04:00.460 --> 04:08.610 So first thing to do was to print this join table where we have first name title and Grade Nothing else 04:08.620 --> 04:12.600 first name title and grade where and what's our joint condition here. 04:12.610 --> 04:21.370 Well basically where the student ID is equal to the student id in the paper and this we could accomplish 04:21.460 --> 04:27.210 through an inner join would be the easiest way we could also do with the right join if you want to which 04:27.200 --> 04:30.200 will take a look at put an inner join is the simplest way. 04:30.520 --> 04:33.610 And I'm going to do an explicit inner join. 04:33.670 --> 04:40.240 So that means select and let's start with Star from and let me put a number here. 04:40.250 --> 04:47.840 Exercise 1 select star from let's do it on a different line from students. 04:50.070 --> 04:57.850 Join papers on students ID schools papers. 04:58.110 --> 04:59.650 Student ID. 04:59.850 --> 05:05.420 And let's be explicit with our joint just like that. 05:05.450 --> 05:09.310 So if we take a look you see we have the right data. 05:09.320 --> 05:13.160 Right we have Caleb and his two books Samantha or two reports. 05:13.280 --> 05:18.230 Samantha her two SS Carlos and his one essay and we have the grades now. 05:18.250 --> 05:23.180 We don't want I.D. and student ID so we just need to get rid of that. 05:23.300 --> 05:29.330 And the simplest way is just to explicitly say what we want. 05:29.430 --> 05:31.360 So great title and great. 05:31.470 --> 05:32.500 OK. 05:32.670 --> 05:35.450 Now we do it and we're good to go. 05:37.100 --> 05:44.000 There's one thing and this is my fault for not mentioning in the instructions I did order this and totally 05:44.510 --> 05:46.580 fine if you didn't do it if you did catch it. 05:46.580 --> 05:48.470 Congratulations on looking at that. 05:48.500 --> 05:49.130 I didn't. 05:49.280 --> 05:49.960 I forgot. 05:50.030 --> 05:52.020 Did this late last night when I created these slides. 05:52.020 --> 05:53.480 I'm recording it the next morning. 05:53.720 --> 05:56.500 So this was ordered by grade. 05:56.600 --> 05:57.840 So that's simple too. 05:58.250 --> 06:01.480 Just going to be order by grade. 06:01.610 --> 06:02.690 And what order is it. 06:02.810 --> 06:04.210 Well it's sending 06:07.700 --> 06:10.750 Now we do it now we have the correct answer. 06:10.760 --> 06:13.220 The other one is correct to a promise. 06:13.220 --> 06:18.230 The only thing that I'll address here is that I mentioned that you could also do a write join and why 06:18.230 --> 06:18.970 is that. 06:19.250 --> 06:20.170 Well let's just verify. 06:20.170 --> 06:21.860 See if I'm crazy. 06:21.860 --> 06:23.330 No it still works. 06:23.510 --> 06:24.290 Good to know. 06:24.510 --> 06:25.170 I'm just kidding. 06:25.200 --> 06:33.020 But the reason that it works is because if we go back to our slides way back when we do an inner join 06:33.020 --> 06:39.120 here between students and papers all of our papers have a student they belong to. 06:39.460 --> 06:49.480 So basically this section is the same as this entire section here the same as this if we pretend that 06:49.540 --> 06:51.180 left join doesn't exist. 06:51.190 --> 06:52.500 Ignore that slide. 06:52.600 --> 07:00.650 This intersection is the same as this because every paper has a student ID in it. 07:00.850 --> 07:02.440 So you could also do it right Turing. 07:02.470 --> 07:10.000 Nothing wrong with that but I think the inner join us easiest although it's it's easiest because it's 07:10.000 --> 07:15.700 just this is how I think about joints most of the time but it's not any easier because it's just a single 07:15.700 --> 07:18.310 line or a single word that we're changing. 07:18.640 --> 07:21.810 OK so I'll just write Allt solution. 07:22.290 --> 07:22.810 OK. 07:23.050 --> 07:24.930 Moving on. 07:25.000 --> 07:26.400 Print this. 07:26.410 --> 07:28.380 So very similar right. 07:28.480 --> 07:31.140 Caleb Samantha their respective books. 07:31.150 --> 07:39.950 But then we have Raj and Lisa who didn't write any reports or any papers and we have no in place. 07:39.970 --> 07:42.050 So this is a left join. 07:42.760 --> 07:49.300 And the way that we're going to do that if we go back to our slides again we're going to grab everything 07:49.300 --> 07:53.260 from students and then any associated papers. 07:53.410 --> 07:54.780 But some of them don't have papers. 07:54.850 --> 07:57.490 And those will then be filled in with no. 07:58.420 --> 08:01.330 So let's do this now call this 08:04.230 --> 08:13.240 let's call this problem too and start with select start again and then we'll alter that from students 08:14.770 --> 08:26.490 and then we need to do a left join papers on students that ID equals papers that student ID and we'll 08:26.490 --> 08:32.380 leave it at that to start and you can see it's working. 08:33.000 --> 08:38.610 We've got Caleb and his stuff Samantha her essays her papers Raj. 08:38.820 --> 08:40.120 No no no. 08:40.290 --> 08:41.710 LISA No no no. 08:42.090 --> 08:49.440 So we're getting every single student Kalos Samantha Raj Carlos Lisa and if they have a matching condition 08:49.650 --> 08:52.990 in the papers table then we also get their paper data. 08:53.070 --> 08:55.270 But if they don't we have no. 08:55.440 --> 08:59.130 The only thing left is to whittle it down to first name title in grade 09:02.420 --> 09:03.740 just like that. 09:04.080 --> 09:06.770 Varify OK. 09:06.910 --> 09:08.300 And is there any sorting here. 09:08.300 --> 09:08.930 No. 09:09.200 --> 09:09.850 And we're done.