1
00:00:00,150 --> 00:00:04,100
Okeydokey, let's go through a solution for this exercise.

2
00:00:04,110 --> 00:00:10,470
The first challenge is to select all the collection, or rather, all the titles that contain stories.

3
00:00:10,920 --> 00:00:15,510
So I'm going to do this from within DB Gate this time just to mix it up.

4
00:00:15,990 --> 00:00:19,710
Obviously, hopefully by now it's clear you can run these queries however you want.

5
00:00:19,740 --> 00:00:21,810
I've tried to show a bunch of different options.

6
00:00:21,810 --> 00:00:26,340
Workbench doing it in line here in the show and using a tool like DB Gate.

7
00:00:26,370 --> 00:00:31,800
I'm going to do it this way because I can write these longer queries and have them on multiple lines.

8
00:00:31,830 --> 00:00:33,440
It will just look a little nicer for you.

9
00:00:33,450 --> 00:00:35,820
But again, the code is exactly the same.

10
00:00:35,820 --> 00:00:46,950
So I'm going to select title from Books Where and All I Want is title where title like.

11
00:00:46,950 --> 00:00:47,730
Remember the like.

12
00:00:47,760 --> 00:00:48,180
Operator.

13
00:00:48,180 --> 00:00:49,080
We just learned.

14
00:00:50,330 --> 00:00:51,170
Like.

15
00:00:51,170 --> 00:00:56,770
And then I can't just do stories, of course, because I'm not going to get any matches when I run it.

16
00:00:56,780 --> 00:01:01,820
We get nothing back because no title is exactly stories.

17
00:01:01,820 --> 00:01:07,850
But if I use my wild card characters at the beginning and end, this means the word stories needs to

18
00:01:07,850 --> 00:01:11,440
come at the very beginning, at the very end, or anywhere in the middle of the string.

19
00:01:11,450 --> 00:01:12,310
We don't care.

20
00:01:12,320 --> 00:01:13,770
Just somewhere in there.

21
00:01:13,790 --> 00:01:16,670
Optionally zero or more characters before or after.

22
00:01:16,880 --> 00:01:20,220
And if I execute that, we see we get three results.

23
00:01:20,240 --> 00:01:25,130
What we talk about when we talk about love stories, where I'm calling from selected stories and Oblivion

24
00:01:25,130 --> 00:01:26,040
stories.

25
00:01:26,060 --> 00:01:29,030
I'll just run it over here in my SQL in the Shell just to show you.

26
00:01:29,090 --> 00:01:29,870
Same thing.

27
00:01:30,140 --> 00:01:33,410
It just looks a little different the way it's presented over here.

28
00:01:33,650 --> 00:01:35,120
So that's our first piece.

29
00:01:35,420 --> 00:01:41,790
The next challenge find the longest book and print out the title and page count for each book.

30
00:01:41,810 --> 00:01:44,390
Well, sorry for that one longest book.

31
00:01:44,480 --> 00:01:50,570
So what we're going to do is let's start by just selecting title and pages from books.

32
00:01:50,570 --> 00:01:54,050
Just get all of them and I'll execute that line.

33
00:01:54,170 --> 00:01:58,100
So you can see here we get title and pages.

34
00:01:58,100 --> 00:02:02,060
But what I want to do is find the longest one.

35
00:02:02,060 --> 00:02:07,190
So I'm going to order by pages and then limit so that we only get one result.

36
00:02:07,550 --> 00:02:10,460
And again, we're going to learn a way of doing this.

37
00:02:10,460 --> 00:02:14,930
It's a little easier very soon, but this will work if I select them all.

38
00:02:14,960 --> 00:02:17,960
Title and pages from books Order by.

39
00:02:18,890 --> 00:02:19,750
Pages.

40
00:02:20,420 --> 00:02:21,710
Limit to one.

41
00:02:21,710 --> 00:02:23,690
But let me just start by ordering by pages.

42
00:02:23,690 --> 00:02:26,840
Make sure that that works and you can see my results here.

