WEBVTT 00:00.540 --> 00:02.030 So we inserted all of our data. 00:02.040 --> 00:05.140 We have our schema ready in our tables created. 00:05.140 --> 00:07.450 Now let's get down to business. 00:07.470 --> 00:08.860 Let's do some stuff with it. 00:08.890 --> 00:11.780 So I'm going to break every problem that I'm going to give you. 00:11.840 --> 00:14.880 There's quite a few here into it's own video. 00:14.970 --> 00:22.110 Hopefully I'll make them a bit more manageable and you won't want to go to sleep or drown yourself after 00:22.110 --> 00:22.800 watching it. 00:22.800 --> 00:28.540 So the first thing that you'll see here the format of these is that I'm going to give you the results. 00:28.620 --> 00:33.900 It's much easier just to give you a picture of them rather than trying to describe a bunch of text with 00:33.960 --> 00:35.420 what it actually means. 00:35.410 --> 00:36.820 It's much easier to show you. 00:37.080 --> 00:41.250 So first thing I should say is that these results are truncated. 00:41.520 --> 00:45.670 These are only for the first 15 or so used limit. 00:45.720 --> 00:50.520 But I want results for everything it's just too hard to fit them onto the screen would be very very 00:50.520 --> 00:51.170 tiny. 00:51.210 --> 00:57.840 But the key thing is that we have a title for a movie or excuse me for a series and a corresponding 00:57.840 --> 00:58.900 rating. 00:59.220 --> 01:01.850 So we're going to need to join those tables together. 01:01.850 --> 01:07.740 We need to join the series table with the reviews table because right now they're not in the same table 01:08.070 --> 01:11.370 we're storing the titles separately from the ratings. 01:11.670 --> 01:19.030 So let's do this now we'll go over to cloud nine and the first thing we'll do is just start simple. 01:19.340 --> 01:28.920 We'll select star and we'll just start from series which we've already done. 01:31.360 --> 01:36.620 Just like this one thing I should highlight before we go too far. 01:36.930 --> 01:43.950 I did deliberately add some series that don't have any reviews so that nobody has rated them yet and 01:44.010 --> 01:46.410 that was deliberate so that we can work with that later. 01:46.410 --> 01:50.830 However in this example I only want to see movies. 01:50.940 --> 01:56.730 I keep saying that we want to see the titles of series that have been rated so we don't have any Knolles 01:56.730 --> 01:59.370 in here which is a bit of a hint. 01:59.520 --> 02:04.470 So select star from series and on its own we get this. 02:04.470 --> 02:07.490 Now what we want to do is join. 02:08.040 --> 02:11.520 So if we do a separate select to start from previews 02:14.880 --> 02:16.270 we want to join. 02:16.530 --> 02:23.490 Basically where this ID of the series is the same as the series ID. 02:23.600 --> 02:31.210 So these first five here one two three four five correspond to Archer which is what we see here. 02:31.880 --> 02:37.140 So to do that we don't need to separate selects you energy select star from Sirius. 02:37.790 --> 02:44.480 Well do join with reviews on. 02:45.160 --> 02:49.060 And where do we want would you want to join on series. 02:49.270 --> 02:59.990 Dot ID equal to revues dot series underscore ID just to reiterate where this idea of the series actually 02:59.990 --> 03:02.440 is going to be easier if I do it here. 03:04.230 --> 03:13.550 Where this series ID is equal to this idea of the series table OK and all we want are title and rating. 03:13.660 --> 03:18.620 We'll start with just getting everything and lets see if were in business yet. 03:20.250 --> 03:25.310 Well its a little hard to tell because we have this formatting issue where we have so much data. 03:25.310 --> 03:32.390 Things are being cascaded over but if we look closely we can see okay we have Archer 8.00 Archer seven 03:32.380 --> 03:39.330 point five or eight point five and so on and if we scroll down you can see that not every single TV 03:39.330 --> 03:40.090 show is here. 03:40.210 --> 03:46.440 You'll notice the IDs jump seven eight nine General Hospital 10 and catch fire. 03:46.570 --> 03:48.540 Then it goes up to 13 so whatever. 03:48.540 --> 03:53.900 11 and 12 are we don't have any reviews so that's good not that we don't have reviews that's not good 03:53.940 --> 03:56.410 but they're not showing up which is what we want. 03:56.580 --> 04:00.590 So now we'll just whittle this down to title and rating. 04:00.600 --> 04:06.300 And that's all we want and I'll format it a little bit nicer. 04:06.480 --> 04:14.670 Here we go hit enter and now we get all of our data on the left side. 04:14.700 --> 04:16.520 We have title on the right side. 04:16.620 --> 04:18.200 We have corresponding ratings. 04:18.390 --> 04:22.980 So hopefully what we just did there seemed relatively similar to what we did in the last section where 04:22.980 --> 04:28.860 we're writing these joints what will build up to in this section is doing a double join where we have 04:28.860 --> 04:33.580 another joint statement because we want to connect all three of our tables. 04:33.690 --> 04:36.760 But for now we're working with one table or two tables at a time. 04:37.030 --> 04:37.410 OK. 04:37.470 --> 04:39.010 So we're done with that one. 04:39.030 --> 04:41.460 I mean just put a comment up here. 04:41.620 --> 04:45.610 First let me just do challenge one. 04:45.920 --> 04:46.870 Now we'll move on. 04:46.920 --> 04:48.960 Next video we'll pick up with challenge to.