1 00:00:00,240 --> 00:00:01,330 Okay, here we go. 2 00:00:01,350 --> 00:00:03,210 Solution time for this exercise. 3 00:00:03,210 --> 00:00:05,130 We've got quite a few pieces starting. 4 00:00:05,130 --> 00:00:05,820 Simple. 5 00:00:05,850 --> 00:00:07,590 Just evaluate these. 6 00:00:07,590 --> 00:00:08,760 Let's start this first one. 7 00:00:08,760 --> 00:00:10,110 Select ten. 8 00:00:10,110 --> 00:00:11,790 Not equal to ten. 9 00:00:12,330 --> 00:00:15,660 So ten is not not equal to ten. 10 00:00:15,660 --> 00:00:18,900 Meaning this would be false because ten is equal to ten. 11 00:00:18,900 --> 00:00:21,660 Meaning that we expect to see zero when we run it. 12 00:00:21,810 --> 00:00:26,250 Zero is the MySQL version of false in this case. 13 00:00:26,910 --> 00:00:33,330 All right, next step, select 15 greater than 14 and 99 minus five, less than or equal to 94. 14 00:00:33,330 --> 00:00:33,840 Wow. 15 00:00:34,470 --> 00:00:39,330 So first up on the left hand side of and we have 15 greater than 14. 16 00:00:39,330 --> 00:00:40,230 That is true. 17 00:00:41,010 --> 00:00:44,640 And 99 minus five that becomes four. 18 00:00:44,850 --> 00:00:49,650 Sorry, that becomes 94 is 94 less than or equal to 94. 19 00:00:49,680 --> 00:00:52,110 That is also true because they're equal. 20 00:00:52,110 --> 00:00:54,210 So we have true and true. 21 00:00:54,330 --> 00:00:58,320 The whole thing evaluates to true, which in my SQL is one. 22 00:00:58,960 --> 00:01:05,140 And then finally select one in five and three or nine between eight and ten. 23 00:01:05,140 --> 00:01:07,830 When I read it out loud, it just sounds like utter gibberish. 24 00:01:07,840 --> 00:01:10,900 But we've got our ore and then the left and the right side. 25 00:01:11,560 --> 00:01:15,910 So it's one in this set of five comma three. 26 00:01:15,940 --> 00:01:18,910 No, it is not five and three are the only options. 27 00:01:18,910 --> 00:01:20,050 One is not in there. 28 00:01:20,260 --> 00:01:21,730 So the left side is false. 29 00:01:22,150 --> 00:01:23,440 What about the right side? 30 00:01:23,470 --> 00:01:26,320 Is nine between eight and ten? 31 00:01:26,650 --> 00:01:27,730 Yes, it is. 32 00:01:27,730 --> 00:01:34,030 So the right side is true, which means the whole thing is true because we're using logical or so false 33 00:01:34,030 --> 00:01:35,680 or true becomes true. 34 00:01:35,950 --> 00:01:39,910 And that will evaluate to true, which is one in my SQL. 35 00:01:40,630 --> 00:01:42,400 Okay, moving on. 36 00:01:42,400 --> 00:01:43,780 Now we need to write some queries. 37 00:01:43,780 --> 00:01:48,670 Select all the books written before 1980, but not including 1980. 38 00:01:49,240 --> 00:01:50,950 So let's do this in a file. 39 00:01:51,490 --> 00:02:01,000 So like, start from books where and then we'll need to work with released date was less than 1980 and 40 00:02:01,000 --> 00:02:02,110 that should do it. 41 00:02:02,410 --> 00:02:06,100 We don't want less than or equal to what did I do. 42 00:02:06,130 --> 00:02:08,620 So let's start from books where Oh, I'm an idiot. 43 00:02:08,620 --> 00:02:10,650 It's released a year now. 44 00:02:11,170 --> 00:02:12,130 Let's try that again. 45 00:02:12,910 --> 00:02:13,360 Okay. 46 00:02:13,360 --> 00:02:13,990 There we are. 47 00:02:14,020 --> 00:02:16,030 We got released here, 1945. 48 00:02:16,030 --> 00:02:19,600 Is that really the only book in there released before 1980? 49 00:02:19,630 --> 00:02:20,560 I guess so. 50 00:02:20,710 --> 00:02:22,120 We trust our query. 51 00:02:22,990 --> 00:02:27,730 Then our next piece is Select all books written by Eggers or Shaban. 52 00:02:29,170 --> 00:02:38,440 So select star from books where and I'll do this on the next line where and then author last name is 53 00:02:38,440 --> 00:02:40,030 exactly equal to Eggers. 54 00:02:40,420 --> 00:02:50,170 But then we also want or last name is equal to seven so or author last name is equal to seven. 55 00:02:51,440 --> 00:02:52,880 Let's try running that one. 56 00:02:55,440 --> 00:02:56,690 And we see Eggers. 57 00:02:56,700 --> 00:02:57,840 Eggers shopping. 58 00:02:57,870 --> 00:02:59,520 Eggers looks good to me. 59 00:03:01,020 --> 00:03:09,360 Now we need to select all the books written by Lahiri, published after the year 2000 OC so select star 60 00:03:09,360 --> 00:03:12,820 from books and we want to have the author. 61 00:03:12,840 --> 00:03:18,060 So where author last name is exactly equal to Lahiri? 62 00:03:19,630 --> 00:03:26,770 And then at the same time, a.k.a. and the release here is greater than. 63 00:03:28,180 --> 00:03:29,140 2000. 64 00:03:30,700 --> 00:03:31,510 Let's try it. 65 00:03:32,570 --> 00:03:33,330 Where's my aunt? 66 00:03:33,350 --> 00:03:33,950 I didn't type. 67 00:03:33,950 --> 00:03:35,720 And somehow there we go. 68 00:03:36,020 --> 00:03:38,570 We have to have that and join those two together. 69 00:03:38,990 --> 00:03:42,490 And then we see The Namesake is written by Lahiri, released here. 70 00:03:42,490 --> 00:03:43,700 It was 2003. 71 00:03:43,790 --> 00:03:47,670 If we got rid of her last year, I think we have two books in here. 72 00:03:47,690 --> 00:03:50,810 Interpreter of Maladies that was released in 96. 73 00:03:50,810 --> 00:03:54,920 So that one was not included when we wrote the more complicated query. 74 00:03:55,550 --> 00:03:59,450 Now we need to select all books with page counts between 102 hundred. 75 00:03:59,630 --> 00:04:01,280 So there's more than one way of doing this. 76 00:04:01,280 --> 00:04:08,660 But let's do select star from books where pages and the longer, more annoying way is to say where pages 77 00:04:08,660 --> 00:04:15,490 is greater than or equal to 100 and pages is less than or equal to 200. 78 00:04:15,500 --> 00:04:18,019 Is that the range we were supposed to do? 79 00:04:18,320 --> 00:04:22,670 102 hundred and I need a from here select star from books. 80 00:04:24,670 --> 00:04:29,610 And if you look at the pages here, well, it's ugly how it's wrapped around like this. 81 00:04:29,620 --> 00:04:32,890 But we have 198, 176, 181. 82 00:04:33,310 --> 00:04:41,440 But the cleaner way is select star from books where pages is between 102 hundred. 83 00:04:42,710 --> 00:04:47,540 Not only is it shorter, but it's just more semantically meaningful. 84 00:04:47,540 --> 00:04:49,960 You look at this and the word between is right there. 85 00:04:49,970 --> 00:04:53,930 It tells us what we're trying to do, even if the end results are the same. 86 00:04:53,930 --> 00:04:58,700 With this other option, you have to look at the code a bit longer to understand what's happening compared 87 00:04:58,700 --> 00:04:59,390 to this. 88 00:04:59,390 --> 00:05:00,620 But either work. 89 00:05:01,790 --> 00:05:06,170 Next up, select all the books where the author last name starts with a C or an S. 90 00:05:06,170 --> 00:05:10,760 So this is another one where there's at least a couple of ways to do this that I can think of. 91 00:05:11,210 --> 00:05:20,000 The the one way that you may have come up with first would be to select star from books where author 92 00:05:20,000 --> 00:05:20,750 last name. 93 00:05:24,240 --> 00:05:32,970 And then we could do something like where author last name like and then our first pattern will be a 94 00:05:33,000 --> 00:05:36,360 C at the beginning and then wild card afterwards. 95 00:05:36,360 --> 00:05:44,280 So it has to start with a C and then anything after or author last name like. 96 00:05:44,280 --> 00:05:51,150 And then I think it was an s let me verify last name starts with a C or an S OC. 97 00:05:51,210 --> 00:05:58,050 So this is one option we use or write and we check to see two different patterns using like C at the 98 00:05:58,050 --> 00:05:59,310 beginning, anything afterwards. 99 00:05:59,310 --> 00:06:00,990 S at the beginning, anything after. 100 00:06:01,020 --> 00:06:02,790 It's very different than this. 101 00:06:03,300 --> 00:06:07,740 I hope you can see this font size where I have the sign at the beginning. 102 00:06:07,740 --> 00:06:10,380 That means C doesn't have to come at the start. 103 00:06:10,380 --> 00:06:11,820 This means it does. 104 00:06:13,600 --> 00:06:16,210 And now we are getting four last name Shaaban. 105 00:06:16,210 --> 00:06:16,660 Smith. 106 00:06:16,660 --> 00:06:17,050 Carver. 107 00:06:17,050 --> 00:06:17,920 Carver, Steinbeck. 108 00:06:17,920 --> 00:06:18,660 Saunders. 109 00:06:18,670 --> 00:06:20,440 So that's one option. 110 00:06:20,620 --> 00:06:21,850 Now there's another option. 111 00:06:21,850 --> 00:06:28,270 It's a little trickier, which is to just take the first letter of the name of every last name. 112 00:06:28,390 --> 00:06:30,550 So we saw how to do this a long time ago. 113 00:06:31,270 --> 00:06:32,140 We can do this. 114 00:06:32,140 --> 00:06:36,970 Let's select author, last name, comma, substring. 115 00:06:36,970 --> 00:06:42,910 If you remember this or sub str is the shorter version, and then we provide the column that we're looking 116 00:06:42,910 --> 00:06:43,540 for. 117 00:06:43,570 --> 00:06:49,390 So I'm looking at author last name and then the starting index is starting position and how many characters 118 00:06:49,390 --> 00:06:50,380 I want to take. 119 00:06:51,010 --> 00:06:58,180 So I want the first starting at character one go for one character, give me that substring from books 120 00:06:58,180 --> 00:06:59,530 and let's see what we get. 121 00:07:02,710 --> 00:07:03,100 Okay. 122 00:07:03,460 --> 00:07:07,350 You can see we get l g for Gaiman, L for Lahiri. 123 00:07:07,360 --> 00:07:08,230 E for Edgars. 124 00:07:08,260 --> 00:07:09,960 C for for Shaban. 125 00:07:10,000 --> 00:07:15,970 So we're getting that first letter, and now we could use that instead of selecting it, we'll just 126 00:07:15,970 --> 00:07:16,630 select the author. 127 00:07:16,630 --> 00:07:25,660 Last name, maybe the title author, the last name from books where that first character is in. 128 00:07:26,640 --> 00:07:31,920 The set of C and S, I think is the two characters we're supposed to do. 129 00:07:33,110 --> 00:07:36,470 So very different approach and we get the same result. 130 00:07:36,500 --> 00:07:37,490 Siobhan Smith. 131 00:07:37,490 --> 00:07:37,970 Carver. 132 00:07:37,970 --> 00:07:38,360 Carver. 133 00:07:38,360 --> 00:07:38,900 Steinbeck. 134 00:07:38,900 --> 00:07:40,610 Saunders Let me just prove it. 135 00:07:40,760 --> 00:07:42,170 Both of those will work. 136 00:07:43,240 --> 00:07:44,410 I'm selecting more. 137 00:07:44,410 --> 00:07:44,520 Right? 138 00:07:44,530 --> 00:07:52,180 I selected star here, but otherwise, if I just slim this down, we should get the same result just 139 00:07:52,180 --> 00:07:54,150 so you can compare apples to apples. 140 00:07:54,160 --> 00:07:59,080 So here is the longer version using like. 141 00:07:59,080 --> 00:08:04,990 And then here is the version using words you go in and substring. 142 00:08:04,990 --> 00:08:05,350 So. 143 00:08:05,350 --> 00:08:06,070 Shaaban Smith. 144 00:08:06,070 --> 00:08:06,400 Carver. 145 00:08:06,400 --> 00:08:06,700 Carver. 146 00:08:06,700 --> 00:08:07,120 Steinbeck. 147 00:08:07,120 --> 00:08:07,520 Saunders. 148 00:08:07,540 --> 00:08:08,590 Same thing here. 149 00:08:09,850 --> 00:08:13,090 I hope that's clear how this one works. 150 00:08:13,090 --> 00:08:15,910 We use that substring function to get the first character. 151 00:08:15,910 --> 00:08:24,010 We check where that first character from author LastName is in this set, whereas here we're just checking 152 00:08:24,040 --> 00:08:25,180 two different patterns. 153 00:08:26,010 --> 00:08:30,640 Okay, next up, this one's a little bit of a pain. 154 00:08:30,940 --> 00:08:36,070 We're going to select title and author last name, and then we have a case situation going on for going 155 00:08:36,070 --> 00:08:37,299 on over on the right side. 156 00:08:37,299 --> 00:08:42,010 So let's select title author, Last name. 157 00:08:43,710 --> 00:08:47,100 And then we're going to have a case. 158 00:08:47,100 --> 00:08:51,780 I'd like to put the end in there and I'd like to do the from books before I forget about that. 159 00:08:52,590 --> 00:08:59,820 And our case is going to start by saying if title contains the word stories anywhere, then we will 160 00:08:59,820 --> 00:09:04,710 have short stories as the value for type so case. 161 00:09:04,710 --> 00:09:06,450 And then we have when. 162 00:09:07,490 --> 00:09:08,170 Author. 163 00:09:08,180 --> 00:09:11,630 Nope when title like. 164 00:09:12,410 --> 00:09:14,710 Percent stories percent. 165 00:09:14,720 --> 00:09:17,210 So we get those wild card characters, neither side. 166 00:09:17,360 --> 00:09:25,280 When that's the case, then we want short stories, I think is what is supposed to be great. 167 00:09:26,110 --> 00:09:28,630 Then let's add in the else just to start with that. 168 00:09:28,630 --> 00:09:31,330 So everything else will have a type of novel. 169 00:09:32,200 --> 00:09:35,800 So else novel just like that. 170 00:09:35,800 --> 00:09:43,030 And then we're going to call this as type, I think, or genre or something, and let's run it and see 171 00:09:43,030 --> 00:09:44,230 if that part's working. 172 00:09:45,190 --> 00:09:46,120 Looks good. 173 00:09:46,120 --> 00:09:47,680 So we see this has stories. 174 00:09:47,680 --> 00:09:52,720 It's classified as short stories, stories, short stories, stories, short stories. 175 00:09:52,720 --> 00:09:54,670 Everything else is novel. 176 00:09:56,200 --> 00:10:03,040 Then we can expand with a second one, one title, and then this is the annoying one we have to match 177 00:10:03,040 --> 00:10:07,720 just kids and also a heartbreaking work of staggering genius. 178 00:10:08,020 --> 00:10:15,640 So one title and we'll just say is directly equal to just kids or title. 179 00:10:17,130 --> 00:10:24,180 Is equal to a heart, and I'm just going to copy this so I don't mess up the spelling. 180 00:10:25,960 --> 00:10:26,710 Like that. 181 00:10:27,100 --> 00:10:29,500 So this is one option for how we do this. 182 00:10:30,530 --> 00:10:32,350 Just kids or title equals heartbreaking work. 183 00:10:32,380 --> 00:10:33,290 Staggering genius. 184 00:10:33,290 --> 00:10:37,160 Then we're going to say memoir is the type. 185 00:10:38,290 --> 00:10:39,490 Let's try running it. 186 00:10:42,030 --> 00:10:43,350 And it looks good. 187 00:10:43,740 --> 00:10:48,450 We've got short stories, novel for everything that is not memoir or short stories. 188 00:10:48,450 --> 00:10:52,420 And these are the two memoirs, just kids in a heartbreaking work of staggering genius. 189 00:10:52,440 --> 00:10:53,720 We called it type. 190 00:10:53,730 --> 00:10:56,760 I guess I capitalized it in this screenshot, but it doesn't matter. 191 00:10:56,760 --> 00:10:59,760 And you probably don't have this no author down here. 192 00:10:59,760 --> 00:11:00,900 My life and words. 193 00:11:00,900 --> 00:11:01,740 That's fine. 194 00:11:02,070 --> 00:11:05,250 But we could exclude anything that had null for author l name. 195 00:11:05,400 --> 00:11:08,280 Using is null or where is not null. 196 00:11:08,460 --> 00:11:11,930 But it's fine because there's a title here and that's what we're working with. 197 00:11:11,940 --> 00:11:15,630 The author last name doesn't really matter and that's it. 198 00:11:15,630 --> 00:11:21,360 Except for if we wanted to clean this up or put this on separate lines, we could just say one title 199 00:11:21,360 --> 00:11:25,110 is equal to just kids, then memoir. 200 00:11:26,380 --> 00:11:32,860 And then on the next line, say, when title was equal to a heartbreaking work of staggering genius, 201 00:11:32,860 --> 00:11:34,060 then memoir. 202 00:11:34,480 --> 00:11:40,960 Either one, it's both of those are essentially equivalent because the only way this would ever run 203 00:11:41,050 --> 00:11:42,790 is if this was false. 204 00:11:43,180 --> 00:11:48,940 So you could put it on separate when then lines or combine it using or either one. 205 00:11:48,940 --> 00:11:49,720 This works. 206 00:11:49,720 --> 00:11:51,010 Just prove one more time. 207 00:11:52,390 --> 00:11:53,020 I run it. 208 00:11:53,020 --> 00:11:57,340 And we still see memoir for Staggering Genius and memoir for Just Kids. 209 00:11:58,390 --> 00:11:59,200 Almost there. 210 00:11:59,230 --> 00:12:01,240 This is our last one, The bonus. 211 00:12:01,360 --> 00:12:07,750 So what we're doing here is generating a table that says how many books each author has written, the 212 00:12:07,750 --> 00:12:09,490 count of number of books. 213 00:12:09,490 --> 00:12:14,710 Each author is written where it's a combination of their first and last name. 214 00:12:14,710 --> 00:12:14,970 Right? 215 00:12:14,980 --> 00:12:16,810 So Dan Harris versus Frieda Harris. 216 00:12:16,810 --> 00:12:19,970 Once again, we're grouping and then counting. 217 00:12:19,990 --> 00:12:21,760 So let's start with that group by. 218 00:12:23,730 --> 00:12:30,960 So we'll select the author F name, author, last name from books. 219 00:12:31,380 --> 00:12:38,400 And we're going to do a group by and we're going to group by both the first name and the last name. 220 00:12:38,670 --> 00:12:40,920 We can't just do the last name, as we already know. 221 00:12:41,790 --> 00:12:49,320 And then I'm going to select the count of the number of rows from that group by. 222 00:12:50,490 --> 00:12:57,000 And I need a semicolon and we see two for the Hery, three for Neil Gaiman, one for Dan Harris. 223 00:12:57,510 --> 00:12:59,430 Why don't I add on a. 224 00:13:00,630 --> 00:13:01,590 Down here. 225 00:13:01,740 --> 00:13:02,820 Where? 226 00:13:04,220 --> 00:13:05,480 From books. 227 00:13:05,840 --> 00:13:07,720 Let's do where. 228 00:13:08,210 --> 00:13:11,270 Author last name is not null. 229 00:13:15,570 --> 00:13:20,400 Just so that I get rid of this bottom row, which you probably don't have to worry about. 230 00:13:20,400 --> 00:13:22,440 If you don't have any nulls, you're good to go. 231 00:13:22,560 --> 00:13:23,990 So that is half of it. 232 00:13:24,000 --> 00:13:27,750 We've got the group by portion done, but we're not just displaying the count. 233 00:13:27,990 --> 00:13:29,550 What are we actually displaying? 234 00:13:30,030 --> 00:13:37,920 Ha we are displaying the count plus some text that says two books, three books, one book. 235 00:13:38,010 --> 00:13:40,140 So we're going to need to use a case. 236 00:13:40,380 --> 00:13:44,250 So instead of displaying count right there, I'm going to do a case. 237 00:13:44,400 --> 00:13:46,620 We can indent it properly if we want. 238 00:13:47,460 --> 00:13:48,570 Case end. 239 00:13:48,570 --> 00:13:54,630 And then I'm going to do something like when the count of the number of rows in each group is equal 240 00:13:54,630 --> 00:13:58,050 to one, then that part is easy. 241 00:13:58,050 --> 00:14:00,360 We just say one book, right? 242 00:14:00,930 --> 00:14:02,220 And then I can do four. 243 00:14:02,220 --> 00:14:05,880 Now just else multiple books. 244 00:14:07,230 --> 00:14:10,470 So that's not exactly what we're supposed to do, but that gets us close. 245 00:14:10,500 --> 00:14:14,160 Oh, and I should give this an as we'll call this count. 246 00:14:15,180 --> 00:14:17,700 And let me put my semicolon in before I forget. 247 00:14:18,750 --> 00:14:24,660 If we run this query, we see count is multiple books for the theory game and Eggers one book for Chabon. 248 00:14:24,660 --> 00:14:25,290 Smith. 249 00:14:25,320 --> 00:14:26,640 Harris, Harris, Saunders. 250 00:14:26,640 --> 00:14:29,460 So you can trust that we're we're it's working. 251 00:14:29,460 --> 00:14:34,500 Except we haven't actually done the right output when there are multiple books. 252 00:14:34,500 --> 00:14:41,730 I need to take the count and then concatenate that with the word books so we know how to do that instead 253 00:14:41,730 --> 00:14:43,350 of else just multiple books. 254 00:14:43,350 --> 00:14:44,550 It's going to be else. 255 00:14:44,550 --> 00:14:51,810 We're going to contact the Count of Star, which again is the number of rows in each group when we do 256 00:14:51,810 --> 00:14:52,620 this group by. 257 00:14:54,130 --> 00:14:58,840 Then we're going to do the string space books. 258 00:14:59,260 --> 00:15:01,010 And that should do it for us. 259 00:15:01,030 --> 00:15:01,900 Let's see. 260 00:15:04,060 --> 00:15:07,750 Two books, three books, three books, one book, one book, two books. 261 00:15:07,930 --> 00:15:08,580 Great. 262 00:15:08,590 --> 00:15:09,490 That's looking good. 263 00:15:09,490 --> 00:15:10,910 And I think we're done right. 264 00:15:10,930 --> 00:15:13,030 One book, one book, one book, two books. 265 00:15:13,390 --> 00:15:14,560 That's what we're supposed to have. 266 00:15:15,370 --> 00:15:17,530 Okay, That was a lot of exercise. 267 00:15:17,560 --> 00:15:18,300 Take a break. 268 00:15:18,310 --> 00:15:19,930 If you feel like you deserve one. 269 00:15:19,930 --> 00:15:21,130 I think you do deserve one. 270 00:15:21,400 --> 00:15:22,630 I'll see you in the next section.