Okay so this is the solution video to our data types.
Problem set.
First one here.
What's a good use case for char?
So there's not a ton of times that I end up using them, but remember they are fixed length, so we
have to specify exactly the length of the char that we want for that column.
So we often use it for text that we know when 100% has a fixed length.
So the one that I've used a couple of times is state abbreviations.
If you're doing sex like M or F, or perhaps you're working on a project where, I don't know, let's
say you're working with companies and you want an abbreviated name for every company that's only five
characters or four characters long, then you could use Char as well.
Hopefully you came up with something better than me, something new and original.
Next up, fill in the blanks of this schema.
So there's a couple of things to talk about.
Item name is text.
It's going to be something like, I don't know, Cabbage Patch Doll or baseball hat.
And that should be a var char.
Most likely you could get away with using a char.
But there's no reason to do that because it's not fixed length, so it makes more sense to do var char.
And then quantity.
I'll skip price for a moment.
Quantity makes sense to be an integer.
We are never going to have partial items in stock.
We're not going to have 2.5 Cabbage Patch dolls, hopefully, unless there was some sort of massacre
with the Cabbage Patch dolls and a chainsaw.
We're hopefully only only going to have whole numbers of them, so we'll use INT.
But price is a little trickier.
And so I added this note price is always less than $1 million, but price is not usually or not exclusively
a whole number.
We want to allow $0.04, so we're going to want to use a decimal.
Now you could use a float or a double, but as we discussed, decimals better for monetary stuff because
it's more precise.
You don't have to worry about losing precision as you get into longer numbers.
So then the question is, what do we put here?
It's not just decimal on its own, right?
We don't just do this.
We have two numbers we pass in.
And those two numbers, if you can remember the names, I believe the first one is precision and the
second one is scale.
Don't quote me on that, though, but basically it's a total number of digits, followed by the number
of digits after the decimal.
So we can start with the number of digits after the decimal, which when you're working with price or
monetary information, it's pretty much always two.
So then the question becomes how many total digits do we need?
And if I said it's always less one, then excuse me, it's always less than 1 million.
That means that our highest price we could have is 999999.99.
So if that's the maximum price, then all we need to do is count up the total number of digits.
So that is three, six, seven, eight.
And that's your answer.
Decimal eight, comma two.
Next up.
What's the difference between date time and time stamp?
So I have the documentation open for date date, time and time stamp.
And the first thing I'll say is that there's a couple of differences.
There are a few technical differences as far as how they're stored and the ranges that they support.
And then there's a big difference into how they're put into use most of the time.
So both of them, let's start off.
They both store date and time information, so they store the date and then the time.
However, there is a big difference in the range that they support.
So time stamps have a much smaller range.
If you remember, we went over this, whereas that time stamp has a range from 1970 to 2038 versus date
time goes from 1000 year 1000 up to year 9999.
So that's one significant difference.
The other is the size of the memory.
They take up a date.
Time is roughly twice as large as a time stamp.
Which brings us to the final difference, which is how they're actually put into practice.
And typically time stamps are used, at least in my experience exclusively when we are trying to add
metadata about when something is created or updated to a table.
Otherwise I use date time.
Now that doesn't mean that you can't use time stamp in.
If you wanted to use timestamp to store birthdays, you could, but you could run into problems if you
have people who are born before 1970.
So again, the key difference is really boil down to the ranges they support and then how much memory
they take up.
Moving on, print out the current time.
Finally, some code.
So to print out the current time, it's just a matter of cur time and we need to select that.
There we go.
1608 13.
Yours, of course, will differ.
Most likely print out the current date, but not the time.
So that one we want to do cur date.
You can see April 22nd, 2017.
It's still my birthday.
Print out the current day of the week.
Okay, so this one's a little bit different.
There's a couple of ways of achieving this.
The first one is to use select day of week, and then we could either give it the date per date or we
could use now.
So today is a Saturday, so it gives us seven.
Just to show you, I can replace this with now, which is a date time and I also get seven.
But there's another way we could do this, which is to use date format.
And if we go to the documentation here.
And we find this date format.
If we scroll down somewhere on here, it's day of the week.
Where is that?
Here we go.
Day of the week.
Percent w lowercase w though we could use that do select date format now or cur date comma and the string
we want is just percent lowercase w.
And actually, I didn't know this until now.
There is a difference.
That's kind of fascinating.
If we read this closely, it says day of the week, zero is Sunday and six is Saturday.
So these are not equivalent actually day of the week.
Sunday is one rather than zero and Saturday is seven.
So I guess if we wanted these to be equivalent, we would need to do a plus one and shift everything
by one or shift everything down one if we're using the previous.
Interesting.
No idea why that's the case, but it is what it is.
So the next thing is to print out the current day of the week.
But I want the date name, day name, Monday, Tuesday, Wednesday, and so on.
I wouldn't expect you to remember this, but there is a day name that we can use like that select day
name, or we can do our format, date or date format.
I always mix up the order there, but it's date underscore format for now and then we just have to figure
out how we get the day of the week name.
So if we go to the docs it's right above weekday name, sunday to Saturday percent uppercase w.
So if we switch back, just change it to an uppercase W now we get Saturday.
Next up, print out the current date in time using this format.
Month, month slash.
Day day slash.
Yea yea.
Yea.
Yea yea.
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,
but we're not actually using the time information.
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
it, if we go to the docs we want, first of all was the month, so we want month numeric, which is
percent.
Oh no, that's not what we want.
What we want is down here this month numeric 0 to 12 so we get m lowercase m percent lowercase m slash
and then we want day of the month numeric zero 0 to 31 because we want those two digits always.
So that's lowercase d.
So we have lowercase M slash lowercase D and then four year.
We want four digits.
So that's uppercase y.
So let's try that.
Percent lowercase m slash.
Percent lowercase dx slash.
Percent uppercase y.
Oh, semicolon.
There we go.
And that works.
Next up, print out the current day and time using this format.
It's a bit different.
Once a month name and then the Anglicized day at and then hours and minutes.
So let's start off with what we know.
We're going to need to use date format and we need to use now because we need date and time rather than
date, which gives us just the date.
Then our format string.
First thing we want is the month name.
Which is right.
Where are you?
This is a abbreviated month name.
Here we go.
Month name.
Uppercase M.
So we want uppercase M space.
And then the Anglicized version of the day.
And where is that?
Here we go.
Day of the month with English suffix.
That's what I was trying to say.
So it's Uppercase RD.
Let's just see if that works to start.
There we go.
April 22nd, followed by at no.
Years necessary.
So at and then hours and minutes.
So again.
Hours.
We have a lowercase h if we want.
I guess I didn't specify if we wanted 24 hour time or 12 hours, so either of these will work.
I'm just going to do lowercase h colon and then 4 minutes.
That's lowercase.
Well, let's see.
I is going to give us.
There we go.
That will work.
Lowercase I.
Okay.
So lowercase h.
Colon lowercase I.
There we go.
April 22nd at 415.
Great.
Now moving on to our final problem.
Create a tweet table that stores the tweet content, a username and the time it was created.
So the tweet itself can vary in length, so it should be a var char cha.
Most likely a username could vary in length as well, and then the time it's created could be a date
time.
I mean, it could just be a time if you wanted it to.
But what makes the most sense, as we discussed already, is to use timestamp and we want to set the
default to be now.
So let's try it.
It's hop over here and the first thing we want is a create table tweets and then we're going to have
tweet content.
So we could call that whatever we want.
I'll just call it content.
And that will be a var char up to 140 characters.
Then we have username which will also be a var char up to 20 characters.
And then finally we have created add or tweeted at or time or whatever you want to call it, which will
be a timestamp.
And then the important part is default now or default current timestamp, but I like to use now, so
let's try it, see if it works.
Make sure we have the right commas in place.
Semicolon.
And good thing we tried it.
Missing parentheses around now to add the semicolon in as well.
Make some space.
Now we run it and it appears to work.
Let's just make sure by inserting one tweet.
So we'll do content username.
Remember, we don't need to insert created app manually.
And let's see content will be this is my first tweet and username will be called Cat.
OC.
Let's do a select star from tweets.
And you can see we have created that in there as a time stamp.
Works perfectly.
Just double check by inserting something else.
Let's just change it slightly.
This is my second tweet.
Now if we select Star.
Two different time stamps.
Just a couple of seconds later.
Okay.
Now, the hardest part by yourself on the back.
You made it through.