43
00:02:26,840 --> 00:02:30,650
We start with 176 all the way down to 634.

44
00:02:30,740 --> 00:02:37,160
So I don't quite want to limit it to one just yet because then I would only get the least number of

45
00:02:37,160 --> 00:02:37,790
pages.

46
00:02:38,090 --> 00:02:42,650
What I'm going to do is change it to sort in descending order, limit one.

47
00:02:43,160 --> 00:02:47,240
Now, when I run this, we see The Amazing Adventures of Kavalier and Clay.

48
00:02:47,270 --> 00:02:50,450
634 and that's what we see over here.

49
00:02:51,820 --> 00:02:58,240
Okay, Next up, print a summary containing the title and the year for the three most recent books.

50
00:02:58,360 --> 00:03:05,350
So I'm going to need to use Kit-Kat to create this new piece of data, which is the concatenation of

51
00:03:05,350 --> 00:03:10,690
the title, and then a space dash space and then the year.

52
00:03:10,780 --> 00:03:12,640
So why don't I start there?

53
00:03:12,670 --> 00:03:16,600
I'll do Select Canned Cat and then title.

54
00:03:17,460 --> 00:03:19,950
And then SpaceX dash SpaceX.

55
00:03:20,250 --> 00:03:23,220
And then released here.

56
00:03:23,760 --> 00:03:27,420
I don't know why I accidentally capitalized that from books.

57
00:03:27,420 --> 00:03:28,470
We'll start with that.

58
00:03:28,470 --> 00:03:30,810
I'll just run this one current query.

59
00:03:31,820 --> 00:03:33,230
And there we are.

60
00:03:33,290 --> 00:03:39,620
We see the namesake Dash 22,003 Lincoln and the Bardo Dash 2017.

61
00:03:39,920 --> 00:03:44,480
Now, I do want to rename that I want to call it summary just to match.

62
00:03:44,480 --> 00:03:47,930
So I'll do as some Oh, I can't do it there.

63
00:03:47,960 --> 00:03:54,020
Of course it has to come right after the piece I want to rename as summary.

64
00:03:54,890 --> 00:03:56,060
Rerun this.

65
00:03:57,550 --> 00:03:59,620
And it's called summary up here.

66
00:03:59,650 --> 00:04:02,460
Or if I run it in my SQL in the shell, there we are.

67
00:04:02,470 --> 00:04:03,090
Summary.

68
00:04:04,480 --> 00:04:06,090
So now we need to narrow it down.

69
00:04:06,100 --> 00:04:11,050
I want the three most recent books, meaning the three Largest Years.

70
00:04:11,680 --> 00:04:14,500
So I'm going to do an order by based off of the year.

71
00:04:14,500 --> 00:04:16,060
So let me get rid of the semicolon.

72
00:04:16,779 --> 00:04:19,329
Order by released here.

73
00:04:20,070 --> 00:04:21,600
We can start with that.

74
00:04:21,600 --> 00:04:23,940
But I first want to reorder.

75
00:04:23,940 --> 00:04:29,400
I want to go in descending order so that then when I take the first three, it's easier.

76
00:04:29,940 --> 00:04:31,680
I'll get them in in the right order.

77
00:04:31,830 --> 00:04:36,420
I want the three most recent, so I'll do release here descending.

78
00:04:37,960 --> 00:04:39,200
Run that again.

79
00:04:39,220 --> 00:04:39,880
I hate that.

80
00:04:39,880 --> 00:04:42,190
I don't want to see this go away.

81
00:04:43,330 --> 00:04:44,260
This right there.

82
00:04:44,260 --> 00:04:46,840
It always shows up every time I rerun this query.

83
00:04:47,470 --> 00:04:49,360
I wish it didn't anyway.

84
00:04:50,170 --> 00:04:55,810
Now I'm getting them in the right order and I simply need to limit it to three results, and that should

85
00:04:55,810 --> 00:04:56,860
do it for us.

86
00:04:57,720 --> 00:04:58,350
There we are.

87
00:04:58,380 --> 00:05:01,230
Lincoln In the Bardo Norse mythology, 10% happier.

