This is the solution video for the exercise.
The first question is what's a good use case for the CHA or car data type?
Just make one up.
Well, remember, the CHA data type is best for data.
Pieces of text that are fixed in size.
So something that's always two characters, always 50 characters.
Something like a zip code, always being five characters in the US.
Right?
I think it's five characters or a year.
If you just needed to store a year and nothing else, four digits or two digits, you could do that
as text using CHA.
But if you need something that varies in length, name, city, anything really that's text that varies,
it's way more common to have text that is variable in size.
Then var cha is more flexible and it's actually more efficient.
But CHA comes out on top.
When we have that fixed data size, it is more efficient compared to var CHA.
It just depends on what we're trying to store.
So fill in the blank here.
We're going to make a table called inventory item.
Name will be a piece of text.
I would make that a var cha because there's no guarantee that our items are going to have the same length
names, so it would be better to make it variable.
And then price here is always less than a million.
So I would do a decimal because we know that we're going to have at most two digits after the decimal
and before the decimal we would at most have what, in 1,000,007 digits.
So I think I would do decimal seven comma two seven digits before the decimal and then two after.
But you could use float.
You could also use double.
But any of those types are going to be correct compared to something like Intt, which is what I would
use for quantity.
If we know we're going to have only a whole number, an integer of some inventory item, we're not going
to have 21.7 teddy bears.
We'll have 21 or 22.
So quantity should just be integer and you could optimize it if there was a maximum.
If you knew you never were going to have whatever the maximum int is at 2 billion.
You might want a medium int or small enter even a tiny int, just depending on what that threshold is.
How many would you ever have at most stored in this column?
Next up, what's the difference between date, time and time stamp?
That's a confusing one.
Both data types store the date and the time, but time stamp takes up less memory.
It's it's, I believe, four bytes compared to eight bytes, something like that.
But it's roughly half.
Last time I checked.
But the biggest difference aside from time stamp being more memory efficient, is that time stamp can
store a much smaller range of dates.
It goes back from 1970 something up to 2038, whereas date time can go from the year 1000 up to the
year 9999.
So big difference in the range.
Otherwise, though, they both store dates and times together.
Okay, so print out the current time.
Now we write some code, so to get the current time we can use cur time, but I have to select it.
So select cur time.
And there's the current time.
It's a little late for me.
Eight at night, just recording before before I go to dinner I guess.
And then the next piece, print out the current date, but not the time.
So I would use cur date.
And for both of these we also have the longer version, which I always forget.
If it's underscore, I think it's current underscore date.
It's the same thing as cur date.
It's just extra characters and then the same thing for time.
We have current time.
So if you did that it's also totally valid.
Next up, print out the current day of the week as a number, so we'll first take the current date.
We could also use the current date time, either one because they both have the date information and
then we'll pass that through to day of week.
I think it is.
But if you ever forget and I definitely forget, we can look at the docs, but I think it is day of
week and then cur date.
There we are, day four.
That's the number for me.
You'll probably see something different.
Sunday is one, Monday is two.
When Tuesday is three and then Wednesday is four.
And it is indeed a Wednesday today.
Next up, print out the current day of the week with the day name, not the number.
So for this one, I believe we want to use day name.
So we have day of week and day name.
And again, it's very easy to mix them up or just forget they exist.
But let's do cur date.
And there we are.
We see Wednesday.
Now, I should also show this with now, remember now gives us the time stamp, the date and the time.
So that works as well.
And it might appear to work with cur time.
It is giving me the current day.
But remember, cur time includes no date information, so de name is just going off of whatever the
current day is.
So this wouldn't work if I tried to.
Do I have my birth?
What is it?
Birth date and birth time?
If I tried to do day name of each birth time in my people table or from people.
They all just say Wednesday, Wednesday, Wednesday, Wednesday.
And it's highly unlikely.
All those dates we stored are Wednesdays, but it's more than unlikely.
It just doesn't work if we only provide a time to day name or any of these other day or date related
functions.
Now, there's actually another way we could have printed out the current day of the week or the number
of the day of the week, which is using the date format function.
Remember how this works.
We provide a format string with these specifiers.
So one of these is day of the week, somewhere in here, maybe already past it, day of the month.
Where are you?
Weekday name.
There we are.
It's percent uppercase W so I could do that select date format and then I will provide it the current
date time or technically it's a timestamp and then my format string will be percent W, uppercase W,
and that also gives me Wednesday.
But this is a little clunkier.
And who is going to remember percent W compared to day name, which I think is just a little bit easier.
Okay.
Next up, print out the current day, day and time using this format.
So we want the two digit month, a/2 digit day slash four digit year.
So for this one, we're definitely going to need to do date format, but I'm going to change my format
string I'll provide now, although I could also just do curr date because we're only using the date
information, we're not using time.
So the current date information.
So we'll find the numeric month right here.
We've got percent.
C Although I think there's another option.
This is the one we want percent lowercase M because it's always going to be two digits 010203 whereas
this one up here percent C we'll just do one, 234 until we get to ten, 11, 12 those will be double
digits.
So if I always want two digits for month, I'll use percent.
M It's kind of tedious trying to find the right to the right specified in that list.
All right.
So that's going to give me my first bit.
Then I want a forward slash.
So that's what it looks like there.
And then after that forward slash I want to digits for the day.
So we probably have the same sort of thing day of the month.
Here we are, two digits, zero zero versus day of the month where we don't have the leading zero.
But I want this one.
So we always have two digits.
That's percent DX.
Percent.
RD All right, so now we have 1005.
Of course, when you're running this, you'll see something different, most likely.
And then we have four digits for the year.
I believe that's going to be something.
Why?
Here it is.
Year.
Numeric, two digits is lowercase, four digits is uppercase Y, and that's exactly what we want.
So let's do that now.
Uppercase Y, percent Uppercase Y.
Now, I forgot the slash.
Here we go.
Ten slash zero five slash 20, 22.
Okay, we got that.
Next up, a little more complicated.
Print out the current day and time using this format.
So we're going to use the same date format function.
But this time instead of cur date, I'm going to pass through now, and this will allow me to work with
the date and the time.
Because remember how now works.
If I just select now versus cur date, that's just the date.
But if I do now, it's the date and time together in a time stamp.
So that's what I'm going to want to do.
Select date format now and then what will my date formatting string look like?
First we want the full month name.
So somewhere in there.
This one right here, my birth name.
The full name is percent Uppercase M and then a space.
So we get October in this case and then we want second.
So that is the day of the month with English suffix percent uppercase RD.
There we go.
October 5th and then at.
That one's easy.
And then finally, we need to get the time here.
Now, there is an easy way to get the time.
If we wanted hours, minutes and seconds.
But I just want hours.
And then I want minutes.
And in fact, 4 hours.
I want a single digit.
I don't want it padded with zeros.
So we need to find hours somewhere on here.
Here we are.
Our.
Here's the padded one with a01.
I don't want that.
I want our that does not have this leading zero so percent lowercase k and then a colon OC.
And then what do we have next?
And again, I didn't specify if it was 12 or 24 hour time, so we're just gonna do 24.
And then the minute and the minute here is percent I so we'll do that percent I And that should do it
October 5th at 20 colon 21 verify.
Same format that we saw here.
Of course the dates are different but it's the same pattern.
Next up, the final piece is to create a tweet table that stores the tweet content, a username and
the time each tweet was created at.
So I'll do this in a file, create table tweets.
This is still very simple for an actual tweets table, there'd be a lot more going on, but we'll have
the I already forgot content, let's call it that.
Content will be a var card and let's limit it to 180 characters.
Then we have the user name and I don't know what the limit is on characters, but let's say I think
somewhere around 20 you don't get extremely long usernames.
It might even be fewer than that, but it is certainly not going to be fixed in length.
So I'm also going to use var for that and then we have time.
It was created, so I'll just call this created add or post it or something like that.
And that's going to be a timestamp.
It could be a date time as well, but I'm going to do a timestamp because it takes up less memory and
I'll have it default to now.
Now instead of using now I could also do current timestamp.
And what's weird, it's kind of a quirk with my SQL for current timestamp.
You can get away without parentheses or with parentheses, but with now you have to have parentheses.
If I don't have prints on now I get an error.
Take a look here.
If I select now with prints, it works.
Select without prints we get an error but with current timestamp.
It works with friends and without.
It's just a weird quirk.
So you can use either one now is a little shorter.
Let's run this and then just try inserting like maybe a single tweet insert into tweets.
Will do name or tweet content.
I think we called it end username values.
And then I'll just say this is my first tweet lol and then my username will be Colt Steel.
All right, I insert that.
Let's select star from tweets and what do we see created at worked?
We've got that now timestamp in there and that's it for this exercise.
Pat yourself on the back.
We did a lot in this section.
It was dull, is boring, but it was important.
Next we move on to some stuff that I think is a bit more exciting, at least as far as SQL goes.