1
00:00:00,270 --> 00:00:00,890
All righty.

2
00:00:00,900 --> 00:00:02,730
So we inserted 500 users.

3
00:00:02,730 --> 00:00:04,990
It was kind of a behemoth of a section to get there.

4
00:00:05,010 --> 00:00:06,300
Lots of ground to cover.

5
00:00:06,300 --> 00:00:08,490
But now that we're there, we're back in our comfort zone.

6
00:00:08,490 --> 00:00:11,370
Hopefully we're going to have a couple of exercises.

7
00:00:11,550 --> 00:00:14,400
I know we have a lot of them, but this is cool.

8
00:00:14,400 --> 00:00:21,180
We have a lot of data now where it was a single, you hit the return key once and you got 500 users

9
00:00:21,180 --> 00:00:24,450
much easier than having to copy and paste like 20 books.

10
00:00:24,540 --> 00:00:26,670
So we're going to play around with them.

11
00:00:26,670 --> 00:00:31,410
And again, your data is randomized just like mine, so your tables and your results will look different,

12
00:00:31,410 --> 00:00:33,480
but the queries will work the same.

13
00:00:34,050 --> 00:00:40,230
So here's the first thing find the earliest date that a user joined, and all we need to see is the

14
00:00:40,230 --> 00:00:40,830
date.

15
00:00:41,130 --> 00:00:43,080
And also notice how I formatted it.

16
00:00:43,950 --> 00:00:45,000
Pay attention to that.

17
00:00:45,970 --> 00:00:47,260
It's just the earliest date.

18
00:00:47,930 --> 00:00:49,280
And then the next problem.

19
00:00:50,760 --> 00:00:53,670
Find the email of the earliest user.

20
00:00:53,760 --> 00:00:54,930
Now, this one's tricky.

21
00:00:54,990 --> 00:01:00,690
Remember how we talked about this with men and Macs way back a couple, I don't know, five, 6 hours

22
00:01:00,690 --> 00:01:06,600
ago about how when you use men and Macs, you don't just get the men, you don't get the row itself.

23
00:01:06,600 --> 00:01:08,070
So we had to use a subquery.

24
00:01:08,070 --> 00:01:13,620
So the hint is use a subquery to find the email that corresponds to the earliest user.

25
00:01:14,870 --> 00:01:21,650
Then take all the users and I want to see a table where we have a month name and then the number of

26
00:01:21,650 --> 00:01:23,870
users joined who joined in that month.

27
00:01:23,870 --> 00:01:26,060
So it doesn't matter what year.

28
00:01:26,060 --> 00:01:29,060
We have some 2016, some 2017 most likely.

29
00:01:29,270 --> 00:01:32,150
So we're not grouping by that just by the month.

30
00:01:32,510 --> 00:01:36,410
So you have to figure out how you extract the month name from the date and then go from there.

31
00:01:37,160 --> 00:01:38,450
Also, notice the order.

32
00:01:39,240 --> 00:01:39,870
Then.

33
00:01:40,690 --> 00:01:43,680
Count the number of emails or the number of users with Yahoo!

34
00:01:43,690 --> 00:01:50,320
Emails, and the way that you can determine that is just if their email ends with at Yahoo.com.

35
00:01:50,350 --> 00:01:51,280
So how do you do that?

36
00:01:52,290 --> 00:01:58,440
Count them all and then finally calculate the total number of users for each email host.

37
00:01:58,440 --> 00:02:05,520
And this one's a little bit complicated because you're basically going to be doing four different checks.

38
00:02:05,550 --> 00:02:09,449
You're going to say, All right, does the email end with Gmail?

39
00:02:10,020 --> 00:02:10,830
Does it end with Yahoo!

40
00:02:10,860 --> 00:02:12,150
Does it end with Hotmail?

41
00:02:12,270 --> 00:02:16,500
And then you need to calculate how many each end with these.

42
00:02:16,710 --> 00:02:18,630
So it's a little bit complicated.

43
00:02:18,810 --> 00:02:20,250
That's why I left it for last.

44
00:02:20,280 --> 00:02:24,120
Hopefully you're able to get it, if not solutions coming up in the next video.

45
00:02:24,150 --> 00:02:26,300
Just remember, your data is going to look different.

46
00:02:26,310 --> 00:02:28,170
Don't panic if your numbers are different.

47
00:02:28,470 --> 00:02:28,860
All right.

48
00:02:28,860 --> 00:02:30,090
I'll see you with the solution.