Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.
Start a free Basic trial
to watch this video
Operators aren't only for comparing values or concatenating strings. They can be used to perform mathematical operations.
Mathematical Operators

*
Multiply 
/
Divide 
+
Add 

Subtract
SELECT <numeric column> <mathematical operator> <numeric value> FROM <table>;
Cheat Sheets

0:01
In your sequel learning journey you've come across several operators.

0:05
We've seen [SOUND] the equality and inequality operators.

0:09
You've seen comparison operator's that test the relationships between two values.

0:16
The operator's include less than, greater than, less than or

0:20
equal to, and greater than or equal to.

0:23
You've also seen the concatenation operator, for adding text types together.

0:29
Now, let's turn to operators that can perform mathematical transformations

0:34
on numeric types.

0:36
Firstly, there's the Addition operator that adds two numbers together.

0:41
This is represented by a plus symbol.

0:44
This may be good if you want to add a credit card processing fee to an invoice

0:47
you sent to your clients.

0:49
Next, the Subtraction operator.

0:53
This is represented by a minus symbol.

0:56
You may wanna do this if you're applying a coupon code or a fixed discount.

1:01
The Multiplication operator is represented by a star or asterisk.

1:07
This is great for calculating cells text or interest on a payment.

1:12
And finally, there's the Division operator which is represented by a forward slash.

1:18
Division could be used to calculate equal monthly payments.

1:22
Let's see these operators in action.

1:25
In an earlier video,

1:26
I showed you that select statements can simply output a value.

1:30
For example, select Hello gives you the word Hello.

1:35
You can do this to test SQL's mathematical operators.

1:40
I have set up some examples in the SQL playground on this page.

1:44
Go ahead and open it up and follow along.

1:47
First, there's Addition.

1:49
When we run these statements we see 5 and 7 as you'd expect.

1:54
In Subtraction, select 5 3, select 12 20,

2:01
when we run these, we get 2 and 8.

2:06
That's fairly straightforward.

2:07
Next, Multiplication.

2:11
When we run these statements, we get 10 and 30, that's what you'd expect.

2:18
Finally, there's Division.

2:20
Without running these, can you guess what results of all of these stems will be?

2:26
Let's Run it and see.

2:27
2, 2.5, and 2.5.

2:31
The reason the first value is 2 is because of

2:36
the way computers handle whole numbers, or integers.

2:40
If a number doesn't have a decimal place, most programming languages,

2:44
including SQL, will drop the remainder.

2:47
The fractional value that normally appears right after the decimal point.

2:52
In other words, the result rounded down.

2:56
In order to make sure the division provides a floating point number, or

3:00
a number with a decimal place.

3:02
You need to include at least one number with a decimal place.

3:05
Let's see how one of these mathematical operations can be applied

3:10
in a real world example.

3:13
Here's the product syllable again.

3:16
The state of Florida adds sales tax to all sales in brick and mortar stores

3:22
Let's modify this statement to include the sales tax in the price.

3:27
Let's multiply the price by 1.06.

3:30
This is 6% sales tax, and provide a reader friendly label,

3:35
Price in Florida, using the AS keyword.

3:42
When you use an arithmetic operator on a column it uniformly applies it to each

3:47
value in that column.

3:49
Let's see this in action.

3:51
The way that floating point numbers in programming languages like SQL

3:55
generate this bizarre looking remainders like this.

3:58
The reason for this is beyond the scope of this course, but

4:01
to fix it, you can use a function called round.

4:05
The round function takes in two arguments.

4:08
The value you want to round and the number of decimal places you want to round it to.

4:14
In our example, we can use the price manipulation as the first argument.

4:21
And the number two for the number of decimal places as the second argument.

4:29
When we run this now,

4:30
the values returned are more in line with what we were expecting.
You need to sign up for Treehouse in order to download course files.
Sign up