88
00:05:02,310 --> 00:05:04,890
Next up, what's our fourth exercise?

89
00:05:04,920 --> 00:05:06,780
Find all the books with an author.

90
00:05:06,780 --> 00:05:10,020
Last name that contains a space and don't cheat.

91
00:05:10,860 --> 00:05:12,030
This one is easy enough.

92
00:05:12,030 --> 00:05:14,720
We actually just need to use like again.

93
00:05:14,730 --> 00:05:15,870
So what are we selecting?

94
00:05:15,870 --> 00:05:17,100
The title and the author?

95
00:05:17,100 --> 00:05:17,880
Last name.

96
00:05:17,880 --> 00:05:23,010
So title comma author L Name from books.

97
00:05:23,610 --> 00:05:24,750
I guess we'll start there.

98
00:05:24,780 --> 00:05:25,800
Nice and simple.

99
00:05:28,200 --> 00:05:31,890
But we want to narrow it down and only get the author last names that have a space.

100
00:05:31,890 --> 00:05:35,670
And the way that we tell if they have a space is to use where.

101
00:05:35,940 --> 00:05:36,290
Right.

102
00:05:36,300 --> 00:05:36,780
So where?

103
00:05:36,810 --> 00:05:38,010
Author ll name.

104
00:05:38,310 --> 00:05:42,720
I'll do this on the next line here where author ll name like.

105
00:05:42,930 --> 00:05:45,480
And then once again it doesn't work just to do a space.

106
00:05:45,480 --> 00:05:50,760
We have to say percent sign space, percent sign because that space could come anywhere.

107
00:05:50,790 --> 00:05:52,770
I mean, I wasn't specific.

108
00:05:52,800 --> 00:05:54,030
It doesn't have to be in the middle.

109
00:05:54,030 --> 00:05:59,400
We only have one author last name with the space anyway, but I don't think anybody has a last name

110
00:05:59,400 --> 00:06:01,530
that starts to the space or enter the space.

111
00:06:01,950 --> 00:06:08,130
Maybe I'm totally underestimating and I'm completely wrong, but it doesn't matter because we're going

112
00:06:08,130 --> 00:06:09,120
to get any space.

113
00:06:09,120 --> 00:06:13,290
It's we're going to match them regardless of where they are in the author last name.

114
00:06:14,570 --> 00:06:17,000
And we just get foster space.

115
00:06:17,000 --> 00:06:18,830
WALLACE And that's it.

116
00:06:19,310 --> 00:06:22,880
Next up, find the three books that have the lowest stock.

117
00:06:22,880 --> 00:06:25,730
So we're going to get the title here in stock quantity.

118
00:06:25,730 --> 00:06:27,020
Let's just start with that.

119
00:06:27,800 --> 00:06:34,660
Select title released year and stock quantity from books.

120
00:06:34,670 --> 00:06:36,980
I'll just do it on separate lines to clean it up a bit.

121
00:06:37,280 --> 00:06:40,640
And this, of course, will be for all the books I accidentally just killed.

122
00:06:40,640 --> 00:06:42,350
I meant execute current.

123
00:06:43,310 --> 00:06:45,350
And there we are.

124
00:06:45,380 --> 00:06:48,320
We get title released your stock quantity.

125
00:06:48,320 --> 00:06:55,760
But what I'm supposed to do is sort them by the stock quantity and then get the bottom three the least

126
00:06:55,760 --> 00:06:59,060
number or the lowest stock quantity rows.

127
00:06:59,300 --> 00:07:00,860
So we'll do an order.

128
00:07:00,860 --> 00:07:04,010
Buy stock quantity.

129
00:07:05,450 --> 00:07:06,640
Stock.

130
00:07:06,650 --> 00:07:07,520
Come on.

131
00:07:10,520 --> 00:07:12,170
And we'll start with that.

132
00:07:14,210 --> 00:07:18,770
It's looking good, except we have this lull that's in the way, which is really obnoxious.

