Performing Math on Numeric Types4:35 with Andrew Chalkley
Operators aren't only for comparing values or concatenating strings. They can be used to perform mathematical operations.
In your sequel learning journey you've come across several operators. 0:01 We've seen [SOUND] the equality and inequality operators. 0:05 You've seen comparison operator's that test the relationships between two values. 0:09 The operator's include less than, greater than, less than or 0:16 equal to, and greater than or equal to. 0:20 You've also seen the concatenation operator, for adding text types together. 0:23 Now, let's turn to operators that can perform mathematical transformations 0:29 on numeric types. 0:34 Firstly, there's the Addition operator that adds two numbers together. 0:36 This is represented by a plus symbol. 0:41 This may be good if you want to add a credit card processing fee to an invoice 0:44 you sent to your clients. 0:47 Next, the Subtraction operator. 0:49 This is represented by a minus symbol. 0:53 You may wanna do this if you're applying a coupon code or a fixed discount. 0:56 The Multiplication operator is represented by a star or asterisk. 1:01 This is great for calculating cells text or interest on a payment. 1:07 And finally, there's the Division operator which is represented by a forward slash. 1:12 Division could be used to calculate equal monthly payments. 1:18 Let's see these operators in action. 1:22 In an earlier video, 1:25 I showed you that select statements can simply output a value. 1:26 For example, select Hello gives you the word Hello. 1:30 You can do this to test SQL's mathematical operators. 1:35 I have set up some examples in the SQL playground on this page. 1:40 Go ahead and open it up and follow along. 1:44 First, there's Addition. 1:47 When we run these statements we see 5 and 7 as you'd expect. 1:49 In Subtraction, select 5- 3, select 12- 20, 1:54 when we run these, we get 2 and -8. 2:01 That's fairly straightforward. 2:06 Next, Multiplication. 2:07 When we run these statements, we get 10 and 30, that's what you'd expect. 2:11 Finally, there's Division. 2:18 Without running these, can you guess what results of all of these stems will be? 2:20 Let's Run it and see. 2:26 2, 2.5, and 2.5. 2:27 The reason the first value is 2 is because of 2:31 the way computers handle whole numbers, or integers. 2:36 If a number doesn't have a decimal place, most programming languages, 2:40 including SQL, will drop the remainder. 2:44 The fractional value that normally appears right after the decimal point. 2:47 In other words, the result rounded down. 2:52 In order to make sure the division provides a floating point number, or 2:56 a number with a decimal place. 3:00 You need to include at least one number with a decimal place. 3:02 Let's see how one of these mathematical operations can be applied 3:05 in a real world example. 3:10 Here's the product syllable again. 3:13 The state of Florida adds sales tax to all sales in brick and mortar stores 3:16 Let's modify this statement to include the sales tax in the price. 3:22 Let's multiply the price by 1.06. 3:27 This is 6% sales tax, and provide a reader friendly label, 3:30 Price in Florida, using the AS keyword. 3:35 When you use an arithmetic operator on a column it uniformly applies it to each 3:42 value in that column. 3:47 Let's see this in action. 3:49 The way that floating point numbers in programming languages like SQL 3:51 generate this bizarre looking remainders like this. 3:55 The reason for this is beyond the scope of this course, but 3:58 to fix it, you can use a function called round. 4:01 The round function takes in two arguments. 4:05 The value you want to round and the number of decimal places you want to round it to. 4:08 In our example, we can use the price manipulation as the first argument. 4:14 And the number two for the number of decimal places as the second argument. 4:21 When we run this now, 4:29 the values returned are more in line with what we were expecting. 4:30
You need to sign up for Treehouse in order to download course files.Sign up