WEBVTT 00:00.300 --> 00:00.880 Get. 00:00.990 --> 00:06.060 So in this video we're just going to go over the solutions to the exercises I introduced in the previous 00:06.060 --> 00:11.160 video and the first one here is to print the total number of books in the database. 00:11.160 --> 00:22.120 So we need to use count and all that we'll do is write a select count star from books just like that 00:22.720 --> 00:25.420 and we get 19. 00:25.540 --> 00:25.940 All right. 00:25.990 --> 00:28.300 And that's it. 00:28.300 --> 00:32.690 The next one is to print out how many books were released in each year. 00:32.890 --> 00:41.050 So we need to use a group by group by released here and then use count in conjunction with that. 00:41.200 --> 00:59.220 So let's do a select and we'll just do count Starr from books GROUP BY released here just like that. 00:59.540 --> 01:08.300 Now on its own this isn't that useful so I'll go ahead and add in like you released here comma count 01:08.820 --> 01:17.180 so that we get the released and then the count you can see most years have one book released 2001 has 01:17.180 --> 01:17.590 three. 01:17.580 --> 01:20.550 2003 has two. 01:20.550 --> 01:21.390 All right. 01:22.350 --> 01:28.220 So the next problem print out the total number of books in stock. 01:28.570 --> 01:32.140 So this one you'll actually need to use some. 01:32.240 --> 01:39.170 You'll need to sum all the stock quantities which is something I guess we haven't really used. 01:39.780 --> 01:42.680 But it's nice fell from. 01:42.730 --> 01:44.510 We haven't used stock. 01:44.530 --> 01:47.280 Why can't you got a chill out. 01:48.660 --> 01:49.500 It's a start. 01:49.500 --> 01:54.240 Quantity is not a piece of data that we use very often but it is here. 01:54.690 --> 01:58.230 We've tried to do it a little bit but it is there as you can see. 01:58.420 --> 02:07.450 So we just want to add them all together and that's just going to be select some stock quantity quantity 02:09.640 --> 02:13.520 from books as we go. 02:13.570 --> 02:16.460 We have 2000 450 books in stock. 02:16.840 --> 02:22.420 So the next problem here is to find the average release year for each author or so many to group by 02:22.420 --> 02:28.970 author and then we need to use average Viji to calculate the average for this year. 02:29.380 --> 02:34.150 So let's give it a shot average release year for each author. 02:34.690 --> 02:43.290 So we need to do a select average and then release here. 02:44.720 --> 02:49.250 From books group by and then we're grouping by author. 02:49.460 --> 02:57.130 So we'll do l name Khama author f name again because of the Harris issue. 02:57.420 --> 02:58.700 What are their names. 02:58.700 --> 03:00.470 Dan Harrison free Harris. 03:00.810 --> 03:06.570 OK so if we do that my cat again. 03:07.180 --> 03:08.140 Oh boy. 03:08.580 --> 03:12.120 Well we've taken care of that and I don't mean. 03:12.780 --> 03:13.520 Wow. 03:13.920 --> 03:16.340 Apparently they have not taken care of it. 03:16.540 --> 03:16.900 Right. 03:16.900 --> 03:18.080 Hang on. 03:18.300 --> 03:20.710 So back to the problem at hand. 03:21.030 --> 03:22.090 This works. 03:22.290 --> 03:23.340 We're getting average years. 03:23.340 --> 03:32.520 But to make it a little bit better we should also add in the author name author Alvin I'm just so we 03:32.520 --> 03:34.290 can make a bit more sense of the data. 03:34.590 --> 03:40.290 So now we can see Raymond Carver's average release years 1985. 03:40.480 --> 03:44.240 See Neil Gaiman's his 2006 point six six six seven. 03:44.790 --> 03:45.390 All right. 03:45.600 --> 03:47.390 So that's all there is to that problem. 03:48.830 --> 03:50.810 Now we get to the one that's a little different. 03:50.810 --> 03:54.610 Find the full name of the author who wrote the longest book. 03:55.070 --> 04:00.370 So there's a couple ways of doing it the the way that doesn't qualify as a solution. 04:00.440 --> 04:04.560 The two step process would be first to find the longest book. 04:04.940 --> 04:18.530 So that would be select max pages from books like this 6:34 and then you know at this point we can make 04:18.530 --> 04:28.660 a second query select and we want to concatenate first name and last name so we do concat author name 04:28.670 --> 04:32.400 comma author L name and we want a space in between 04:35.620 --> 04:36.040 that 04:38.930 --> 04:47.490 from books where pages equals 6:34 and there we go we get Michael Chaplin. 04:47.490 --> 04:56.310 However we want to do it in one step so we can either do the sub query route so do so like concat author. 04:56.760 --> 05:06.480 However instead of we'll do from books but instead of just doing web pages 6:34 will do where pages 05:07.560 --> 05:11.050 equals and then we use parentheses for the sub query. 05:11.070 --> 05:15.660 Hopefully you remember this part and then in here all that we do is write our queries. 05:15.660 --> 05:27.410 That's going to be select max pages from books and we get the same thing. 05:27.430 --> 05:35.340 I go Siobhan and then there's the other way of going about it which is to not use Macs at all and circumvent 05:35.340 --> 05:41.820 that which is actually faster to use order by and then limit it to one. 05:41.820 --> 05:49.870 So we could do the same thing so I can cat author F. name author L name from books and then we just 05:49.870 --> 06:00.840 do an order by pages ascending limit one and they believe have that backwards. 06:00.900 --> 06:07.990 We got the longest book not the shortest one area so we want it in descending order so that we get the 06:08.140 --> 06:10.910 longest one at the beginning. 06:11.650 --> 06:13.590 And that's going to be 6:34. 06:13.780 --> 06:26.090 So if we if we get rid of this limit one and actually we get rid of limit 1 and let's do pages comma. 06:26.460 --> 06:26.830 All right. 06:26.860 --> 06:32.180 So you can see now you've got six thirty four all the way down to 176. 06:32.200 --> 06:34.550 And I was I had it backwards. 06:35.020 --> 06:35.280 OK. 06:35.290 --> 06:38.400 So both are valid ways of getting a solution. 06:38.410 --> 06:41.240 This one is a little bit. 06:41.770 --> 06:45.310 Well it's a little faster because we're running one query. 06:45.310 --> 06:54.610 This version is more complex more advanced I guess but I would definitely stick to using this when possible 06:54.670 --> 06:56.430 using order right. 06:56.440 --> 06:56.890 All right. 06:56.920 --> 07:00.400 Let's move on to our final challenge here. 07:00.430 --> 07:01.680 Make this happen. 07:02.140 --> 07:04.490 So I've got a couple of things going on. 07:04.870 --> 07:11.980 We've got a year so things let's see if we get Year number of books in that given year and the average 07:11.980 --> 07:14.440 pages for all those books for that given year. 07:14.440 --> 07:19.160 So things are grouped by year and we have three alias's going on. 07:19.540 --> 07:21.060 OK so let's give it a shot. 07:21.430 --> 07:29.860 So we'll start off by selecting year or released year and giving it the alias of year 07:33.480 --> 07:35.740 and the next thing is the count. 07:35.910 --> 07:41.370 Remember we're going to have a group by at the end so we're doing all of this you know writing the three 07:41.370 --> 07:43.940 things we're selecting before we actually have the group by. 07:43.950 --> 07:46.920 But at the end we're going to have group five at least here. 07:46.950 --> 07:52.150 So this here number books is count to count the number of books released that year. 07:52.260 --> 07:57.290 So all in debt and just do count star paths. 07:57.540 --> 08:04.110 And I need quotes here because we have that Octa Thorpey I think is what that's called the hash symbol 08:05.100 --> 08:06.260 as well as a space. 08:06.450 --> 08:12.750 So we need quotes for Aliase if we want to match this then we have average pages. 08:12.950 --> 08:16.120 So it's going to be the same thing as far as our alias is concerned. 08:16.250 --> 08:22.170 So we need average number of pages as a Viji pages. 08:22.290 --> 08:23.120 That's correct. 08:23.120 --> 08:28.980 There we go OK from books. 08:29.600 --> 08:33.510 And you know all and done this one to group by. 08:34.280 --> 08:40.220 And we want to group by released here hit enter and pray. 08:40.500 --> 08:43.100 And it looks like it worked out. 08:43.230 --> 08:47.790 19:45 had one book 181 pages long. 08:47.910 --> 08:48.690 Take a look at. 08:48.690 --> 08:55.090 Like 2001 there were three books and the average number of pages is 443 and the third. 08:55.560 --> 08:56.510 All right. 08:56.730 --> 09:01.740 So that matches what I had here which means that we got it right. 09:01.740 --> 09:03.670 Don't ask why you would do this. 09:03.720 --> 09:06.410 Just don't ask. 09:06.480 --> 09:10.230 That's it for this section and help us in the next section.