1 00:00:00,150 --> 00:00:00,570 All right. 2 00:00:00,570 --> 00:00:05,820 So you probably guessed it either, because in the last video I told you or because of this gigantic 3 00:00:05,820 --> 00:00:09,420 text here, it's your turn to try putting this into practice. 4 00:00:09,600 --> 00:00:11,190 So we get a couple of exercises. 5 00:00:11,190 --> 00:00:18,360 They range from trivial, simple logic questions all the way up to more complex case statements. 6 00:00:18,360 --> 00:00:23,460 So I encourage you to go through all of them, especially the case statements towards the end, but 7 00:00:23,460 --> 00:00:25,710 hopefully you don't lose interest before then. 8 00:00:25,710 --> 00:00:30,360 Oh, and one thing, this is the last time you have to work with those darn books. 9 00:00:30,390 --> 00:00:34,620 Last time that books data, unless you want to keep working with it, you can definitely play around 10 00:00:34,620 --> 00:00:35,070 with it. 11 00:00:35,070 --> 00:00:36,690 But I'm getting pretty sick of it. 12 00:00:36,690 --> 00:00:37,800 I imagine you are. 13 00:00:37,800 --> 00:00:43,770 And the good news is we're now moving on to join tables, which allows us to get much more complex and 14 00:00:43,770 --> 00:00:49,020 real, real life data, things that are more accurate and realistic and that's really exciting. 15 00:00:49,650 --> 00:00:51,600 So get excited if you're not. 16 00:00:52,410 --> 00:00:52,980 All right. 17 00:00:53,100 --> 00:00:54,360 So here's the first couple. 18 00:00:54,660 --> 00:00:56,100 Evaluate the following. 19 00:00:56,100 --> 00:00:57,000 Now try doing them. 20 00:00:57,000 --> 00:01:00,000 Don't just copy them and paste them in and hit enter. 21 00:01:00,000 --> 00:01:03,930 Actually walk through and evaluate what you think the result is. 22 00:01:04,319 --> 00:01:07,860 So the first one, select ten, not equal to ten. 23 00:01:07,890 --> 00:01:09,000 Is that true or false? 24 00:01:09,000 --> 00:01:17,010 Zero or one next select 15 greater than 14 and 99 minus five less than or equal to 94. 25 00:01:17,730 --> 00:01:18,570 True or false? 26 00:01:18,840 --> 00:01:19,560 Zero one. 27 00:01:20,280 --> 00:01:26,730 Finally select one in five, comma, three or nine between eight and ten. 28 00:01:27,530 --> 00:01:28,430 True or false? 29 00:01:28,580 --> 00:01:33,300 So do those and then move on to these code based challenges. 30 00:01:33,360 --> 00:01:40,460 So the first one here is select all books written before 1980, and that does not include 1980. 31 00:01:40,460 --> 00:01:43,340 So everything up to 1980, but not 1980. 32 00:01:44,440 --> 00:01:45,520 Once you've done that. 33 00:01:46,360 --> 00:01:46,870 Move on. 34 00:01:46,880 --> 00:01:48,880 I've got some beautiful red text. 35 00:01:49,300 --> 00:01:51,130 Really expanding the colour palette. 36 00:01:51,430 --> 00:01:55,750 Select all books written by Eggers or Shaban. 37 00:01:56,200 --> 00:01:58,000 I don't know how you say his name, honestly. 38 00:01:58,600 --> 00:01:59,470 That guy. 39 00:01:59,800 --> 00:02:00,740 Great author. 40 00:02:00,760 --> 00:02:04,480 Well, I just googled it and apparently it's pronounced Shaban. 41 00:02:05,020 --> 00:02:09,220 So select all books written by Eggers or Shaban. 42 00:02:10,570 --> 00:02:11,500 Make sense now? 43 00:02:11,680 --> 00:02:12,550 I think about it. 44 00:02:13,390 --> 00:02:19,780 Now onto the beautiful yellow select all books written by Lahiri, published after 2000. 45 00:02:20,730 --> 00:02:23,940 So books written by Lahiri released after 2000. 46 00:02:26,170 --> 00:02:32,110 Then select all books where the page count is between 102 hundred pages. 47 00:02:33,590 --> 00:02:39,320 So our middle length or short books, those are pretty short between 102 hundred pages. 48 00:02:40,130 --> 00:02:47,090 Next, select all books where the author last name starts with a C or an S. 49 00:02:47,540 --> 00:02:50,270 So this one does a couple of ways of approaching it. 50 00:02:51,260 --> 00:02:53,610 I'll let you discover what works best. 51 00:02:53,630 --> 00:02:58,460 You may want to use well, you'll need to use things that we've learned in the past, especially when 52 00:02:58,460 --> 00:02:59,420 we're working with strings. 53 00:02:59,420 --> 00:03:03,620 So you may want to go back or just go to the string documentation to figure out how to do this. 54 00:03:03,740 --> 00:03:07,250 Select all books where the author last name starts with a C or an S. 55 00:03:07,430 --> 00:03:12,710 Those are my initials, so maybe I'm crazy and I just want books to start with who are written by authors 56 00:03:12,710 --> 00:03:14,360 who start with my initials? 57 00:03:14,360 --> 00:03:14,930 I don't know. 58 00:03:16,860 --> 00:03:21,420 Now moving on to the main event, our wonderful case statements. 59 00:03:22,050 --> 00:03:25,430 So here is another example. 60 00:03:25,440 --> 00:03:29,100 This is done using a case statement and let me explain it. 61 00:03:29,100 --> 00:03:33,870 So you've got title and the author last name, but then we've got a type over here. 62 00:03:33,870 --> 00:03:35,340 What type of book is it? 63 00:03:35,340 --> 00:03:37,590 Which I'm really bad at this. 64 00:03:37,590 --> 00:03:38,940 This is just be called genre. 65 00:03:38,940 --> 00:03:39,990 It doesn't matter though. 66 00:03:40,770 --> 00:03:46,830 Basically the way that it works, if the title contains the word stories, we're calling it short stories. 67 00:03:47,520 --> 00:03:52,710 So as you can see where I'm calling from, selected stories is categorized as short stories. 68 00:03:52,730 --> 00:03:57,990 What we talk about when we talk about love, great collection is categorized as short stories because 69 00:03:57,990 --> 00:03:59,430 it contains stories. 70 00:04:00,330 --> 00:04:02,220 Same thing with Oblivion stories. 71 00:04:02,670 --> 00:04:04,140 But then there's another part. 72 00:04:04,930 --> 00:04:12,220 If the title is just kids or its heartbreaking work of Staggering Genius, which I didn't write all 73 00:04:12,220 --> 00:04:13,570 the way up here because it's giant. 74 00:04:13,570 --> 00:04:18,130 But basically, if it's this book or if it's this book, both are memoirs. 75 00:04:18,130 --> 00:04:22,029 So we have these two exceptions that you'll need to write in. 76 00:04:23,400 --> 00:04:27,300 Finally everything else, which hopefully sounds familiar. 77 00:04:27,330 --> 00:04:30,390 Otherwise, everything else at the end is called a novel. 78 00:04:30,900 --> 00:04:35,490 And I know some of these aren't actually novels like Consider the Lobster. 79 00:04:35,550 --> 00:04:37,710 It's more of an essay or collection of essays. 80 00:04:37,710 --> 00:04:41,790 10% Happier is like a self-help book, but I didn't want to get too complex. 81 00:04:41,790 --> 00:04:45,180 So just to reiterate, the title contains stories. 82 00:04:45,300 --> 00:04:46,920 Type is short stories. 83 00:04:47,250 --> 00:04:53,740 If the title is exactly just kids or exactly a heartbreaking work of Staggering Genius, then it's memoir. 84 00:04:53,760 --> 00:04:55,640 Both great memoirs, by the way. 85 00:04:55,650 --> 00:04:58,320 And then finally, everything else is a novel. 86 00:04:58,480 --> 00:05:00,000 Okay, one more. 87 00:05:00,030 --> 00:05:01,590 This is a bit of a bonus. 88 00:05:01,800 --> 00:05:03,300 So it's more complex. 89 00:05:03,300 --> 00:05:05,040 You have to put a few things together. 90 00:05:05,790 --> 00:05:09,900 So we have title of the book and the author last name once again. 91 00:05:10,170 --> 00:05:15,930 But then there's this count section and count, which we've already seen under do is a little different. 92 00:05:15,930 --> 00:05:20,340 It's not just saying two or one or three what it's actually doing. 93 00:05:20,340 --> 00:05:21,780 We can ignore the title part. 94 00:05:21,780 --> 00:05:23,160 Actually, you don't need to do that. 95 00:05:23,340 --> 00:05:25,770 Just focus on the last name of the author and the count. 96 00:05:25,890 --> 00:05:30,660 It's saying How many books each author has, how many they've written. 97 00:05:30,660 --> 00:05:32,520 So Carver has two books. 98 00:05:32,550 --> 00:05:33,990 Gaiman has three books. 99 00:05:34,770 --> 00:05:37,080 Harris, Dan Harris or Frieda Harris. 100 00:05:37,080 --> 00:05:38,310 They each have one book. 101 00:05:38,670 --> 00:05:40,590 Jhumpa Lahiri has two books and so on. 102 00:05:40,860 --> 00:05:48,840 But not only is it printing the number, but there's also the properly plural sized books or book following 103 00:05:48,840 --> 00:05:49,050 it. 104 00:05:49,050 --> 00:05:54,930 So one book, singular, two books, three books, one book and so on. 105 00:05:55,260 --> 00:05:57,270 So you'll need to use a couple of things. 106 00:05:57,270 --> 00:05:58,460 I can give you a hints. 107 00:05:58,470 --> 00:06:00,810 If you don't want the hints, then stop right now. 108 00:06:00,810 --> 00:06:02,940 But the hints are you need to use group by. 109 00:06:03,630 --> 00:06:08,070 You need to also use a case statement to generate this right here. 110 00:06:08,760 --> 00:06:13,350 But it is a bonus because you do need to combine a couple of things to a decent amount of work. 111 00:06:13,860 --> 00:06:16,470 Okay, so that's it. 112 00:06:16,590 --> 00:06:21,390 Once you finish that, burn all the books, we're done with them, whatever that means to you. 113 00:06:21,540 --> 00:06:26,460 I don't actually condone burning books, but if you want to get rid of the database, go ahead. 114 00:06:26,460 --> 00:06:31,200 Although you may want to refer back to it, this is more just me saying that we're done, done with 115 00:06:31,200 --> 00:06:31,890 the books. 116 00:06:31,890 --> 00:06:32,880 This is a turning point. 117 00:06:32,880 --> 00:06:39,390 We're moving on to some bigger and better data and to pretty important section, which is joint. 118 00:06:39,390 --> 00:06:43,890 So I definitely recommend you take a break, but I will reiterate that in the Solution video, which 119 00:06:43,890 --> 00:06:44,850 is coming up next.