1 00:00:00,180 --> 00:00:00,550 Okay. 2 00:00:00,630 --> 00:00:01,380 Here we go. 3 00:00:01,560 --> 00:00:05,280 So let's first start off by just making a new file to work in. 4 00:00:05,280 --> 00:00:07,020 So we have some clean. 5 00:00:07,410 --> 00:00:13,050 We'll just call it exercises dot SQL, just so we have a nice place to put the answers. 6 00:00:13,500 --> 00:00:19,530 Okay, so our first one here, find the earliest date a user joint and we're not selecting anything 7 00:00:19,530 --> 00:00:21,270 except the earliest date. 8 00:00:21,540 --> 00:00:29,220 So all we need to do is take advantage of created at and use the min operator the min function. 9 00:00:29,220 --> 00:00:38,490 So it's just a matter of select min created it from users and we'll save that. 10 00:00:41,640 --> 00:00:43,950 Copy it, paste it. 11 00:00:44,730 --> 00:00:47,400 And the one that I get here looks like this. 12 00:00:47,790 --> 00:00:50,880 So, 2016 501. 13 00:00:52,400 --> 00:00:54,170 Notice it says earliest date. 14 00:00:54,170 --> 00:00:57,740 So that's that's the easier part to fix and then notice the format. 15 00:00:58,280 --> 00:01:03,260 So let's do as earliest date first. 16 00:01:03,260 --> 00:01:04,910 That will make it cleaner. 17 00:01:05,890 --> 00:01:07,540 Then we need to format the date. 18 00:01:08,410 --> 00:01:10,960 So I'm going to put this on a new line. 19 00:01:10,960 --> 00:01:16,130 And the way that we format remember is we use date underscore format. 20 00:01:16,150 --> 00:01:17,860 Think back to our date section. 21 00:01:18,580 --> 00:01:22,450 So we're formatting the result of the minimum created, which is this. 22 00:01:23,200 --> 00:01:27,270 And we have a second argument, which is the date string, how we want to format it. 23 00:01:27,280 --> 00:01:36,190 And the one I gave you had the month name, which I believe is Capital M and then the day with the suffix, 24 00:01:36,190 --> 00:01:41,710 which is, I believe Capital DX and then the year four letter or four numbers, capital Y. 25 00:01:41,740 --> 00:01:50,530 So you need percent capital D, percent capital M note backwards capital M percent capital D, percent 26 00:01:50,530 --> 00:01:51,490 capital Y. 27 00:01:51,970 --> 00:01:52,780 Let's see. 28 00:01:54,060 --> 00:01:56,580 Oh, I have to copy this pasted in. 29 00:01:57,090 --> 00:01:57,780 And there we go. 30 00:01:57,810 --> 00:01:59,280 May 1st, 2016. 31 00:01:59,930 --> 00:02:05,490 Okay, look, we're only a day off from the first data set that I added when I made these exercises, 32 00:02:05,490 --> 00:02:10,680 I had a different set of data, and you just watch me, rerun it, delete it all, create 500 new ones. 33 00:02:10,680 --> 00:02:15,480 So these will be slightly different than my answers to next. 34 00:02:15,600 --> 00:02:17,970 Find the email of that user. 35 00:02:17,970 --> 00:02:21,510 So the same one that we just found, this is the earliest date. 36 00:02:21,780 --> 00:02:23,790 How do we find the email? 37 00:02:24,510 --> 00:02:32,130 So let me label this challenge one and this one, we have to use a subquery because basically what we're 38 00:02:32,130 --> 00:02:36,480 trying to do is, well, first we can clean this up a bit. 39 00:02:36,510 --> 00:02:38,100 We don't need to format this one. 40 00:02:39,070 --> 00:02:46,420 But basically what we're trying to do is take this earliest date, which looks like this, and then 41 00:02:46,420 --> 00:02:56,380 we want to do a select we'll do an email from users where created it is equal to that date. 42 00:02:57,190 --> 00:02:58,600 How do we find that user? 43 00:03:00,070 --> 00:03:01,990 So let's just do it manually. 44 00:03:02,950 --> 00:03:06,580 It looks like our answer is Cole Bailey at Yahoo.com. 45 00:03:07,240 --> 00:03:11,250 So we can use a subquery to help us where all we have to do. 46 00:03:11,260 --> 00:03:15,490 We can get rid of this and say select email comma created. 47 00:03:15,640 --> 00:03:24,910 Or we could just say select star to make it shorter from users where created it equals. 48 00:03:25,610 --> 00:03:27,650 And then we have to do our subquery. 49 00:03:28,430 --> 00:03:35,420 Select min created debt from users. 50 00:03:36,080 --> 00:03:40,580 So those parentheses indicate that this is a query that needs to happen. 51 00:03:40,700 --> 00:03:42,200 So this is evaluated. 52 00:03:42,200 --> 00:03:43,910 What is min created at? 53 00:03:44,000 --> 00:03:45,790 I need my parentheses to work there. 54 00:03:45,800 --> 00:03:46,450 There we go. 55 00:03:46,460 --> 00:03:47,810 What is the min created at? 56 00:03:47,810 --> 00:03:49,820 Well, it's going to be this. 57 00:03:50,730 --> 00:03:54,270 So then basically this whole thing is replaced by that. 58 00:03:54,750 --> 00:03:59,520 And then we're finding users, staff from users where created is equal to that. 59 00:04:00,090 --> 00:04:01,230 So let's try it. 60 00:04:02,960 --> 00:04:03,680 There we go. 61 00:04:04,340 --> 00:04:05,080 Looks good. 62 00:04:05,090 --> 00:04:06,830 We've got email and created that. 63 00:04:06,860 --> 00:04:10,640 Of course it looks different, but this is the correct answer called Bailey. 64 00:04:10,640 --> 00:04:16,610 And we saw that because earlier we just manually selected it based off of that date and it worked. 65 00:04:17,570 --> 00:04:18,320 Perfect. 66 00:04:18,769 --> 00:04:19,550 Moving on. 67 00:04:19,910 --> 00:04:25,970 So we're taking every user taking their created at date and then we're extracting the month name from 68 00:04:26,000 --> 00:04:28,040 that date, whatever the month is. 69 00:04:28,070 --> 00:04:31,750 Combining them all, we're grouping them and counting how many exist. 70 00:04:31,760 --> 00:04:34,130 So how many users signed up in November? 71 00:04:34,130 --> 00:04:38,360 In our case, November was the highest and then we're sorting as well. 72 00:04:38,480 --> 00:04:41,450 So let's start by just extracting the month name. 73 00:04:41,990 --> 00:04:44,120 So if we were to take a date like. 74 00:04:45,970 --> 00:04:46,900 Where is that? 75 00:04:47,110 --> 00:04:48,500 This one right here. 76 00:04:48,520 --> 00:04:50,380 How do we get the month name from that? 77 00:04:51,220 --> 00:04:59,980 Because if we just do a select month of that, it gives us a number. 78 00:05:01,060 --> 00:05:06,490 So what we can do instead is month name and that gives us may. 79 00:05:06,880 --> 00:05:08,140 So we'll start with that. 80 00:05:08,170 --> 00:05:16,120 We'll do select month name of created ET from users. 81 00:05:19,370 --> 00:05:22,510 And we get 500 different month names. 82 00:05:22,520 --> 00:05:25,910 Well, there's only 12 different ones, but 500 rose. 83 00:05:26,600 --> 00:05:36,470 So then what we want to do is a simple group buy and let's just give it a name like as month and group 84 00:05:36,470 --> 00:05:37,940 by month instead. 85 00:05:38,840 --> 00:05:40,610 Rather than retyping that whole thing. 86 00:05:42,200 --> 00:05:48,920 So now we have 12, assuming that we have a date in every month of the year, which we probably do if 87 00:05:48,920 --> 00:05:52,600 we have 500, but we don't see how many there are. 88 00:05:52,610 --> 00:05:54,710 So then we need our second field. 89 00:05:56,980 --> 00:06:00,730 And our second field is count. 90 00:06:02,140 --> 00:06:03,400 We just do count star. 91 00:06:04,520 --> 00:06:05,380 Just like that. 92 00:06:08,030 --> 00:06:08,930 And here we go. 93 00:06:09,080 --> 00:06:10,010 Almost there. 94 00:06:10,700 --> 00:06:11,900 All that we need to do. 95 00:06:11,930 --> 00:06:14,420 I believe I gave this an alias of Count. 96 00:06:14,900 --> 00:06:19,850 And then we also want to order by count. 97 00:06:20,870 --> 00:06:22,640 And we want it to be descending. 98 00:06:23,600 --> 00:06:24,710 Let's see if it works. 99 00:06:26,410 --> 00:06:27,100 Here we go. 100 00:06:27,430 --> 00:06:33,440 November, the highest count as well with 52, although it diverges. 101 00:06:33,460 --> 00:06:37,900 January was first or was second, in my case, May and then April. 102 00:06:38,440 --> 00:06:39,160 All right. 103 00:06:39,160 --> 00:06:44,560 So we use this month name and we group them based off of that month. 104 00:06:44,830 --> 00:06:49,390 You could also just replace it with this, but it's easier just to type a month and then we're grouping 105 00:06:49,390 --> 00:06:51,460 them, counting them and ordering them. 106 00:06:52,330 --> 00:06:58,900 Let me just put a note that this is challenge three, moving on, challenge for count the number of 107 00:06:58,900 --> 00:07:00,070 users with Yahoo! 108 00:07:00,070 --> 00:07:00,820 Emails. 109 00:07:01,210 --> 00:07:03,310 So for this one, it's a bit of a throwback. 110 00:07:03,310 --> 00:07:08,530 All we need to do is use like to to figure out which emails have Yahoo! 111 00:07:08,530 --> 00:07:09,070 In them. 112 00:07:09,340 --> 00:07:19,120 So we'll do a challenge for let's do a select star from users where we just need to say we're created 113 00:07:19,120 --> 00:07:23,800 at like and we don't just want to say Yahoo! 114 00:07:23,830 --> 00:07:29,950 Remember that's only going to find and what am I saying created it where email like Yahoo we don't want 115 00:07:29,950 --> 00:07:32,620 to do that because that's only where email is exactly. 116 00:07:32,620 --> 00:07:33,310 Yahoo! 117 00:07:33,640 --> 00:07:36,490 So we need these the placeholders. 118 00:07:36,490 --> 00:07:42,040 But we could improve this a bit because this would match anything that had Yahoo in the string anywhere 119 00:07:42,040 --> 00:07:45,160 and we could have someone's email be Yahoo! 120 00:07:45,160 --> 00:07:54,640 Tim at gmail.com or maybe someone's last name is Yahoo or something Yahoo's so we don't want that. 121 00:07:54,940 --> 00:08:04,720 So all we want to change is we'll do at yahoo.com and this will guarantee that the string ends in yahoo.com. 122 00:08:05,920 --> 00:08:07,480 So now let's take a look. 123 00:08:07,630 --> 00:08:08,200 Here we go. 124 00:08:08,200 --> 00:08:10,300 These are all Yahoo.com emails. 125 00:08:10,630 --> 00:08:13,240 And then all we wanted to do is count how many. 126 00:08:13,240 --> 00:08:19,990 So rather than selecting Star, we'll do select Count Star as Yahoo users I think is what we called 127 00:08:19,990 --> 00:08:24,670 it from users where email is like Yahoo! 128 00:08:24,670 --> 00:08:27,160 And this time there's 170. 129 00:08:28,540 --> 00:08:34,179 And finally, challenge number five, calculate the total number of users for each email. 130 00:08:34,179 --> 00:08:39,850 Host So this one involves using like along with a case statement, it's a little bit complicated. 131 00:08:39,850 --> 00:08:44,500 So we're doing the same thing where we're counting the number of users, but we're doing it dependent 132 00:08:44,500 --> 00:08:45,850 on the provider. 133 00:08:46,510 --> 00:08:48,220 So we're going to have a case statement. 134 00:08:48,220 --> 00:08:50,380 So we'll just start right off with Select. 135 00:08:50,890 --> 00:08:56,650 We'll do Case and I like to do my end as I think we called it, provider. 136 00:08:56,650 --> 00:08:57,130 Yep. 137 00:08:57,550 --> 00:09:02,050 So we'll start with that and we'll do from users. 138 00:09:03,040 --> 00:09:04,390 And what is our case? 139 00:09:04,870 --> 00:09:07,210 Well, we can start with this. 140 00:09:07,210 --> 00:09:07,570 Yahoo! 141 00:09:07,570 --> 00:09:12,190 One will say when email is like Yahoo! 142 00:09:12,340 --> 00:09:15,370 Then provider is Yahoo! 143 00:09:15,850 --> 00:09:16,390 Right? 144 00:09:16,900 --> 00:09:18,580 So we'll do the same thing. 145 00:09:18,610 --> 00:09:20,230 Just basically copy this. 146 00:09:21,940 --> 00:09:25,500 And say one email is like at gmail.com. 147 00:09:25,510 --> 00:09:29,410 I'm going to end in Gmail, then Gmail. 148 00:09:30,880 --> 00:09:35,590 One thing I should note, there is a kind of nicer way of doing this if you are familiar with regular 149 00:09:35,590 --> 00:09:38,620 expressions, but that's out of the scope of this course. 150 00:09:38,920 --> 00:09:41,390 But if you do know it, you can get by with that. 151 00:09:41,450 --> 00:09:42,940 So we've also got Hotmail. 152 00:09:46,340 --> 00:09:47,900 Then Hotmail. 153 00:09:48,350 --> 00:09:49,010 There we go. 154 00:09:51,110 --> 00:09:54,560 Then finally we'll have our else, which will just be other. 155 00:09:54,860 --> 00:10:00,680 So we have this provider field now, a column that will have Gmail, Yahoo and Hotmail or other. 156 00:10:01,490 --> 00:10:09,290 So let's do email, comma, case so that we can see the email side by side against what we pulled out 157 00:10:09,290 --> 00:10:09,470 of it. 158 00:10:09,480 --> 00:10:10,470 So we've got 500. 159 00:10:10,490 --> 00:10:12,320 Let's take this one ray dot more one. 160 00:10:12,320 --> 00:10:14,090 At Gmail we get Gmail. 161 00:10:14,150 --> 00:10:16,010 Rubin Medhurst at Yahoo! 162 00:10:16,040 --> 00:10:17,000 We get Yahoo! 163 00:10:17,180 --> 00:10:18,070 Perfect. 164 00:10:18,080 --> 00:10:23,120 So now it's just a matter of grouping them together and counting so we can get rid of email. 165 00:10:23,120 --> 00:10:24,710 We don't need to display it. 166 00:10:25,250 --> 00:10:31,520 Then we're just going to group by provider, which is what we just created. 167 00:10:32,450 --> 00:10:33,650 So let's start with that. 168 00:10:36,680 --> 00:10:37,300 Cool. 169 00:10:37,310 --> 00:10:38,440 So we're grouping them together. 170 00:10:38,480 --> 00:10:40,550 Looks like we don't have any others in this case. 171 00:10:41,090 --> 00:10:44,270 We're going to group them and then finally we're going to count. 172 00:10:44,570 --> 00:10:49,280 After our case statement ends, we add a comma count star. 173 00:10:52,130 --> 00:10:54,140 Copy paste. 174 00:10:54,410 --> 00:10:58,480 And there's just a small discrepancy, which is this is called total users. 175 00:10:58,490 --> 00:11:00,050 Also, this is sorted. 176 00:11:00,620 --> 00:11:02,150 Very easy to change. 177 00:11:02,990 --> 00:11:05,300 Call it as total users. 178 00:11:06,500 --> 00:11:08,330 I'd like to capitalize that, actually. 179 00:11:09,860 --> 00:11:11,170 And then finally. 180 00:11:11,180 --> 00:11:13,400 Well, let's just make sure that that works to start. 181 00:11:14,870 --> 00:11:15,510 Great. 182 00:11:15,530 --> 00:11:20,210 And now we need to sort it order by total users. 183 00:11:20,450 --> 00:11:21,650 And if we do it this way. 184 00:11:23,100 --> 00:11:24,950 You'll see that it's ascending. 185 00:11:24,960 --> 00:11:26,670 We want to do descending. 186 00:11:28,160 --> 00:11:32,960 All right, now we've got all the providers on the left and their total number of users. 187 00:11:33,290 --> 00:11:34,130 Perfect. 188 00:11:34,460 --> 00:11:40,730 All right, so hopefully you enjoyed that chance of working with more data that isn't hardcoded in some 189 00:11:40,730 --> 00:11:44,090 of its dynamic, all of its dynamic, 500 plus users. 190 00:11:44,120 --> 00:11:49,280 Next up in the next section, we're finally going to connect the database we just created. 191 00:11:49,280 --> 00:11:53,840 We just filled with all that seed data, and we're going to connect it with a web application and figure 192 00:11:53,840 --> 00:11:55,400 out how to get those to talk.