1
00:00:00,510 --> 00:00:02,280
Let's go through some of these solutions.

2
00:00:02,700 --> 00:00:09,630
So the first one verse in uppercase, this following sentence Why does my cat look at me with such hatred?

3
00:00:10,410 --> 00:00:11,730
So I'm just going to copy it.

4
00:00:12,000 --> 00:00:15,900
And the first thing I'm actually going to do is make a new file.

5
00:00:16,470 --> 00:00:23,220
So I'll come up here and do new file and I'll just call this exercises dot SQL.

6
00:00:24,600 --> 00:00:27,140
Then in there, I'll paste this.

7
00:00:27,150 --> 00:00:29,510
And we know that we need to do two things.

8
00:00:29,520 --> 00:00:31,820
We need to reverse it in uppercase.

9
00:00:31,830 --> 00:00:33,540
The order doesn't really matter.

10
00:00:33,840 --> 00:00:36,750
So let's do a we need to do a select first.

11
00:00:36,750 --> 00:00:44,040
Either way, select reverse and we're going to select the reverse of this.

12
00:00:44,730 --> 00:00:48,210
And then we can do upper here.

13
00:00:48,930 --> 00:00:49,890
Just like that.

14
00:00:51,660 --> 00:00:55,080
Or we could switch them and do select upper reverse.

15
00:00:55,590 --> 00:00:56,820
But let's give it a shot.

16
00:00:58,470 --> 00:01:01,110
And I'm missing my quote there.

17
00:01:01,140 --> 00:01:04,739
Let's get out of here, missing that closing quote.

18
00:01:05,190 --> 00:01:05,880
There we go.

19
00:01:06,360 --> 00:01:07,290
Try it again.

20
00:01:09,940 --> 00:01:10,860
And there we go.

21
00:01:10,870 --> 00:01:13,270
It's reversed and all uppercase.

22
00:01:13,780 --> 00:01:17,230
So now I'm going to comment that out and we'll move on to the next one.

23
00:01:18,670 --> 00:01:20,140
What does this print out?

24
00:01:20,680 --> 00:01:23,350
So let's start on the inside.

25
00:01:23,530 --> 00:01:29,260
Concatenate I with the space with like space and cats.

26
00:01:29,260 --> 00:01:33,010
So it will be I like cats, the sentence with spaces.

27
00:01:33,370 --> 00:01:39,160
But then we have a replace and replace is going to get rid of all those spaces and put dashes.

28
00:01:39,370 --> 00:01:43,360
So it should be I dash like dash cats.

29
00:01:44,230 --> 00:01:45,160
Let's try it.

30
00:01:48,580 --> 00:01:49,300
There we go.

31
00:01:49,690 --> 00:01:51,730
I like cats with dashes.

32
00:01:53,020 --> 00:01:54,490
So that one is really just testing that.

33
00:01:54,490 --> 00:02:01,330
You understand how these are actually executed and that you can stack multiple or nest multiple functions.

34
00:02:02,560 --> 00:02:05,710
Next up, we have our first one really working with the books data.

35
00:02:05,950 --> 00:02:09,669
So replace all spaces in book titles with the arrow.

36
00:02:11,410 --> 00:02:19,390
So we need to do a select and we know that there's going to be a replace and we know that it's going

37
00:02:19,390 --> 00:02:21,400
to be from books.

38
00:02:21,400 --> 00:02:22,630
So let's start with that.

39
00:02:23,500 --> 00:02:24,920
So what are we replacing?

40
00:02:24,940 --> 00:02:32,620
Well, we're looking for all titles and we're replacing a space with the arrow.

41
00:02:33,430 --> 00:02:36,300
Double check, replace spaces with arrows.

42
00:02:36,310 --> 00:02:37,330
That should be good.

43
00:02:37,810 --> 00:02:44,980
And the other thing was that up here we had title, so we need to use as title.

44
00:02:46,180 --> 00:02:47,380
So let's copy it.

45
00:02:47,980 --> 00:02:49,090
Make sure it works.

46
00:02:49,930 --> 00:02:50,650
There we go.

47
00:02:50,680 --> 00:02:55,510
Title the arrow, namesake, Norse Arrow mythology and so on.

48
00:02:56,170 --> 00:03:00,040
I'll comment that one out and we'll move on to the next one.

49
00:03:00,520 --> 00:03:01,720
Print this out.

50
00:03:02,050 --> 00:03:09,280
So we should have forwards and backwards and this is last name printed regularly and this is last name

51
00:03:09,280 --> 00:03:10,180
reversed.

