1
00:00:00,540 --> 00:00:03,420
This is the solution video for the exercise.

2
00:00:03,870 --> 00:00:08,860
The first question is what's a good use case for the CHA or car data type?

3
00:00:08,880 --> 00:00:09,900
Just make one up.

4
00:00:10,110 --> 00:00:13,740
Well, remember, the CHA data type is best for data.

5
00:00:13,770 --> 00:00:16,740
Pieces of text that are fixed in size.

6
00:00:16,740 --> 00:00:19,920
So something that's always two characters, always 50 characters.

7
00:00:19,920 --> 00:00:23,250
Something like a zip code, always being five characters in the US.

8
00:00:23,250 --> 00:00:23,790
Right?

9
00:00:23,790 --> 00:00:28,080
I think it's five characters or a year.

10
00:00:28,080 --> 00:00:33,150
If you just needed to store a year and nothing else, four digits or two digits, you could do that

11
00:00:33,150 --> 00:00:34,890
as text using CHA.

12
00:00:34,980 --> 00:00:42,090
But if you need something that varies in length, name, city, anything really that's text that varies,

13
00:00:42,090 --> 00:00:45,270
it's way more common to have text that is variable in size.

14
00:00:45,270 --> 00:00:49,770
Then var cha is more flexible and it's actually more efficient.

15
00:00:49,890 --> 00:00:51,870
But CHA comes out on top.

16
00:00:51,870 --> 00:00:56,160
When we have that fixed data size, it is more efficient compared to var CHA.

17
00:00:56,190 --> 00:00:57,840
It just depends on what we're trying to store.

18
00:00:58,350 --> 00:00:59,700
So fill in the blank here.

19
00:00:59,700 --> 00:01:02,820
We're going to make a table called inventory item.

20
00:01:02,820 --> 00:01:04,650
Name will be a piece of text.

21
00:01:04,650 --> 00:01:10,740
I would make that a var cha because there's no guarantee that our items are going to have the same length

22
00:01:10,740 --> 00:01:13,260
names, so it would be better to make it variable.

23
00:01:13,710 --> 00:01:16,860
And then price here is always less than a million.

24
00:01:17,400 --> 00:01:23,460
So I would do a decimal because we know that we're going to have at most two digits after the decimal

25
00:01:23,460 --> 00:01:28,560
and before the decimal we would at most have what, in 1,000,007 digits.

26
00:01:28,560 --> 00:01:36,060
So I think I would do decimal seven comma two seven digits before the decimal and then two after.

27
00:01:36,980 --> 00:01:38,300
But you could use float.

28
00:01:38,330 --> 00:01:40,150
You could also use double.

29
00:01:40,160 --> 00:01:44,900
But any of those types are going to be correct compared to something like Intt, which is what I would

30
00:01:44,900 --> 00:01:46,020
use for quantity.

31
00:01:46,040 --> 00:01:54,020
If we know we're going to have only a whole number, an integer of some inventory item, we're not going

32
00:01:54,020 --> 00:01:56,780
to have 21.7 teddy bears.

33
00:01:56,990 --> 00:01:58,740
We'll have 21 or 22.

34
00:01:58,760 --> 00:02:03,320
So quantity should just be integer and you could optimize it if there was a maximum.

35
00:02:03,320 --> 00:02:08,699
If you knew you never were going to have whatever the maximum int is at 2 billion.

36
00:02:08,720 --> 00:02:14,240
You might want a medium int or small enter even a tiny int, just depending on what that threshold is.

37
00:02:14,240 --> 00:02:18,740
How many would you ever have at most stored in this column?

38
00:02:19,630 --> 00:02:22,330
Next up, what's the difference between date, time and time stamp?

39
00:02:22,420 --> 00:02:23,730
That's a confusing one.

40
00:02:23,740 --> 00:02:29,950
Both data types store the date and the time, but time stamp takes up less memory.

41
00:02:29,950 --> 00:02:33,310
It's it's, I believe, four bytes compared to eight bytes, something like that.

42
00:02:33,310 --> 00:02:35,320
But it's roughly half.

43
00:02:35,530 --> 00:02:36,580
Last time I checked.