133
00:07:19,820 --> 00:07:23,700
I guess we probably should just drop that book if you made that book when I did.

134
00:07:23,720 --> 00:07:26,210
Then we can get rid of it.

135
00:07:26,420 --> 00:07:27,770
Or you should get rid of it.

136
00:07:27,770 --> 00:07:30,080
But I don't think I asked you to do this.

137
00:07:30,200 --> 00:07:36,230
I was just demonstrating what happened when we inserted a row that only had a title and a last name,

138
00:07:36,230 --> 00:07:36,720
I believe.

139
00:07:36,740 --> 00:07:42,260
But anyway, if you do have any NOLs in there like I do, let's go ahead and just drop that or delete

140
00:07:42,260 --> 00:07:42,740
it.

141
00:07:43,070 --> 00:07:45,230
I'll do it the old fashioned way.

142
00:07:45,590 --> 00:07:46,820
I'll just run a query to do it.

143
00:07:46,820 --> 00:07:47,510
I'll do it over here.

144
00:07:47,510 --> 00:07:58,400
So delete from books where and I'll just go where title equals my life, I think is what I called it.

145
00:07:59,030 --> 00:08:00,130
That should have gotten rid of it.

146
00:08:00,140 --> 00:08:02,090
Let's try rerunning this query now.

147
00:08:03,280 --> 00:08:04,060
Looking good.

148
00:08:04,240 --> 00:08:04,630
All right.

149
00:08:04,630 --> 00:08:07,870
So we see the 12, 12, 23 American gods where I'm calling from.

150
00:08:07,870 --> 00:08:12,880
What we talk about when we talk about love, I just need to limit it because we want the bottom three,

151
00:08:12,880 --> 00:08:17,230
while technically the first three, which is the lowest stock quantity.

152
00:08:17,230 --> 00:08:18,610
So let's run that.

153
00:08:20,160 --> 00:08:20,940
Looks good.

154
00:08:21,700 --> 00:08:24,870
OC And I think it's just our last one.

155
00:08:24,870 --> 00:08:26,280
No, almost last one.

156
00:08:26,280 --> 00:08:31,230
Print the title and author last name sorted first by author, last name, and then by title.

157
00:08:31,650 --> 00:08:33,150
So this one's not too bad.

158
00:08:33,179 --> 00:08:38,730
We're going to select title author L Name from books.

159
00:08:39,360 --> 00:08:41,850
And then we want to order by.

160
00:08:41,880 --> 00:08:43,289
Let's just run it first, though.

161
00:08:44,550 --> 00:08:47,460
We want to order by the author's last name.

162
00:08:47,460 --> 00:08:52,320
So we should see Carver up top and then Eggers all the way down to Steinbeck at the bottom.

163
00:08:53,730 --> 00:08:57,240
Order by author l name.

164
00:08:57,750 --> 00:08:58,770
Run it again.

165
00:09:00,760 --> 00:09:08,890
And we see Carver down to Steinbeck, but we're going to sort with a secondary sort meaning after every

166
00:09:08,890 --> 00:09:10,840
row has been sorted by author last name.

167
00:09:10,850 --> 00:09:16,300
Any time that we have the same author, last name, let's also sort by the title.

168
00:09:16,690 --> 00:09:22,750
So we just add an order by author L name, comma title, and we should see that.

169
00:09:23,350 --> 00:09:29,110
Whereas an example where it would change, consider the lobster will come before oblivion there by the

170
00:09:29,110 --> 00:09:29,860
same author.

171
00:09:29,860 --> 00:09:35,380
But this alphabetically will come before let's check and run it.

172
00:09:36,690 --> 00:09:37,470
Scroll down.

173
00:09:37,470 --> 00:09:37,890
There we are.

174
00:09:37,890 --> 00:09:40,230
Consider the lobster comes before oblivion.

175
00:09:40,440 --> 00:09:42,150
Or with Eggers.

176
00:09:42,180 --> 00:09:42,750
A hologram.

177
00:09:42,780 --> 00:09:45,750
Hologram for the king comes before the circle.

