1
00:00:00,330 --> 00:00:03,990
Okay so this is the solution video to our data types.

2
00:00:03,990 --> 00:00:04,960
Problem set.

3
00:00:04,980 --> 00:00:06,180
First one here.

4
00:00:06,270 --> 00:00:08,430
What's a good use case for char?

5
00:00:08,790 --> 00:00:14,850
So there's not a ton of times that I end up using them, but remember they are fixed length, so we

6
00:00:14,850 --> 00:00:19,620
have to specify exactly the length of the char that we want for that column.

7
00:00:19,980 --> 00:00:24,960
So we often use it for text that we know when 100% has a fixed length.

8
00:00:24,960 --> 00:00:29,520
So the one that I've used a couple of times is state abbreviations.

9
00:00:30,330 --> 00:00:37,920
If you're doing sex like M or F, or perhaps you're working on a project where, I don't know, let's

10
00:00:37,920 --> 00:00:43,920
say you're working with companies and you want an abbreviated name for every company that's only five

11
00:00:43,920 --> 00:00:47,910
characters or four characters long, then you could use Char as well.

12
00:00:49,080 --> 00:00:52,740
Hopefully you came up with something better than me, something new and original.

13
00:00:54,010 --> 00:00:56,620
Next up, fill in the blanks of this schema.

14
00:00:57,640 --> 00:00:59,860
So there's a couple of things to talk about.

15
00:00:59,890 --> 00:01:02,170
Item name is text.

16
00:01:03,190 --> 00:01:08,740
It's going to be something like, I don't know, Cabbage Patch Doll or baseball hat.

17
00:01:09,280 --> 00:01:12,160
And that should be a var char.

18
00:01:12,190 --> 00:01:14,740
Most likely you could get away with using a char.

19
00:01:15,250 --> 00:01:20,710
But there's no reason to do that because it's not fixed length, so it makes more sense to do var char.

20
00:01:22,060 --> 00:01:23,220
And then quantity.

21
00:01:23,230 --> 00:01:24,310
I'll skip price for a moment.

22
00:01:24,310 --> 00:01:26,500
Quantity makes sense to be an integer.

23
00:01:26,800 --> 00:01:31,000
We are never going to have partial items in stock.

24
00:01:31,000 --> 00:01:37,300
We're not going to have 2.5 Cabbage Patch dolls, hopefully, unless there was some sort of massacre

25
00:01:37,300 --> 00:01:39,520
with the Cabbage Patch dolls and a chainsaw.

26
00:01:39,550 --> 00:01:43,530
We're hopefully only only going to have whole numbers of them, so we'll use INT.

27
00:01:43,540 --> 00:01:45,760
But price is a little trickier.

28
00:01:45,790 --> 00:01:54,160
And so I added this note price is always less than $1 million, but price is not usually or not exclusively

29
00:01:54,160 --> 00:01:54,970
a whole number.

30
00:01:54,970 --> 00:02:00,160
We want to allow $0.04, so we're going to want to use a decimal.

31
00:02:00,160 --> 00:02:07,420
Now you could use a float or a double, but as we discussed, decimals better for monetary stuff because

32
00:02:07,420 --> 00:02:08,410
it's more precise.

33
00:02:08,410 --> 00:02:12,610
You don't have to worry about losing precision as you get into longer numbers.

34
00:02:13,480 --> 00:02:16,000
So then the question is, what do we put here?

35
00:02:16,000 --> 00:02:19,810
It's not just decimal on its own, right?

36
00:02:19,810 --> 00:02:21,370
We don't just do this.

37
00:02:22,270 --> 00:02:24,130
We have two numbers we pass in.

38
00:02:24,780 --> 00:02:30,150
And those two numbers, if you can remember the names, I believe the first one is precision and the

39
00:02:30,150 --> 00:02:31,410
second one is scale.

40
00:02:31,440 --> 00:02:36,810
Don't quote me on that, though, but basically it's a total number of digits, followed by the number

41
00:02:36,810 --> 00:02:38,790
of digits after the decimal.

42
00:02:39,330 --> 00:02:44,670
So we can start with the number of digits after the decimal, which when you're working with price or

