1 00:00:00,120 --> 00:00:00,680 Okay. 2 00:00:00,750 --> 00:00:05,610 We're at the final video here, the final challenge and this is the one that puts everything together 3 00:00:06,360 --> 00:00:10,890 or really more what it's doing is putting together all three tables that we have. 4 00:00:10,920 --> 00:00:14,190 So what we're actually getting out of it is not that impressive. 5 00:00:14,490 --> 00:00:18,680 We're not doing any crazy analytics or anything like the previous one. 6 00:00:18,690 --> 00:00:21,470 Not that that was too crazy, but nothing like that. 7 00:00:21,480 --> 00:00:29,760 All that we're doing is putting the title of a movie or a series and then every rating that it got along 8 00:00:29,760 --> 00:00:31,950 with the reviewer who wrote that rating. 9 00:00:32,549 --> 00:00:38,340 So we've done the first part up until here, right where we've done title and rating together or where 10 00:00:38,340 --> 00:00:40,080 we've done reviewer and rating together. 11 00:00:40,260 --> 00:00:45,480 But now we're going to put them all together so that this is the information for one review, right? 12 00:00:45,720 --> 00:00:48,480 It's the movie that was the series that was reviewed. 13 00:00:48,480 --> 00:00:50,100 It was the person who reviewed it. 14 00:00:50,340 --> 00:00:56,250 Notice that we can cut them into one reviewer instead of first name and last name, but that's secondary 15 00:00:56,250 --> 00:00:56,880 at the end. 16 00:00:57,060 --> 00:01:01,650 So we have the series title, the reviewer's name, and then the rating they gave it. 17 00:01:02,070 --> 00:01:07,380 So we're basically just trying to take let's do it here. 18 00:01:07,380 --> 00:01:08,340 Take this. 19 00:01:09,090 --> 00:01:10,140 And fill in the blanks. 20 00:01:10,140 --> 00:01:13,260 So instead of series ID, we're going to get the name of the series. 21 00:01:13,260 --> 00:01:16,080 Instead of the reviewer ID, we're going to get the name of the reviewer. 22 00:01:17,790 --> 00:01:22,950 So let's go ahead and start over here with our handy dandy comment. 23 00:01:22,950 --> 00:01:25,170 And we are on what is this seven? 24 00:01:27,170 --> 00:01:27,560 Six. 25 00:01:27,560 --> 00:01:28,640 Yes, seven. 26 00:01:30,920 --> 00:01:32,720 Three tables. 27 00:01:34,610 --> 00:01:37,160 So we can start off with any of the tables. 28 00:01:37,160 --> 00:01:38,540 We're going to join them all together. 29 00:01:38,540 --> 00:01:41,570 But I'll start with reviewers. 30 00:01:41,570 --> 00:01:45,110 So we'll select start from your viewers just like that. 31 00:01:46,370 --> 00:01:50,120 Except I need from boring. 32 00:01:50,120 --> 00:01:51,230 We've done this before. 33 00:01:51,270 --> 00:01:52,530 Blah, blah, blah, blah, blah. 34 00:01:52,550 --> 00:01:57,470 So the first step is to join it with the reviews table. 35 00:01:57,830 --> 00:02:07,130 So we'll have review ers along with their reviews, along with the series associated with that review. 36 00:02:07,310 --> 00:02:11,320 And we're not going to worry about people who haven't written reviews. 37 00:02:11,330 --> 00:02:12,020 That's fine. 38 00:02:12,020 --> 00:02:13,040 We're ignoring that. 39 00:02:14,630 --> 00:02:19,820 So to do this, let's start off, first of all, by joining it with. 40 00:02:22,330 --> 00:02:25,390 Join reviews. 41 00:02:26,380 --> 00:02:31,840 On reviewers dot id equals reviews dot. 42 00:02:32,170 --> 00:02:35,170 And then we need reviewer ID. 43 00:02:35,980 --> 00:02:37,210 So fun to write. 44 00:02:38,290 --> 00:02:41,720 And we do that and we see things like OC. 45 00:02:41,800 --> 00:02:45,600 Thomas Stoneman gave an 8.0 to some movie. 46 00:02:45,610 --> 00:02:47,740 There are some series with ID of one. 47 00:02:48,100 --> 00:02:51,700 He also gave an 8.1 to whatever has ad2 and so on. 48 00:02:51,700 --> 00:02:54,730 So we're getting close ish. 49 00:02:54,820 --> 00:03:01,960 Now what we need to do is also throw on the series data over here, and the first thing I'll do is just 50 00:03:01,960 --> 00:03:02,890 make this explicit. 51 00:03:02,890 --> 00:03:06,700 It's an inner join, so we're going to do another join. 52 00:03:06,700 --> 00:03:10,630 And it's really easy, at least syntactically, it's easy. 53 00:03:10,630 --> 00:03:12,790 We just write inner join again. 54 00:03:12,790 --> 00:03:14,710 So what are we joining on this time? 55 00:03:14,860 --> 00:03:19,030 Well, we're now going to join on only thing that's left, which is series. 56 00:03:19,180 --> 00:03:23,860 So we took reviewers and reviews Sandridge them together where they intersect. 57 00:03:24,550 --> 00:03:33,700 Now what we're going to do is join a series where the series ID is equal to series ID here inside of 58 00:03:33,700 --> 00:03:34,630 the reviews. 59 00:03:35,200 --> 00:03:38,830 So let's do inner join series. 60 00:03:40,320 --> 00:03:50,790 On Series ID equals reviews dot review dot series underscore ID such a nightmare trying to speak all 61 00:03:50,790 --> 00:03:54,270 this out loud, but let's step through this. 62 00:03:55,620 --> 00:03:59,850 So we have sort of this middle table, let's say, which is right here. 63 00:04:01,140 --> 00:04:08,190 We have ID rating and series ID and reviewer ID for our review that every review has that. 64 00:04:08,190 --> 00:04:12,840 And what we started off was joining it with the relevant reviewers. 65 00:04:13,170 --> 00:04:18,810 Now we're going to also take the series ID and find all matching series and stick them over here. 66 00:04:19,950 --> 00:04:21,750 And it's going to be a messy table. 67 00:04:21,779 --> 00:04:23,940 It's going to get all collapsed and screwed up. 68 00:04:23,940 --> 00:04:25,830 So let's filter out some things. 69 00:04:25,830 --> 00:04:36,870 So let's only work with the title, the rating, first name and last name. 70 00:04:41,810 --> 00:04:42,290 Okay. 71 00:04:42,440 --> 00:04:45,140 So we're getting close now, so we're pretty much there. 72 00:04:45,140 --> 00:04:46,970 We have all of the data together. 73 00:04:47,360 --> 00:04:52,910 So we have Archer has an 8.0 rating from Thomas Stoneman. 74 00:04:53,690 --> 00:04:59,820 Thomas Stoneman also gave Arrested Development an 8.1, and he gave Bob's Burgers at 7.0. 75 00:04:59,840 --> 00:05:05,390 And then we can see we have Archer again down here from Wyatt Scaggs, who gave it a 7.5. 76 00:05:05,690 --> 00:05:08,860 So we have all of our data synthesized in one table. 77 00:05:08,870 --> 00:05:12,920 Now, what we need to do is concatenate things together over here. 78 00:05:13,610 --> 00:05:20,000 So that's a simple all we need to do is instead of doing first name and last name, we'll do a king 79 00:05:20,000 --> 00:05:20,750 cat. 80 00:05:20,750 --> 00:05:22,250 First name, comma. 81 00:05:23,470 --> 00:05:24,030 Space. 82 00:05:24,040 --> 00:05:27,880 Last name as reviewer, I think, is what we called it. 83 00:05:28,300 --> 00:05:28,870 Yep. 84 00:05:30,450 --> 00:05:35,730 And then there's only one thing that we need to change other than that, which is right now things are 85 00:05:35,730 --> 00:05:37,470 ordered by reviewer. 86 00:05:38,940 --> 00:05:42,360 Let's order them by title, which is what we did over here. 87 00:05:42,570 --> 00:05:46,650 So that's just a simple order by title. 88 00:05:47,730 --> 00:05:49,600 Now we should be done. 89 00:05:49,620 --> 00:05:51,990 If I get rid of that extra semicolon. 90 00:05:53,130 --> 00:05:53,970 And here we go. 91 00:05:54,420 --> 00:05:59,700 So we have Archer, all of the ratings for Archer and the people who wrote them. 92 00:06:00,150 --> 00:06:06,120 Arrested Development, all the ratings and people who wrote them and all the way down through all of 93 00:06:06,120 --> 00:06:08,430 our series that have reviews. 94 00:06:08,430 --> 00:06:09,430 So that's important. 95 00:06:09,450 --> 00:06:10,980 It's only where we have reviews. 96 00:06:11,760 --> 00:06:14,490 So to summarize, let's go all the way back here. 97 00:06:14,550 --> 00:06:21,510 What we just did was basically take the reviewers table, smash it with the reviews table where they 98 00:06:21,510 --> 00:06:22,170 match. 99 00:06:22,200 --> 00:06:23,190 It's an inner join. 100 00:06:23,200 --> 00:06:24,810 Where's the overlap? 101 00:06:24,810 --> 00:06:25,320 Right. 102 00:06:25,470 --> 00:06:30,360 So we take the overlap between them based off of ID here and reviewer ID. 103 00:06:30,390 --> 00:06:37,890 Then we have that second table that we then joined with series where the series ID matched the ID of 104 00:06:37,890 --> 00:06:38,640 a series. 105 00:06:38,760 --> 00:06:42,720 So it's a three way to join statements but three tables together. 106 00:06:42,720 --> 00:06:48,570 And that's why I decided to do many to many relationships after the simpler one to many. 107 00:06:48,570 --> 00:06:50,520 But hopefully it's not too intimidating. 108 00:06:50,550 --> 00:06:52,860 We did a bunch of exercises with them. 109 00:06:54,120 --> 00:06:55,500 You're probably a bit burnt out. 110 00:06:55,500 --> 00:07:00,420 I know I am from recording all of them, so I definitely recommend that you take a break. 111 00:07:00,630 --> 00:07:05,220 Before we move on, we're going to continue, of course, with joint and relationships, but we're only 112 00:07:05,220 --> 00:07:06,610 building from here on out. 113 00:07:06,630 --> 00:07:08,910 So congrats on making it through this part.