Mathematical Functions

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 we 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.

Use these tables for the exercises
items_ordered
customers

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.

    Show Answer

 

Enter SQL Statement here:

Results will be displayed here

Previous article
Next article

All Advanced Courses

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis