I'll get.
So this is the solution video to our data types problem.
That 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 only use it for text that we know 100 percent has a fixed length.
So the one that I have use a couple of times the state abbreviations.
If you're doing sex like AM 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 the something new and original.
Next up fill in the blanks of this schema.
OK.
So there's a couple of things to talk about.
Item Name is text.
It's gonna be something like I don't know Cabbage Patch doll or baseball hat.
And that should be a far Chaar 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 Vardar 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 when I going to have 2.5.
Cabbage Patch dolls.
Hopefully unless there is some sort of massacre with the Cabbage Patch dolls and the chainsaw.
Or hopefully I'm only going to have whole numbers of them so we'll use it.
But price is a little trickier.
And so I added this note.
Price is always less than 1 million dollars.
OK.
But price is not usually or not exclusively a whole number.
We want to allow for sense.
So we're going to want to use a decimal.
Now you could use a float or 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.
OK so then the question is what do we put here.
It's not just decimal on it's own right we don't just do this.
We have to number three 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 the 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 pricer 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 than zero It's always less than 1 million.
That means that our highest price we could have is nine nine nine nine nine nine point ninety nine.
So if that's the maximum price then all we need to do is count up the total number of digits.
So that is 3 6 7 8 and that's her answer.
Decimal 8 comma 2.
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 in how they're putting to 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.
Where's that time stamp has a range from 1970 to 2038 versus date time goes from 1000 1000 up to year
nine thousand nine hundred ninety nine.
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 the 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 meta
data 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 you know if you wanted to use time stamps to
store birthdays you could but you could run into problems if you have people who are born before 1970.
So again the key differences really boil down to the ranges they support and then how much memory they
take up.
OK moving on.
Print out the current time.
Finally some code so to print out the current time.
It's just a matter of Kuhr time.
Me to select that.
Every go.
Sixteen oh eight 13.
Yours of course will differ.
Most likely print out the current date but not the time so that one you want to do.
Curr date and see April 22nd 2017.
Still my birthday print out the current day of the week.
OK so this one's a little bit different.
There's a couple ways of achieving this.
The first one is to use Select day of week and then we could either give it the date or date or we could
use now.
So today is a Saturday so it gives us 7 just to show you.
And it can replace this with now which is a date time.
They also get 7 but there's another way we could do this which is to use date format and if we go to
the documentation here and refined this date format if we scroll down somewhere on here day that week
where is that.
Here we go.
Day of the week.
Percent w lowercase W.
We could use that use the like date format now or Khurd date comma and the string we want.
It's just percent lower case 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 the week zero is Sunday and 6 is Saturday.
So these are not equivalent Actually day of the week.
Sunday is 1 rather than zero and Saturday 7.
So I guess if we wanted DS to be equivalent we would need to do plus 1 and shift everything by one or
shift everything down one for use in 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 its date underscore format for now and then we just have to figure
out how we get the day that week name.
If you go to the docs it's right above weekday name Sunday to Saturday percent upper case w.
So if we switch back just change it to an uppercase W..
Now we get Saturday OK.
Next up.
Print out the current date and time using this form in month month slash day day slash year year year
year.
OK so we need to use date format and we can just use Kuhr date or we can use now.
I like to use now typically but we were not actually using the time information.
So why don't we just use Kuhr 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 with the month.
So we want month Gnumeric which is present.
No that's not what we want what we want is done here this month.
Numeric 0 to 12.
So we get them lowercase M..
Percent lower case slash.
And then one day of the month numeric 0 0 to 31 years you want those two digits always.
So that's lowercase D.
So we have lowercase m slash or lower D.
And then for a year we want four digits so that's uppercase y.
So let's try that percent lower case M slash percent lower case D slash percent upper case Y.
OK.
So Michael when we go and that works.
Next up.
Print out the current day and time using this format a bit different month the month name and then Anglicize
day at and then hours in 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 abbreviated month name.
Here we go month name uppercase m.
So we want uppercase space and then Anglicized version of the day and where where's that.
Here we go.
Day of the month with English suffix that's what I was trying to say.
So it's uppercase D.
OK 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 upper or lower case H if we want and I guess I didn't specify if we wanted 24
hour time or 12 hours so either these will work I'm just going to do lowercase h colon.
And then for a minute it's lower case.
Well as I is going to get this.
There you go.
I'll work on a case I
so lowercase H.
Colon lowercase i There we go.
April 22nd at 4:15 great.
Now moving on to our final problem create tweets table that stores the tweet content a user name and
the time it was created.
So the tweet itself can vary in length so it should be of our are most likely a username could vary
in length as well.
And then the time it is 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've discussed already is to use time stamp and we want to set the
default to be now.
So let's try top 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.
Now we are char up to 140 characters and we have user name to also be of our char up to 20 characters.
And then finally we have created that or tweeted add or time or whatever you want to call it which will
be a time stamp and then the important part is default now or default current time stamp.
But I like to use now.
So let's try and see if it works.
Make sure you have the right commas in place.
Semi-colon.
And we tried it missing parentheses around.
Now the semi-colon in as well.
OK make some space.
Now we run it and it appears to work.
Let's just make sure by inserting one tweet
so will do content.
User name number we don't need to insert created out manually
and that the content will be.
This is my first tweet and username will be called kept OK.
Let's do a select star from tweets and you can see we have created that and there is a time stamp works
perfectly.
Just double checked 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.
OK.
Now the hardest part.
Put yourself on the back.
You made it through.