1 00:00:00,090 --> 00:00:00,470 All right. 2 00:00:00,480 --> 00:00:01,320 Welcome back. 3 00:00:01,440 --> 00:00:03,210 Me and my cat. 4 00:00:03,390 --> 00:00:06,720 Can you per say hi, ma'am. 5 00:00:07,380 --> 00:00:08,220 That was my cat. 6 00:00:08,220 --> 00:00:09,090 I promised. 7 00:00:09,090 --> 00:00:13,410 So in this video, we're now going to spend some time or you were going to spend some time getting some 8 00:00:13,410 --> 00:00:15,780 practice with the different functions. 9 00:00:15,780 --> 00:00:21,350 We just saw things like Count Min, max and average as well as using them with group. 10 00:00:21,350 --> 00:00:21,750 Bye. 11 00:00:22,260 --> 00:00:25,920 So in this video, I'm just going to lay out the challenges for the exercises. 12 00:00:25,920 --> 00:00:28,800 And then in the next video we'll talk about solutions. 13 00:00:29,280 --> 00:00:34,350 So the first one is to print the number of books in the database. 14 00:00:35,040 --> 00:00:42,450 So don't just do a select star from books and then manually go through and tabulate or count them. 15 00:00:43,020 --> 00:00:46,230 Write a single line that will print out the number of books in the database. 16 00:00:47,190 --> 00:00:49,860 And then once you've done that, move on to this one. 17 00:00:50,310 --> 00:00:53,730 Print out how many books were released in each year. 18 00:00:54,060 --> 00:01:00,030 And I have to say the wording on some of these is it's not great, but I kind of struggle to figure 19 00:01:00,030 --> 00:01:07,260 out how to explain this the best way, basically group things by year that they're released and then 20 00:01:07,260 --> 00:01:11,880 just print out how many were released that year or each year. 21 00:01:12,030 --> 00:01:19,830 So it should look something like 2005 one, 2006 two books, 1945 one book. 22 00:01:20,070 --> 00:01:24,510 Now those numbers I just made up, so don't worry about those numbers. 23 00:01:24,510 --> 00:01:26,580 But basically that's the idea. 24 00:01:26,580 --> 00:01:31,380 You should have the year and then account for how many books were released that year. 25 00:01:32,370 --> 00:01:37,560 And then next up, print out the total number of books in stock. 26 00:01:37,560 --> 00:01:42,300 So there's a stock quantity and every book has a stock quantity. 27 00:01:42,690 --> 00:01:45,800 So print out the sum. 28 00:01:46,230 --> 00:01:47,790 Oh, I just gave it away. 29 00:01:47,790 --> 00:01:52,620 Well, print out the sum of all of the books in stock. 30 00:01:52,620 --> 00:02:00,990 So just add together all the quantities and then find the average release year for each author. 31 00:02:01,320 --> 00:02:09,509 So you'll need to go through group things by author first or last and first name, and then find the 32 00:02:09,509 --> 00:02:12,060 average year that they were released for each author. 33 00:02:12,060 --> 00:02:16,770 So your results should look something like Raymond and then Carver. 34 00:02:16,770 --> 00:02:20,250 And then the third column should be the average released year. 35 00:02:20,310 --> 00:02:23,490 Let's say that's 1980 5.2 or something. 36 00:02:25,680 --> 00:02:29,730 Next up, find the full name of the author who wrote the longest book. 37 00:02:29,730 --> 00:02:34,110 So full name here is in a different color because it's a little different. 38 00:02:34,770 --> 00:02:41,490 This one, you'll need to use one of the tricks that we saw in that video where we talked about men 39 00:02:41,490 --> 00:02:45,600 and Max and that issue, my cats being a nightmare. 40 00:02:45,630 --> 00:02:47,280 Hey, come on. 41 00:02:48,420 --> 00:02:48,870 Hello? 42 00:02:49,290 --> 00:02:49,760 No, no. 43 00:02:49,800 --> 00:02:50,730 Oh, my God. 44 00:02:52,670 --> 00:02:54,860 She's just knocking everything off my desk. 45 00:02:56,270 --> 00:02:56,930 Hello? 46 00:02:59,140 --> 00:02:59,480 Okay. 47 00:02:59,500 --> 00:03:01,480 So where was I? 48 00:03:01,510 --> 00:03:08,860 The idea here is to not just find the longest book or the page count for the longest book, but actually 49 00:03:08,860 --> 00:03:11,620 print out the name of the author, the full name. 50 00:03:11,620 --> 00:03:16,010 So you'll need to use that full name. 51 00:03:16,030 --> 00:03:22,270 We want first plus last name with a space in between of the author who wrote the longest book. 52 00:03:22,270 --> 00:03:28,210 So you need to find the longest book and then use that to find the author, basically. 53 00:03:28,570 --> 00:03:29,830 And there's a couple of ways of doing that. 54 00:03:29,830 --> 00:03:36,340 If you remember the Minimax video that the second Minimax video where I talked about some of the problems 55 00:03:36,340 --> 00:03:44,440 or one problem in particular that arises when you try and print information like title or author based 56 00:03:44,440 --> 00:03:46,420 off of something being mean or max. 57 00:03:47,920 --> 00:03:51,460 And then the last challenge here is to print this data out. 58 00:03:51,820 --> 00:03:56,650 So we should have the euros or at least notice that we have an alias for all three of these. 59 00:03:56,650 --> 00:04:01,870 So the year 1945, the number of books released in that year. 60 00:04:01,870 --> 00:04:04,090 So one one, one, one. 61 00:04:04,450 --> 00:04:05,410 Then here we go. 62 00:04:05,410 --> 00:04:11,590 In 2001, there were three books and then the average number of pages for those books released in that 63 00:04:11,590 --> 00:04:12,010 year. 64 00:04:13,600 --> 00:04:18,300 So I won't tell you how you need to grip things in this case, but hopefully you can figure that out 65 00:04:18,310 --> 00:04:19,510 based off of this image. 66 00:04:20,290 --> 00:04:20,920 All right. 67 00:04:21,140 --> 00:04:21,519 Got it. 68 00:04:21,550 --> 00:04:23,330 Take care of this cat situation. 69 00:04:23,350 --> 00:04:24,190 Oh, my God. 70 00:04:24,580 --> 00:04:27,250 And then in the next video, we'll have a solution.