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 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 d.
• 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:

1. 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 (where x is in radians).
• COS(x): Returns the cosine of x (where x is in radians).
• TAN(x): Returns the tangent of x (where x 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) of x.
• LOG10(x): Returns the base-10 logarithm of x.
• EXP(x): Returns e raised to the power of x.

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 of x.
• 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.