WEBVTT 00:00.240 --> 00:00.740 OK. 00:00.900 --> 00:02.070 So let's go over. 00:02.070 --> 00:03.330 Hopefully you're still alive. 00:03.530 --> 00:06.020 It was maybe a bit of a challenging problem set. 00:06.450 --> 00:10.500 But if you're still here let's go with these first three. 00:10.530 --> 00:11.950 So evaluate the following. 00:12.060 --> 00:14.450 Select 10 not equal to 10. 00:14.760 --> 00:16.870 Well 10 is equal to 10. 00:17.130 --> 00:22.050 So this should return false and we get zero. 00:22.050 --> 00:24.760 Next One little more complex we've got. 00:24.790 --> 00:27.970 And remember that means both sides have to be true. 00:28.000 --> 00:30.860 Start the left is 15 grade than 14. 00:31.020 --> 00:32.350 Yes that's true. 00:32.670 --> 00:34.710 But how about this side. 00:34.850 --> 00:40.600 Ninety nine minus five gives us 94 is 94 less than or equal to 94. 00:40.800 --> 00:42.100 Yes it's equal to. 00:42.390 --> 00:51.080 So that means both sides are true meaning the whole thing is true which we get 1 finally select 1 in 00:51.280 --> 00:54.220 5 come a three or nine between eight and 10. 00:54.570 --> 00:55.430 So we've got more. 00:55.450 --> 00:59.370 I mean it's either side can be true for me to make the whole thing true. 00:59.400 --> 01:03.760 So let's start with this select one in five come three. 01:03.770 --> 01:07.060 Remember he didn't show you this explicitly. 01:07.080 --> 01:09.600 But we saw how to use in. 01:09.630 --> 01:14.610 So you should know how it works is one in that set of five comma three. 01:14.640 --> 01:15.210 No. 01:15.210 --> 01:21.120 So this is false but that doesn't nullify the whole thing and falsify it because what's on the right 01:21.120 --> 01:24.230 side is nine between eight and 10. 01:24.390 --> 01:25.160 Yes it is. 01:25.260 --> 01:28.220 So this is true making the whole thing true. 01:28.830 --> 01:31.750 Let's try it. 01:31.770 --> 01:32.200 There we go. 01:32.220 --> 01:33.120 We get one. 01:33.480 --> 01:34.400 So false. 01:34.410 --> 01:36.190 True true. 01:36.270 --> 01:40.410 Next select all the books written before 1980. 01:40.680 --> 01:42.800 So I didn't specify what part of the book. 01:42.900 --> 01:45.030 That's totally up to you. 01:45.120 --> 01:53.250 Let's just do title and release here so we can see that released year from books and then the key thing 01:53.700 --> 01:59.610 is we want to do where released here is less than 1980 and not less than or equal to because it says 01:59.610 --> 02:07.570 non-inclusive so less then we're released here is less than 1980 to make sure it works. 02:09.000 --> 02:10.290 And there we go. 02:10.290 --> 02:11.850 All books written before 1980. 02:11.940 --> 02:13.250 Apparently we only have one. 02:13.440 --> 02:15.530 Let's just actually verify that. 02:15.780 --> 02:17.910 It's kind of catching me off guard there. 02:21.060 --> 02:23.430 And it looks like that's the case. 02:23.430 --> 02:30.160 All we have is Cannery Row Well I apologize if we don't have more exciting data. 02:30.160 --> 02:37.820 Next up select all books written by Eggers or shavin which I now know how to pronounce. 02:38.170 --> 02:40.260 So there's a couple of ways of doing this. 02:40.360 --> 02:44.000 Let's start with the most straightforward maybe which would be. 02:44.350 --> 02:50.540 And also let's do title and author last name. 02:50.600 --> 02:53.930 So again I didn't specify what parts he had to select. 02:54.020 --> 02:58.880 It's up to you but this makes most sense if we can see the authors last names. 02:58.880 --> 03:06.350 So what we can do is select them where and we can use and or so we can do a double equality where author 03:06.350 --> 03:14.510 last name is equal to Aggers or author last name is equal to Shaban where author L name is equal to 03:14.660 --> 03:23.410 Aggers or author name is equal to shavin just like that. 03:23.450 --> 03:25.300 That's one way of doing it. 03:25.550 --> 03:29.110 And you can see we get those for books. 03:29.120 --> 03:36.530 However we can also use in because all that we're doing is trying to see if author L name is in the 03:36.530 --> 03:40.650 set of Eggers and shavin so we can combine them. 03:41.110 --> 03:50.610 Basically say where author name in that set copy that never go. 03:50.620 --> 03:51.600 Same result. 03:51.760 --> 03:55.750 So whichever you prefer this one is a little bit easier to see. 03:55.750 --> 04:01.270 I guess it's shorter easier to edit and if you want to add another author and it's pretty simple but 04:01.300 --> 04:04.720 this is also perfectly valid. 04:04.750 --> 04:10.510 Next select all the books written by Lahiri published after 2000. 04:12.130 --> 04:13.810 So the first thing will do. 04:13.810 --> 04:15.990 Title and this time will do. 04:16.000 --> 04:18.520 Author L name. 04:20.930 --> 04:27.080 And released here from books and will start by all books written by the. 04:27.140 --> 04:36.120 So where author L name equals Harry so we can start there. 04:36.170 --> 04:43.550 We know this should work but we get two books and now we want only the ones published after 2000. 04:43.970 --> 04:53.480 So what we need is a logical and so where author name is here to live here and released here is greater 04:53.480 --> 04:57.830 than 2000 and I didn't specify if it was inclusive or not. 04:57.830 --> 05:04.070 So based off of language published after 2000 will keep it as a greater than if you put an equal sign 05:04.070 --> 05:04.270 there. 05:04.270 --> 05:05.220 It's not. 05:05.260 --> 05:06.100 I mean you got it right. 05:06.110 --> 05:10.150 Essentially I didn't do a great job of clarifying that before. 05:10.190 --> 05:14.400 So either one works now we only get the namesake. 05:14.630 --> 05:17.470 Also really great read. 05:17.510 --> 05:22.930 Next up select all books with page count between 100 and 200. 05:22.940 --> 05:27.620 So just like the problem we did back here there's a couple of solutions. 05:27.620 --> 05:36.470 The first one is to use logical and so we can do select title and page count or is it just pages from 05:36.470 --> 05:44.440 books where pages is greater than 100 and less than 200. 05:44.600 --> 05:52.400 So where pages will do greater than or equal to pages is greater than or equal to 100 and pages less 05:52.400 --> 05:55.060 than or equal to 200. 05:55.280 --> 05:58.160 That's one way of selecting based off of a range. 05:58.310 --> 06:05.540 As we get these shorter books but we could also use between which maybe you gathered because of the 06:05.540 --> 06:06.550 language here. 06:06.740 --> 06:08.650 It screams out between. 06:09.050 --> 06:10.720 And that might simplify it a bit. 06:10.730 --> 06:17.560 So all we have to do is change it to where pages between 100 and 200. 06:18.800 --> 06:20.250 Nice easy to read version. 06:20.270 --> 06:22.120 We get the same results. 06:22.930 --> 06:23.490 OK. 06:23.750 --> 06:26.290 One thing remember that this is inclusive. 06:26.360 --> 06:34.190 So if we didn't want to include 100 and 200 we could either do this or we would have to change just 06:34.190 --> 06:38.930 to be 100 one and when ninety nine or something. 06:38.930 --> 06:39.280 OK. 06:39.410 --> 06:42.170 So I'll leave it as 200. 06:42.380 --> 06:49.140 Moving on select all books where the author last name starts with a C or an S. 06:49.180 --> 06:51.500 So there's also a couple of ways of doing this one. 06:51.800 --> 06:55.430 We'll start with the simplest one. 06:55.430 --> 06:59.230 So we want to select and this time will do. 06:59.230 --> 07:06.380 Title and author L. name from books and basically we want to let's just type it out with the pseudo 07:06.380 --> 07:08.330 code or English code. 07:08.360 --> 07:17.720 So where author Al name starts with C or author 07:21.810 --> 07:25.020 Al name starts with s. 07:25.110 --> 07:27.590 So those are not you know it's not valid code. 07:27.630 --> 07:29.240 So how do we do this. 07:29.250 --> 07:30.590 There's a couple of options. 07:30.630 --> 07:36.860 The first one is to just use like if you're comfortable with that sort of starts with C.. 07:36.990 --> 07:45.780 You can just say where author I'll name like and then we use the percent sign to force the C to be at 07:45.780 --> 07:47.530 the beginning of the string. 07:48.060 --> 07:52.780 So remember that this says string starts with C and then anything afterwards. 07:52.800 --> 07:59.940 So that's one way to say it starts with the C and then we just do the same thing done here like s but 07:59.940 --> 08:04.470 the percent sign after that and that should do it for us. 08:04.470 --> 08:10.770 So we get shavin Smith Carver Steinback Saunders good collection of authors. 08:10.770 --> 08:14.960 However there's another way which is to use substring. 08:15.030 --> 08:22.590 So if you remember substring Maybe you remember me complaining about how there is also an abbreviated 08:22.620 --> 08:33.770 subset S T R version so we could use that so we can say were author L name sub stra of author L name 08:34.400 --> 08:39.800 and we want one common one which will give us the first character we want to check where that's equal 08:39.860 --> 08:48.550 to C or not just duplicate this whole thing where it's equal to S.. 08:49.750 --> 08:54.860 So we could do this and we get the same thing. 08:55.480 --> 09:00.640 But there's yet another way to clean this up which is to then I'll just keep them all here so you can 09:00.640 --> 09:01.910 see them all. 09:02.100 --> 09:12.940 We could go and because we're checking these two values we could also use in C comma s just like that 09:12.940 --> 09:14.620 and get rid of the OR. 09:15.270 --> 09:19.930 So now we're saying it's like title author realname from books where the first character of author all 09:19.930 --> 09:26.370 name is in the set of C or S And We get the same result. 09:26.590 --> 09:28.930 So three different ways. 09:28.930 --> 09:29.970 Next up. 09:30.040 --> 09:32.020 Moving on to case statements. 09:32.020 --> 09:37.300 So in this one I tried to throw you for a little bit of a curve ball because unlike the other case statements 09:37.300 --> 09:39.510 we saw where we were just using greater than the must. 09:39.510 --> 09:41.880 Then there's a couple of things we're doing here. 09:42.100 --> 09:46.850 So we're just working with title but you're seeing if title is like stories. 09:47.170 --> 09:52.140 And then you're seeing if it exactly equals just kids or a heartbreaking work of Staggering Genius. 09:52.600 --> 09:55.090 So let's let's start off with the basics. 09:55.240 --> 10:05.760 Select title and author name and we'll just do from books just like that. 10:05.950 --> 10:12.370 And then we need our case statements syntax so we're going to have case and I like to just put my and 10:12.520 --> 10:18.910 right in there as well as my as I call it type which I don't actually think is a great name genre would 10:18.910 --> 10:19.800 be better. 10:20.320 --> 10:23.560 But inside of here we're going to have our statements. 10:23.560 --> 10:30.730 So the first thing we're going to do is figure out OK if Title contains the word stories anywhere type 10:30.730 --> 10:36.120 as short stories so when title like. 10:36.500 --> 10:41.990 And how do we write or express the fact that it can contain stories anywhere not just at the beginning 10:42.800 --> 10:43.870 but anywhere in there. 10:44.000 --> 10:47.490 Well we use parentheses accesory for sentence. 10:48.230 --> 10:54.320 So this means that it contains the string stories with anything afterwards which technically we could 10:54.320 --> 11:00.980 have histories or something which maybe is not what we want but we can leave it at that. 11:00.980 --> 11:06.080 You could put a space here or something to make sure that is just stories but I'll keep it simple with 11:06.080 --> 11:07.400 stories like this. 11:09.250 --> 11:15.440 Then that's going to be short stories as our type. 11:16.330 --> 11:19.900 OK then we have another statement. 11:20.140 --> 11:26.980 When title and when title is exactly just kids or Heartbreaking Work is Staggering Genius. 11:27.160 --> 11:28.480 We should print out memoir. 11:28.510 --> 11:32.710 So let's leave that part blank and just add in then 11:35.530 --> 11:37.360 memoir. 11:37.810 --> 11:39.980 So then how do we fill this part in. 11:40.280 --> 11:42.700 It's pretty straightforward it's going to be a bit annoying. 11:42.710 --> 11:52.130 So how do we check it's equal to just kids when title equals just kids or when title equals. 11:52.130 --> 11:55.740 And this is going to be very long so I'm going to copy it. 11:55.820 --> 11:56.270 Where are you 11:59.610 --> 12:03.180 just like that. 12:03.350 --> 12:05.120 And I might shorten that. 12:05.150 --> 12:10.340 I definitely would shorten it so maybe it would be better to use it like or something and search for 12:10.340 --> 12:12.650 a particular part rather than the entire name. 12:12.650 --> 12:14.610 But that was the problem here. 12:16.150 --> 12:21.970 Followed by our else which is otherwise everything is a novel. 12:21.970 --> 12:24.170 So that's where this comes in. 12:24.220 --> 12:29.050 Else novel just like that. 12:29.170 --> 12:30.070 Now let's double check. 12:30.070 --> 12:34.660 We don't have any extra commas which I said I sometimes do looks good. 12:34.720 --> 12:36.020 Let's try it out. 12:37.780 --> 12:46.000 And we get all novels except for just kids and a heartbreaking work is staggering genius. 12:46.210 --> 12:50.800 And then those are memoirs and then we have to Raymond Carver collections of short stories and a David 12:50.800 --> 12:52.680 Foster Wallace Collection. 12:53.440 --> 12:53.840 OK. 12:53.890 --> 12:54.520 So that's it. 12:54.520 --> 12:58.950 And of course he could clean this up so you could have used in here. 12:59.030 --> 13:01.030 Title equals so you could say where title. 13:01.060 --> 13:08.770 In just kids comma Heartbreaking Work Staggering Genius you could use the like something like this or 13:08.770 --> 13:14.630 a title like heartbreaking although maybe we have multiple books with heartbreaking the title. 13:15.070 --> 13:18.730 So I'm going to leave that and move on to our final one. 13:19.330 --> 13:20.580 How do we make this happen. 13:20.830 --> 13:22.810 So again what's happening we're grouping things right. 13:22.840 --> 13:29.050 Author and we're counting how many books each author has written but we're not just printing that out. 13:29.050 --> 13:35.240 We're printing it along with a space and then to correct the pluralize version of books. 13:35.800 --> 13:37.500 So we'll start by just printing out. 13:37.600 --> 13:43.750 We can ignore title just to author last name and account without the book's part. 13:43.750 --> 13:58.820 So that should be review of group by and count so we'll select author I'll name Khama count star from 13:59.330 --> 14:04.000 Books Group II and we need to group by author. 14:04.840 --> 14:05.930 So if we do this 14:11.470 --> 14:17.000 you'll see we end up with Carver has two books Aggers has three books and so on. 14:17.080 --> 14:19.760 We do have that problem with Harris having two books. 14:19.930 --> 14:22.540 So we need to make this more specific. 14:22.780 --> 14:30.370 Are we also grouped by author first name and now we have two different Harris's perfect son now how 14:30.370 --> 14:35.490 do we instead of just printing out like this how do we add in that variable. 14:35.490 --> 14:40.100 The changeable suffix book or books that use the case statement. 14:40.180 --> 14:45.880 So I'm going to make some space here and rather than just printing the count we're going to add in our 14:45.880 --> 14:48.690 case case. 14:49.090 --> 14:54.160 And and if you look at how I printed it I just call that count. 14:54.180 --> 14:58.880 So and as counts then what is the logic we're actually trying to do. 14:59.020 --> 15:05.030 What we're trying to figure out when the number of books count star is equal to 1. 15:05.080 --> 15:07.740 Let's say then we want to do something. 15:08.050 --> 15:10.500 Let's just say singular book. 15:10.510 --> 15:12.060 This isn't the right answer. 15:12.880 --> 15:16.390 Otherwise will you. 15:16.400 --> 15:18.580 Books. 15:18.670 --> 15:23.300 OK so the logic is going down the right page regrouping things by author. 15:23.550 --> 15:28.350 Then we're having a case statement that says when the total count of the number of books that authors 15:28.350 --> 15:33.030 written is equal to 1 then count is book. 15:33.270 --> 15:36.840 And before I forget we are missing our then's. 15:37.050 --> 15:42.190 So one count equals 1 then book that's very important. 15:42.240 --> 15:47.280 Now if we paste it OK so we get Carver has books. 15:47.280 --> 15:50.770 Eggers has books but huge Harris has book. 15:50.850 --> 15:53.670 So we're close but how do we get the number in there. 15:54.360 --> 15:56.090 And we need to use Concat. 15:56.220 --> 15:57.690 Hopefully you guessed that. 15:57.810 --> 16:05.770 So we're going to concat Basically we want to take the number which is going to be count and we want 16:05.770 --> 16:09.910 to concatenate it with space book. 16:09.940 --> 16:14.310 Now if we wanted to shorten this we could just hard code this as one book. 16:14.320 --> 16:14.580 Right. 16:14.580 --> 16:19.510 Because when Count is equal to 1 then it's always going to be one book. 16:19.510 --> 16:26.110 This is the one where we have multiple books we need to add in the number because it could be two books 16:26.110 --> 16:27.290 could be 10 books. 16:27.640 --> 16:37.690 So of concat the two pieces which are going to be Count star comma and I know this looks like a lot 16:37.690 --> 16:38.180 here. 16:38.230 --> 16:39.980 So let's step through it. 16:40.060 --> 16:43.730 Concatenate the count with space books. 16:43.900 --> 16:49.580 So two books three books and that should do it. 16:51.790 --> 16:52.890 Let's double check. 16:54.000 --> 16:54.840 Here we go. 16:54.840 --> 16:55.980 Carver has two books. 16:56.100 --> 16:57.380 Shaban has one book. 16:57.390 --> 17:00.000 Harris has one book here he has two books. 17:00.060 --> 17:05.790 The only other improvement I guess to suggest would be to also put author first name just to make it 17:05.790 --> 17:07.050 a little clearer. 17:07.230 --> 17:14.460 In the case of Harris we now can see Dan Harris has one book and Frida Harris has one book to play here 17:14.460 --> 17:15.880 he has two books. 17:16.440 --> 17:17.480 Oh right. 17:17.760 --> 17:18.780 Quite a bit there. 17:19.150 --> 17:21.720 You made it through time to move on. 17:21.760 --> 17:24.900 We can burn that part of us that's attached to the books. 17:24.930 --> 17:25.900 We are done. 17:25.920 --> 17:28.300 We're moving on to bigger more exciting things. 17:28.320 --> 17:34.830 I highly recommend that you if you're at your computer get up and take a break because joined our big 17:34.830 --> 17:35.220 section. 17:35.220 --> 17:36.160 Very important stuff. 17:36.180 --> 17:39.840 Coming up next but a bit dense so go for a walk. 17:39.960 --> 17:46.470 If you'd like or do whatever you need to do to get prepared to focus on joints.