178
00:09:46,290 --> 00:09:49,560
And that's pretty much it for this one.

179
00:09:49,620 --> 00:09:51,570
We don't need to rename any of our columns.

180
00:09:51,570 --> 00:09:53,940
It looks good and we don't need to limit it.

181
00:09:55,560 --> 00:09:56,680
Make this happen.

182
00:09:56,700 --> 00:10:00,420
We want to yell for every row we're yelling.

183
00:10:00,420 --> 00:10:08,100
My favorite author is Raymond Carver, and it is not only concatenated right where we have a piece of

184
00:10:08,100 --> 00:10:12,930
text plus the author phone first and last name at the space in the middle.

185
00:10:13,140 --> 00:10:15,420
It is also capitalized.

186
00:10:15,840 --> 00:10:17,820
So we have different ways of doing this.

187
00:10:18,840 --> 00:10:24,450
A lot of ways of doing this because we could capitalize the author first and last name first and then

188
00:10:24,450 --> 00:10:25,620
concatenate that.

189
00:10:25,620 --> 00:10:30,270
Or we could just concatenate everything and then uppercase at the end, which might be the simplest

190
00:10:30,270 --> 00:10:30,720
way.

191
00:10:31,230 --> 00:10:34,830
So why don't we start by doing the concatenation?

192
00:10:35,430 --> 00:10:41,340
We'll do a select and then can cat we want to contact.

193
00:10:41,340 --> 00:10:45,540
My favorite author is.

194
00:10:46,410 --> 00:10:48,690
And then author first names.

195
00:10:48,690 --> 00:10:50,010
I'm going to put a space there.

196
00:10:50,370 --> 00:10:51,810
Author F name.

197
00:10:53,080 --> 00:10:57,790
And then, comma, we want another space and then comma.

198
00:10:58,530 --> 00:11:01,740
Author l name not in quotes.

199
00:11:01,740 --> 00:11:02,640
That is an accident.

200
00:11:02,640 --> 00:11:03,840
Should not be in quotes there.

201
00:11:04,320 --> 00:11:08,160
And this is getting kind of long for a single line, at least in this window.

202
00:11:08,880 --> 00:11:14,770
So I'm going to then let me just close my friends here and run that and see what we end up with.

203
00:11:14,790 --> 00:11:15,250
Whoops.

204
00:11:16,810 --> 00:11:17,650
Let's see.

205
00:11:19,960 --> 00:11:26,140
And I'm an idiot once again because I never said what we're selecting from.

206
00:11:26,140 --> 00:11:28,840
So from books, that's our table.

207
00:11:29,140 --> 00:11:30,490
Let's try that again.

208
00:11:31,990 --> 00:11:38,500
And we now see, my favorite author is Jhumpa Lahiri, Neil Gaiman, and so on, but not capitalized.

209
00:11:38,500 --> 00:11:40,110
So that's the first thing we need to do.

210
00:11:40,120 --> 00:11:42,720
And we're also supposed to sort at the end.

211
00:11:42,730 --> 00:11:52,810
So when I want to uppercase all of this, I could do upper author f name and upper.

212
00:11:53,940 --> 00:11:55,140
Author l name.

213
00:11:56,970 --> 00:11:57,990
Run that.

214
00:12:02,320 --> 00:12:03,550
And our results.

215
00:12:03,550 --> 00:12:04,660
That looks good.

216
00:12:05,410 --> 00:12:10,630
To make this a bit more legible, though, this is frustrating on this super long line, let's just

217
00:12:10,630 --> 00:12:12,190
space things out with Con Cat.

218
00:12:12,190 --> 00:12:14,620
So we'll select our con cat.

219
00:12:14,620 --> 00:12:16,420
But we're going to concatenate.

220
00:12:17,080 --> 00:12:24,310
My favorite author is and then upper of author first name and then an empty string or a space rather

221
00:12:24,310 --> 00:12:25,540
than upper of the author.

222
00:12:25,540 --> 00:12:27,910
Last name, something like that.

