1 00:00:00,120 --> 00:00:06,150 And I'm back here with a solution or multiple solutions to go over these joint problems. 2 00:00:06,270 --> 00:00:09,960 So again, this was a one to many relationship students and papers. 3 00:00:10,200 --> 00:00:14,340 And the first thing we're going to do is go over to cloud nine. 4 00:00:14,340 --> 00:00:18,900 And I'm just going to work in the same database, but I'm going to make a new file just because it's 5 00:00:18,900 --> 00:00:19,850 getting crazy. 6 00:00:19,860 --> 00:00:23,820 And I'll call this one student papers SQL. 7 00:00:25,740 --> 00:00:30,450 So we know we need to create two tables, create table students, and I'm just going to leave it empty 8 00:00:30,450 --> 00:00:34,770 for now and then create table papers. 9 00:00:34,920 --> 00:00:36,060 Also leave it empty. 10 00:00:37,260 --> 00:00:37,860 Great. 11 00:00:38,730 --> 00:00:40,920 So next, what do we put in them? 12 00:00:42,170 --> 00:00:43,430 So we'll start with students. 13 00:00:43,430 --> 00:00:44,030 Simple. 14 00:00:44,030 --> 00:00:50,000 It's an ID, but we said it's a primary key integer, which means it should also auto increment unless 15 00:00:50,000 --> 00:00:51,590 we want to do it manually, but we don't. 16 00:00:51,590 --> 00:00:53,450 And then first name is a var char. 17 00:00:54,200 --> 00:01:06,380 So we have ID int auto increment primary key and then we have first name which is just var char and 18 00:01:06,380 --> 00:01:07,250 we'll do 100. 19 00:01:07,250 --> 00:01:09,200 That's a good even nice number. 20 00:01:10,130 --> 00:01:11,570 So that's it for students. 21 00:01:11,840 --> 00:01:13,130 But then we have papers. 22 00:01:13,280 --> 00:01:15,980 So papers has a title grade in student ID. 23 00:01:16,940 --> 00:01:23,210 Let's start with just title grade student underscore ID. 24 00:01:24,350 --> 00:01:25,130 Perfect. 25 00:01:25,760 --> 00:01:27,860 And I'm noticing I capitalize this. 26 00:01:28,280 --> 00:01:31,400 It doesn't really matter, but it's going to be annoying to remember that. 27 00:01:31,400 --> 00:01:33,860 So I'm just going to put it back as lowercase ID. 28 00:01:34,790 --> 00:01:36,680 So we've got title, grade and student ID. 29 00:01:36,710 --> 00:01:38,180 Title is a bar chart. 30 00:01:38,360 --> 00:01:39,680 We'll also do 100. 31 00:01:40,370 --> 00:01:41,930 Grade is an int. 32 00:01:43,040 --> 00:01:51,320 And then we have student ID and student ID is a foreign key referencing student's dot ID. 33 00:01:51,620 --> 00:01:53,810 So it's just an integer. 34 00:01:54,020 --> 00:01:57,830 And this sometimes trips people up that we just write student ID. 35 00:01:57,860 --> 00:02:01,160 INT And then the next line is foreign key. 36 00:02:03,020 --> 00:02:10,880 The field that is foreign key is student ID this right here and we need to say what it references. 37 00:02:11,240 --> 00:02:21,560 So it's referencing student table, the ID field and we could also indent it like this because this 38 00:02:21,560 --> 00:02:22,820 has to do with this. 39 00:02:23,660 --> 00:02:26,630 And then if we wanted to, which you did not need to do. 40 00:02:26,630 --> 00:02:28,400 But this is where we could add. 41 00:02:30,250 --> 00:02:34,480 On Delete Cascade, which you may. 42 00:02:34,480 --> 00:02:38,200 And we saw in the last video or two videos ago, it's not necessary here. 43 00:02:38,200 --> 00:02:40,810 We're not deleting anything, but you could put that there. 44 00:02:41,350 --> 00:02:41,520 Okay. 45 00:02:41,710 --> 00:02:43,690 So the next step is now just to create them. 46 00:02:44,050 --> 00:02:49,750 So before I go too far, I did catch this, I have an extra semicolon there, get rid of that. 47 00:02:50,290 --> 00:02:53,800 But we'll create our students table and we'll create our papers table. 48 00:02:54,550 --> 00:02:55,780 Everything looks good. 49 00:02:55,780 --> 00:03:01,060 And if we do show tables, I'm still working in our same database, so I have customers and orders. 50 00:03:01,060 --> 00:03:06,820 That's fine, but I have papers and students and we haven't done this a while. 51 00:03:06,850 --> 00:03:08,890 How about we describe students? 52 00:03:10,370 --> 00:03:10,880 Cool. 53 00:03:11,150 --> 00:03:15,650 So we now have auto increment in their primary key and that's for students. 54 00:03:15,740 --> 00:03:17,050 And what about papers? 55 00:03:17,060 --> 00:03:19,160 If we do describe papers. 56 00:03:20,750 --> 00:03:24,110 But you can see we have title and grade and then student ID. 57 00:03:25,370 --> 00:03:28,220 So next up is to get our data in there. 58 00:03:28,220 --> 00:03:34,130 And I said that you definitely didn't have to do this yourself, so I'm just going to put it in this 59 00:03:34,130 --> 00:03:35,870 file so you have a solution file. 60 00:03:37,130 --> 00:03:38,690 But it would be annoying to type this. 61 00:03:38,690 --> 00:03:40,970 I totally I mean, I had to type it. 62 00:03:41,300 --> 00:03:42,170 I totally get it. 63 00:03:42,170 --> 00:03:44,330 So paste that in perfect. 64 00:03:44,330 --> 00:03:52,070 So now we can do a select star from students and we should see our five students and do a select star 65 00:03:53,300 --> 00:03:59,030 from papers and we see our five papers with student IDs. 66 00:03:59,270 --> 00:03:59,960 Great. 67 00:04:00,350 --> 00:04:07,370 So the first thing to do was to print this joint table where we have first name, title and grade, 68 00:04:07,940 --> 00:04:09,950 nothing else, first name, title and grade. 69 00:04:09,950 --> 00:04:12,620 Where and what's our joint condition here? 70 00:04:12,620 --> 00:04:21,440 Well, basically where the student ID is equal to the student ID in the paper, and this we could accomplish 71 00:04:21,440 --> 00:04:22,550 through an inner join. 72 00:04:22,550 --> 00:04:23,810 That would be the easiest way. 73 00:04:23,930 --> 00:04:28,100 We could also do it with the right join if you wanted to, which we'll take a look at. 74 00:04:28,100 --> 00:04:33,470 But an inner joint is the simplest way and I'm going to do an explicit inner join. 75 00:04:33,470 --> 00:04:35,510 So that means select. 76 00:04:35,510 --> 00:04:39,950 And let's start with star from and let me put a number here. 77 00:04:40,190 --> 00:04:44,180 Exercise one select star from. 78 00:04:45,260 --> 00:04:47,690 I'll do it on a different line from students. 79 00:04:50,010 --> 00:05:03,270 Join papers on student ID equals papers dot student ID and let's be explicit with inner join just like 80 00:05:03,270 --> 00:05:03,720 that. 81 00:05:05,420 --> 00:05:09,530 So if we take a look, you see, we have the right data, right? 82 00:05:09,530 --> 00:05:13,160 We have Caleb and his two books, Samantha or two reports. 83 00:05:13,160 --> 00:05:14,630 Samantha her two essays. 84 00:05:14,870 --> 00:05:16,420 Carlos and his one essay. 85 00:05:16,430 --> 00:05:17,600 And we have the grades. 86 00:05:17,720 --> 00:05:23,170 Now we don't want ID and student ID, so we just need to get rid of that. 87 00:05:23,180 --> 00:05:25,880 And the simplest way is just to. 88 00:05:27,810 --> 00:05:29,350 Explicitly say what we want. 89 00:05:29,370 --> 00:05:31,410 So grade, title and grade. 90 00:05:32,580 --> 00:05:35,040 Now we do it and we're good to go. 91 00:05:37,070 --> 00:05:41,180 There's one thing, and this is my fault for not mentioning in the instructions. 92 00:05:41,180 --> 00:05:45,790 I did order this and totally fine if you didn't do it. 93 00:05:45,800 --> 00:05:46,600 If you did catch it. 94 00:05:46,610 --> 00:05:48,380 Congratulations on looking at that. 95 00:05:48,380 --> 00:05:52,000 I didn't I forgot I did this late last night when I created these slides. 96 00:05:52,030 --> 00:05:53,390 I'm recording it the next morning. 97 00:05:53,540 --> 00:05:56,540 So this was ordered by grade. 98 00:05:56,570 --> 00:06:01,400 So that's simple to just going to be order by grade. 99 00:06:01,400 --> 00:06:02,690 And what order is it? 100 00:06:02,690 --> 00:06:04,490 Well, it's descending. 101 00:06:07,640 --> 00:06:08,720 Now we do it. 102 00:06:08,720 --> 00:06:10,580 Now we have the correct answer. 103 00:06:10,610 --> 00:06:12,470 The other one is correct too, I promise. 104 00:06:13,190 --> 00:06:17,680 The only thing that I'll address here is that I mentioned that you could also do a write join. 105 00:06:17,690 --> 00:06:19,010 And why is that? 106 00:06:19,100 --> 00:06:21,290 Well, let's just verify, see if I'm crazy. 107 00:06:21,710 --> 00:06:22,130 Nope. 108 00:06:22,130 --> 00:06:23,000 It still works. 109 00:06:23,480 --> 00:06:24,170 Good to know. 110 00:06:24,510 --> 00:06:25,160 I'm just kidding. 111 00:06:25,160 --> 00:06:33,050 But the reason that it works is because if we go back to our slides way back when we do an inner join 112 00:06:33,050 --> 00:06:35,390 here between students and papers. 113 00:06:36,250 --> 00:06:39,070 All of our papers have a student they belong to. 114 00:06:39,250 --> 00:06:47,680 So basically this section is the same as this entire section here, the same as this. 115 00:06:48,370 --> 00:06:50,710 If we pretend that left join doesn't exist. 116 00:06:51,180 --> 00:06:52,240 Ignore that slide. 117 00:06:52,480 --> 00:07:00,070 This intersection is the same as this because every paper has a student ID in it. 118 00:07:00,700 --> 00:07:02,320 So you could also do a right join. 119 00:07:02,350 --> 00:07:03,490 Nothing wrong with that. 120 00:07:04,180 --> 00:07:12,190 But I think the inner join is easiest, although it's its easiest because it's just this is how I think 121 00:07:12,190 --> 00:07:13,810 about joints most of the time. 122 00:07:13,810 --> 00:07:18,250 But it's not any easier because it's just a single line or a single word that we're changing. 123 00:07:18,880 --> 00:07:21,580 So I'll just write alt solution. 124 00:07:22,400 --> 00:07:23,680 OC Moving on. 125 00:07:25,010 --> 00:07:25,940 Print this. 126 00:07:26,270 --> 00:07:28,010 So very similar, right? 127 00:07:28,400 --> 00:07:31,160 Caleb, Samantha, their respective books. 128 00:07:31,160 --> 00:07:39,410 But then we have Raj and Lisa who didn't write any reports or any papers and we have null in place. 129 00:07:39,920 --> 00:07:41,930 So this is a left join. 130 00:07:42,650 --> 00:07:49,340 And the way that we're going to do that, if we go back to our slides again, we're going to grab everything 131 00:07:49,340 --> 00:07:55,610 from students and then any associated papers, but some of them don't have papers and those will then 132 00:07:55,610 --> 00:07:56,780 be filled in with null. 133 00:07:58,310 --> 00:07:59,780 So let's do this now. 134 00:08:00,860 --> 00:08:01,580 Call this 135 00:08:04,250 --> 00:08:13,520 let's call this problem two and we'll start with Select Star again and then we'll alter that from students. 136 00:08:14,660 --> 00:08:26,540 And then we need to do a left join papers on students that ID equals papers dot student ID and we'll 137 00:08:26,540 --> 00:08:27,800 leave it at that to start. 138 00:08:29,970 --> 00:08:32,100 And you can see it's working. 139 00:08:32,909 --> 00:08:34,860 We've got Caleb and his stuff. 140 00:08:34,860 --> 00:08:37,549 Samantha, her essays or her papers. 141 00:08:37,559 --> 00:08:38,549 Raj. 142 00:08:38,640 --> 00:08:40,140 No, no, no. 143 00:08:40,169 --> 00:08:40,700 Lisa. 144 00:08:40,710 --> 00:08:41,539 No, no, no. 145 00:08:41,940 --> 00:08:44,910 So we're getting every single student. 146 00:08:45,030 --> 00:08:46,820 Caleb, Samantha, Raj, Carlos, Lisa. 147 00:08:46,830 --> 00:08:53,010 And if they have a matching join condition in the papers table, then we also get their paper data. 148 00:08:53,010 --> 00:08:54,360 But if they don't, we have. 149 00:08:54,360 --> 00:08:59,370 No, the only thing left is to whittle it down to first name, title and grade. 150 00:09:02,340 --> 00:09:03,330 Just like that. 151 00:09:03,720 --> 00:09:04,680 Let's verify. 152 00:09:06,880 --> 00:09:08,170 And is there any sorting here? 153 00:09:08,200 --> 00:09:08,800 No. 154 00:09:09,070 --> 00:09:09,940 Then we're done.