52
00:03:12,370 --> 00:03:13,240
So let's start.

53
00:03:13,240 --> 00:03:20,230
We know we need to have a select and the first thing that we know is that we're going to have author

54
00:03:21,280 --> 00:03:25,930
l name as forwards.

55
00:03:26,500 --> 00:03:27,550
Just make sure I have that.

56
00:03:27,910 --> 00:03:35,080
So that's just that will give us this right here and let's close off let's do from books just like that

57
00:03:35,080 --> 00:03:42,580
so we can start with this if we wanted and we get that left portion now we need this right portion.

58
00:03:42,580 --> 00:03:46,590
So we need a comma because we're now selecting two different things.

59
00:03:46,600 --> 00:03:56,350
And what we want to do here is use reverse author l name as backwards.

60
00:03:57,250 --> 00:03:58,180
Just like that.

61
00:03:59,320 --> 00:04:00,370
Let's copy it.

62
00:04:02,730 --> 00:04:03,780
And there we go.

63
00:04:04,260 --> 00:04:05,700
Forwards, backwards.

64
00:04:05,820 --> 00:04:07,020
Regular last name.

65
00:04:07,020 --> 00:04:08,160
Reverse last name.

66
00:04:09,270 --> 00:04:12,090
All right, comment that one out and we move on.

67
00:04:13,890 --> 00:04:16,740
This one, we need to print a full name.

68
00:04:16,740 --> 00:04:20,190
So first and last name of the author, all in caps.

69
00:04:20,970 --> 00:04:22,890
So there's a couple of ways we could approach this.

70
00:04:23,370 --> 00:04:26,430
The easiest way is just to capitalize one string.

71
00:04:26,430 --> 00:04:29,940
So we combine the 2/1 and last and then capitalize.

72
00:04:30,270 --> 00:04:34,890
If you wanted to, though, you could take capital first and then capital second.

73
00:04:35,070 --> 00:04:38,850
Excuse me, capital first name, capital last name, then combine them.

74
00:04:38,850 --> 00:04:40,280
And that works just as well.

75
00:04:40,290 --> 00:04:43,350
But it's easier to just do it once and it's also faster.

76
00:04:44,220 --> 00:04:47,490
So the other thing is we have full name in caps up here.

77
00:04:47,640 --> 00:04:49,110
So we need to use as.

78
00:04:49,950 --> 00:04:52,650
So let's start by printing full name.

79
00:04:53,070 --> 00:05:03,270
So we have a select from books and what we need to do is can cat and whatever we can catting author

80
00:05:04,650 --> 00:05:08,580
f name comma author l name.

81
00:05:09,480 --> 00:05:14,130
And I'll leave it like this for now, but hopefully you can predict what the problem is.

82
00:05:14,400 --> 00:05:23,130
When I do that, we don't have spaces, so let's add our space in and that should give us the concatenated

83
00:05:23,130 --> 00:05:23,850
version.

84
00:05:24,330 --> 00:05:26,880
And well, if we add the right comma

85
00:05:29,340 --> 00:05:30,540
now let's try it.

86
00:05:31,350 --> 00:05:32,010
There we go.

87
00:05:32,010 --> 00:05:36,540
So we get Jhumpa Lahiri, Dave Eggers and so on with the spaces.

88
00:05:36,750 --> 00:05:39,120
So now what we want to do is just uppercase that.

89
00:05:40,140 --> 00:05:47,910
So we could do it on the same line because uppercase is so short, we can just say upper of that, copy

90
00:05:47,910 --> 00:05:51,030
it, paste it in.

91
00:05:51,750 --> 00:05:53,730
We also need to rename this up there.

92
00:05:53,730 --> 00:05:58,170
So we need as and what did I call it, full name and caps.

93
00:05:58,170 --> 00:05:59,700
So there are spaces in that.

94
00:05:59,700 --> 00:06:02,730
So we need to use quotes full name in caps.

95
00:06:04,200 --> 00:06:05,280
Copy that.

96
00:06:06,480 --> 00:06:07,370
There we go.

97
00:06:07,380 --> 00:06:08,790
Full name in caps.

98
00:06:09,480 --> 00:06:13,350
And if we want to format a little bit better here, we could do this.

99
00:06:18,330 --> 00:06:19,290
Just like that.

100
00:06:20,520 --> 00:06:21,960
And it we'll work just the same.

101
00:06:23,640 --> 00:06:25,950
OC Let's comment that one out and move on.

102
00:06:27,570 --> 00:06:28,520
Make this happen.

103
00:06:28,530 --> 00:06:35,610
So what we have again is the first excuse me, the title was released in and then the year.

104
00:06:35,670 --> 00:06:38,130
So we're going to need to make use of concatenate.

105
00:06:38,700 --> 00:06:43,320
So let's start off with our select and we'll have a cat.

106
00:06:44,790 --> 00:06:48,570
But we will also have from books just like everything else we've done.

107
00:06:49,350 --> 00:06:51,070
So what are we going to concatenate?

108
00:06:51,090 --> 00:06:52,770
Well, first is the title.

109
00:06:53,910 --> 00:06:59,100
After the title, we have space was released in space.

110
00:06:59,100 --> 00:07:04,050
So we need those spaces, comma like that.

111
00:07:04,920 --> 00:07:07,950
And we could just start with that if we want to take a look

112
00:07:10,980 --> 00:07:16,410
and we get the namesake was released in Norse mythology was released in then we just need to add in

113
00:07:16,440 --> 00:07:23,610
the year comma released year and we haven't really worked with a year in a while.

114
00:07:23,610 --> 00:07:29,010
So if you forgot what the name of that column is earlier I when I was recording something, I ended

115
00:07:29,010 --> 00:07:33,030
up having to redo it because I was referring to year rather than released year.

116
00:07:33,300 --> 00:07:40,890
So you could just do a described books and see that it's called released year.

117
00:07:41,880 --> 00:07:42,450
All right.

118
00:07:42,690 --> 00:07:50,190
So then we'll copy that in paste it and we get The Namesake was released in 2003 and so on.

119
00:07:50,430 --> 00:07:57,000
The only thing we need to change is this up here, it's supposed to be called blurb as blurb.

120
00:08:00,960 --> 00:08:02,070
And there we go.

121
00:08:02,790 --> 00:08:05,070
So if we're done with that one, comment it out.

122
00:08:07,490 --> 00:08:11,600
Next up, print book titles and the length of each title.

123
00:08:12,830 --> 00:08:14,420
So printing the title is easy.

124
00:08:14,900 --> 00:08:19,380
Then we just need to use char length and print the character length.

125
00:08:19,400 --> 00:08:20,750
So let's do that now.

126
00:08:21,050 --> 00:08:25,250
Select from books.

127
00:08:25,250 --> 00:08:26,550
And what do we want to select?

128
00:08:26,570 --> 00:08:32,690
Well, first we want title and then after we select the title, we want char length.

129
00:08:32,720 --> 00:08:37,789
And one thing I should highlight there is that because this is a SQL file, you might have noticed,

130
00:08:37,789 --> 00:08:45,110
I get these autocomplete suggestions so I can either click or I can hit tab and that's much faster in

131
00:08:45,110 --> 00:08:46,730
my experience is to hit tab.

132
00:08:47,150 --> 00:08:50,570
So we want the length of the title.

133
00:08:52,940 --> 00:08:54,140
Just like that.

134
00:08:57,260 --> 00:08:59,780
And we get the namesake 12 Norse mythology.

135
00:08:59,780 --> 00:09:07,910
15 And that's what we want, except we need character count, just like that.

136
00:09:07,940 --> 00:09:09,950
However, this will cause an error.

137
00:09:11,940 --> 00:09:12,250
Whoops.

138
00:09:12,830 --> 00:09:15,410
We need to put it in quotes because there's a space there.

139
00:09:17,420 --> 00:09:20,540
Now we give it a shot and it works.

140
00:09:21,560 --> 00:09:22,250
Right.

141
00:09:23,570 --> 00:09:25,460
Last one, I promise.

142
00:09:25,490 --> 00:09:27,850
So this one is more complex.

143
00:09:27,860 --> 00:09:29,630
Three different things we're selecting.

144
00:09:29,660 --> 00:09:32,600
Each one is complex on its own.

145
00:09:32,990 --> 00:09:34,620
So let's tackle the first bit.

146
00:09:34,640 --> 00:09:35,810
The short title.

147
00:09:36,200 --> 00:09:42,140
So we want the first ten characters of the title plus dot, dot, dot.

148
00:09:43,220 --> 00:09:44,540
So we'll comment this out.

149
00:09:45,590 --> 00:09:53,720
We'll do our same select from books and we'll start by selecting the first ten characters.

150
00:09:53,720 --> 00:09:55,330
So that's substring.

151
00:09:55,340 --> 00:10:00,080
Or if you're lazy, substring, whatever, let's do substring.

152
00:10:00,500 --> 00:10:04,670
And we want it of title from 1 to 10.

153
00:10:05,060 --> 00:10:06,050
Just like that.

