1 00:00:00,180 --> 00:00:00,570 All right. 2 00:00:00,570 --> 00:00:02,070 So we inserted all of our data. 3 00:00:02,070 --> 00:00:04,950 We have our schema ready and all of our tables created. 4 00:00:04,980 --> 00:00:07,350 Now let's get down to business. 5 00:00:07,380 --> 00:00:08,730 Let's do some stuff with it. 6 00:00:08,730 --> 00:00:11,550 So I'm going to break every problem that I'm going to give you. 7 00:00:11,550 --> 00:00:14,900 And there's quite a few here into its own video. 8 00:00:14,940 --> 00:00:21,540 Hopefully we'll make them a bit more manageable and you won't want to go to sleep or drown yourself 9 00:00:21,840 --> 00:00:22,730 after watching it. 10 00:00:22,740 --> 00:00:28,230 So the first thing that you'll see here, the format of these is that I'm going to give you the results. 11 00:00:28,500 --> 00:00:33,630 It's much easier just to give you a picture of them, rather than trying to describe in a bunch of text 12 00:00:33,720 --> 00:00:35,430 what it actually means. 13 00:00:35,430 --> 00:00:36,750 It's much easier just to show you. 14 00:00:36,930 --> 00:00:40,800 So first thing I should say is that these results are truncated. 15 00:00:41,370 --> 00:00:44,580 These are only for the first 15 or so. 16 00:00:44,670 --> 00:00:47,430 I used a limit, but I want results for everything. 17 00:00:47,430 --> 00:00:49,350 It's just too hard to fit them onto. 18 00:00:49,350 --> 00:00:51,060 The screen would be very, very tiny. 19 00:00:51,060 --> 00:00:57,870 But the key thing is that we have a title for a movie or excuse me, for a series and a corresponding 20 00:00:57,870 --> 00:00:58,470 rating. 21 00:00:59,100 --> 00:01:01,830 So we're going to need to join those tables together. 22 00:01:01,830 --> 00:01:07,320 We need to join the series table with the reviews table, because right now they're not in the same 23 00:01:07,320 --> 00:01:07,920 table. 24 00:01:07,920 --> 00:01:11,100 We're storing the titles separately from the ratings. 25 00:01:11,520 --> 00:01:12,900 So let's do this now. 26 00:01:13,680 --> 00:01:19,050 We'll go over to Cloud nine and the first thing we'll do is just start simple. 27 00:01:19,260 --> 00:01:21,030 We'll select Star. 28 00:01:22,040 --> 00:01:28,640 And we'll just start from series, which we've already done. 29 00:01:31,270 --> 00:01:32,290 Just like this. 30 00:01:33,910 --> 00:01:36,640 One thing I should highlight before we go too far. 31 00:01:36,760 --> 00:01:44,020 I did deliberately add some series that don't have any reviews so that nobody has rated them yet, and 32 00:01:44,020 --> 00:01:46,340 that was deliberate so that we can work with that later. 33 00:01:46,360 --> 00:01:50,920 However, in this example, I only want to see movies. 34 00:01:50,950 --> 00:01:55,510 I keep saying that I only want to see titles of series that have been rated. 35 00:01:55,510 --> 00:01:58,420 So we don't have any nulls in here, which is a bit of a hint. 36 00:01:59,380 --> 00:02:03,790 So select star from series and on its own we get this. 37 00:02:04,330 --> 00:02:07,360 Now what we want to do is join. 38 00:02:07,960 --> 00:02:11,770 So if we do a separate select star from reviews. 39 00:02:14,760 --> 00:02:16,050 We want to join. 40 00:02:16,380 --> 00:02:22,080 Basically where this ID of the series is the same as the series ID. 41 00:02:23,530 --> 00:02:26,020 So these first let's see, five here. 42 00:02:26,050 --> 00:02:27,400 One, two, three, four, five. 43 00:02:27,910 --> 00:02:30,790 Correspond to Archer, which is what we see here. 44 00:02:31,780 --> 00:02:33,820 So to do that, we don't need to separate. 45 00:02:33,820 --> 00:02:34,570 Select. 46 00:02:34,930 --> 00:02:36,730 We want to do select star from series. 47 00:02:37,750 --> 00:02:44,170 We'll do join with reviews on. 48 00:02:45,140 --> 00:02:46,170 And where do we want. 49 00:02:46,190 --> 00:02:48,320 We want to join on series. 50 00:02:49,250 --> 00:02:50,610 Dot ID. 51 00:02:50,720 --> 00:02:52,730 Equal to reviews. 52 00:02:52,730 --> 00:02:54,560 Dot series. 53 00:02:54,560 --> 00:02:56,210 Underscore ID. 54 00:02:56,660 --> 00:03:01,670 Just to reiterate where this idea of the series actually it's going to be easier if I do it here. 55 00:03:04,130 --> 00:03:09,170 Where this series ID is equal to this ID of the series table. 56 00:03:10,220 --> 00:03:10,730 Okay. 57 00:03:11,650 --> 00:03:13,540 And all we want are title and rating. 58 00:03:13,540 --> 00:03:15,700 But we'll start with just getting everything. 59 00:03:16,540 --> 00:03:18,550 And let's see if we're in business yet. 60 00:03:20,160 --> 00:03:25,350 Well, it's a little hard to tell because we have this formatting issue where we have so much data, 61 00:03:25,350 --> 00:03:27,210 things are being cascaded over. 62 00:03:27,870 --> 00:03:34,740 But if we look closely, we can see we have Archer 8.0, Archer 7.5, Archer 8.5, and so on. 63 00:03:35,520 --> 00:03:40,160 And if we scroll down, you can see that not every single TV show is here. 64 00:03:40,220 --> 00:03:46,470 Like, you'll notice the IDs jump seven, eight, nine, General Hospital, ten halt and catch fire, 65 00:03:46,510 --> 00:03:47,670 then goes up to 13. 66 00:03:47,670 --> 00:03:50,880 So whatever 11 and 12 are, we don't have any reviews. 67 00:03:50,880 --> 00:03:51,810 So that's good. 68 00:03:52,200 --> 00:03:53,400 Not that we don't have reviews. 69 00:03:53,400 --> 00:03:53,940 That's not good. 70 00:03:53,940 --> 00:03:56,390 But they're not showing up, which is what we want. 71 00:03:56,400 --> 00:04:03,720 So now we'll just whittle this down to title and rating, and that's all we want and I'll format it 72 00:04:03,720 --> 00:04:04,770 a little bit nicer. 73 00:04:06,420 --> 00:04:07,200 Here we go. 74 00:04:09,400 --> 00:04:10,060 Hit Enter. 75 00:04:11,210 --> 00:04:13,550 And now we get all of our data. 76 00:04:13,700 --> 00:04:15,560 On the left side, we have title. 77 00:04:15,560 --> 00:04:18,140 On the right side, we have corresponding ratings. 78 00:04:18,230 --> 00:04:23,030 So hopefully what we just did there seemed relatively similar to what we did in the last section where 79 00:04:23,030 --> 00:04:24,290 we're writing these joints. 80 00:04:24,590 --> 00:04:30,110 What we'll build up to in this section is doing a double join where we have another joint statement 81 00:04:30,110 --> 00:04:33,560 because we want to connect all three of our tables. 82 00:04:33,560 --> 00:04:36,740 But for now we're working with one table or two tables at a time. 83 00:04:37,010 --> 00:04:37,430 Okay. 84 00:04:37,430 --> 00:04:38,690 So we're done with that one. 85 00:04:38,960 --> 00:04:42,410 Let me just put a comment up here first. 86 00:04:42,590 --> 00:04:46,820 Let me just do challenge one and now we'll move on. 87 00:04:46,820 --> 00:04:49,220 Next video we'll pick up with challenge two.