1
00:00:00,240 --> 00:00:01,170
All right.

2
00:00:01,170 --> 00:00:06,420
So hopefully you made it through those exercises, or maybe you're just wanting to watch the solutions,

3
00:00:06,840 --> 00:00:08,290
or maybe you're here accidentally.

4
00:00:08,310 --> 00:00:13,890
However you got here, what I'm going to do in this video is go over the solutions to the previous video,

5
00:00:15,060 --> 00:00:16,710
and that's a bad noise.

6
00:00:17,190 --> 00:00:18,000
Did that happen?

7
00:00:18,480 --> 00:00:19,080
All right.

8
00:00:19,380 --> 00:00:23,010
So the first one, select all story collections that.

9
00:00:23,010 --> 00:00:27,270
So those are titles that contain the word stories in them somewhere.

10
00:00:27,720 --> 00:00:29,250
And we just want to print the title.

11
00:00:30,210 --> 00:00:32,790
So I'm going to go over here and make some space.

12
00:00:33,180 --> 00:00:41,580
And the first thing that I'll do is just make a new file and I'll just call it exercises dot SQL, and

13
00:00:41,580 --> 00:00:51,180
then I'll open that up and we'll start off with this first one select title from books and we'll start

14
00:00:51,180 --> 00:00:51,480
there.

15
00:00:51,480 --> 00:01:00,960
Just make sure we're doing everything correctly, source refining selections, slash exercises, SQL,

16
00:01:00,990 --> 00:01:03,750
make sure that it's working, selecting the titles.

17
00:01:04,200 --> 00:01:07,530
So now we only want the ones that contain stories.

18
00:01:07,680 --> 00:01:16,680
So that's going to be aware and we're going to do title like and this is if we just do stories, it's

19
00:01:16,680 --> 00:01:17,520
not going to work.

20
00:01:17,820 --> 00:01:20,100
That gives us exactly stories.

21
00:01:20,400 --> 00:01:23,910
We're going to want that wild card character just like that.

22
00:01:25,060 --> 00:01:27,340
And now we get what we're looking for.

23
00:01:27,850 --> 00:01:33,400
Technically with our specific data, we could leave that last percentage, sign off that last wild card,

24
00:01:33,400 --> 00:01:37,180
because all of the books that contain the word stories, it comes at the end.

25
00:01:37,780 --> 00:01:39,880
But that may not always be the case.

26
00:01:40,120 --> 00:01:46,450
There might be a book out there called My Favorite Stories for Kids or something like that.

27
00:01:47,260 --> 00:01:53,050
And if we wanted this truly to just find all books that have stories in the title, we need both of

28
00:01:53,050 --> 00:01:54,640
those wild card characters.

29
00:01:55,270 --> 00:01:55,840
All right.

30
00:01:55,840 --> 00:01:57,070
So that's the first one.

31
00:01:58,000 --> 00:02:01,030
Next up, find the longest book.

32
00:02:01,330 --> 00:02:03,040
So print out the title and the page.

33
00:02:03,040 --> 00:02:05,830
Count for the one longest book.

34
00:02:06,190 --> 00:02:13,870
So we'll go ahead and comment this line out and let's just do a select title and pages from books just

35
00:02:13,870 --> 00:02:15,040
like that to start.

36
00:02:16,630 --> 00:02:22,030
So now what we want to do is sort them, we'll sort them by the pages.

37
00:02:22,570 --> 00:02:25,510
So that's an order by pages.

38
00:02:25,510 --> 00:02:27,310
And if we just do the default sort.

39
00:02:28,570 --> 00:02:32,980
Remember that we get ascending rather than descending, which is what we want.

40
00:02:33,010 --> 00:02:36,370
What we're going to do is use limit to give us the first result.

41
00:02:36,370 --> 00:02:42,820
But right now, if we do limit one, it's going to give us the shortest book rather than the longest

42
00:02:42,820 --> 00:02:43,300
one.

43
00:02:43,780 --> 00:02:49,000
So we want to reverse that sort with the descending order.

44
00:02:50,150 --> 00:02:52,140
So now the first one is the longest.

45
00:02:52,500 --> 00:02:58,530
And then we just add our limit one and we run it again and again and again.

46
00:02:58,710 --> 00:03:00,100
And it always works.

47
00:03:00,120 --> 00:03:05,670
And so whatever book has the longest page count will always be what is printed out here.

48
00:03:06,510 --> 00:03:08,130
All right, so that's that one.

49
00:03:08,130 --> 00:03:10,710
Let's comment it out and move on.

50
00:03:11,790 --> 00:03:16,710
Print out a summary containing the title and year for the three most recent books.

51
00:03:16,710 --> 00:03:25,230
So this one required you to not only use order by and limit we're ordering by most recent year and then

52
00:03:25,230 --> 00:03:26,520
limiting to three.

53
00:03:26,520 --> 00:03:32,070
But you also needed to use Concatenate to concatenate the title with a dash with a year.

54
00:03:33,060 --> 00:03:34,380
So let's do that now.

55
00:03:34,560 --> 00:03:41,070
Let's just start by doing the select Kann cat and it was title comma.

56
00:03:41,380 --> 00:03:46,500
Then we have a space and a dash and a space comma released here.

57
00:03:48,780 --> 00:03:52,980
And we'll start with just that from Books Save.

58
00:03:54,740 --> 00:03:55,230
Okay.

59
00:03:55,910 --> 00:03:58,370
Oh, the other thing we wanted to do is clean this up.

60
00:03:58,370 --> 00:03:59,830
So it says summery up there.

61
00:03:59,840 --> 00:04:03,440
So this is also review, we'll say as summery.

62
00:04:05,170 --> 00:04:05,860
Okay.

63
00:04:06,040 --> 00:04:07,030
So now we get that.

64
00:04:07,420 --> 00:04:10,660
Now we want to order them by most recent.

65
00:04:10,900 --> 00:04:13,780
And let's break this up on to some different lines here.

66
00:04:15,970 --> 00:04:22,089
From Books Order by and we'll order by released here.

67
00:04:23,920 --> 00:04:30,970
And if we try that now, we also see that it does work, but it's in the opposite order that we want

68
00:04:30,970 --> 00:04:32,470
we want most recent.

69
00:04:32,470 --> 00:04:35,230
So we need to reverse that again with descending.

70
00:04:36,940 --> 00:04:46,750
And then finally, we want to limit it to the first three, limit three or if you want to get fancy,

71
00:04:46,750 --> 00:04:51,730
we could do this, but I don't like doing that, so that's much easier.

72
00:04:53,050 --> 00:05:00,460
So that gives us Lincoln in the Bardo Dash 2017 Norse Mythology Desk 2016 10% happier dash 2014 summary.

73
00:05:00,670 --> 00:05:01,780
That's all good to go.

74
00:05:02,800 --> 00:05:09,310
Next up, find all the books that have an author last name containing a space and don't cheat by just

75
00:05:09,310 --> 00:05:10,720
doing Foster Wallace.

76
00:05:11,500 --> 00:05:13,870
So let's comment this out first.

77
00:05:14,410 --> 00:05:16,210
And this one is actually pretty short and easy.

78
00:05:16,210 --> 00:05:25,390
It's just a select and we're looking for title and author l name select title comma author l name where

79
00:05:26,050 --> 00:05:28,750
author l name like.

80
00:05:29,530 --> 00:05:35,620
And of course, we can't just say space because that's only going to find authors whose last name is

81
00:05:35,620 --> 00:05:36,700
exactly space.

82
00:05:37,420 --> 00:05:41,260
And it looks like, Oh, I'm forgetting from books.

83
00:05:41,350 --> 00:05:43,090
Hopefully you caught that before I did.

84
00:05:43,900 --> 00:05:48,880
But back to what I was saying, this isn't going to work because we don't have any authors whose last

85
00:05:48,880 --> 00:05:49,930
name is space.

86
00:05:49,930 --> 00:05:53,170
So what we want is that wild card.

87
00:05:55,830 --> 00:05:59,610
On either side, anything with a space and then anything afterwards.

88
00:06:00,600 --> 00:06:03,750
Now we get Foster Wallace, and that's it for that one.

89
00:06:05,820 --> 00:06:09,330
Next up, find the three books with the lowest stock.

90
00:06:09,600 --> 00:06:13,260
So we want the title, the release here and the stock quantity.

91
00:06:13,800 --> 00:06:15,570
Three books with the lowest stock.

92
00:06:15,810 --> 00:06:21,990
So let's start with commenting this out just like we ve done with all the others select title.

93
00:06:22,860 --> 00:06:25,380
But also is it released here in stock quantity.

94
00:06:29,280 --> 00:06:30,240
Just like that.

95
00:06:31,170 --> 00:06:33,390
And we'll have our from books.

96
00:06:35,370 --> 00:06:37,470
We'll start there, make sure that works.

97
00:06:38,300 --> 00:06:38,830
Okay.

98
00:06:38,880 --> 00:06:41,910
So now what we want to do is sort them by stock quantity.

99
00:06:41,910 --> 00:06:45,510
So that's just an order by stock quantity.

100
00:06:47,880 --> 00:06:49,920
And you can see that gives us the order we want.

101
00:06:49,950 --> 00:06:54,630
We want them to be in ascending order this time rather than descending, because we're looking for the

102
00:06:54,630 --> 00:06:58,470
lowest stock quantity 12 all the way up to 1000.

103
00:06:59,940 --> 00:07:02,130
And then we want to limit it to three.

104
00:07:02,910 --> 00:07:05,730
I don't know why I picked three so many times in these exercises.

105
00:07:07,530 --> 00:07:09,810
Let's try it now with that limit three.

106
00:07:10,260 --> 00:07:15,600
And there we go, American gods, where I'm calling from, what we talk about when we talk about love,

107
00:07:16,800 --> 00:07:17,910
that's what we're looking for.

108
00:07:19,790 --> 00:07:25,460
Then we've got this one print the title and author last name sorted first by author last name and then

109
00:07:25,460 --> 00:07:26,210
by title.

110
00:07:27,080 --> 00:07:28,520
So let's get the title and author.

111
00:07:28,520 --> 00:07:30,320
Last name to print first.

112
00:07:31,430 --> 00:07:32,210
Select.

113
00:07:34,230 --> 00:07:37,470
Title comma author l name from books.

114
00:07:38,160 --> 00:07:39,240
Just like that.

115
00:07:40,110 --> 00:07:44,100
And at this point, we all know what that does.

116
00:07:44,820 --> 00:07:47,970
So now let's sort by the author last name.

117
00:07:47,970 --> 00:07:51,720
So that's just a simple order by author LL name.

118
00:07:53,370 --> 00:07:56,790
And that gives us the alphabetical order we're looking for so we don't need to use descending.

119
00:07:56,790 --> 00:07:57,660
That's all right.

120
00:07:58,170 --> 00:08:03,900
But then if you look over here, if we take Carver's books, notice that where I'm calling from comes

121
00:08:03,910 --> 00:08:10,050
before what we talk about when we talk about love, even though w h a comes before w h e.

122
00:08:10,440 --> 00:08:13,200
So we want to now sort by title there.

123
00:08:13,200 --> 00:08:20,280
And the way we do that is not by just changing that to B title, because then it purely starts by title

124
00:08:20,280 --> 00:08:23,040
and ignores author l name rather.

125
00:08:23,070 --> 00:08:25,740
We want to do this.

126
00:08:25,740 --> 00:08:27,870
Author L name comma title.

127
00:08:30,660 --> 00:08:31,890
And there we go.

128
00:08:32,460 --> 00:08:40,020
Now, you could also replace this whole thing with two comma one, which I really don't like, just

129
00:08:40,020 --> 00:08:42,150
because it's not very semantic.

130
00:08:42,179 --> 00:08:45,390
It's not meaningful to look at this, but it is shorter.

131
00:08:45,600 --> 00:08:49,760
And again, that too is referring to author L name, and one is referring to title.

132
00:08:49,770 --> 00:08:51,610
It's just the order that we're selecting them in.

133
00:08:52,260 --> 00:09:00,450
So I'm going to change it back, but I will also duplicate that below and change it to two.

134
00:09:00,450 --> 00:09:03,020
Come on one and comment that out as well.

135
00:09:03,030 --> 00:09:04,650
Just so we have both solutions.

136
00:09:05,040 --> 00:09:07,110
Which brings us to the last problem.

137
00:09:07,530 --> 00:09:08,430
Hopefully it's the last.

138
00:09:08,670 --> 00:09:09,190
It is.

139
00:09:09,240 --> 00:09:10,470
Make this happen.

140
00:09:10,560 --> 00:09:12,480
So we want to yell.

141
00:09:12,510 --> 00:09:13,980
My favorite author is.

142
00:09:13,980 --> 00:09:16,710
And then the uppercase first name of an author.

143
00:09:16,710 --> 00:09:19,230
And then a space in the uppercase last name of an author.

144
00:09:19,230 --> 00:09:21,150
And then exclamation point.

145
00:09:21,900 --> 00:09:23,310
Yeah, it's kind of a lot.

146
00:09:23,670 --> 00:09:26,550
Okay, so let's start with selecting.

147
00:09:26,970 --> 00:09:30,950
And we know already that well, we can start with select from books.

148
00:09:30,960 --> 00:09:31,490
Sure.

149
00:09:32,910 --> 00:09:35,640
What we're going to want to do is concatenate.

150
00:09:35,640 --> 00:09:42,510
My favorite author is and then an uppercase version of Raymond and a space and then uppercase version

