WEBVTT 00:00.480 --> 00:06.210 Right so hopefully you made it through those exercises or maybe you're just wanting to watch the solutions 00:06.930 --> 00:09.360 or maybe you're curious to know how you got here. 00:09.540 --> 00:16.470 What I'm going to do in this video is go over the solutions to the previous video and that's about noise 00:17.230 --> 00:18.510 that happened. 00:18.540 --> 00:19.190 All right. 00:19.500 --> 00:20.390 So the first one. 00:20.400 --> 00:22.970 Select All story collections that. 00:22.980 --> 00:27.390 So these are titles that contain the word stories in them somewhere. 00:27.840 --> 00:29.750 And we just want to print the title. 00:30.240 --> 00:33.330 So I'm going to go over here and make some space. 00:33.330 --> 00:41.550 And the first thing that I'll do is just make a new file and I'll just call it exercises as well and 00:41.550 --> 00:46.850 then I'll open that up and we'll start off with this first one. 00:46.860 --> 00:51.450 Select title from books and we'll start there. 00:51.450 --> 00:53.300 Just make sure we're doing everything correctly. 00:53.580 --> 01:02.640 Source refining selections slash exercises that is to make sure that it's working. 01:02.640 --> 01:04.210 Selecting the titles. 01:04.320 --> 01:07.630 So now we only want the ones that contain stories. 01:07.830 --> 01:14.670 So that's going to be where and we're going to do title like. 01:14.710 --> 01:16.560 And this is you know we just do stories. 01:16.570 --> 01:17.760 It's not going to work. 01:17.850 --> 01:24.750 That gives us exactly the stories we're going to want that wildcard character just like that. 01:25.100 --> 01:31.920 And now we get what we're looking for technically with our specific data we could leave that last percentage 01:31.920 --> 01:35.850 sign off the last wildcard because all of the books that contain the word stories. 01:35.940 --> 01:39.990 It comes at the end but that may not always be the case. 01:40.020 --> 01:47.240 There might be a book out there called my favorite stories for kids or something like that. 01:47.430 --> 01:53.340 And if we wanted this truly to just find all books that have stories in the title we need both of those 01:53.340 --> 01:55.320 wild card characters. 01:55.320 --> 01:55.980 All right. 01:55.980 --> 01:58.080 So that's the first one. 01:58.080 --> 02:01.500 Next up find the longest book. 02:01.500 --> 02:06.020 So print out the title and the page count for the one longest book. 02:06.360 --> 02:13.860 So we'll go ahead and comment this line out and let's just do a select title and pages from books just 02:13.860 --> 02:15.210 like that to start. 02:15.660 --> 02:16.230 OK. 02:16.580 --> 02:22.300 So now we want to do is sort them we'll sort them by the pages. 02:22.580 --> 02:25.510 So that's an order by pages. 02:25.530 --> 02:32.250 And if we just do the default sort remember that we get ascending rather than descending which is what 02:32.250 --> 02:36.390 we want what we're going to do is use a limit to give us the first result. 02:36.510 --> 02:43.430 But right now if we do limit one it's going to give us the shortest book rather than the longest one. 02:43.920 --> 02:49.570 So we want to reverse that sort with the descending order. 02:50.220 --> 02:57.690 It's now the first one is the longest and then we just add our limit 1 and we run it again and again 02:57.720 --> 03:00.310 and again and it always works. 03:00.330 --> 03:06.460 And so whatever book has the longest page count will always be what is printed out here. 03:06.500 --> 03:07.370 All right. 03:07.380 --> 03:10.750 So that's that one that's commented out and move on. 03:11.880 --> 03:16.860 Print out a summary of containing the title and year for the three most recent books. 03:16.890 --> 03:25.230 So this one required you to not only use ORDER BY and limit or ordering by most recent year and then 03:25.230 --> 03:31.590 limiting it to three but you also needed to use concatenate to concatenate the title the dash with a 03:31.590 --> 03:32.490 year. 03:33.180 --> 03:38.370 So let's do that now let's just start by doing this select Concat. 03:39.000 --> 03:41.300 And it was title comma. 03:41.520 --> 03:46.770 Then we have a space and a dash and a space comma released here. 03:48.910 --> 03:52.160 And we'll start with just that from books. 03:52.160 --> 03:55.550 Save OK. 03:55.940 --> 03:58.430 Oh the other thing we wanted to do is clean this up. 03:58.440 --> 03:59.960 It says summary up there. 03:59.990 --> 04:01.870 So this is also review. 04:01.940 --> 04:04.110 Say as summary. 04:05.150 --> 04:05.840 OK. 04:05.990 --> 04:12.950 So now we get that now now we want to order them by most recent and let's break this up on to some different 04:12.950 --> 04:13.860 lines here. 04:16.060 --> 04:18.000 From books. 04:18.100 --> 04:22.660 Order by and we'll order by at least here. 04:23.980 --> 04:31.120 And if we try that now we also see that it does work but it's in the opposite order that we want we 04:31.120 --> 04:32.450 want most recent. 04:32.650 --> 04:41.690 So we need to reverse that again with descending and then finally we want to limit it to the first three. 04:41.950 --> 04:49.750 I met three or if you want to get fancy you could do this but I don't like doing that. 04:49.960 --> 04:52.330 So that's much easier. 04:52.360 --> 04:52.920 Get. 04:52.970 --> 04:57.620 So say give this link in the bardo Dasch 20:17 Norse mythology d'Este 2016. 04:57.640 --> 05:00.660 10 percent happier dasht 2014 summary. 05:00.730 --> 05:02.890 That's all good to go. 05:02.890 --> 05:08.210 Next up find all the books that have an author last name containing a space. 05:08.330 --> 05:11.160 Don't cheat by just doing Foster Wallace. 05:11.620 --> 05:14.280 So that's come with this out first. 05:14.550 --> 05:16.180 This one is actually pretty short and easy. 05:16.210 --> 05:25.150 It's just a select and we're looking for a title and author name select title comma author name where 05:26.080 --> 05:35.050 author name like and of course you can't just say space because that's only going to find authors whose 05:35.050 --> 05:41.390 last name is exactly space and it looks like I'm forgetting from books. 05:41.390 --> 05:43.430 Hopefully you caught that before I did. 05:44.020 --> 05:49.030 But back to what are saying this isn't going to work because we don't have any authors whose last name 05:49.060 --> 05:49.950 is space. 05:50.020 --> 05:52.920 So what we want is that wildcard 05:55.860 --> 06:00.040 on either side anything with a space and then anything afterwards. 06:00.690 --> 06:04.130 Now we get Foster Wallace and that's it for that one. 06:05.790 --> 06:09.660 Next up find three books with the lowest stock. 06:09.730 --> 06:15.670 So we want the title released here and the stock quantity three books with the lowest stock. 06:15.960 --> 06:20.440 So let's start with commenting this out just like we've done with all the others. 06:20.640 --> 06:22.590 It's like tidal. 06:22.910 --> 06:25.400 What else was it released here in stock quantity. 06:25.410 --> 06:29.100 OK. 06:29.270 --> 06:31.280 Just like that. 06:31.280 --> 06:34.680 And we have are from books. 06:35.350 --> 06:36.280 We'll start there. 06:36.280 --> 06:37.570 Make sure that works. 06:38.260 --> 06:38.830 OK. 06:39.040 --> 06:41.930 So now we want to do is sort them by stock quantity. 06:41.950 --> 06:45.240 So that's just an order by stock quantity 06:47.940 --> 06:52.140 and you can see that gives us the order we want we want them to be in ascending order this time rather 06:52.140 --> 06:58.950 than descending because we are looking for the lowest stock quantity 12 all the way up to 1000. 06:59.640 --> 06:59.910 Okay. 06:59.910 --> 07:02.880 And then we want to limit it to 3. 07:02.940 --> 07:07.530 I don't know why I picked three so many times in these exercises. 07:07.530 --> 07:08.040 Let's try it. 07:08.040 --> 07:11.830 Now with that limit 3 and there we go. 07:12.000 --> 07:15.950 American Gods where I'm calling from what we talk about when we talk about love. 07:16.830 --> 07:18.310 That's what we're looking for. 07:19.820 --> 07:25.610 Then we've got this one print title and author last name sorted first by author last name and then by 07:25.610 --> 07:26.560 title. 07:27.170 --> 07:37.220 So let's get the title and author a last name to print first select title comma author name from books 07:38.240 --> 07:40.070 just like that. 07:40.250 --> 07:44.400 And at this point we all know what that does. 07:44.960 --> 07:47.950 So now let's sort by the author last name. 07:48.050 --> 07:55.220 So that's just a simple order by author Al name and that gives just the alphabetical order we're looking 07:55.220 --> 07:55.500 for. 07:55.520 --> 07:56.780 We don't need to use descending. 07:56.810 --> 07:58.340 That's all right. 07:58.340 --> 08:01.790 But then if you look over here we take Carver's books. 08:01.790 --> 08:07.040 Notice that where I'm calling from comes before what we talked about when we talk about love even though 08:07.180 --> 08:10.180 W H A comes before W h e. 08:10.580 --> 08:17.260 So we want to now sort by title there and the way we do that is not by just changing that to be title 08:17.300 --> 08:20.970 because then it purely starts by title and ignores. 08:21.000 --> 08:27.670 Author name rather we want to do this author name comma title 08:30.750 --> 08:38.460 and there we go now you could also replace this whole thing with two comma one which I really don't 08:38.460 --> 08:42.060 like just because it's not very semantic. 08:42.270 --> 08:45.580 It's not meaningful to look at this but it is shorter. 08:45.720 --> 08:50.580 And again that too is referring to author L name and one is referring to title it's just the order that 08:50.580 --> 08:53.840 we're selecting them and so I'm going to change it back. 08:54.090 --> 09:03.070 But I will also duplicate that fellow and change it to to come a one incumbent that out as well. 09:03.160 --> 09:05.140 So we have both solutions. 09:05.140 --> 09:07.540 Which brings us to the last problem. 09:07.600 --> 09:10.450 Hopefully it's OK to make this happen. 09:10.720 --> 09:12.430 So we want to yeah. 09:12.550 --> 09:13.900 My favorite author is. 09:14.140 --> 09:19.210 And then the uppercase first name of an author and then a space in uppercase last name of an author 09:19.210 --> 09:21.490 and an exclamation point. 09:22.000 --> 09:23.660 He has kind of a lot. 09:23.730 --> 09:30.970 OK so let's start with selecting and we know already that we can start with select from books. 09:30.970 --> 09:35.620 Sure what we're going to want to do is concatenate. 09:35.710 --> 09:37.330 My favorite author is. 09:37.820 --> 09:44.060 And then in upper case version of Raymond and space and then uppercase version for last name. 09:44.230 --> 09:47.640 So an exclamation point to a lot of concatenation. 09:47.710 --> 09:52.100 So we're going to have a concat and I'll just do this. 09:52.260 --> 09:53.540 Well let's do this way. 09:53.580 --> 09:54.300 Just like that. 09:54.400 --> 09:55.730 What are we concatenating. 09:56.050 --> 09:58.860 Well let's start with uppercase or without. 09:58.900 --> 10:14.060 So let's just start with my favorite author is ideaspace comma and then we want Arthur F. name and then 10:14.240 --> 10:16.340 we want another space. 10:16.340 --> 10:17.630 This space here. 10:17.930 --> 10:19.850 So they don't just run together. 10:19.850 --> 10:20.560 Comma. 10:20.810 --> 10:25.320 Author L name finally an exclamation point. 10:25.460 --> 10:27.380 That's a lot. 10:27.420 --> 10:30.550 So let me drag this down a bit so we can see the whole query. 10:30.570 --> 10:36.870 Select the concatenation of my favorite author is space the first name of an author space the last name 10:36.870 --> 10:37.620 of an author. 10:37.650 --> 10:38.890 Exclamation point. 10:39.330 --> 10:42.190 Let's save it and run it. 10:42.240 --> 10:43.240 It's pretty long. 10:43.470 --> 10:51.780 Oh and this is partially messed up because you see appear the column heading here is this entire thing 10:51.790 --> 10:55.460 so let's add the as yet. 10:56.880 --> 10:59.290 Now if I run it there we go. 10:59.610 --> 11:00.360 OK. 11:00.870 --> 11:02.480 So we're close. 11:02.490 --> 11:09.120 The next thing we need to do is capitalize author f name and our author last name which is easy just 11:09.120 --> 11:09.810 in Upper 11:12.750 --> 11:14.860 like that save. 11:14.980 --> 11:15.940 Rerun it. 11:16.440 --> 11:17.490 OK it's all uppercase. 11:17.490 --> 11:23.520 Now we have one last thing which is sorting alphabetically by the last name. 11:23.560 --> 11:25.670 So from books. 11:25.820 --> 11:33.090 By Author Elman one query here. 11:33.420 --> 11:35.870 Let's try it. 11:35.910 --> 11:36.930 Looks like it worked. 11:36.990 --> 11:41.970 We got Raymond Carver all the way down to my favorite authors John Steinbeck and that's about as loud 11:41.970 --> 11:42.870 as lot Yellin here. 11:42.870 --> 11:44.010 Don't want to wake my neighbors. 11:44.010 --> 11:44.880 All right. 11:44.880 --> 11:49.040 So congratulations Hopefully you made it through this relatively unharmed. 11:49.170 --> 11:52.760 It's a lot of exercises a lot of ordering by and limiting. 11:52.940 --> 11:55.090 But the reality is we use those a lot. 11:55.140 --> 12:01.740 It is very useful to be able to determine you know the I mean highest page count maybe not but the best 12:01.740 --> 12:02.710 selling book. 12:02.730 --> 12:07.920 Once we get to sales and users and stuff for the most active users are like it keep coming back to the 12:08.190 --> 12:16.020 highest commented post or the most trafficked the most clicked on ad all those sorts of bits of data 12:16.350 --> 12:17.410 are really useful. 12:17.680 --> 12:23.640 And we use ORDER BY and limit all the time as well as other tools that we saw distinct and like they're 12:23.640 --> 12:24.630 all very useful. 12:24.630 --> 12:29.520 All right well I recommend you take a quick little break and then we'll move on.