1 00:00:01,190 --> 00:00:05,880 In your sequel learning journey you've come across several operators. 2 00:00:05,880 --> 00:00:09,871 We've seen [SOUND] the equality and inequality operators. 3 00:00:09,871 --> 00:00:16,390 You've seen comparison operator's that test the relationships between two values. 4 00:00:16,390 --> 00:00:20,400 The operator's include less than, greater than, less than or 5 00:00:20,400 --> 00:00:23,630 equal to, and greater than or equal to. 6 00:00:23,630 --> 00:00:28,630 You've also seen the concatenation operator, for adding text types together. 7 00:00:29,860 --> 00:00:34,600 Now, let's turn to operators that can perform mathematical transformations 8 00:00:34,600 --> 00:00:36,770 on numeric types. 9 00:00:36,770 --> 00:00:41,250 Firstly, there's the Addition operator that adds two numbers together. 10 00:00:41,250 --> 00:00:44,080 This is represented by a plus symbol. 11 00:00:44,080 --> 00:00:47,830 This may be good if you want to add a credit card processing fee to an invoice 12 00:00:47,830 --> 00:00:49,950 you sent to your clients. 13 00:00:49,950 --> 00:00:53,080 Next, the Subtraction operator. 14 00:00:53,080 --> 00:00:56,250 This is represented by a minus symbol. 15 00:00:56,250 --> 00:01:01,700 You may wanna do this if you're applying a coupon code or a fixed discount. 16 00:01:01,700 --> 00:01:07,130 The Multiplication operator is represented by a star or asterisk. 17 00:01:07,130 --> 00:01:12,210 This is great for calculating cells text or interest on a payment. 18 00:01:12,210 --> 00:01:18,100 And finally, there's the Division operator which is represented by a forward slash. 19 00:01:18,100 --> 00:01:22,350 Division could be used to calculate equal monthly payments. 20 00:01:22,350 --> 00:01:25,180 Let's see these operators in action. 21 00:01:25,180 --> 00:01:26,610 In an earlier video, 22 00:01:26,610 --> 00:01:30,700 I showed you that select statements can simply output a value. 23 00:01:30,700 --> 00:01:35,730 For example, select Hello gives you the word Hello. 24 00:01:35,730 --> 00:01:40,210 You can do this to test SQL's mathematical operators. 25 00:01:40,210 --> 00:01:44,620 I have set up some examples in the SQL playground on this page. 26 00:01:44,620 --> 00:01:47,510 Go ahead and open it up and follow along. 27 00:01:47,510 --> 00:01:49,130 First, there's Addition. 28 00:01:49,130 --> 00:01:54,905 When we run these statements we see 5 and 7 as you'd expect. 29 00:01:54,905 --> 00:02:01,532 In Subtraction, select 5- 3, select 12- 20, 30 00:02:01,532 --> 00:02:06,046 when we run these, we get 2 and -8. 31 00:02:06,046 --> 00:02:07,906 That's fairly straightforward. 32 00:02:07,906 --> 00:02:11,310 Next, Multiplication. 33 00:02:11,310 --> 00:02:16,890 When we run these statements, we get 10 and 30, that's what you'd expect. 34 00:02:18,250 --> 00:02:20,920 Finally, there's Division. 35 00:02:20,920 --> 00:02:25,260 Without running these, can you guess what results of all of these stems will be? 36 00:02:26,560 --> 00:02:27,470 Let's Run it and see. 37 00:02:27,470 --> 00:02:31,909 2, 2.5, and 2.5. 38 00:02:31,909 --> 00:02:36,170 The reason the first value is 2 is because of 39 00:02:36,170 --> 00:02:40,400 the way computers handle whole numbers, or integers. 40 00:02:40,400 --> 00:02:44,820 If a number doesn't have a decimal place, most programming languages, 41 00:02:44,820 --> 00:02:47,600 including SQL, will drop the remainder. 42 00:02:47,600 --> 00:02:52,660 The fractional value that normally appears right after the decimal point. 43 00:02:52,660 --> 00:02:55,010 In other words, the result rounded down. 44 00:02:56,200 --> 00:03:00,460 In order to make sure the division provides a floating point number, or 45 00:03:00,460 --> 00:03:02,610 a number with a decimal place. 46 00:03:02,610 --> 00:03:05,710 You need to include at least one number with a decimal place. 47 00:03:05,710 --> 00:03:10,830 Let's see how one of these mathematical operations can be applied 48 00:03:10,830 --> 00:03:12,380 in a real world example. 49 00:03:13,570 --> 00:03:15,220 Here's the product syllable again. 50 00:03:16,630 --> 00:03:22,300 The state of Florida adds sales tax to all sales in brick and mortar stores 51 00:03:22,300 --> 00:03:27,060 Let's modify this statement to include the sales tax in the price. 52 00:03:27,060 --> 00:03:30,152 Let's multiply the price by 1.06. 53 00:03:30,152 --> 00:03:35,167 This is 6% sales tax, and provide a reader friendly label, 54 00:03:35,167 --> 00:03:38,550 Price in Florida, using the AS keyword. 55 00:03:42,380 --> 00:03:47,070 When you use an arithmetic operator on a column it uniformly applies it to each 56 00:03:47,070 --> 00:03:48,450 value in that column. 57 00:03:49,480 --> 00:03:51,120 Let's see this in action. 58 00:03:51,120 --> 00:03:55,330 The way that floating point numbers in programming languages like SQL 59 00:03:55,330 --> 00:03:58,330 generate this bizarre looking remainders like this. 60 00:03:58,330 --> 00:04:01,810 The reason for this is beyond the scope of this course, but 61 00:04:01,810 --> 00:04:05,500 to fix it, you can use a function called round. 62 00:04:05,500 --> 00:04:08,700 The round function takes in two arguments. 63 00:04:08,700 --> 00:04:14,210 The value you want to round and the number of decimal places you want to round it to. 64 00:04:14,210 --> 00:04:18,980 In our example, we can use the price manipulation as the first argument. 65 00:04:21,650 --> 00:04:26,830 And the number two for the number of decimal places as the second argument. 66 00:04:29,270 --> 00:04:30,780 When we run this now, 67 00:04:30,780 --> 00:04:34,570 the values returned are more in line with what we were expecting.