151
00:09:42,840 --> 00:09:43,980
of the last name.

152
00:09:44,160 --> 00:09:45,600
So an exclamation point.

153
00:09:45,630 --> 00:09:47,070
It's a lot of concatenation.

154
00:09:47,670 --> 00:09:51,840
So we're going to have a King Cat and I'll just do this.

155
00:09:52,230 --> 00:09:53,340
Well, let's do it this way.

156
00:09:53,430 --> 00:09:54,290
Just like that.

157
00:09:54,300 --> 00:09:55,650
What are we, Concatenating?

158
00:09:55,920 --> 00:09:58,890
Well, let's start without the uppercase or without.

159
00:09:58,890 --> 00:10:11,760
So let's just start with my favorite author is and we'll add a space comma and then we want author f

160
00:10:11,760 --> 00:10:19,560
name and then we want another space, this space here so they don't just run together.

161
00:10:19,710 --> 00:10:20,490
Comma.

162
00:10:20,790 --> 00:10:21,480
Author.

163
00:10:21,510 --> 00:10:22,440
LL Name.

164
00:10:23,070 --> 00:10:25,410
Finally an exclamation point.

165
00:10:25,440 --> 00:10:26,190
It's a lot.

166
00:10:27,350 --> 00:10:30,170
So let me drag this down a bit so we can see the whole query.

167
00:10:30,440 --> 00:10:34,070
Select the concatenation of my favorite author is Space.

168
00:10:34,550 --> 00:10:37,570
The first name of an author space the last name of an author.

169
00:10:37,580 --> 00:10:38,630
Exclamation point.

170
00:10:39,290 --> 00:10:40,550
Let's save it and run it.

171
00:10:42,250 --> 00:10:43,240
It's pretty long.

172
00:10:43,420 --> 00:10:51,610
Oh, and this is partially messed up because you can see up here the column heading here is this entire

173
00:10:51,610 --> 00:10:51,810
thing.

174
00:10:51,820 --> 00:10:54,310
So let's add the as yellow.

175
00:10:56,830 --> 00:10:58,120
And now if I run it.

176
00:10:58,330 --> 00:10:59,140
There we go.

177
00:10:59,750 --> 00:11:02,230
Okay, so we're close.

178
00:11:02,350 --> 00:11:08,790
The next thing we need to do is capitalize author f name and author last name, which is easy.

179
00:11:08,810 --> 00:11:10,000
Just an upper.

180
00:11:12,800 --> 00:11:13,520
Like that.

181
00:11:14,240 --> 00:11:15,830
Save re run it.

182
00:11:16,700 --> 00:11:17,510
It's all uppercase.

183
00:11:17,510 --> 00:11:22,160
Now we have one last thing which is sorting alphabetically by the last name.

184
00:11:23,450 --> 00:11:27,620
So from books order by author.

185
00:11:29,240 --> 00:11:32,820
Any long query here.

186
00:11:33,360 --> 00:11:34,350
Let's try it.

187
00:11:35,850 --> 00:11:36,870
It looks like it worked.

188
00:11:36,900 --> 00:11:40,500
We got Raymond Carver all the way down to my favorite author is John Steinbeck.

189
00:11:40,950 --> 00:11:42,850
And that's about as loud as I'll yell in here.

190
00:11:42,880 --> 00:11:43,970
Don't want to wake my neighbors.

191
00:11:43,980 --> 00:11:44,720
All right.

192
00:11:44,730 --> 00:11:46,080
So congratulations.

193
00:11:46,080 --> 00:11:49,120
Hopefully, you made it through this relatively unharmed.

194
00:11:49,140 --> 00:11:52,530
It's a lot of exercises, a lot of ordering by and limiting.

195
00:11:52,800 --> 00:11:54,930
But the reality is you'll use those a lot.

196
00:11:54,960 --> 00:12:01,530
It is very useful to be able to determine the the I mean, highest page count, maybe not, but the

197
00:12:01,530 --> 00:12:07,200
best selling book once we get to to sales and users and stuff for the most active users or like I keep

198
00:12:07,200 --> 00:12:13,710
coming back to the highest commented post or the most trafficked the most clicked on add.

199
00:12:13,710 --> 00:12:20,580
All those sorts of bits of data are really useful and you'll use order, buy and limit all the time

200
00:12:20,580 --> 00:12:24,590
as well as other tools that we saw distinct and like they're all very useful.

201
00:12:24,600 --> 00:12:25,230
All right.

202
00:12:25,710 --> 00:12:29,580
Well, I recommend you take a quick little break and then we'll move on.