1
00:00:00,270 --> 00:00:02,630
All right, solution time for the exercises.

2
00:00:02,640 --> 00:00:10,770
The first exercise is to take this sentence, and we want to reverse it in uppercase it at the same

3
00:00:10,770 --> 00:00:11,400
time.

4
00:00:11,610 --> 00:00:15,570
So that is as simple as I'll do it in the workbench.

5
00:00:15,600 --> 00:00:25,950
I'll take this sentence and select the reverse of the uppercase, which I also could do use case of

6
00:00:25,950 --> 00:00:26,700
this string.

7
00:00:27,000 --> 00:00:28,410
The order doesn't really matter.

8
00:00:28,410 --> 00:00:32,009
Do we uppercase and then reverse, or do we reverse and then uppercase?

9
00:00:32,100 --> 00:00:32,790
It doesn't matter.

10
00:00:32,790 --> 00:00:34,170
But let's run this.

11
00:00:34,940 --> 00:00:36,920
And that's what we get back.

12
00:00:37,490 --> 00:00:41,540
Right there, this lovely piece of text.

13
00:00:41,540 --> 00:00:45,020
I can also, of course, just paste it over here, and that's what we get.

14
00:00:45,050 --> 00:00:50,140
Why does my cat look at me with such hatred?

15
00:00:50,150 --> 00:00:52,400
Question mark, but in reverse.

16
00:00:53,270 --> 00:00:55,730
Next up, what does this printout?

17
00:00:55,910 --> 00:00:59,500
So we evaluate this from the inside out, right?

18
00:00:59,510 --> 00:01:02,990
This cat is nested in the replace, so we start with the cat.

19
00:01:03,080 --> 00:01:04,459
What does this give us?

20
00:01:04,489 --> 00:01:06,390
I space like space cats.

21
00:01:06,410 --> 00:01:07,880
It gives us gives us the sentence.

22
00:01:07,880 --> 00:01:13,310
I like cats, but immediately afterwards we have our replace which takes that sentence.

23
00:01:13,310 --> 00:01:20,180
I like cats and replaces all spaces with dashes so our output will look something like this.

24
00:01:20,360 --> 00:01:23,960
I like cats.

25
00:01:24,710 --> 00:01:25,280
Let's see.

26
00:01:25,280 --> 00:01:26,120
Am I correct?

27
00:01:26,660 --> 00:01:27,660
And there it is.

28
00:01:27,680 --> 00:01:34,120
I like cats so we can cat with the spaces first and then replace that string.

29
00:01:34,130 --> 00:01:36,080
Replace all spaces with dashes.

30
00:01:37,320 --> 00:01:44,100
Next up, we're going to write a query from our select from our books table to work with the titles,

31
00:01:44,100 --> 00:01:47,550
replace the spaces in the titles with a arrow.

32
00:01:48,330 --> 00:01:51,210
So let's do this again over in an editor.

33
00:01:51,870 --> 00:01:52,800
Let's do it here.

34
00:01:53,400 --> 00:01:54,420
Select.

35
00:01:54,810 --> 00:01:59,130
And then we want to replace from the title.

36
00:01:59,130 --> 00:02:05,190
We want to replace space characters with an arrow from books.

37
00:02:05,820 --> 00:02:07,530
And if we run that.

38
00:02:10,440 --> 00:02:11,910
Look at the output here.

39
00:02:12,760 --> 00:02:19,570
That looks correct, except for one thing, which remember all of these tables or all of these outputs

40
00:02:19,570 --> 00:02:24,220
I've shown you have customized or alias column names there.

41
00:02:24,580 --> 00:02:26,120
So let's change that.

42
00:02:26,140 --> 00:02:31,720
Let's take this and do an as title from books.

43
00:02:32,170 --> 00:02:33,090
Paste this over here.

44
00:02:33,100 --> 00:02:33,840
Make sure it works.

45
00:02:33,850 --> 00:02:34,630
There we are.

46
00:02:34,930 --> 00:02:35,670
It looks good.

47
00:02:35,680 --> 00:02:42,100
If I didn't have that as title, then we just end up with this sort of ugly heading that just says the

48
00:02:42,100 --> 00:02:43,540
operation, the function name.

49
00:02:44,140 --> 00:02:48,340
All right, so that's how we can replace in every title all spaces with arrows.

50
00:02:49,000 --> 00:02:51,940
Next up, print this out.

51
00:02:52,210 --> 00:02:56,590
So we have the last name of an author and then the last name in reverse.

52
00:02:56,590 --> 00:02:58,480
And it's called Forwards and Backwards.

53
00:02:58,480 --> 00:03:00,520
So let's start over here.