43
00:02:44,670 --> 00:02:47,460
monetary information, it's pretty much always two.

44
00:02:48,060 --> 00:02:51,780
So then the question becomes how many total digits do we need?

45
00:02:51,810 --> 00:02:56,760
And if I said it's always less one, then excuse me, it's always less than 1 million.

46
00:02:57,060 --> 00:03:03,570
That means that our highest price we could have is 999999.99.

47
00:03:04,710 --> 00:03:10,530
So if that's the maximum price, then all we need to do is count up the total number of digits.

48
00:03:10,530 --> 00:03:14,130
So that is three, six, seven, eight.

49
00:03:16,530 --> 00:03:17,610
And that's your answer.

50
00:03:17,760 --> 00:03:19,890
Decimal eight, comma two.

51
00:03:21,530 --> 00:03:22,340
Next up.

52
00:03:23,430 --> 00:03:26,220
What's the difference between date time and time stamp?

53
00:03:27,000 --> 00:03:31,430
So I have the documentation open for date date, time and time stamp.

54
00:03:31,440 --> 00:03:34,410
And the first thing I'll say is that there's a couple of differences.

55
00:03:34,410 --> 00:03:40,140
There are a few technical differences as far as how they're stored and the ranges that they support.

56
00:03:40,140 --> 00:03:43,710
And then there's a big difference into how they're put into use most of the time.

57
00:03:44,070 --> 00:03:46,230
So both of them, let's start off.

58
00:03:46,230 --> 00:03:50,790
They both store date and time information, so they store the date and then the time.

59
00:03:51,420 --> 00:03:55,860
However, there is a big difference in the range that they support.

60
00:03:55,860 --> 00:03:58,320
So time stamps have a much smaller range.

61
00:03:58,620 --> 00:04:06,180
If you remember, we went over this, whereas that time stamp has a range from 1970 to 2038 versus date

62
00:04:06,180 --> 00:04:11,130
time goes from 1000 year 1000 up to year 9999.

63
00:04:11,700 --> 00:04:13,560
So that's one significant difference.

64
00:04:13,590 --> 00:04:16,170
The other is the size of the memory.

65
00:04:16,170 --> 00:04:17,730
They take up a date.

66
00:04:17,730 --> 00:04:20,760
Time is roughly twice as large as a time stamp.

67
00:04:20,970 --> 00:04:24,690
Which brings us to the final difference, which is how they're actually put into practice.

68
00:04:24,690 --> 00:04:33,060
And typically time stamps are used, at least in my experience exclusively when we are trying to add

69
00:04:33,060 --> 00:04:38,190
metadata about when something is created or updated to a table.

70
00:04:38,460 --> 00:04:40,380
Otherwise I use date time.

71
00:04:40,410 --> 00:04:43,800
Now that doesn't mean that you can't use time stamp in.

72
00:04:43,980 --> 00:04:48,900
If you wanted to use timestamp to store birthdays, you could, but you could run into problems if you

73
00:04:48,900 --> 00:04:52,500
have people who are born before 1970.

74
00:04:53,520 --> 00:04:58,800
So again, the key difference is really boil down to the ranges they support and then how much memory

75
00:04:58,800 --> 00:04:59,580
they take up.

76
00:05:01,560 --> 00:05:04,800
Moving on, print out the current time.

77
00:05:04,860 --> 00:05:06,390
Finally, some code.

78
00:05:06,390 --> 00:05:13,920
So to print out the current time, it's just a matter of cur time and we need to select that.

79
00:05:15,250 --> 00:05:16,060
There we go.

80
00:05:16,210 --> 00:05:18,160
1608 13.

81
00:05:18,940 --> 00:05:20,710
Yours, of course, will differ.

82
00:05:20,710 --> 00:05:25,330
Most likely print out the current date, but not the time.

83
00:05:25,630 --> 00:05:30,370
So that one we want to do cur date.

84
00:05:32,470 --> 00:05:36,220
You can see April 22nd, 2017.

85
00:05:36,620 --> 00:05:37,720
It's still my birthday.

86
00:05:39,280 --> 00:05:41,110
Print out the current day of the week.

87
00:05:41,510 --> 00:05:43,160
Okay, so this one's a little bit different.

