1 00:00:00,090 --> 00:00:03,610 So far, all the numbers we've stored have been whole numbers integers, right. 2 00:00:03,630 --> 00:00:07,650 Some flavor of int, if you include tiny int and big int and all that. 3 00:00:07,650 --> 00:00:08,940 But they're whole numbers. 4 00:00:08,940 --> 00:00:16,410 If I try and insert a fractional child into this table 1.5 for the type is tiny int or into doesn't 5 00:00:16,410 --> 00:00:18,020 matter what happens. 6 00:00:18,030 --> 00:00:24,660 Well let's see, I don't get an error select star from parent which I actually call that table parents. 7 00:00:25,020 --> 00:00:27,870 But instead our data in this case was rounded. 8 00:00:27,870 --> 00:00:29,370 Let's try one more example. 9 00:00:29,370 --> 00:00:31,620 Let's insert how about 10 00:00:31,620 --> 00:00:37,950 90.0008? 11 00:00:38,160 --> 00:00:39,990 What did we get back when we select? 12 00:00:39,990 --> 00:00:41,970 Well, we just get 90. 13 00:00:42,420 --> 00:00:50,430 So definitely not a great solution for storing things like prices or anything around finances or mortgage 14 00:00:50,430 --> 00:00:55,860 rates, anything where we need a decimal because we won't be able to use a decimal. 15 00:00:55,860 --> 00:01:00,750 We can only have whole numbers and my SQL will just get rid of anything after the decimal. 16 00:01:00,750 --> 00:01:05,850 If we try and insert a fractional number into an integer column. 17 00:01:06,000 --> 00:01:08,910 So we have some choices, we have some options. 18 00:01:08,910 --> 00:01:10,740 We have one type called decimal. 19 00:01:10,740 --> 00:01:12,440 That's what we're going to cover in this video. 20 00:01:12,450 --> 00:01:19,890 Decimal allows us to store precise decimals, so numbers that might be large might be small, but we'll 21 00:01:19,890 --> 00:01:23,670 always be pretty much guaranteed to be accurate. 22 00:01:24,060 --> 00:01:31,530 Storing decimals in computers in computer memory is tricky, and doing it precisely costs a lot of memory. 23 00:01:31,530 --> 00:01:32,400 Or it can. 24 00:01:32,850 --> 00:01:36,270 And doing math with decimal numbers can be difficult. 25 00:01:36,270 --> 00:01:38,040 So there are different flavors. 26 00:01:38,040 --> 00:01:42,690 We're going to see decimals, but we're also going to see if I keep going float and this thing called 27 00:01:42,690 --> 00:01:48,300 double and they all could be used to store numbers that have decimals, but we're starting with decimal. 28 00:01:48,360 --> 00:01:52,230 The plain old decimal type is the most accurate, it's the most precise. 29 00:01:52,230 --> 00:01:53,430 And here's the syntax. 30 00:01:53,430 --> 00:01:55,440 We don't just say decimal. 31 00:01:55,440 --> 00:01:58,470 Unfortunately, we have to provide two numbers. 32 00:01:58,470 --> 00:02:05,160 The first number says the total number of digits that we want to allocate for a given number. 33 00:02:05,160 --> 00:02:06,750 So five digits total. 34 00:02:06,750 --> 00:02:10,259 And then how many of those digits come after a decimal? 35 00:02:10,740 --> 00:02:15,150 So this is saying five total numbers, two of which come after the decimal. 36 00:02:15,150 --> 00:02:17,070 But this is a maximum size. 37 00:02:17,070 --> 00:02:20,010 That doesn't mean we have to have five numbers. 38 00:02:20,010 --> 00:02:24,840 It just says up to five numbers and up to two digits after the decimal. 39 00:02:24,840 --> 00:02:26,700 So here's a visualization of it. 40 00:02:27,120 --> 00:02:35,310 This right here, 999.99 would be the largest number we could store in a column with this type. 41 00:02:35,760 --> 00:02:42,300 So five total characters, two of which are after the decimal means we can have three before the decimal. 42 00:02:42,330 --> 00:02:43,890 That's the largest number. 43 00:02:43,920 --> 00:02:47,070 That doesn't mean, like I said, that we have to store five digits. 44 00:02:47,070 --> 00:02:58,470 We could instead have 0.1 and that would be valid, but it would not be valid to have 1,520.1 or something 45 00:02:58,470 --> 00:02:59,220 like that. 46 00:02:59,220 --> 00:03:06,390 We have to have maximum of five digits, but it's really maximum of three before the decimal and then 47 00:03:06,390 --> 00:03:07,440 two after. 48 00:03:07,890 --> 00:03:09,600 So let's take a look at this. 49 00:03:09,600 --> 00:03:12,330 In practice, why don't we create a table? 50 00:03:13,880 --> 00:03:15,170 Let's call it products. 51 00:03:15,170 --> 00:03:16,190 We'll keep it really simple. 52 00:03:16,190 --> 00:03:22,250 I'm not going to bother with any of the other columns that would make this useful, like a product name, 53 00:03:22,700 --> 00:03:24,410 a description, anything like that. 54 00:03:24,410 --> 00:03:25,790 We'll just add a price. 55 00:03:26,390 --> 00:03:28,430 So price will be a decimal. 56 00:03:28,430 --> 00:03:30,980 And why don't we just stick with the five comma two? 57 00:03:31,430 --> 00:03:36,620 Because it's simple enough and most product prices will only have two decimals, right? 58 00:03:36,620 --> 00:03:42,800 We're not going to have like 0.75 to 1 cents most of the time if we're talking about a product with 59 00:03:42,800 --> 00:03:43,640 a set price. 60 00:03:43,640 --> 00:03:51,560 Now, if we're talking about financial markets or cryptocurrency, then yeah, you might own 0.012340001 61 00:03:51,560 --> 00:03:52,730 of something. 62 00:03:53,060 --> 00:03:55,400 But in this case, I'm keeping it simple. 63 00:03:55,400 --> 00:04:00,320 Let's say the price tag on some item, it's probably just going to have two two numbers at most after 64 00:04:00,320 --> 00:04:01,010 the decimal. 65 00:04:01,780 --> 00:04:08,710 Okay, so let's try inserting into products price values. 66 00:04:09,220 --> 00:04:12,220 Something simple like $4.50. 67 00:04:13,480 --> 00:04:15,030 4.50. 68 00:04:15,460 --> 00:04:16,240 No problem. 69 00:04:16,240 --> 00:04:17,829 Select star from products. 70 00:04:18,220 --> 00:04:19,240 And there it is. 71 00:04:19,480 --> 00:04:22,300 So again, we're not saying it has to be five digits. 72 00:04:22,300 --> 00:04:26,980 It's saying up to five total digits, two of which can come after the decimal. 73 00:04:27,970 --> 00:04:35,440 So I could also do something like 456 point let's do nine nine. 74 00:04:35,560 --> 00:04:40,810 That is five total characters or digits, two of which are after the decimal. 75 00:04:41,230 --> 00:04:43,000 So that looks like it works, right? 76 00:04:43,000 --> 00:04:43,960 No problem. 77 00:04:43,960 --> 00:04:48,250 But where I see some students get confused is they think they can do this. 78 00:04:48,990 --> 00:04:53,730 How about eight 3 to 4 7.1? 79 00:04:53,910 --> 00:04:57,120 So this is still under five total digits. 80 00:04:57,240 --> 00:05:00,020 It's under that limit that this says here. 81 00:05:00,030 --> 00:05:03,150 But remember, that's not saying five digits anywhere. 82 00:05:03,180 --> 00:05:06,560 It's saying five digits, two of which come after the decimal. 83 00:05:06,570 --> 00:05:11,020 So this would be four digits before the decimal, which is not allowed. 84 00:05:11,040 --> 00:05:14,640 We get an out of range error and it's not inserted. 85 00:05:15,290 --> 00:05:22,940 Now, what about if I do something like, I don't know, 5.0 to 6? 86 00:05:22,940 --> 00:05:26,030 So that's three digits after the decimal. 87 00:05:26,720 --> 00:05:28,760 I don't get an error this time. 88 00:05:29,480 --> 00:05:31,370 Earlier, I just got a straight up error. 89 00:05:31,370 --> 00:05:31,700 Right? 90 00:05:31,700 --> 00:05:32,060 Right here. 91 00:05:32,060 --> 00:05:33,110 It said out of range. 92 00:05:33,110 --> 00:05:38,750 It didn't insert when I did a number that was too big, too significant, too many digits before the 93 00:05:38,750 --> 00:05:39,380 decimal. 94 00:05:39,500 --> 00:05:45,770 But here, if I try and store a number that is more precise or more digits after the decimal than I've 95 00:05:45,770 --> 00:05:50,690 allocated, in this case three digits, I get a warning but not an error. 96 00:05:50,990 --> 00:05:54,680 So I can look at the warning if I wanted to with show warnings. 97 00:05:54,680 --> 00:05:58,430 And it says your data was truncated for column price. 98 00:05:59,630 --> 00:06:00,800 So that's different. 99 00:06:00,800 --> 00:06:02,960 It didn't outright reject the value. 100 00:06:02,960 --> 00:06:07,490 It's just less precise now than it was originally. 101 00:06:07,490 --> 00:06:11,180 If I do a select star from products, there it is. 102 00:06:11,180 --> 00:06:12,620 5.03. 103 00:06:12,620 --> 00:06:16,160 It was rounded up to fit within those two digits. 104 00:06:16,550 --> 00:06:19,670 So of course I could say I want three digits or ten digits. 105 00:06:19,670 --> 00:06:25,550 I could have a lot of digits after the decimal, but if I exceed that, when I actually insert a value, 106 00:06:25,550 --> 00:06:26,780 it will be truncated. 107 00:06:26,780 --> 00:06:32,660 But if I exceed the maximum size of a number, the number of digits before the decimal, then I'm going 108 00:06:32,660 --> 00:06:33,590 to get an error. 109 00:06:34,250 --> 00:06:37,040 So that's pretty much it for the decimal data type. 110 00:06:37,340 --> 00:06:40,490 The whole purpose, of course, is to store numbers that do have decimals. 111 00:06:40,490 --> 00:06:44,360 So if you're going to be working with whole numbers, there's no reason to bother with the decimal type 112 00:06:44,360 --> 00:06:49,280 just to use INT But as I hinted at at the beginning, there's other ways of storing numbers that have 113 00:06:49,280 --> 00:06:51,010 decimals like float. 114 00:06:51,020 --> 00:06:52,370 So what's the difference? 115 00:06:52,370 --> 00:06:53,620 When do we use each? 116 00:06:53,630 --> 00:06:55,220 We'll cover that in the next video.