|
Mathematical Operators
Standard ANSI SQL-92 supports the following first four basic
arithmetic operators:
| + |
addition |
| - |
subtraction |
| * |
multiplication |
| / |
division |
| % |
modulo |
The modulo operator determines the integer remainder of the
division. This operator is not ANSI SQL supported, however, most
databases support it. The following are some more useful
mathematical functions to be aware of since you might need them.
These functions are not standard in the ANSI SQL-92 specs, therefore
they may or may not be available on the specific RDBMS that you are
using. However, they were available on several major database
systems that I tested. They WILL work on this tutorial.
| ABS(x) |
returns the absolute value of x |
| SIGN(x) |
returns the sign of input x as -1, 0, or 1 (negative,
zero, or positive respectively) |
| MOD(x,y) |
modulo - returns the integer remainder of x divided by y
(same as x%y) |
| FLOOR(x) |
returns the largest integer value that is less than or
equal to x |
| CEILING(x) or CEIL(x) |
returns the smallest integer value that is greater than or
equal to x |
| POWER(x,y) |
returns the value of x raised to the power of y |
| ROUND(x) |
returns the value of x rounded to the nearest whole
integer |
| ROUND(x,d) |
returns the value of x rounded to the number of decimal
places specified by the value d |
| SQRT(x) |
returns the square-root value of x |
For example:
SELECT round(salary), firstname
FROM employee_info
This statement will select the salary rounded to the nearest
whole value and the firstname from the employee_info table.
Review Exercises
1) Select the item and per unit price for each item in the
items_ordered table. Hint: Divide the price by the quantity.
2)
Click the exercise answers link below if you have any problems.
Answers to
these Exercises
Enter SQL Statement
here:
|