88
00:05:43,180 --> 00:05:45,070
There's a couple of ways of achieving this.

89
00:05:45,310 --> 00:05:58,510
The first one is to use select day of week, and then we could either give it the date per date or we

90
00:05:58,510 --> 00:05:59,560
could use now.

91
00:05:59,920 --> 00:06:03,070
So today is a Saturday, so it gives us seven.

92
00:06:03,670 --> 00:06:08,980
Just to show you, I can replace this with now, which is a date time and I also get seven.

93
00:06:08,980 --> 00:06:13,180
But there's another way we could do this, which is to use date format.

94
00:06:13,480 --> 00:06:15,910
And if we go to the documentation here.

95
00:06:16,920 --> 00:06:18,990
And we find this date format.

96
00:06:19,410 --> 00:06:22,800
If we scroll down somewhere on here, it's day of the week.

97
00:06:22,980 --> 00:06:23,760
Where is that?

98
00:06:23,790 --> 00:06:24,510
Here we go.

99
00:06:24,660 --> 00:06:25,710
Day of the week.

100
00:06:26,280 --> 00:06:36,870
Percent w lowercase w though we could use that do select date format now or cur date comma and the string

101
00:06:36,870 --> 00:06:40,020
we want is just percent lowercase w.

102
00:06:42,000 --> 00:06:44,160
And actually, I didn't know this until now.

103
00:06:44,310 --> 00:06:45,170
There is a difference.

104
00:06:45,180 --> 00:06:46,410
That's kind of fascinating.

105
00:06:46,980 --> 00:06:52,840
If we read this closely, it says day of the week, zero is Sunday and six is Saturday.

106
00:06:52,860 --> 00:06:57,390
So these are not equivalent actually day of the week.

107
00:06:57,420 --> 00:07:01,320
Sunday is one rather than zero and Saturday is seven.

108
00:07:01,800 --> 00:07:08,520
So I guess if we wanted these to be equivalent, we would need to do a plus one and shift everything

109
00:07:08,520 --> 00:07:13,350
by one or shift everything down one if we're using the previous.

110
00:07:13,770 --> 00:07:14,580
Interesting.

111
00:07:14,910 --> 00:07:17,850
No idea why that's the case, but it is what it is.

112
00:07:18,690 --> 00:07:21,210
So the next thing is to print out the current day of the week.

113
00:07:21,300 --> 00:07:25,710
But I want the date name, day name, Monday, Tuesday, Wednesday, and so on.

114
00:07:25,890 --> 00:07:33,540
I wouldn't expect you to remember this, but there is a day name that we can use like that select day

115
00:07:33,540 --> 00:07:38,340
name, or we can do our format, date or date format.

116
00:07:39,150 --> 00:07:45,180
I always mix up the order there, but it's date underscore format for now and then we just have to figure

117
00:07:45,180 --> 00:07:47,280
out how we get the day of the week name.

118
00:07:47,310 --> 00:07:55,260
So if we go to the docs it's right above weekday name, sunday to Saturday percent uppercase w.

119
00:07:56,040 --> 00:08:02,280
So if we switch back, just change it to an uppercase W now we get Saturday.

120
00:08:04,980 --> 00:08:09,210
Next up, print out the current date in time using this format.

121
00:08:09,590 --> 00:08:11,250
Month, month slash.

122
00:08:11,250 --> 00:08:12,600
Day day slash.

123
00:08:12,630 --> 00:08:13,200
Yea yea.

124
00:08:13,200 --> 00:08:13,380
Yea.

125
00:08:13,380 --> 00:08:14,250
Yea yea.

126
00:08:16,590 --> 00:08:26,250
So we'll need to use date format and we can just use curr date or we can use now I like to use now typically,

127
00:08:26,490 --> 00:08:29,310
but we're not actually using the time information.

128
00:08:29,310 --> 00:08:34,500
So why don't we just use cur date in this case just to show you we can and then the way we want to format

129
00:08:34,500 --> 00:08:42,510
it, if we go to the docs we want, first of all was the month, so we want month numeric, which is

130
00:08:42,690 --> 00:08:43,470
percent.

131
00:08:43,710 --> 00:08:45,160
Oh no, that's not what we want.

132
00:08:45,180 --> 00:08:58,080
What we want is down here this month numeric 0 to 12 so we get m lowercase m percent lowercase m slash

133
00:08:58,080 --> 00:09:04,080
and then we want day of the month numeric zero 0 to 31 because we want those two digits always.

134
00:09:04,080 --> 00:09:05,640
So that's lowercase d.

135
00:09:06,460 --> 00:09:10,420
So we have lowercase M slash lowercase D and then four year.

136
00:09:11,410 --> 00:09:12,700
We want four digits.

137
00:09:12,700 --> 00:09:14,530
So that's uppercase y.

138
00:09:15,130 --> 00:09:16,180
So let's try that.

139
00:09:16,780 --> 00:09:19,360
Percent lowercase m slash.

140
00:09:19,510 --> 00:09:22,870
Percent lowercase dx slash.

141
00:09:22,870 --> 00:09:24,910
Percent uppercase y.

142
00:09:25,750 --> 00:09:27,010
Oh, semicolon.

143
00:09:27,640 --> 00:09:28,450
There we go.

144
00:09:28,660 --> 00:09:29,650
And that works.

145
00:09:30,670 --> 00:09:34,150
Next up, print out the current day and time using this format.

146
00:09:34,150 --> 00:09:35,120
It's a bit different.

147
00:09:35,140 --> 00:09:41,200
Once a month name and then the Anglicized day at and then hours and minutes.

148
00:09:42,460 --> 00:09:44,650
So let's start off with what we know.

149
00:09:45,190 --> 00:09:51,850
We're going to need to use date format and we need to use now because we need date and time rather than

150
00:09:52,030 --> 00:09:53,770
date, which gives us just the date.

151
00:09:54,640 --> 00:09:56,440
Then our format string.

152
00:09:57,010 --> 00:10:00,370
First thing we want is the month name.

153
00:10:01,130 --> 00:10:02,420
Which is right.

154
00:10:02,420 --> 00:10:03,050
Where are you?

155
00:10:03,080 --> 00:10:04,880
This is a abbreviated month name.

156
00:10:06,200 --> 00:10:06,860
Here we go.

157
00:10:06,910 --> 00:10:07,800
Month name.

158
00:10:07,820 --> 00:10:09,140
Uppercase M.

159
00:10:10,190 --> 00:10:13,940
So we want uppercase M space.

160
00:10:14,120 --> 00:10:18,320
And then the Anglicized version of the day.

161
00:10:20,730 --> 00:10:22,200
And where is that?

162
00:10:25,930 --> 00:10:26,560
Here we go.

163
00:10:27,280 --> 00:10:29,110
Day of the month with English suffix.

164
00:10:29,110 --> 00:10:30,670
That's what I was trying to say.

165
00:10:31,510 --> 00:10:33,520
So it's Uppercase RD.

166
00:10:36,650 --> 00:10:38,360
Let's just see if that works to start.

167
00:10:38,390 --> 00:10:39,040
There we go.

168
00:10:39,050 --> 00:10:44,930
April 22nd, followed by at no.

169
00:10:44,960 --> 00:10:45,800
Years necessary.

170
00:10:45,800 --> 00:10:48,080
So at and then hours and minutes.

171
00:10:48,260 --> 00:10:49,970
So again.

172
00:10:49,970 --> 00:10:50,870
Hours.

173
00:10:52,120 --> 00:10:56,090
We have a lowercase h if we want.

174
00:10:56,110 --> 00:11:01,570
I guess I didn't specify if we wanted 24 hour time or 12 hours, so either of these will work.

175
00:11:02,200 --> 00:11:05,990
I'm just going to do lowercase h colon and then 4 minutes.

176
00:11:06,550 --> 00:11:07,660
That's lowercase.

177
00:11:08,020 --> 00:11:08,920
Well, let's see.

178
00:11:08,920 --> 00:11:10,420
I is going to give us.

179
00:11:11,130 --> 00:11:11,660
There we go.

180
00:11:11,680 --> 00:11:12,450
That will work.

181
00:11:12,480 --> 00:11:13,500
Lowercase I.

