Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed Reporting with SQL!
You have completed Reporting with SQL!
Preview
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
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
In your SQL 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 operators that
test the relationships between two values.
0:09
The operators 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 statements 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 products table 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 upYou need to sign up for Treehouse in order to set up Workspace
Sign up