13. Mathematical Functions in SQL
SQL supports various mathematical functions which are useful for performing arithmetic operations on data. Below are the key arithmetic operators and mathematical functions, their definitions, and usage examples.
13.1. Arithmetic Operators
The following are the basic arithmetic operators supported by standard ANSI SQL-92:
- +: Addition
- -: Subtraction
- *: Multiplication
- /: Division
- %: Modulo (not ANSI SQL supported, but commonly available)
The modulo operator determines the integer remainder of the division. While not part of ANSI SQL-92, it is supported by most databases.
13.2. Mathematical Functions
These functions are not standard in ANSI SQL-92 specs, so their availability may vary by RDBMS. However, they are available in many major database systems and will work for the purposes of this tutorial.
- ABS(x): Returns the absolute value of
x
. - SIGN(x): Returns the sign of
x
as -1, 0, or 1 (negative, zero, or positive respectively). - MOD(x, y): Returns the integer remainder of
x
divided byy
(same asx % 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 ofy
. - 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 byd
. - SQRT(x): Returns the square-root value of
x
.
13.3. Example Query
Consider the following example which uses the ROUND
function to round salaries:
SELECT round(salary), firstname
FROM employee_info;
This query selects the salary rounded to the nearest whole value along with the firstname from the employee_info
table.
13.4. Exercises
Use the tables items_ordered
and customers
for the following exercises:
- Select the item and per unit price for each item in the
items_ordered
table. Hint: Divide the price by the quantity.
13.5. Exercise Answers
The SQL query for Exercise #1 is as follows:
SELECT item, SUM(price) / SUM(quantity) AS per_unit_price
FROM items_ordered
GROUP BY item;
This query calculates the per unit price by dividing the sum of the price by the sum of the quantity for each item.
13.6. Advanced Mathematical Functions in SQL
In addition to basic arithmetic operations and functions, SQL offers more advanced mathematical functions that can be useful for various complex calculations. These functions are often part of extended SQL specifications and are supported by many modern RDBMSs.
13.6.1. Trigonometric Functions
Trigonometric functions are used to perform calculations related to angles and periodic functions.
- SIN(x): Returns the sine of
x
(wherex
is in radians). - COS(x): Returns the cosine of
x
(wherex
is in radians). - TAN(x): Returns the tangent of
x
(wherex
is in radians). - ASIN(x): Returns the arc sine of
x
. - ACOS(x): Returns the arc cosine of
x
. - ATAN(x): Returns the arc tangent of
x
. - ATAN2(y, x): Returns the arc tangent of
y/x
, using the signs of both arguments to determine the quadrant of the result.
13.6.2. Logarithmic Functions
Logarithmic functions are used to perform calculations related to logarithms.
- LOG(x): Returns the natural logarithm (base
e
) ofx
. - LOG10(x): Returns the base-10 logarithm of
x
. - EXP(x): Returns
e
raised to the power ofx
.
13.6.3. Exponential and Hyperbolic Functions
Exponential and hyperbolic functions are used for growth and decay calculations as well as certain types of wave calculations.
- EXP(x): Returns the value of
e
(the base of natural logarithms) raised to the power ofx
. - SINH(x): Returns the hyperbolic sine of
x
. - COSH(x): Returns the hyperbolic cosine of
x
. - TANH(x): Returns the hyperbolic tangent of
x
.
13.6.4. Random Number Functions
Random number functions are used to generate random numbers, which can be useful for sampling, simulations, and randomized algorithms.
- RAND(): Returns a random float value between 0 and 1.
- RAND(seed): Returns a random float value between 0 and 1, where
seed
sets the starting point for the random number generator. - NEWID(): Returns a unique identifier, which can be used as a random seed for random number generation in some databases.
13.6.5. Aggregate Functions
Aggregate functions perform a calculation on a set of values and return a single value. These functions are essential for summarizing data.
- SUM(column): Returns the sum of all values in a column.
- AVG(column): Returns the average of all values in a column.
- MIN(column): Returns the minimum value in a column.
- MAX(column): Returns the maximum value in a column.
- COUNT(column): Returns the number of non-null values in a column.
13.6.6. Example Queries
13.6.6.1 Trigonometric Functions
SELECT SIN(angle) AS sine_value,
COS(angle) AS cosine_value,
TAN(angle) AS tangent_value
FROM angles;
This query calculates the sine, cosine, and tangent values for each angle in the angles
table.
13.6.6.2 Logarithmic Functions
SELECT LOG(value) AS natural_log,
LOG10(value) AS base10_log,
EXP(value) AS exponential_value
FROM numeric_values;
This query returns the natural logarithm, base-10 logarithm, and exponential value for each entry in the numeric_values
table.
13.6.6.3 Random Number Functions
SELECT RAND() AS random_value,
RAND(12345) AS seeded_random_value,
NEWID() AS unique_identifier
FROM dual;
This query generates a random value, a seeded random value, and a unique identifier.
13.6.6.4 Aggregate Functions
SELECT SUM(price) AS total_price,
AVG(price) AS average_price,
MIN(price) AS min_price,
MAX(price) AS max_price,
COUNT(*) AS total_items
FROM items_ordered;
This query calculates the total price, average price, minimum price, maximum price, and total number of items from the items_ordered
table.