182
00:11:14,480 --> 00:11:14,990
Okay.

183
00:11:16,400 --> 00:11:17,900
So lowercase h.

184
00:11:18,380 --> 00:11:20,420
Colon lowercase I.

185
00:11:20,780 --> 00:11:21,500
There we go.

186
00:11:21,740 --> 00:11:23,900
April 22nd at 415.

187
00:11:24,600 --> 00:11:25,230
Great.

188
00:11:25,680 --> 00:11:27,630
Now moving on to our final problem.

189
00:11:27,750 --> 00:11:34,470
Create a tweet table that stores the tweet content, a username and the time it was created.

190
00:11:35,550 --> 00:11:39,300
So the tweet itself can vary in length, so it should be a var char cha.

191
00:11:39,330 --> 00:11:45,570
Most likely a username could vary in length as well, and then the time it's created could be a date

192
00:11:45,570 --> 00:11:46,200
time.

193
00:11:46,830 --> 00:11:48,900
I mean, it could just be a time if you wanted it to.

194
00:11:48,900 --> 00:11:54,150
But what makes the most sense, as we discussed already, is to use timestamp and we want to set the

195
00:11:54,150 --> 00:11:56,370
default to be now.

196
00:11:56,970 --> 00:11:57,900
So let's try it.

197
00:11:58,820 --> 00:12:07,950
It's hop over here and the first thing we want is a create table tweets and then we're going to have

198
00:12:08,970 --> 00:12:09,750
tweet content.

199
00:12:09,750 --> 00:12:11,550
So we could call that whatever we want.

200
00:12:11,550 --> 00:12:12,810
I'll just call it content.

201
00:12:13,260 --> 00:12:16,710
And that will be a var char up to 140 characters.

202
00:12:17,070 --> 00:12:22,020
Then we have username which will also be a var char up to 20 characters.

203
00:12:22,470 --> 00:12:28,680
And then finally we have created add or tweeted at or time or whatever you want to call it, which will

204
00:12:28,680 --> 00:12:29,850
be a timestamp.

205
00:12:31,080 --> 00:12:40,950
And then the important part is default now or default current timestamp, but I like to use now, so

206
00:12:40,950 --> 00:12:43,110
let's try it, see if it works.

207
00:12:43,740 --> 00:12:45,510
Make sure we have the right commas in place.

208
00:12:46,740 --> 00:12:47,680
Semicolon.

209
00:12:48,000 --> 00:12:49,650
And good thing we tried it.

210
00:12:49,770 --> 00:12:54,030
Missing parentheses around now to add the semicolon in as well.

211
00:12:55,080 --> 00:12:56,160
Make some space.

212
00:12:56,970 --> 00:12:59,220
Now we run it and it appears to work.

213
00:12:59,250 --> 00:13:01,650
Let's just make sure by inserting one tweet.

214
00:13:04,870 --> 00:13:07,510
So we'll do content username.

215
00:13:08,530 --> 00:13:11,080
Remember, we don't need to insert created app manually.

216
00:13:14,540 --> 00:13:24,260
And let's see content will be this is my first tweet and username will be called Cat.

217
00:13:26,130 --> 00:13:26,560
OC.

218
00:13:26,700 --> 00:13:30,120
Let's do a select star from tweets.

219
00:13:31,290 --> 00:13:34,540
And you can see we have created that in there as a time stamp.

220
00:13:34,560 --> 00:13:35,790
Works perfectly.

221
00:13:35,820 --> 00:13:38,190
Just double check by inserting something else.

222
00:13:38,310 --> 00:13:39,540
Let's just change it slightly.

223
00:13:39,540 --> 00:13:42,690
This is my second tweet.

224
00:13:45,640 --> 00:13:47,200
Now if we select Star.

225
00:13:48,740 --> 00:13:50,040
Two different time stamps.

226
00:13:50,060 --> 00:13:51,530
Just a couple of seconds later.

227
00:13:51,890 --> 00:13:52,460
Okay.

228
00:13:53,090 --> 00:13:56,430
Now, the hardest part by yourself on the back.

229
00:13:56,450 --> 00:13:57,470
You made it through.