44
00:02:36,670 --> 00:02:42,910
But the biggest difference aside from time stamp being more memory efficient, is that time stamp can

45
00:02:42,910 --> 00:02:45,220
store a much smaller range of dates.

46
00:02:45,250 --> 00:02:52,570
It goes back from 1970 something up to 2038, whereas date time can go from the year 1000 up to the

47
00:02:52,570 --> 00:02:54,760
year 9999.

48
00:02:55,030 --> 00:02:56,880
So big difference in the range.

49
00:02:56,890 --> 00:02:59,950
Otherwise, though, they both store dates and times together.

50
00:03:00,000 --> 00:03:02,670
Okay, so print out the current time.

51
00:03:02,680 --> 00:03:09,040
Now we write some code, so to get the current time we can use cur time, but I have to select it.

52
00:03:09,040 --> 00:03:11,710
So select cur time.

53
00:03:11,710 --> 00:03:12,700
And there's the current time.

54
00:03:12,700 --> 00:03:14,080
It's a little late for me.

55
00:03:14,260 --> 00:03:19,510
Eight at night, just recording before before I go to dinner I guess.

56
00:03:20,170 --> 00:03:24,850
And then the next piece, print out the current date, but not the time.

57
00:03:25,060 --> 00:03:27,640
So I would use cur date.

58
00:03:28,540 --> 00:03:33,310
And for both of these we also have the longer version, which I always forget.

59
00:03:33,310 --> 00:03:36,250
If it's underscore, I think it's current underscore date.

60
00:03:36,250 --> 00:03:38,230
It's the same thing as cur date.

61
00:03:38,230 --> 00:03:41,680
It's just extra characters and then the same thing for time.

62
00:03:42,250 --> 00:03:44,140
We have current time.

63
00:03:44,140 --> 00:03:46,870
So if you did that it's also totally valid.

64
00:03:47,770 --> 00:03:55,120
Next up, print out the current day of the week as a number, so we'll first take the current date.

65
00:03:55,120 --> 00:04:00,550
We could also use the current date time, either one because they both have the date information and

66
00:04:00,550 --> 00:04:04,270
then we'll pass that through to day of week.

67
00:04:04,270 --> 00:04:05,050
I think it is.

68
00:04:05,050 --> 00:04:11,710
But if you ever forget and I definitely forget, we can look at the docs, but I think it is day of

69
00:04:11,740 --> 00:04:15,100
week and then cur date.

70
00:04:16,450 --> 00:04:18,730
There we are, day four.

71
00:04:18,760 --> 00:04:20,029
That's the number for me.

72
00:04:20,050 --> 00:04:22,029
You'll probably see something different.

73
00:04:22,270 --> 00:04:24,490
Sunday is one, Monday is two.

74
00:04:24,490 --> 00:04:27,600
When Tuesday is three and then Wednesday is four.

75
00:04:27,610 --> 00:04:29,770
And it is indeed a Wednesday today.

76
00:04:29,920 --> 00:04:35,950
Next up, print out the current day of the week with the day name, not the number.

77
00:04:36,130 --> 00:04:40,020
So for this one, I believe we want to use day name.

78
00:04:40,030 --> 00:04:42,070
So we have day of week and day name.

79
00:04:42,070 --> 00:04:45,940
And again, it's very easy to mix them up or just forget they exist.

80
00:04:46,210 --> 00:04:47,770
But let's do cur date.

81
00:04:49,420 --> 00:04:50,260
And there we are.

82
00:04:50,260 --> 00:04:51,850
We see Wednesday.

83
00:04:51,880 --> 00:04:58,630
Now, I should also show this with now, remember now gives us the time stamp, the date and the time.

84
00:04:58,630 --> 00:04:59,860
So that works as well.

85
00:04:59,950 --> 00:05:02,840
And it might appear to work with cur time.

86
00:05:02,860 --> 00:05:05,050
It is giving me the current day.

87
00:05:05,260 --> 00:05:10,870
But remember, cur time includes no date information, so de name is just going off of whatever the

88
00:05:10,870 --> 00:05:12,430
current day is.

89
00:05:12,520 --> 00:05:14,440
So this wouldn't work if I tried to.

90
00:05:14,440 --> 00:05:17,140
Do I have my birth?

91
00:05:17,380 --> 00:05:17,920
What is it?

92
00:05:17,920 --> 00:05:20,110
Birth date and birth time?

93
00:05:20,410 --> 00:05:27,910
If I tried to do day name of each birth time in my people table or from people.

94
00:05:30,440 --> 00:05:32,600
They all just say Wednesday, Wednesday, Wednesday, Wednesday.

95
00:05:32,600 --> 00:05:34,280
And it's highly unlikely.

96
00:05:34,280 --> 00:05:37,820
All those dates we stored are Wednesdays, but it's more than unlikely.

97
00:05:37,820 --> 00:05:44,780
It just doesn't work if we only provide a time to day name or any of these other day or date related

98
00:05:44,780 --> 00:05:45,570
functions.

99
00:05:45,590 --> 00:05:50,180
Now, there's actually another way we could have printed out the current day of the week or the number

100
00:05:50,180 --> 00:05:54,150
of the day of the week, which is using the date format function.

101
00:05:54,170 --> 00:05:55,400
Remember how this works.

102
00:05:55,400 --> 00:05:58,460
We provide a format string with these specifiers.

103
00:05:58,460 --> 00:06:04,760
So one of these is day of the week, somewhere in here, maybe already past it, day of the month.

104
00:06:06,670 --> 00:06:07,190
Where are you?

105
00:06:07,210 --> 00:06:07,920
Weekday name.

106
00:06:07,930 --> 00:06:08,530
There we are.

107
00:06:08,560 --> 00:06:16,390
It's percent uppercase W so I could do that select date format and then I will provide it the current

108
00:06:16,390 --> 00:06:24,550
date time or technically it's a timestamp and then my format string will be percent W, uppercase W,

109
00:06:24,790 --> 00:06:26,650
and that also gives me Wednesday.

110
00:06:26,650 --> 00:06:27,910
But this is a little clunkier.

111
00:06:27,910 --> 00:06:34,180
And who is going to remember percent W compared to day name, which I think is just a little bit easier.

112
00:06:34,720 --> 00:06:35,280
Okay.

113
00:06:35,290 --> 00:06:39,730
Next up, print out the current day, day and time using this format.

114
00:06:39,730 --> 00:06:44,290
So we want the two digit month, a/2 digit day slash four digit year.

115
00:06:44,440 --> 00:06:50,740
So for this one, we're definitely going to need to do date format, but I'm going to change my format

116
00:06:50,740 --> 00:06:57,160
string I'll provide now, although I could also just do curr date because we're only using the date

117
00:06:57,160 --> 00:06:58,960
information, we're not using time.

118
00:06:59,440 --> 00:07:01,540
So the current date information.

119
00:07:01,540 --> 00:07:04,510
So we'll find the numeric month right here.

120
00:07:04,510 --> 00:07:05,500
We've got percent.

121
00:07:05,500 --> 00:07:09,220
C Although I think there's another option.

122
00:07:09,220 --> 00:07:17,440
This is the one we want percent lowercase M because it's always going to be two digits 010203 whereas

123
00:07:17,440 --> 00:07:24,010
this one up here percent C we'll just do one, 234 until we get to ten, 11, 12 those will be double

124
00:07:24,010 --> 00:07:24,520
digits.

125
00:07:24,520 --> 00:07:27,970
So if I always want two digits for month, I'll use percent.

126
00:07:27,970 --> 00:07:34,390
M It's kind of tedious trying to find the right to the right specified in that list.

127
00:07:34,480 --> 00:07:34,840
All right.

128
00:07:34,840 --> 00:07:37,030
So that's going to give me my first bit.

129
00:07:37,300 --> 00:07:39,100
Then I want a forward slash.

130
00:07:39,100 --> 00:07:40,630
So that's what it looks like there.

131
00:07:40,630 --> 00:07:44,110
And then after that forward slash I want to digits for the day.

132
00:07:44,470 --> 00:07:47,800
So we probably have the same sort of thing day of the month.

133
00:07:47,830 --> 00:07:54,550
Here we are, two digits, zero zero versus day of the month where we don't have the leading zero.

134
00:07:54,640 --> 00:07:55,630
But I want this one.

135
00:07:55,630 --> 00:07:57,070
So we always have two digits.

136
00:07:57,070 --> 00:07:58,870
That's percent DX.

137
00:07:59,850 --> 00:08:00,820
Percent.

138
00:08:00,840 --> 00:08:05,610
RD All right, so now we have 1005.

139
00:08:05,640 --> 00:08:08,700
Of course, when you're running this, you'll see something different, most likely.

140
00:08:08,790 --> 00:08:11,130
And then we have four digits for the year.

141
00:08:11,160 --> 00:08:12,840
I believe that's going to be something.

142
00:08:12,840 --> 00:08:13,560
Why?

143
00:08:14,580 --> 00:08:15,300
Here it is.

144
00:08:15,300 --> 00:08:15,670
Year.

145
00:08:15,690 --> 00:08:21,750
Numeric, two digits is lowercase, four digits is uppercase Y, and that's exactly what we want.

146
00:08:21,750 --> 00:08:22,730
So let's do that now.

147
00:08:22,740 --> 00:08:25,320
Uppercase Y, percent Uppercase Y.

148
00:08:25,350 --> 00:08:26,880
Now, I forgot the slash.

149
00:08:27,390 --> 00:08:28,080
Here we go.

150
00:08:28,320 --> 00:08:31,320
Ten slash zero five slash 20, 22.

151
00:08:31,740 --> 00:08:33,210
Okay, we got that.

152
00:08:33,750 --> 00:08:35,850
Next up, a little more complicated.

153
00:08:35,850 --> 00:08:39,299
Print out the current day and time using this format.

154
00:08:39,299 --> 00:08:42,150
So we're going to use the same date format function.

155
00:08:42,750 --> 00:08:50,070
But this time instead of cur date, I'm going to pass through now, and this will allow me to work with

156
00:08:50,070 --> 00:08:51,360
the date and the time.

157
00:08:51,360 --> 00:08:54,780
Because remember how now works.

158
00:08:54,780 --> 00:08:59,940
If I just select now versus cur date, that's just the date.

159
00:08:59,940 --> 00:09:05,310
But if I do now, it's the date and time together in a time stamp.

160
00:09:05,310 --> 00:09:06,600
So that's what I'm going to want to do.

161
00:09:06,600 --> 00:09:14,220
Select date format now and then what will my date formatting string look like?

162
00:09:14,220 --> 00:09:16,530
First we want the full month name.

163
00:09:17,940 --> 00:09:19,680
So somewhere in there.

164
00:09:20,160 --> 00:09:21,510
This one right here, my birth name.

165
00:09:21,510 --> 00:09:26,430
The full name is percent Uppercase M and then a space.

166
00:09:26,670 --> 00:09:30,630
So we get October in this case and then we want second.

167
00:09:30,960 --> 00:09:37,680
So that is the day of the month with English suffix percent uppercase RD.

168
00:09:39,570 --> 00:09:40,040
There we go.

169
00:09:40,050 --> 00:09:42,540
October 5th and then at.

170
00:09:43,210 --> 00:09:44,230
That one's easy.

171
00:09:45,790 --> 00:09:48,460
And then finally, we need to get the time here.

172
00:09:48,670 --> 00:09:50,800
Now, there is an easy way to get the time.

173
00:09:50,800 --> 00:09:53,740
If we wanted hours, minutes and seconds.

174
00:09:53,740 --> 00:09:55,840
But I just want hours.

175
00:09:55,930 --> 00:09:57,280
And then I want minutes.

176
00:09:57,280 --> 00:09:58,450
And in fact, 4 hours.

177
00:09:58,450 --> 00:09:59,890
I want a single digit.

178
00:09:59,890 --> 00:10:02,050
I don't want it padded with zeros.

179
00:10:02,740 --> 00:10:05,590
So we need to find hours somewhere on here.

180
00:10:06,650 --> 00:10:07,610
Here we are.

181
00:10:07,820 --> 00:10:08,900
Our.

182
00:10:09,690 --> 00:10:12,210
Here's the padded one with a01.

183
00:10:12,210 --> 00:10:13,160
I don't want that.

184
00:10:13,170 --> 00:10:24,630
I want our that does not have this leading zero so percent lowercase k and then a colon OC.

185
00:10:24,900 --> 00:10:27,960
And then what do we have next?

186
00:10:27,960 --> 00:10:32,310
And again, I didn't specify if it was 12 or 24 hour time, so we're just gonna do 24.

187
00:10:32,670 --> 00:10:41,280
And then the minute and the minute here is percent I so we'll do that percent I And that should do it

188
00:10:41,280 --> 00:10:44,800
October 5th at 20 colon 21 verify.

189
00:10:44,850 --> 00:10:46,200
Same format that we saw here.

190
00:10:46,200 --> 00:10:48,770
Of course the dates are different but it's the same pattern.

191
00:10:48,780 --> 00:10:54,390
Next up, the final piece is to create a tweet table that stores the tweet content, a username and

192
00:10:54,390 --> 00:10:56,550
the time each tweet was created at.

193
00:10:56,580 --> 00:11:00,300
So I'll do this in a file, create table tweets.

194
00:11:00,300 --> 00:11:05,910
This is still very simple for an actual tweets table, there'd be a lot more going on, but we'll have

195
00:11:05,910 --> 00:11:10,620
the I already forgot content, let's call it that.

196
00:11:11,690 --> 00:11:17,720
Content will be a var card and let's limit it to 180 characters.

197
00:11:17,930 --> 00:11:25,010
Then we have the user name and I don't know what the limit is on characters, but let's say I think

198
00:11:25,010 --> 00:11:28,130
somewhere around 20 you don't get extremely long usernames.

199
00:11:28,130 --> 00:11:32,540
It might even be fewer than that, but it is certainly not going to be fixed in length.

200
00:11:32,540 --> 00:11:36,770
So I'm also going to use var for that and then we have time.

201
00:11:36,770 --> 00:11:42,740
It was created, so I'll just call this created add or post it or something like that.

202
00:11:43,130 --> 00:11:45,350
And that's going to be a timestamp.

203
00:11:45,350 --> 00:11:50,150
It could be a date time as well, but I'm going to do a timestamp because it takes up less memory and

204
00:11:50,150 --> 00:11:52,430
I'll have it default to now.

205
00:11:52,520 --> 00:11:57,710
Now instead of using now I could also do current timestamp.

206
00:11:57,710 --> 00:12:01,730
And what's weird, it's kind of a quirk with my SQL for current timestamp.

207
00:12:01,730 --> 00:12:07,820
You can get away without parentheses or with parentheses, but with now you have to have parentheses.

208
00:12:07,820 --> 00:12:10,880
If I don't have prints on now I get an error.

209
00:12:11,300 --> 00:12:12,080
Take a look here.

210
00:12:12,080 --> 00:12:14,360
If I select now with prints, it works.

211
00:12:14,360 --> 00:12:19,220
Select without prints we get an error but with current timestamp.

212
00:12:20,960 --> 00:12:23,090
It works with friends and without.

213
00:12:23,090 --> 00:12:24,800
It's just a weird quirk.

214
00:12:25,010 --> 00:12:28,590
So you can use either one now is a little shorter.

215
00:12:28,610 --> 00:12:35,540
Let's run this and then just try inserting like maybe a single tweet insert into tweets.

216
00:12:36,830 --> 00:12:40,190
Will do name or tweet content.

217
00:12:40,190 --> 00:12:45,380
I think we called it end username values.

218
00:12:45,380 --> 00:12:54,260
And then I'll just say this is my first tweet lol and then my username will be Colt Steel.

219
00:12:54,890 --> 00:12:56,420
All right, I insert that.

220
00:12:56,420 --> 00:13:01,550
Let's select star from tweets and what do we see created at worked?

221
00:13:01,550 --> 00:13:06,350
We've got that now timestamp in there and that's it for this exercise.

222
00:13:06,380 --> 00:13:07,610
Pat yourself on the back.

223
00:13:07,610 --> 00:13:09,230
We did a lot in this section.

224
00:13:09,290 --> 00:13:11,690
It was dull, is boring, but it was important.

225
00:13:11,720 --> 00:13:16,730
Next we move on to some stuff that I think is a bit more exciting, at least as far as SQL goes.