223
00:12:28,360 --> 00:12:30,550
Or we might even indent all of these.

224
00:12:31,660 --> 00:12:32,590
Like that.

225
00:12:32,710 --> 00:12:34,300
So that's one option.

226
00:12:34,930 --> 00:12:36,560
And select that from books.

227
00:12:36,580 --> 00:12:41,500
The other option I'll comment that out is to do a single call to upper.

228
00:12:42,180 --> 00:12:49,140
And just do author F name author L name as is and then upper.

229
00:12:49,890 --> 00:12:52,590
That entire King cat thing.

230
00:12:52,650 --> 00:12:57,810
All of this is going to be uppercase, so I'll indent that and dent that.

231
00:12:58,990 --> 00:13:00,730
Indentation, of course, doesn't matter.

232
00:13:00,730 --> 00:13:04,510
It's just about making it slightly more legible, possibly.

233
00:13:05,220 --> 00:13:06,000
Like that.

234
00:13:06,580 --> 00:13:11,730
Okay, so we're going to run this first, concatenate everything together, then uppercase the entire

235
00:13:11,730 --> 00:13:12,170
thing.

236
00:13:12,180 --> 00:13:16,260
Remember, we can also do, I think, UCS instead of upper.

237
00:13:16,620 --> 00:13:22,110
Let's just run this, make sure it works, execute current, and it still does in fact work.

238
00:13:22,940 --> 00:13:27,350
All right, then what we're supposed to do is rename that to be called Yell.

239
00:13:27,920 --> 00:13:29,240
So let's do that.

240
00:13:30,010 --> 00:13:31,900
As know.

241
00:13:33,310 --> 00:13:34,390
Simple enough.

242
00:13:34,930 --> 00:13:36,100
It's called yell here.

243
00:13:36,490 --> 00:13:39,360
And then we need to sort alphabetically by last name.

244
00:13:39,370 --> 00:13:41,350
So we'll do that here.

245
00:13:41,380 --> 00:13:45,070
Order by author LL name.

246
00:13:45,070 --> 00:13:50,270
And it looks like it's in a no by last name.

247
00:13:50,290 --> 00:13:51,350
Si, si, si.

248
00:13:51,370 --> 00:13:51,760
Yeah.

249
00:13:51,790 --> 00:13:55,600
It's in the ascending order that it comes in, so we don't need to change that.

250
00:13:56,560 --> 00:13:57,490
And there we are.

251
00:13:57,490 --> 00:14:00,050
We should see the correct answer.

252
00:14:00,070 --> 00:14:01,540
My favorite author is Raymond Carver.

253
00:14:01,540 --> 00:14:07,510
Raymond Carver, Michael Chabon, Don DeLillo, all the way down to John Steinbeck.

254
00:14:07,510 --> 00:14:08,680
And that looks good.

255
00:14:08,920 --> 00:14:09,880
We did it.

256
00:14:09,880 --> 00:14:10,780
We answered that one.

257
00:14:10,780 --> 00:14:14,770
It was a bit of a mess, but that looks good to me.

258
00:14:14,770 --> 00:14:15,880
We had two options.

259
00:14:15,880 --> 00:14:20,110
I mean, technically, with this second option, we don't have to uppercase this text.

260
00:14:20,110 --> 00:14:26,020
It could be lowercase, it will be uppercase by upper, but that works just great.

261
00:14:26,050 --> 00:14:28,260
Let's see if we format this differently.

262
00:14:28,270 --> 00:14:29,410
Does it look nicer?

263
00:14:30,440 --> 00:14:32,240
Yeah, I guess this looks slightly nicer.

264
00:14:32,270 --> 00:14:32,750
There we go.

265
00:14:32,750 --> 00:14:33,680
That's a little prettier.

266
00:14:33,770 --> 00:14:36,700
Okay, so that was it for the challenge.

267
00:14:36,710 --> 00:14:37,430
Moving on.

268
00:14:37,430 --> 00:14:38,960
We've got a really important topic.

269
00:14:38,960 --> 00:14:40,250
Aggregate functions.