1 00:00:00,210 --> 00:00:00,800 Okey dokey. 2 00:00:00,840 --> 00:00:03,940 Let's start working on some solutions to these challenges. 3 00:00:03,960 --> 00:00:07,380 The first one, print the number of books in the database. 4 00:00:07,830 --> 00:00:13,710 And again, I don't want to do the cheesy way select star from books and then just look at the bottom 5 00:00:13,710 --> 00:00:19,380 where it says 22 rows, which by the way, your number will likely differ because I inserted some of 6 00:00:19,380 --> 00:00:24,900 these null rows deliberately or partially null rows. 7 00:00:24,900 --> 00:00:27,450 So it's okay if you don't see that answer. 8 00:00:27,450 --> 00:00:35,910 Whatever answer you see though, you should see it as a result of running select count star from books. 9 00:00:36,090 --> 00:00:42,270 And when we have Count star, remember that means count the rows in this case from the entire table 10 00:00:42,270 --> 00:00:44,010 and we see 22. 11 00:00:44,040 --> 00:00:47,280 You may see 18, 17, 19, something like that. 12 00:00:47,880 --> 00:00:51,690 Next up, print out how many books were released in each year. 13 00:00:51,690 --> 00:00:56,040 So we're going to use count again, but this time we're going to group by the release here. 14 00:00:56,640 --> 00:01:08,220 So we'll have a select and then we want the released year and then we'll also get the Count star from 15 00:01:08,220 --> 00:01:08,850 books. 16 00:01:08,850 --> 00:01:12,450 But we're going to group by release year. 17 00:01:13,100 --> 00:01:15,140 So we'll group them all by release here. 18 00:01:15,590 --> 00:01:21,560 However many books are in each group, we count it up and we'll select that count alongside the year 19 00:01:21,560 --> 00:01:22,490 from each group. 20 00:01:22,490 --> 00:01:30,200 And we see 2003 had two books, 16 one book, 2001 three books, 2017 one book. 21 00:01:30,230 --> 00:01:31,250 You know, there we are. 22 00:01:31,280 --> 00:01:32,290 That looks good. 23 00:01:32,300 --> 00:01:34,070 That's what we're supposed to do. 24 00:01:34,190 --> 00:01:35,720 Or what I intended for you to do. 25 00:01:35,990 --> 00:01:39,260 Next up, print out the total number of books in stock. 26 00:01:39,260 --> 00:01:45,380 So we need to not count, but some together all of the stock quantities. 27 00:01:45,380 --> 00:01:51,290 Let me just remember, I came up with some kind of not great names for these columns, in my opinion, 28 00:01:51,290 --> 00:01:53,510 describe books. 29 00:01:54,320 --> 00:01:56,090 Stock quantity. 30 00:01:56,090 --> 00:01:56,630 Yeah. 31 00:01:56,630 --> 00:01:59,180 I mean, I should have just called it quantity probably. 32 00:01:59,180 --> 00:02:00,890 Or q t y even. 33 00:02:00,890 --> 00:02:07,430 Anyway, I think I want it to be super clear, but in doing that I made it hard to remember or clunky. 34 00:02:07,490 --> 00:02:09,380 Anyway, we ought to sum that all up. 35 00:02:09,380 --> 00:02:21,860 So that's going to be a select some, but not star select some of stock quantity from books all rows. 36 00:02:22,250 --> 00:02:25,730 And we see we have a total of 2450. 37 00:02:25,760 --> 00:02:29,240 Let's say we just looked at stock quantity on its own. 38 00:02:31,260 --> 00:02:32,610 That seems about right. 39 00:02:32,640 --> 00:02:39,120 We've got 30, 40, 100, 150, 100, 100, 170, 195. 40 00:02:39,120 --> 00:02:40,580 And then we have 1000 here. 41 00:02:40,590 --> 00:02:43,260 That adds up to be 2450. 42 00:02:43,590 --> 00:02:44,340 Okay. 43 00:02:44,940 --> 00:02:50,340 Next up, find the average released here for each author. 44 00:02:51,080 --> 00:02:55,190 So let's start by grouping together by each author. 45 00:02:55,190 --> 00:02:58,790 So that's going to be select and then let's just do. 46 00:02:59,540 --> 00:03:00,260 Author. 47 00:03:00,260 --> 00:03:01,340 F Name. 48 00:03:01,970 --> 00:03:02,870 Author. 49 00:03:02,900 --> 00:03:05,510 LL name from books. 50 00:03:05,510 --> 00:03:07,820 But we're going to group by. 51 00:03:09,470 --> 00:03:10,580 And we'll do the same thing. 52 00:03:10,610 --> 00:03:19,100 Author F name and author ll name because I want to make sure we're grouping by both so that Dan Harris 53 00:03:19,100 --> 00:03:24,770 and Frieda Harris and whatever other data we have where people have the same first name and or the same 54 00:03:24,770 --> 00:03:27,680 last name, we're at least distinguishing between them. 55 00:03:27,710 --> 00:03:33,620 If we had authors with the same first and last name, then we'd be kind of out of luck with this approach. 56 00:03:33,620 --> 00:03:39,110 But if we really had to worry about that, we likely would already have some sort of unique identifier 57 00:03:39,110 --> 00:03:43,010 baked in or some way of telling the author names apart. 58 00:03:43,010 --> 00:03:47,420 We would probably just use the ID of each author, but we're not doing that right now. 59 00:03:47,450 --> 00:03:50,040 Okay, so this gets me. 60 00:03:50,060 --> 00:03:51,290 Author first name and last name. 61 00:03:51,290 --> 00:03:54,450 They're grouped together, but that's not exactly what we want. 62 00:03:54,470 --> 00:03:57,890 We need to find the average released year. 63 00:03:57,890 --> 00:03:59,930 So I'm going to recall this line. 64 00:04:00,080 --> 00:04:06,440 And instead of doing author F name, author, L name on their own, I'm also going to do the average 65 00:04:06,440 --> 00:04:11,060 released year for all of the books in each one of these groups. 66 00:04:11,480 --> 00:04:12,920 And there we are. 67 00:04:13,100 --> 00:04:17,990 The average release year for Jhumpa Lahiri is 19 99.5. 68 00:04:18,740 --> 00:04:22,100 For a lot of the authors, you only have one book like Patti Smith. 69 00:04:22,100 --> 00:04:26,420 It's just 2010, although you can see it's been turned into a decimal number. 70 00:04:26,420 --> 00:04:29,930 We'll talk more about data types in just a little bit very soon. 71 00:04:30,590 --> 00:04:37,430 And then for somebody like who has a bunch of them, Neil Gaiman had three books, I believe it's 2,006.666, 72 00:04:37,430 --> 00:04:38,030 seven. 73 00:04:38,030 --> 00:04:41,090 So that's what I wanted you to do for this challenge. 74 00:04:41,090 --> 00:04:44,420 Although there are other ways you could have done it, you could have grouped differently. 75 00:04:44,630 --> 00:04:48,110 Last name first, then first or last name and then first name. 76 00:04:48,740 --> 00:04:51,230 You don't necessarily have to display first name and last name. 77 00:04:51,230 --> 00:04:54,680 Here you could concoct first and last name and then grouped by that. 78 00:04:54,800 --> 00:04:56,120 But that's good enough. 79 00:04:56,450 --> 00:05:01,670 Next up, find the full name of the author who wrote the longest book. 80 00:05:02,270 --> 00:05:07,400 So this is possibly going to include a subquery. 81 00:05:07,820 --> 00:05:09,080 I'll show two solutions. 82 00:05:09,080 --> 00:05:11,210 Let's start with the subquery solution. 83 00:05:11,480 --> 00:05:14,300 So I want to find the longest book. 84 00:05:14,300 --> 00:05:22,490 We can just start with that Select max pages from books that's going to give me the number of pages. 85 00:05:22,490 --> 00:05:24,080 So we've already seen this before. 86 00:05:24,080 --> 00:05:31,880 We happen to know 634 is correct and then we can take that and I'll do this over in a file here. 87 00:05:32,150 --> 00:05:39,020 We're going to run that as a sub query like this and actually select what are we trying to find the 88 00:05:39,020 --> 00:05:42,800 title or the full name of the author who wrote the longest book. 89 00:05:42,800 --> 00:05:51,770 So we're going to do let's just start with author F name for first name, author L name for last name 90 00:05:52,160 --> 00:05:55,790 from books, and then we'll say where. 91 00:05:56,420 --> 00:06:02,090 And then we want the pages to be equal to this subquery. 92 00:06:02,720 --> 00:06:04,040 So this will run first. 93 00:06:04,040 --> 00:06:06,200 Here it turns into whatever the number is. 94 00:06:06,200 --> 00:06:11,780 If the highest pages six for three in our case, and then we run this query, what, we run it all at 95 00:06:11,780 --> 00:06:18,080 once, but then SQL will run this query using six for three there, and we're almost there. 96 00:06:18,960 --> 00:06:23,910 It looks like we have two books, though, remember that have the number of pages. 97 00:06:23,910 --> 00:06:25,780 Let me just display the pages here. 98 00:06:25,800 --> 00:06:28,620 You won't unless you ran that line with me. 99 00:06:29,910 --> 00:06:32,430 I have two books, though, with 634. 100 00:06:32,580 --> 00:06:34,140 Okay, that's fine. 101 00:06:34,140 --> 00:06:40,650 This answer still is valid because it's finding the book or books that have the most pages. 102 00:06:40,710 --> 00:06:44,880 I just have this dumb one that says Noel Noel, four Author, First name and last name. 103 00:06:44,880 --> 00:06:48,240 Don't worry about it if you didn't run that line when I ran it. 104 00:06:48,240 --> 00:06:50,790 So we should be seeing Michael Chabon. 105 00:06:50,790 --> 00:06:52,590 Siobhan, Siobhan I don't know. 106 00:06:52,680 --> 00:06:57,900 634 But what I'm supposed to do is actually get the full name as a single value. 107 00:06:57,990 --> 00:07:03,060 So instead of first name and then last name, I'll just can get them together. 108 00:07:03,060 --> 00:07:08,040 So can Cat, first name and then a space and then last name. 109 00:07:08,040 --> 00:07:12,720 We've seen this a bunch of times, and then I'll give it an alias as maybe just author. 110 00:07:13,460 --> 00:07:15,590 And then pages secondly. 111 00:07:16,680 --> 00:07:19,260 Let's select this and run that query again. 112 00:07:19,260 --> 00:07:23,430 And we see author Michael Chabon, Pages 634. 113 00:07:23,460 --> 00:07:24,330 And that's good enough. 114 00:07:24,330 --> 00:07:25,530 That's what we're supposed to do. 115 00:07:25,650 --> 00:07:32,640 But there was a second approach, which is we could order by the number of pages, so let's try that. 116 00:07:32,640 --> 00:07:41,760 We'll select author, F name, author, L name and pages from books. 117 00:07:41,760 --> 00:07:47,820 But we're going to order by the number of pages, and I want to go descending from highest to lowest. 118 00:07:48,480 --> 00:07:49,770 So we could start with that. 119 00:07:51,920 --> 00:07:54,890 And up top we see Michael Chabon and then no, no. 120 00:07:55,160 --> 00:07:56,180 Six, three, four. 121 00:07:56,180 --> 00:08:00,230 And then I can limit that to one result, if that's what I want. 122 00:08:00,230 --> 00:08:05,350 If I only want the first, even if there's multiple that are tied, I just want the first result. 123 00:08:05,360 --> 00:08:05,980 There we are. 124 00:08:05,990 --> 00:08:10,700 Michael Chabon But again, I want the concatenated author name. 125 00:08:10,700 --> 00:08:17,630 So author F name space author ll name in parent we can cat that whole thing. 126 00:08:17,630 --> 00:08:19,370 We'll call it author. 127 00:08:21,570 --> 00:08:22,530 Try that again. 128 00:08:22,920 --> 00:08:26,100 And now we see Michael space Shab in one word. 129 00:08:26,100 --> 00:08:31,410 Well, not one word, one column, one value we concatenated and pages. 130 00:08:31,410 --> 00:08:40,919 634 OC And our final piece is to make this happen where we have all the books collected by year. 131 00:08:40,950 --> 00:08:47,460 We count how many were released in each year, and then we have the average number of pages for each 132 00:08:47,460 --> 00:08:49,190 or all of the books in a given year. 133 00:08:49,200 --> 00:08:55,040 And I didn't call this out originally when I introduced this, but there's an order here. 134 00:08:55,050 --> 00:08:57,240 They are all sorted by the year. 135 00:08:58,340 --> 00:09:00,820 And also we have these aliases up top. 136 00:09:00,830 --> 00:09:12,500 So let's start by selecting the released year from books and we'll do a group VI release here. 137 00:09:12,500 --> 00:09:13,670 I know we want that. 138 00:09:14,810 --> 00:09:21,290 And that's not going to get us very far, but that gets us the release years in their own groups. 139 00:09:21,290 --> 00:09:23,130 But we're not doing anything with those groups. 140 00:09:23,150 --> 00:09:30,110 What I want to do is start by getting the count of the books in each group, and I'll do this on separate 141 00:09:30,110 --> 00:09:34,010 lines here, like this release here. 142 00:09:34,010 --> 00:09:38,930 And then I want to select the count of the number of rows in each group. 143 00:09:40,010 --> 00:09:45,470 And now we see two for 2003, one for 2016. 144 00:09:45,620 --> 00:09:48,440 And then I want to give it an alias to match what I have here. 145 00:09:48,440 --> 00:09:56,180 I called it number books, so I'll call it number books and do it just like that. 146 00:09:58,760 --> 00:09:59,750 There we are. 147 00:09:59,930 --> 00:10:00,840 Looks good. 148 00:10:00,860 --> 00:10:03,900 2003 we see two 2016, we see one. 149 00:10:03,920 --> 00:10:04,850 Let's verify. 150 00:10:04,880 --> 00:10:05,720 2003. 151 00:10:05,720 --> 00:10:06,610 We see two. 152 00:10:06,620 --> 00:10:07,550 2016. 153 00:10:07,550 --> 00:10:08,560 We see one. 154 00:10:08,570 --> 00:10:10,790 The order is different, but we'll get there. 155 00:10:10,820 --> 00:10:15,860 The next thing we'll select is the average number of pages in each one of those groups. 156 00:10:15,860 --> 00:10:19,220 So we'll add a comma, average pages. 157 00:10:19,400 --> 00:10:23,990 And remember, this is averaging the pages for for each of the groups that have been created based off 158 00:10:23,990 --> 00:10:25,220 of the released year. 159 00:10:25,730 --> 00:10:27,440 So we can start with that. 160 00:10:28,160 --> 00:10:29,210 Run it again. 161 00:10:29,300 --> 00:10:34,190 Looking good, but I want to change this name up top to be average pages. 162 00:10:34,190 --> 00:10:45,020 So as average pages and we're almost done, it's looking good, except we're also supposed to call this 163 00:10:45,020 --> 00:10:47,420 year instead of released year. 164 00:10:47,420 --> 00:10:50,510 So we're also going to Alias that as year. 165 00:10:50,510 --> 00:10:54,020 And I don't need quotes here because I don't have a space on this example. 166 00:10:54,020 --> 00:11:01,160 I have a space and it gets unhappy with me because it thinks that this is some sort of command and then 167 00:11:01,160 --> 00:11:03,920 this is referencing the books table maybe. 168 00:11:03,980 --> 00:11:11,030 So if I put this there, it realizes, Oh, this is just what you're aliasing with as whereas here it's 169 00:11:11,030 --> 00:11:12,110 just a single word. 170 00:11:12,260 --> 00:11:16,490 Anyway, I run that almost there. 171 00:11:16,520 --> 00:11:20,390 The last thing we have to do is sort all the rows based off of that year. 172 00:11:21,780 --> 00:11:25,350 Ordered by released year. 173 00:11:26,180 --> 00:11:27,720 And that should do it. 174 00:11:27,740 --> 00:11:28,640 Let's see. 175 00:11:29,640 --> 00:11:34,260 From 1945 up to 2017, 1945 up to 2017. 176 00:11:34,260 --> 00:11:38,130 I'm ignoring the NOLs that I have because you likely don't have those. 177 00:11:38,130 --> 00:11:41,160 But if you do, they'll be up top and that's it. 178 00:11:41,160 --> 00:11:43,050 45 one 181. 179 00:11:43,050 --> 00:11:50,040 Let's verify 45 one 181 2017 one 367 2017 one 367. 180 00:11:50,040 --> 00:11:55,030 That's a terrible way of checking your work, but we're just comparing it to the screen shot. 181 00:11:55,080 --> 00:11:55,920 It looks good. 182 00:11:55,920 --> 00:11:59,940 That's sort of the culmination of a lot of what we learned in this section. 183 00:11:59,940 --> 00:12:06,210 We've got count, we've got average, but we also have grouped by in there plus order by from the previous 184 00:12:06,210 --> 00:12:06,840 section. 185 00:12:06,840 --> 00:12:08,160 We've got aliases. 186 00:12:08,170 --> 00:12:13,170 There's a lot of stuff going on, even though it's not that complex of a query compared to what we'll 187 00:12:13,170 --> 00:12:15,810 see soon, especially once we get to joins. 188 00:12:16,020 --> 00:12:19,320 It does combine a lot of the elements we've seen in the course so far. 189 00:12:19,680 --> 00:12:20,100 All right. 190 00:12:20,100 --> 00:12:21,630 This might be a good time to take a break. 191 00:12:21,630 --> 00:12:27,750 If you are inclined to take a break, I recommend you do, because next, we've got a lot of other skill, 192 00:12:27,750 --> 00:12:31,410 nitty gritty stuff to talk about data types and dates and times. 193 00:12:31,410 --> 00:12:34,740 And it just might be a good time to take a break. 194 00:12:34,740 --> 00:12:35,200 Okay.