WEBVTT 00:00.810 --> 00:06.660 So we now have one more video one new bit of information before we move on to the exercises and wrap 00:06.660 --> 00:09.690 up this section and go on to join tables. 00:09.690 --> 00:11.640 Very important big topic. 00:11.640 --> 00:15.790 But before we do that we have this video which is on this thing called Case statements. 00:15.810 --> 00:18.420 This is a pretty well exciting is relative. 00:18.420 --> 00:22.570 This is very different than things that we've seen in this section. 00:22.620 --> 00:26.900 It has to do with logic but it's kind of on a bigger scale. 00:27.030 --> 00:29.030 It allows us to make decisions. 00:29.250 --> 00:33.570 So if you're familiar with programming languages it's something I've been saying a lot in this section 00:33.600 --> 00:38.370 but if you are you've probably come across case statements or conditional statements which allow you 00:38.370 --> 00:43.360 to add logic in to do things like conditionally print something out. 00:43.380 --> 00:50.010 It's a very simple example but if a number is greater than some than 5 print one thing. 00:50.130 --> 00:51.960 Otherwise print something else. 00:52.290 --> 00:54.020 Well the same thing holds true in my essay. 00:54.030 --> 00:57.600 Q Well we use these case statements all of which I'll show you in just a moment. 00:57.600 --> 01:00.430 To do things like this. 01:00.510 --> 01:06.430 So over here I have a genre category Herge on a column that is not in our database. 01:07.240 --> 01:12.100 And what I've what I've gone and done it's basically said print out title and released year which are 01:12.100 --> 01:16.430 very straightforward but then take that release year. 01:16.780 --> 01:25.180 And if it's greater than 2000 genre should be equal to modern one if it's less than 2000 than it should 01:25.180 --> 01:32.050 be 20th century but very very simple genres that we could easily expand that or maybe genres and wrong 01:32.060 --> 01:37.930 word maybe period is better or era but I call that genre. 01:37.990 --> 01:42.020 It's a lot of work to change sides now so we'll just call it that. 01:42.250 --> 01:47.190 But again what what's happening here is we have a conditional statement. 01:47.230 --> 01:50.350 This depends on this. 01:50.620 --> 01:57.880 So if we change Release Year of the namesake to be 1990 This would also change to be 20th century. 01:58.330 --> 02:04.810 So it's adding in logic a new layer of complexity that we haven't seen how to do to make these decisions. 02:04.840 --> 02:09.220 And we're not limited just to of course we could break it up into a whole bunch of things and I'll show 02:09.220 --> 02:10.460 you a few more examples. 02:10.660 --> 02:13.510 But let's first identify how this works. 02:13.690 --> 02:17.190 And I warn you there's kind of a lot of syntax. 02:17.230 --> 02:18.860 OK so here it is. 02:19.090 --> 02:21.280 This is what results in what we just saw. 02:21.730 --> 02:23.860 So the beginning and end are the same. 02:23.860 --> 02:27.380 Select title and release year from books. 02:27.700 --> 02:30.670 But then we have this whole case thing four separate lines. 02:30.850 --> 02:33.710 They don't have to be separate Of course like anything in my school. 02:33.820 --> 02:40.240 But it makes it a lot easier to see what's related to what first thing that we have in this case. 02:40.280 --> 02:46.390 That's a word that says this is a case that is going to consist of a couple of points of logic that 02:46.390 --> 02:52.020 will spit one thing out and that thing ends right here. 02:52.390 --> 02:54.040 And then I have an AS. 02:54.070 --> 02:55.890 So I don't have to have as genre. 02:55.990 --> 02:57.350 Remember that's called an alias. 02:57.610 --> 03:03.370 But if I left that off it will look really really gross when it printed out in the table because instead 03:03.370 --> 03:11.260 of genre here it would say this entire thing which would make the table gigantic It would go way off 03:11.260 --> 03:13.920 the page and would ruin our formatting. 03:14.170 --> 03:16.340 So usually we want to use as. 03:16.870 --> 03:20.140 But then the more important part is what we have right here. 03:20.590 --> 03:23.220 So Case statements take the following form. 03:23.260 --> 03:25.360 There's always a when at least one. 03:25.450 --> 03:30.450 And then there's an ounce as well as the opening case in the closing end. 03:30.550 --> 03:38.650 So let's step through what this says when released year is greater than or equal to 2000 then return 03:38.740 --> 03:39.380 modern. 03:39.430 --> 03:44.820 But when I say return it's a programming term but basically means spit out. 03:45.250 --> 03:49.400 Think of it as just making new genre equal to modern. 03:49.930 --> 03:53.520 So that happens when released here is greater than or equal to 2000. 03:55.280 --> 04:01.430 Then we have an else which is otherwise we're going to return 20th century late. 04:01.490 --> 04:07.310 So this is basically saying if it's greater than 2000 if it was released in the 2000s then make genre 04:07.370 --> 04:08.190 modern. 04:08.600 --> 04:10.570 Otherwise it was released in the 9900. 04:10.730 --> 04:16.400 So we're going to print out 20th century for a genre and that's how we end up with this. 04:16.460 --> 04:23.010 So we have this bit of conditional logic where genre changes depending on the value of release year. 04:23.630 --> 04:26.410 So let's actually try this now. 04:29.270 --> 04:33.570 Run it and you can see we get the namesake. 04:33.570 --> 04:39.270 2003 is modern but interpretor maladies however is from 1996. 04:39.510 --> 04:44.900 So we get 20th century lit and I will show you if I get rid of the as 04:48.430 --> 04:54.610 it's a nightmare because if you look at what we have in our headers here we have title release year 04:54.640 --> 04:59.250 and then we have case when released greater than 2000 than modern else blah blah blah blah blah. 04:59.440 --> 05:02.670 So you definitely want to use as one you can. 05:03.370 --> 05:04.940 OK so that's one example. 05:05.380 --> 05:11.110 But I do think this one benefits from a couple because it's a bit weird especially if you don't come 05:11.110 --> 05:15.750 from a programming background so don't panic if you're not comfortable with this type of logic. 05:15.910 --> 05:18.940 But if you are it's very similar if you're not. 05:18.940 --> 05:21.110 It's kind of a new big new idea. 05:21.280 --> 05:22.450 So here's another one. 05:22.840 --> 05:30.640 What I've done here printed out title and then start quantity and then I'm using stock quantity to then 05:30.640 --> 05:34.060 print a graphical representation of how much stock we have. 05:34.060 --> 05:40.870 So one star is things that are lower in-stock two star has a medium of stock and three stars means we 05:40.870 --> 05:46.060 have a lot of stock and I can't remember the exact numerical breakdown where that happens. 05:46.060 --> 05:49.690 I believe if it is your to 50 copies we have one star. 05:49.690 --> 05:51.910 If it's 50 to 100 We have two stars. 05:51.910 --> 05:55.660 And then if it's 100 or more we have three stars. 05:55.840 --> 06:04.510 So it looks like this let me show you select title and start quantity from books again beginning and 06:04.510 --> 06:05.600 end is the same. 06:05.800 --> 06:11.300 Then we have our case and then the end of the case with this alias stock. 06:11.320 --> 06:14.560 And so that's just going to give us stock right here. 06:14.560 --> 06:16.020 And then the important part. 06:16.540 --> 06:23.450 So when the stock quantity is between 0 and 50 and we don't have to choose between we could use you 06:23.450 --> 06:30.430 know what we saw earlier are logical and say when it is greater than or equal to zero and star quantity 06:30.430 --> 06:33.600 is less than 50 then blah blah blah. 06:34.150 --> 06:36.490 But it's much easier to use between. 06:36.550 --> 06:43.150 When stock quantity is between zero and 50 then stock it won't start. 06:43.260 --> 06:46.660 So that's how we end up with one star. 06:46.720 --> 06:49.680 But if that's not true then it moves on to the next one. 06:50.050 --> 06:52.220 So if stock quantity is one hundred. 06:52.360 --> 06:53.480 This is false. 06:53.890 --> 06:59.590 So it ignores it and it tries this well stock quantity between 50 one in 100. 06:59.950 --> 07:03.970 If it is then stock is going to be two stars. 07:04.510 --> 07:07.350 And if it's greater I would say it's 153. 07:07.690 --> 07:08.580 Well this is false. 07:08.620 --> 07:09.790 And this is false. 07:10.180 --> 07:13.390 So it goes to this last one else has no other choices. 07:13.510 --> 07:14.100 Otherwise. 07:14.170 --> 07:20.160 If nothing else above was true well then this one is three stars. 07:20.170 --> 07:22.710 So goes through and it does that for every single one. 07:22.780 --> 07:25.510 So we end up with one two and three stars. 07:25.510 --> 07:26.920 So hopefully you're getting the idea here. 07:26.950 --> 07:28.000 Pretty powerful. 07:28.000 --> 07:33.410 It allows us to make decisions or do have different outcomes depending on our data. 07:33.970 --> 07:38.030 One thing I definitely need to point out because I can see this is being confusing. 07:38.350 --> 07:43.260 You don't have to have stock quantity printed out in order to reference it. 07:43.270 --> 07:49.780 In your case statement or back here I don't have to work with Release Year appear in order to work with 07:49.780 --> 07:49.890 it. 07:49.900 --> 07:52.000 In that case statement. 07:52.240 --> 07:54.430 So let's actually copy this one over and run it 07:57.710 --> 07:59.460 make some space. 07:59.540 --> 08:00.500 Here we go. 08:01.040 --> 08:01.470 OK. 08:01.550 --> 08:03.070 So we see the same thing. 08:04.310 --> 08:10.450 But just to show you are not going to use stock quantity appear just you know like the title and our 08:10.640 --> 08:14.430 stock visualizer and there we go. 08:14.860 --> 08:20.440 So I just wanted to show you don't have to have stock quantity referenced in order to use it inside 08:20.440 --> 08:22.270 of your case. 08:22.270 --> 08:26.410 And another point I should make is you're not limited in this number of how many conditions we could 08:26.410 --> 08:26.970 have. 08:26.980 --> 08:29.640 So if we wanted to we could have another one here. 08:29.710 --> 08:40.040 When stock quantity is between what would you 101 and 150 let's say then it's three stars. 08:40.840 --> 08:42.170 Otherwise it's higher than that. 08:42.170 --> 08:43.850 So I will say four stars. 08:43.960 --> 08:47.430 So let's try that. 08:47.580 --> 08:49.530 And that's actually ad stock quantity back. 08:49.530 --> 08:56.730 And it makes sense to be able to see that and what's copied over and run it this time. 08:57.500 --> 09:05.690 And now we have one star two star got four stars for 154 and three stars for 104. 09:06.290 --> 09:08.560 So my point is that you're not limited. 09:08.570 --> 09:14.780 Another thing that that trips me up personally is I'm inclined to put commas here just because most 09:14.780 --> 09:20.300 things in my school are separated by comments if you have a list of values or a list of anything that 09:20.300 --> 09:21.030 will break it. 09:21.050 --> 09:22.650 So no commas there. 09:23.000 --> 09:24.670 Troopship all the time. 09:25.270 --> 09:26.000 OK. 09:26.420 --> 09:31.420 So one other thing I did want to address is that we could make it shorter if we don't use between. 09:31.790 --> 09:36.730 And the way that we can do that is by taking advantage of the execution order of things. 09:36.740 --> 09:38.190 So let me show you. 09:38.420 --> 09:45.410 Here's another the same exact outcome is this line of code where we have one two and three stars. 09:45.440 --> 09:48.590 However it's much shorter because what's happening here. 09:48.620 --> 09:51.220 So if it's less than or equal to 50 then we're done. 09:51.410 --> 09:57.000 We get one star if that's not true that means already it's greater than 50. 09:57.110 --> 10:02.680 So if this line is running we know for sure start quantity is greater than 50. 10:02.680 --> 10:03.850 They go in order. 10:04.270 --> 10:11.710 So we don't have to necessarily add this between because we know it's definitely greater than 50 so 10:11.860 --> 10:18.070 we can just take advantage of it and just say OK well is it less than or equal to 100 which is the equivalent 10:18.070 --> 10:22.780 of saying Is it between 50 and 100 if it is then it's two stars. 10:22.800 --> 10:27.880 But if it's not less than or equal to one hundred then that means it's greater than 100. 10:27.910 --> 10:29.460 And so if we get three stars. 10:29.740 --> 10:34.600 So this is more just showing you a way of cleaning it up if you wanted to. 10:35.430 --> 10:36.940 So you don't have to choose between. 10:37.030 --> 10:42.100 And you can see if we get the exact same outcome one two and three stars we don't have to use those 10:42.250 --> 10:47.110 longer between statements although some people would argue between is easier to read. 10:47.110 --> 10:53.260 It's more semantic more understandable when you look at it rather than working with this definitely 10:53.260 --> 10:54.460 up for debate. 10:54.550 --> 11:00.690 So that wraps up the section on case statements which hopefully you see what I mean when I say they 11:00.680 --> 11:06.690 are both very useful but possibly a little intimidating especially syntactically there's a lot there. 11:06.910 --> 11:10.980 So definitely you know take some time on the next couple of exercises. 11:11.020 --> 11:14.970 We're going to have a few that require Do you require you to write Case statements. 11:15.010 --> 11:18.420 Hopefully you'll get some practice there and you'll see how powerful they are. 11:18.430 --> 11:20.590 With that said we're moving on to exercises.