WEBVTT 00:00.120 --> 00:02.800 Oh okey artichoke. 00:03.170 --> 00:06.200 Yep that's how I started this video off. 00:06.690 --> 00:07.570 It's your turn. 00:07.770 --> 00:13.000 So we've covered four different things here for new tools for this mysterious magical toolbox. 00:13.020 --> 00:17.060 We've got distinct as well as ORDER BY and then limit. 00:17.130 --> 00:22.380 And then finally like you're going to put those to use along with some of the stuff you've learned up 00:22.380 --> 00:27.960 until this point with a couple of exercises and there are some clown images here to show you how excited 00:27.960 --> 00:30.960 you should be and they're not at all creepy. 00:30.960 --> 00:32.580 So here's the first thing I need you to do. 00:32.680 --> 00:33.630 Or I'd like you to do. 00:33.630 --> 00:34.980 Please please do it. 00:35.220 --> 00:37.370 Select All story collections. 00:37.650 --> 00:42.930 So some of these books have titles that contain the word stories which tells you that they're are a 00:42.930 --> 00:44.170 collection of stories. 00:44.520 --> 00:49.230 What We Talk About When We Talk About Love Stories really good read by the way where I'm calling from 00:49.290 --> 00:52.280 Selected Stories oblivian stories. 00:52.320 --> 00:57.350 So using what we just saw and that's something I just want to point out for all of these exercises. 00:57.420 --> 01:04.590 There are ways that you could directly select things maybe a little bit easier or you could cheat in 01:04.590 --> 01:08.830 a way but make sure that you do it the way that I specify please. 01:08.850 --> 01:16.140 Using that things you just learned so specifically titles that contain the word stories not exactly 01:16.140 --> 01:19.840 stories but something that contains stories in it. 01:19.860 --> 01:21.240 So that's the first one. 01:21.810 --> 01:22.190 All right. 01:22.200 --> 01:27.680 And then the next one find the longest book in the entire database or in the entire books table. 01:27.810 --> 01:29.810 So this is what you should get back. 01:29.820 --> 01:36.870 Print out the title and the pages The Amazing Adventures of Kavalier and Clay 634 pages of course don't 01:36.870 --> 01:43.290 just select based off of four pages is exactly 6:34 or where the title is The Amazing Adventures of 01:43.290 --> 01:43.770 cavalier. 01:43.770 --> 01:51.030 Anyway please do it the right way so that your queery would always work no matter how many books are 01:51.040 --> 01:55.010 in the table and what they're named and how many pages they have. 01:55.110 --> 01:59.290 It will always get the longest book OK. 01:59.500 --> 02:00.400 Just one. 02:00.730 --> 02:04.380 Then the next problem print summary. 02:04.450 --> 02:12.340 In our case a summary contains the title Dasch the year and print 3. 02:12.400 --> 02:14.980 Only three of the most recent books. 02:15.250 --> 02:18.410 So 2017 2016 and 2014. 02:18.430 --> 02:23.090 So again a summary and it says it says summaries of that. 02:23.110 --> 02:25.160 Remember how to do that. 02:25.240 --> 02:32.670 The title Dasch the year it was released and then only print out the three most recently released books. 02:33.280 --> 02:33.790 All right. 02:33.790 --> 02:42.580 Next Next up is find all the books whose author's last name contains a space character and only David 02:42.580 --> 02:43.180 Foster Wallace. 02:43.180 --> 02:44.350 In our case. 02:44.350 --> 02:45.690 Don't cheat and just say. 02:45.700 --> 02:47.910 Author L name is exactly foster space. 02:47.910 --> 02:52.870 WALLACE But how can you find any author last name that has a space in it. 02:53.930 --> 03:01.610 And then when to do that find the three books with the lowest number and the lowest stock quantity. 03:01.610 --> 03:07.000 So as you can see here you forgot American Gods is 12 where I'm calling from has 12. 03:07.130 --> 03:12.130 What we talk about when we talk about love has 23 and I'd like you to print the title. 03:12.150 --> 03:14.560 The released year and the stock quantity. 03:14.610 --> 03:19.510 So just to reiterate the three lowest stocks. 03:19.540 --> 03:22.050 All right then we have this one. 03:22.240 --> 03:24.330 Print the title and the author last name. 03:24.580 --> 03:29.140 So you've got title author out name sorted by the author's last name. 03:29.200 --> 03:36.010 So this is all of the books by the way so sort by the author last name and then sort by title you get 03:36.010 --> 03:41.250 some of these authors for example Carver Eggers are game and have multiple books. 03:41.290 --> 03:46.480 So if we look at Carver for instance Carver has what we talk about blah blah blah where I'm calling 03:46.480 --> 03:51.700 from what we talk about comes before where I'm calling from alphabetically. 03:51.960 --> 03:52.570 If that makes sense. 03:52.560 --> 03:55.830 Same thing with Eggers a heartbreaking a hologram. 03:55.930 --> 04:03.850 The circle so sort first by the last name of the author and then on top of that the title then we've 04:03.850 --> 04:05.310 got this one just make this happen. 04:05.320 --> 04:09.070 It's kind of difficult to write out exactly what's happening. 04:09.070 --> 04:15.790 The main idea is that we're going to go through every book you're going to print out this string here. 04:15.850 --> 04:19.600 My favorite author is and this is yelling in all caps I won't wake my neighbors. 04:19.600 --> 04:25.420 It's kind of fleet but my favorite author is and then the author's first name capitalized and the author's 04:25.420 --> 04:28.590 last name capitalized and an exclamation point at the end. 04:28.900 --> 04:35.530 And do that for every book sorted alphabetically by the author's last name so you can see Carver comes 04:35.530 --> 04:36.480 first. 04:36.640 --> 04:40.960 Aggers Foster Wallace game and down to Steinback and then McCrery. 04:40.960 --> 04:42.240 Call it yellow up here. 04:42.460 --> 04:45.610 Yes it's a bit of a complicated one but fortunately it's last. 04:45.970 --> 04:47.920 Hopefully you can make this one work. 04:48.010 --> 04:49.260 It's a little bit long. 04:49.660 --> 04:50.370 Good luck. 04:50.420 --> 04:51.820 See in this Lucian video.