WEBVTT 00:00.150 --> 00:00.570 All right. 00:00.570 --> 00:06.090 So you probably guessed it either because in the last video I told you or because of this gigantic text 00:06.090 --> 00:09.540 here it's your turn to try putting this into practice. 00:09.750 --> 00:11.240 So you get a couple of exercises. 00:11.280 --> 00:18.460 They range from trivial simple logic questions all the way up to more complex case statements. 00:18.540 --> 00:24.180 So I encourage you to go through all of them especially the case statements towards the end but hopefully 00:24.270 --> 00:25.580 you don't lose interest before then. 00:25.740 --> 00:26.870 Oh and one thing. 00:27.240 --> 00:30.390 This is the last time you have to work with those darn books. 00:30.540 --> 00:35.100 Last time that books data unless you want to keep working with it you can definitely play around that. 00:35.250 --> 00:36.660 But I'm getting pretty sick of it. 00:36.660 --> 00:37.980 I imagine you are. 00:37.980 --> 00:43.740 And the good news is we're now moving on to join tables which allows us to get much more complex and 00:43.740 --> 00:47.510 real real life data things that are more accurate and realistic. 00:47.760 --> 00:49.280 And that's really exciting. 00:49.800 --> 00:50.860 So get excited. 00:50.910 --> 00:56.250 If you're not all right there's the first couple evaluate the following. 00:56.250 --> 01:02.400 Now try doing them don't just copy them and paste them in and hit enter actually walk through and evaluate 01:02.400 --> 01:04.410 what you think the result is. 01:04.420 --> 01:05.520 So the first one. 01:05.520 --> 01:07.880 Select ten not equal to 10. 01:07.890 --> 01:10.750 Is that true or false 0 or 1. 01:10.980 --> 01:15.320 Next select 15 greater than 14 and 99 minus five. 01:15.360 --> 01:17.620 Less than or equal to 94. 01:17.730 --> 01:18.700 True or false. 01:18.800 --> 01:27.520 0 1 finally select 1 in 5 come with 3 or 9 between 8 and 10. 01:27.560 --> 01:28.440 True or false. 01:28.700 --> 01:33.400 So do those and then move on to these code based challenges. 01:33.440 --> 01:41.000 The first one here is select all books written before 1980 and that does not include 1980 so everything 01:41.030 --> 01:43.260 up to 1980 but not 1980. 01:44.440 --> 01:47.000 Once you've done that move on. 01:47.080 --> 01:51.220 A beautiful read text really expanding the color palette. 01:51.550 --> 01:57.250 Select all books written by Eggers or siobhán should know how you say his name. 01:57.250 --> 02:05.170 Honestly that guy great author Well I just googled it and apparently it's pronounced Shaban. 02:05.170 --> 02:10.240 So it's like all books written by Eggers or shavin. 02:10.570 --> 02:13.470 Makes sense now I think about it. 02:13.520 --> 02:19.770 Now onto the beautiful yellow select all books written by Lahiri published after 2000. 02:20.820 --> 02:26.010 So books written by the here he released after 2000. 02:26.230 --> 02:35.450 Then select all books where the page count is between 100 and 200 pages so are middle length or short 02:35.450 --> 02:40.090 books those are pretty short between 100 and 200 pages. 02:40.190 --> 02:47.530 Next select all books where the author last name starts with a C or an S. 02:47.690 --> 02:50.370 So this one there's a couple of ways of approaching it. 02:51.320 --> 02:53.810 I'll let you discover what works best. 02:53.840 --> 02:55.190 You may want to use. 02:55.250 --> 02:59.400 You'll need to use things that we've learned in the past especially when we're working with strings 02:59.410 --> 03:03.910 so you may want to go back or just go to the string documentation to figure out how to do this. 03:03.920 --> 03:07.560 Select all books where the author last name starts with a C or an S. 03:07.580 --> 03:12.650 Those are my initials so maybe I'm crazy and I just want books that start with who are written by authors 03:12.770 --> 03:16.910 who start with my initials I don't know. 03:16.960 --> 03:22.210 Now moving on to the main event are wonderful case statements. 03:22.200 --> 03:25.530 So here is another example. 03:25.540 --> 03:29.070 This is done using a case statement and let me explain it. 03:29.070 --> 03:31.380 So we've got title and the author's last name. 03:31.660 --> 03:35.380 But then we've got to type over here what type of book is it. 03:35.510 --> 03:37.570 Which I'm really bad at this. 03:37.560 --> 03:40.670 This is just because a genre doesn't matter though. 03:40.900 --> 03:42.390 Basically the way that it works. 03:42.460 --> 03:49.420 If the title contains the word stories recalling it short stories so as you can see where I'm calling 03:49.420 --> 03:52.650 from selected stories is categorized as short stories. 03:52.900 --> 03:57.880 Well what we talk about when we talk about love great collection is categorized as short stories because 03:58.000 --> 04:00.280 it contains stories. 04:00.280 --> 04:02.290 Same thing with oblivian stories. 04:02.770 --> 04:04.900 But then there's another part. 04:05.020 --> 04:12.280 If the title is just kids or it's heartbreaking work of Staggering Genius which I didn't write all the 04:12.280 --> 04:13.620 way I appear it's giant. 04:13.780 --> 04:18.130 But basically if it's this book or if it's this book both are memoirs. 04:18.280 --> 04:20.730 So we have these two exceptions. 04:20.750 --> 04:27.220 You'll need to write in Finally everything else which hopefully sounds familiar. 04:27.440 --> 04:30.690 Otherwise everything else at the end is called a novel. 04:31.040 --> 04:35.520 And I know some of these aren't actually novels like considered the lobster. 04:35.560 --> 04:40.790 It's more of an essay or a collection of essays 10 percent happier is like a self-help book but I don't 04:40.790 --> 04:41.840 want to get too complex. 04:41.840 --> 04:47.030 So just to reiterate the title contains stories type in short stories. 04:47.360 --> 04:53.760 If the title is exactly just kids or exactly a heartbreaking work of Staggering Genius then it's memoir. 04:53.930 --> 04:55.850 Both great memoirs by the way. 04:55.850 --> 04:58.420 And then finally everything else is a novel. 04:58.480 --> 04:59.150 OK. 04:59.360 --> 05:01.810 One more this is a bit of a bonus. 05:02.090 --> 05:04.960 It's more complex to put a few things together. 05:05.880 --> 05:10.030 So we have title of a book and the author's last name once again. 05:10.320 --> 05:15.920 But then there's this count section and count which we've already seen do is a little different. 05:15.920 --> 05:21.750 It's not just saying two or one or three but it's actually doing we can ignore the title part. 05:21.750 --> 05:23.250 Actually you don't need to do that. 05:23.490 --> 05:26.080 Just focus on the last name of the author in the count. 05:26.100 --> 05:29.470 It's saying how many books each author has. 05:29.880 --> 05:34.560 How many they've written so Carver has two books Geymann has three books. 05:34.770 --> 05:37.050 Harris Dan Harris or Frida Harris. 05:37.050 --> 05:40.730 They each have one book jubilee here he has two books and so on. 05:40.980 --> 05:48.810 But not only is it printing the number but there's also the properly pluralized books or book following 05:48.810 --> 05:49.160 it. 05:49.170 --> 05:55.050 So one book singular two books three books one book and so on. 05:55.380 --> 06:00.330 So you'll need to use a couple things I can give you hints if you don't want the hints then stop right 06:00.330 --> 06:00.720 now. 06:00.990 --> 06:07.620 But the hints are you need to use group by Nietzsche also use a case statement to generate this right 06:07.620 --> 06:08.300 here. 06:08.880 --> 06:13.830 But it is a bonus because you do need to combine a couple of things to a decent amount of work. 06:13.930 --> 06:16.710 OK so that's it. 06:16.710 --> 06:19.940 Once you finish that burn all the books are done with them. 06:19.980 --> 06:21.400 Whatever that means to you. 06:21.720 --> 06:26.440 I don't actually condone burning books but if you to get rid of the database go ahead. 06:26.490 --> 06:28.080 Although you may want to refer back to it. 06:28.200 --> 06:30.660 This is more just me saying that we're done. 06:30.840 --> 06:31.950 Done with the books. 06:31.950 --> 06:32.890 This is a turning point. 06:32.880 --> 06:40.170 We're moving on to bigger and better data and two pretty important section which is joint so I'd definitely 06:40.170 --> 06:41.360 recommend you take a break. 06:41.610 --> 06:44.630 But I will reiterate that in the solution video which is coming up next.