1 00:00:00,210 --> 00:00:00,690 All right. 2 00:00:00,690 --> 00:00:01,230 You know the drill. 3 00:00:01,230 --> 00:00:06,120 By now, it is your turn to get some practice using the stuff we learned in this section. 4 00:00:06,120 --> 00:00:13,160 So some group buys, maybe some some average count min, max aggregate function, something like that. 5 00:00:13,170 --> 00:00:15,540 Here we go using the books data sets. 6 00:00:15,840 --> 00:00:21,180 I'd like you to start by printing the total number of books in the database using something we just 7 00:00:21,180 --> 00:00:21,480 learned. 8 00:00:21,480 --> 00:00:26,400 So don't do like a select star and then just look at the bottom where it tells you how many rows, but 9 00:00:26,400 --> 00:00:29,730 actually get a number as a result of the select query. 10 00:00:30,180 --> 00:00:34,320 Then print out how many books were released by each year. 11 00:00:34,620 --> 00:00:36,810 So you'll need to use a group by. 12 00:00:36,810 --> 00:00:42,960 And I'd like to see on the left side the year on the right side the count of how many books were released 13 00:00:42,960 --> 00:00:43,620 that year. 14 00:00:44,630 --> 00:00:48,200 Then print out the total number of books in stock. 15 00:00:48,200 --> 00:00:50,600 So we have the stock quantity for each book. 16 00:00:50,630 --> 00:00:52,340 Just sum that all together. 17 00:00:53,020 --> 00:00:57,370 And then find the average released here for each author. 18 00:00:57,400 --> 00:01:04,239 Now, for each author, I'd like you to be conscious of the author first name and last name because 19 00:01:04,239 --> 00:01:06,130 of the whole Harris situation. 20 00:01:06,130 --> 00:01:10,780 But in general, if this is a much bigger data set, we would definitely have a lot of duplicate last 21 00:01:10,780 --> 00:01:11,260 names. 22 00:01:11,260 --> 00:01:14,860 So you should definitely use the first and last name. 23 00:01:14,860 --> 00:01:16,000 When you are grouping. 24 00:01:16,030 --> 00:01:17,680 Find the average released year. 25 00:01:17,830 --> 00:01:22,980 Next, find the full name of the author who wrote the longest book. 26 00:01:22,990 --> 00:01:27,430 So there's maybe more than one way of doing this, but you might need to use a subquery. 27 00:01:27,430 --> 00:01:28,000 Maybe. 28 00:01:28,000 --> 00:01:33,820 And when I say find the full name, I'd like you to have the first name with a space and then the last 29 00:01:33,820 --> 00:01:38,290 name combined or concatenated into a single piece of text. 30 00:01:38,680 --> 00:01:45,250 And then finally, the last exercise here is to make this happen, what you see on the screen. 31 00:01:45,790 --> 00:01:48,370 So we have each release here. 32 00:01:48,790 --> 00:01:55,390 How many books were released in that year and the average number of pages in those books. 33 00:01:55,660 --> 00:02:01,810 So you'll do another group by write by the year, and then you'll count up and average things and make 34 00:02:01,810 --> 00:02:06,760 sure you have this text show up as well, using an alias or aliases. 35 00:02:07,480 --> 00:02:07,820 Okay. 36 00:02:07,870 --> 00:02:09,430 So please do this. 37 00:02:09,430 --> 00:02:14,190 If you have any inclination towards these exercises, get some practice. 38 00:02:14,200 --> 00:02:17,890 You know, these are it's a very simple data set so it can help. 39 00:02:18,670 --> 00:02:20,050 It's a good place to practice. 40 00:02:20,050 --> 00:02:20,770 Basically. 41 00:02:21,010 --> 00:02:22,540 We know the data set by now. 42 00:02:22,540 --> 00:02:27,350 We know how many books were released in each year, how many books. 43 00:02:27,370 --> 00:02:33,160 Dave Eggers has written three in our data set, unlike working with some really large data set. 44 00:02:33,160 --> 00:02:38,080 And we'll do some of that later where you kind of just have to trust that your answers are right in 45 00:02:38,080 --> 00:02:39,130 this sort of data set. 46 00:02:39,130 --> 00:02:44,740 We know if we're right or wrong, so we get some practice, pause the screen, look at the slides, 47 00:02:44,740 --> 00:02:49,870 do eat past the screen, pause video, look at the slides and do each one of these and I'll be back 48 00:02:49,870 --> 00:02:51,670 with a solution in just a second.