54
00:03:02,160 --> 00:03:03,180
Shrink you down.

55
00:03:03,180 --> 00:03:07,350
Let's select the easy one is just author.

56
00:03:07,350 --> 00:03:13,050
I think it's author ll name from author or from books.

57
00:03:14,160 --> 00:03:15,450
Let's just start with that.

58
00:03:15,690 --> 00:03:19,380
Make sure that works and that gives us the last name.

59
00:03:19,380 --> 00:03:20,010
It's great.

60
00:03:20,160 --> 00:03:28,890
But then I want to also, with a comma, select the reverse of author L name.

61
00:03:30,890 --> 00:03:31,680
From.

62
00:03:31,700 --> 00:03:33,110
And I messed that up.

63
00:03:33,260 --> 00:03:38,820
This needs to go right after our first column, then our second one from books.

64
00:03:38,840 --> 00:03:39,800
Let's run it.

65
00:03:41,030 --> 00:03:45,290
And just as a reminder, when I click that second lightning bolt, it's just running where my cursor

66
00:03:45,290 --> 00:03:45,530
is.

67
00:03:45,530 --> 00:03:47,420
So none of these other queries are running.

68
00:03:48,360 --> 00:03:50,130
And that looks good, right?

69
00:03:50,130 --> 00:03:52,650
Lahiri And then Eerie Hall or whatever.

70
00:03:52,650 --> 00:03:54,630
It's supposed to be pronounced backwards.

71
00:03:55,290 --> 00:03:57,780
The only thing we have remaining is to rename our columns.

72
00:03:57,780 --> 00:04:05,340
So I'm going to call this one as forwards, and this one will be as backwards.

73
00:04:05,340 --> 00:04:07,980
Or did I call it reverse now, forwards and backwards.

74
00:04:08,220 --> 00:04:08,700
All right.

75
00:04:08,700 --> 00:04:11,580
And then we could format this as well if we really wanted to.

76
00:04:12,300 --> 00:04:13,500
Does that make it much better?

77
00:04:13,530 --> 00:04:14,590
Maybe a little bit.

78
00:04:14,610 --> 00:04:16,380
Let's run it in my console this time.

79
00:04:16,380 --> 00:04:17,130
And there we are.

80
00:04:17,160 --> 00:04:19,079
Forwards and backwards.

81
00:04:20,829 --> 00:04:25,210
Next up, print out the full name of each author in caps.

82
00:04:25,330 --> 00:04:29,470
So full name means their first name and last name smushed together with a space.

83
00:04:29,470 --> 00:04:33,160
So that's going to be using canned cat or we're going to do that using con cat.

84
00:04:34,210 --> 00:04:38,020
Let's do it again over here in a file.

85
00:04:38,740 --> 00:04:45,610
So let's start by worrying about the con cat and we'll wait to worry about the upper casing.

86
00:04:45,610 --> 00:04:56,770
So select we're going to concatenate author f name with a space with author l name from books.

87
00:04:56,770 --> 00:05:01,240
And that should hopefully just give us first and last name with a space.

88
00:05:01,240 --> 00:05:02,350
Looks good to me.

89
00:05:02,500 --> 00:05:03,370
Jhumpa Lahiri.

90
00:05:03,370 --> 00:05:04,180
Neil Gaiman.

91
00:05:04,180 --> 00:05:05,170
The space is there.

92
00:05:05,650 --> 00:05:07,480
So now we need to uppercase.

93
00:05:07,480 --> 00:05:13,510
And there's a couple of ways of doing this, of course, but the simplest one is to uppercase after

94
00:05:13,510 --> 00:05:14,410
we can cat.

95
00:05:14,410 --> 00:05:19,600
But we could have uppercase this and uppercase this and then concatenated them.

96
00:05:19,600 --> 00:05:25,060
I think it makes more sense to just upper the result of that concatenation.

97
00:05:25,480 --> 00:05:28,360
So if we try that, there we are.

98
00:05:28,390 --> 00:05:33,910
We're getting the full name with the space in uppercase, the last thing we need to do is rename the

99
00:05:33,910 --> 00:05:34,720
column there.

100
00:05:34,720 --> 00:05:42,730
Our output is supposed to say full name and caps, so let's do that as full name in caps just like that.

101
00:05:43,030 --> 00:05:46,870
And let's run this one in my shell again.

102
00:05:47,110 --> 00:05:49,360
Full name and caps looks good.

103
00:05:51,110 --> 00:05:53,720
Next up, we've got to make this blurb happen.

104
00:05:53,720 --> 00:06:01,010
So the blurb is the title of a book and then a space and was well, it's this whole piece of text here

105
00:06:01,010 --> 00:06:05,840
was released in and then the year and then of course we call it blurb.

106
00:06:06,260 --> 00:06:08,180
So we're going to do some concatenation.

107
00:06:08,180 --> 00:06:09,260
We know that.

108
00:06:09,260 --> 00:06:16,850
What are we Concatenating Well we're going to concatenate the book's title with was with a space at

109
00:06:16,850 --> 00:06:23,360
the beginning was released in and then another space because we need those spaces because the title

110
00:06:23,360 --> 00:06:24,740
doesn't have a trailing space.

111
00:06:25,490 --> 00:06:31,580
So we want that space was released in space and then the year and I believe what we're looking for is

112
00:06:31,580 --> 00:06:34,490
called released year.

113
00:06:34,730 --> 00:06:35,990
Let's just verify.

114
00:06:36,290 --> 00:06:38,060
Yes, released here.

115
00:06:38,420 --> 00:06:43,520
So we'll put that in here, released, underscore year from books.

116
00:06:43,820 --> 00:06:45,500
And if we run that.

117
00:06:46,930 --> 00:06:48,100
And expand this.

118
00:06:48,100 --> 00:06:50,170
It's really annoying that this keeps shrinking down.

119
00:06:50,200 --> 00:06:52,350
The Namesake was released in 2003.

120
00:06:52,360 --> 00:06:54,710
Norse mythology was released in 2016.

121
00:06:54,730 --> 00:06:56,080
That's what we're supposed to do.

122
00:06:56,110 --> 00:07:00,520
All we have left to do now is rename this use as.

123
00:07:01,460 --> 00:07:02,300
Blurb.

124
00:07:03,530 --> 00:07:04,490
Try it again.

125
00:07:05,810 --> 00:07:09,980
And it's probably impossible for you to see that it says blurb there, but it does.

126
00:07:10,100 --> 00:07:12,440
Let's just copy and paste it over to our shell.

127
00:07:12,470 --> 00:07:13,220
There we are.

128
00:07:13,460 --> 00:07:14,120
Blurb.

129
00:07:15,090 --> 00:07:19,370
OC Next up, print the book titles and the length of each title.

130
00:07:19,550 --> 00:07:24,200
So the title part is easy, and then the length of each title is in characters.

131
00:07:24,200 --> 00:07:29,600
So don't forget that we're going to do a select title.

132
00:07:30,360 --> 00:07:31,200
Comma.

133
00:07:31,590 --> 00:07:34,740
And then we want char length.

134
00:07:35,930 --> 00:07:39,620
Of the title from books just like that.

135
00:07:41,050 --> 00:07:42,930
And we should see.

136
00:07:42,940 --> 00:07:43,210
Yeah.

137
00:07:43,270 --> 00:07:44,800
The Namesake 12.

138
00:07:45,400 --> 00:07:46,240
Is that correct?

139
00:07:46,270 --> 00:07:47,380
Namesake 12.

140
00:07:47,380 --> 00:07:48,370
Norse mythology.

141
00:07:48,370 --> 00:07:49,210
15.

142
00:07:49,600 --> 00:07:51,970
And then we just need to rename the columns again.

143
00:07:52,000 --> 00:07:54,700
Character count is what that one should say.

144
00:07:54,850 --> 00:07:59,110
Because right now, if we run this, it says char length of title.

145
00:07:59,410 --> 00:08:03,310
So let's rename that as character count.

146
00:08:04,360 --> 00:08:07,240
And copy that pasted here.

147
00:08:07,250 --> 00:08:12,400
And just as a reminder, I'm only pasting it over here because it's impossible for you to see the results

148
00:08:12,400 --> 00:08:15,280
and the name of each one of these columns.

149
00:08:15,280 --> 00:08:18,550
And if we take a look, it looks good character count and title.

150
00:08:19,300 --> 00:08:22,540
And I think we have one more, the most complicated one.

151
00:08:22,540 --> 00:08:24,160
We have three pieces of information.

152
00:08:24,160 --> 00:08:26,200
Let's start with the short title.

153
00:08:26,200 --> 00:08:28,990
It's the first ten characters, so it's a substring.

154
00:08:29,500 --> 00:08:33,820
And then we concatenate, but let's just get the substring of the title working first.

155
00:08:33,820 --> 00:08:35,740
We actually saw how to do this already.

156
00:08:35,740 --> 00:08:41,799
It's substring or sub str of the title.

157
00:08:41,799 --> 00:08:44,140
And then we have to say what character we start at.

158
00:08:44,240 --> 00:08:47,620
So position one and how many characters we move on for.

159
00:08:47,620 --> 00:08:50,170
Ten, I think is what we wanted.

160
00:08:50,170 --> 00:08:51,370
Let's verify.

161
00:08:52,660 --> 00:08:54,910
The name say essay.

162
00:08:54,940 --> 00:08:56,370
That's what we were supposed to have.

163
00:08:56,380 --> 00:08:57,610
American Gee.

164
00:08:57,640 --> 00:08:58,630
Looks good to me.

165
00:08:59,160 --> 00:09:03,310
OC Then we want to concatenate that with a dot, dot, dot.

166
00:09:03,310 --> 00:09:03,550
Right.

167
00:09:03,580 --> 00:09:12,790
So I'm going to take my cat, wrap it around this substrate and then add in dot, dot, dot.

168
00:09:12,790 --> 00:09:18,940
So this should then concatenate the shortened version, the substring with the dot, dot, dot.

169
00:09:19,180 --> 00:09:22,090
And we want to call that short title.

170
00:09:22,090 --> 00:09:25,270
So we'll start with that as short.

171
00:09:27,280 --> 00:09:27,910
Title.

172
00:09:29,030 --> 00:09:31,070
All right, let's format this.

173
00:09:31,070 --> 00:09:38,030
And now we have one third of what we need, at least in terms of the number of columns, maybe not in

174
00:09:38,030 --> 00:09:39,230
terms of the amount of work.

175
00:09:39,410 --> 00:09:43,430
Now, let's move on to the next piece, which is the author.

176
00:09:43,430 --> 00:09:48,740
But our author field here is going to have the author's last name, a comma, then their first name.

177
00:09:49,040 --> 00:09:53,720
So we're going to concatenate once again the author's last name.

178
00:09:53,720 --> 00:09:57,080
So author, LL name and then a comma.

179
00:09:57,980 --> 00:10:00,720
And you could technically use that W.

180
00:10:00,830 --> 00:10:03,260
S for the separator and put the comma first.

181
00:10:03,260 --> 00:10:04,700
But this is easy enough.

182
00:10:04,700 --> 00:10:10,220
And then author last name and I first name rather, because it's last.

183
00:10:10,220 --> 00:10:13,580
Then first and we're calling this author, I believe.

184
00:10:13,580 --> 00:10:16,310
Make sure I have that correct as author.

185
00:10:16,310 --> 00:10:17,630
Let's run this one.

186
00:10:18,980 --> 00:10:23,660
Author Lahiri comma, Jhumpa Gaiman, comma, Neil Eggers, comma, Dave.

187
00:10:23,660 --> 00:10:24,830
It's what it's supposed to be.

188
00:10:24,830 --> 00:10:28,820
And then our quantity is supposed to be the number in stock.

189
00:10:29,120 --> 00:10:30,860
So that's going to be another.

190
00:10:30,900 --> 00:10:36,830
Venkat And this time we're contacting can catting the number that we have.

191
00:10:36,830 --> 00:10:39,860
I think it's called stock Quantity can Verify.

192
00:10:39,860 --> 00:10:41,270
Again, Select Star.

193
00:10:42,550 --> 00:10:48,070
From books and it is indeed called stock quantity.

194
00:10:48,070 --> 00:10:52,180
So stock quantity.

195
00:10:52,420 --> 00:11:01,780
And then we're concatenating that with in stock or technically space in stock as.

196
00:11:02,590 --> 00:11:04,960
Quantity, I think, is what it's supposed to be called.

197
00:11:05,590 --> 00:11:06,880
Moment of truth here.

198
00:11:06,910 --> 00:11:07,590
Oof!

199
00:11:08,020 --> 00:11:09,580
There's a lot to this exercise.

200
00:11:10,420 --> 00:11:11,190
Let's see.

201
00:11:11,200 --> 00:11:12,250
32 in stock.

202
00:11:12,250 --> 00:11:13,350
43 in stock.

203
00:11:13,360 --> 00:11:14,500
12 in stock.

204
00:11:14,500 --> 00:11:15,740
97 in stock.

205
00:11:15,760 --> 00:11:17,080
That looks good to me.

206
00:11:17,350 --> 00:11:21,400
104 in stock for a heart breaking work of staggering genius.

207
00:11:22,030 --> 00:11:23,770
That's it for this exercise.

208
00:11:23,800 --> 00:11:24,280
Woof.

209
00:11:24,310 --> 00:11:26,860
There's a lot a lot of practice with string functions.

210
00:11:26,890 --> 00:11:31,840
Next up, we're going to continue leveling up our skills and learn how to refine our selections even

211
00:11:31,840 --> 00:11:32,440
further.