So far, all the numbers we've stored have been whole numbers integers, right.
Some flavor of int, if you include tiny int and big int and all that.
But they're whole numbers.
If I try and insert a fractional child into this table 1.5 for the type is tiny int or into doesn't
matter what happens.
Well let's see, I don't get an error select star from parent which I actually call that table parents.
But instead our data in this case was rounded.
Let's try one more example.
Let's insert how about
90.0008?
What did we get back when we select?
Well, we just get 90.
So definitely not a great solution for storing things like prices or anything around finances or mortgage
rates, anything where we need a decimal because we won't be able to use a decimal.
We can only have whole numbers and my SQL will just get rid of anything after the decimal.
If we try and insert a fractional number into an integer column.
So we have some choices, we have some options.
We have one type called decimal.
That's what we're going to cover in this video.
Decimal allows us to store precise decimals, so numbers that might be large might be small, but we'll
always be pretty much guaranteed to be accurate.
Storing decimals in computers in computer memory is tricky, and doing it precisely costs a lot of memory.
Or it can.
And doing math with decimal numbers can be difficult.
So there are different flavors.
We're going to see decimals, but we're also going to see if I keep going float and this thing called
double and they all could be used to store numbers that have decimals, but we're starting with decimal.
The plain old decimal type is the most accurate, it's the most precise.
And here's the syntax.
We don't just say decimal.
Unfortunately, we have to provide two numbers.
The first number says the total number of digits that we want to allocate for a given number.
So five digits total.
And then how many of those digits come after a decimal?
So this is saying five total numbers, two of which come after the decimal.
But this is a maximum size.
That doesn't mean we have to have five numbers.
It just says up to five numbers and up to two digits after the decimal.
So here's a visualization of it.
This right here, 999.99 would be the largest number we could store in a column with this type.
So five total characters, two of which are after the decimal means we can have three before the decimal.
That's the largest number.
That doesn't mean, like I said, that we have to store five digits.
We could instead have 0.1 and that would be valid, but it would not be valid to have 1,520.1 or something
like that.
We have to have maximum of five digits, but it's really maximum of three before the decimal and then
two after.
So let's take a look at this.
In practice, why don't we create a table?
Let's call it products.
We'll keep it really simple.
I'm not going to bother with any of the other columns that would make this useful, like a product name,
a description, anything like that.
We'll just add a price.
So price will be a decimal.
And why don't we just stick with the five comma two?
Because it's simple enough and most product prices will only have two decimals, right?
We're not going to have like 0.75 to 1 cents most of the time if we're talking about a product with
a set price.
Now, if we're talking about financial markets or cryptocurrency, then yeah, you might own 0.012340001
of something.
But in this case, I'm keeping it simple.
Let's say the price tag on some item, it's probably just going to have two two numbers at most after
the decimal.
Okay, so let's try inserting into products price values.
Something simple like $4.50.
4.50.
No problem.
Select star from products.
And there it is.
So again, we're not saying it has to be five digits.
It's saying up to five total digits, two of which can come after the decimal.
So I could also do something like 456 point let's do nine nine.
That is five total characters or digits, two of which are after the decimal.
So that looks like it works, right?
No problem.
But where I see some students get confused is they think they can do this.
How about eight 3 to 4 7.1?
So this is still under five total digits.
It's under that limit that this says here.
But remember, that's not saying five digits anywhere.
It's saying five digits, two of which come after the decimal.
So this would be four digits before the decimal, which is not allowed.
We get an out of range error and it's not inserted.
Now, what about if I do something like, I don't know, 5.0 to 6?
So that's three digits after the decimal.
I don't get an error this time.
Earlier, I just got a straight up error.
Right?
Right here.
It said out of range.
It didn't insert when I did a number that was too big, too significant, too many digits before the
decimal.
But here, if I try and store a number that is more precise or more digits after the decimal than I've
allocated, in this case three digits, I get a warning but not an error.
So I can look at the warning if I wanted to with show warnings.
And it says your data was truncated for column price.
So that's different.
It didn't outright reject the value.
It's just less precise now than it was originally.
If I do a select star from products, there it is.
5.03.
It was rounded up to fit within those two digits.
So of course I could say I want three digits or ten digits.
I could have a lot of digits after the decimal, but if I exceed that, when I actually insert a value,
it will be truncated.
But if I exceed the maximum size of a number, the number of digits before the decimal, then I'm going
to get an error.
So that's pretty much it for the decimal data type.
The whole purpose, of course, is to store numbers that do have decimals.
So if you're going to be working with whole numbers, there's no reason to bother with the decimal type
just to use INT But as I hinted at at the beginning, there's other ways of storing numbers that have
decimals like float.
So what's the difference?
When do we use each?
We'll cover that in the next video.