154
00:10:07,400 --> 00:10:09,260
So that should just give us.

155
00:10:09,890 --> 00:10:11,870
At this point, you should know what it does.

156
00:10:11,900 --> 00:10:13,790
It gives us the first ten characters.

157
00:10:13,790 --> 00:10:17,870
But we also want to concatenate that with dot, dot, dot.

158
00:10:18,500 --> 00:10:19,850
So we'll do that here.

159
00:10:20,270 --> 00:10:25,340
Can cat with the string dot, dot, dot, not comicbook.com dot, dot, dot.

160
00:10:26,930 --> 00:10:31,160
And now if we copy that, that works.

161
00:10:31,640 --> 00:10:34,160
But it's supposed to be called short title.

162
00:10:34,460 --> 00:10:39,320
So we add as short title.

163
00:10:41,650 --> 00:10:43,870
And we just triple check our work.

164
00:10:44,440 --> 00:10:45,250
Looks good.

165
00:10:45,820 --> 00:10:49,660
Now we have our next thing we're selecting, which is the author.

166
00:10:49,900 --> 00:10:52,630
But we want last name, comma, first name.

167
00:10:53,110 --> 00:10:55,660
So it's going to be another king cat, actually.

168
00:10:55,840 --> 00:11:04,850
And we'll start with the author l name comma a comma in quotes.

169
00:11:04,870 --> 00:11:08,380
Kind of hard to talk about comma, comma, comma.

170
00:11:08,380 --> 00:11:12,640
But this comma here is actually going to be concatenated in.

171
00:11:13,120 --> 00:11:15,400
This is quite a string to look at there.

172
00:11:16,090 --> 00:11:18,190
Author f name.

173
00:11:18,910 --> 00:11:24,010
And we want it to be as author so we don't have to use quotes for that.

174
00:11:25,180 --> 00:11:25,990
Let's try it.

175
00:11:26,530 --> 00:11:28,090
See if we made any mistakes.

176
00:11:29,140 --> 00:11:30,050
It looks good.

177
00:11:30,070 --> 00:11:31,180
So we get Lahiri.

178
00:11:31,180 --> 00:11:34,300
Comma, Jhumpa, Gaiman, comma, Neil.

179
00:11:34,450 --> 00:11:35,650
That's what we expect.

180
00:11:36,220 --> 00:11:37,510
And it's called author.

181
00:11:38,320 --> 00:11:38,830
Good.

182
00:11:39,250 --> 00:11:41,770
Finally, we need this last portion quantity.

183
00:11:41,890 --> 00:11:46,030
And this should be the number in stock, followed by the string in stock.

184
00:11:46,780 --> 00:11:51,880
So if you don't remember what the column name is, we can do describe books.

185
00:11:53,140 --> 00:11:57,130
And you can see we have stock quantity.

186
00:11:57,130 --> 00:11:58,600
So that's what we want to use.

187
00:11:59,530 --> 00:12:03,220
So this time, we're starting off with a stock quantity comma.

188
00:12:04,330 --> 00:12:08,560
And then we want space in space stock.

189
00:12:11,550 --> 00:12:12,540
Just like that.

190
00:12:13,950 --> 00:12:16,920
And then we need to use as quantity.

191
00:12:19,280 --> 00:12:20,870
Boy, it's a long one.

192
00:12:21,050 --> 00:12:22,280
So let's try it out.

193
00:12:22,290 --> 00:12:23,660
Make sure we didn't do anything wrong.

194
00:12:24,560 --> 00:12:25,450
And that looks good.

195
00:12:25,460 --> 00:12:28,130
32 in stock, 154 in stock.

196
00:12:28,460 --> 00:12:31,040
And then we have author and short title both working.

197
00:12:31,490 --> 00:12:38,390
Now, one thing I want to point out here, that stock quantity is a number, it's an int, but it doesn't

198
00:12:38,390 --> 00:12:42,920
break everything can cat is okay with numbers like this.

199
00:12:42,920 --> 00:12:49,430
What it does is it just takes the string version, the text version of 32 and adds it to in stock.

200
00:12:50,210 --> 00:12:55,970
So it basically does this 32 plus in stock.

201
00:12:56,960 --> 00:13:00,170
So that's very different than trying to add a number to text.

202
00:13:01,070 --> 00:13:01,460
All right.

203
00:13:01,460 --> 00:13:02,720
So that was a lot.

204
00:13:02,720 --> 00:13:03,260
I know.

205
00:13:03,260 --> 00:13:04,370
But we're done now.

206
00:13:04,400 --> 00:13:07,790
We're done with these string functions and we're moving on.