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.

