# SQL Mathematical Functions and Operators

Mathematical functions and operators operate on numbers and return numbers. If the type of the input argument or arguments is not a number type, NuoDB enforces coercion rules as described by Data Type Conversion to convert the arguments to numbers.

## Mathematical Operators

Mathematical operators affect one or two values, perform a mathematical operation, and return a value of a numeric data type.

Operator Usage Description

`+`

`a` `+` `b`

Addition of numeric quantities `a` and `b`

`-`

`a` `-` `b`

Subtraction of numeric quantity `b` from `a`

`*`

`a` `*` `b`

Multiplication of numeric quantities `a` and `b`

`/`

`a` `/` `b`

Division of numeric quantity `a` by `b`

`%`

`a` `%` `b`

Modulus, or remainder, from dividing `a` by `b`

## Comparison Operators

Numeric comparison operators draw a conclusion based on two numeric values (such as whether one is larger than the other) and returns a value of type boolean, set to either true or false.

Operator Description

`<`

less than

`>`

greater than

`<=`

less than or equal to

`>=`

greater than or equal to

`=`

equal

`<>` or `!=`

no equal

## Mathematical Functions

In the following table `dp` indicates a `DOUBLE PRECISION` number and `numeric` indicates a fixed point number.

Function Description

ABS (`x`)

Absolute value function. Returns the absolute value of a number. The absolute value of a number is its distance from 0 on the number line.

``````SELECT abs(-34.8) FROM dual;
[ABS]
------
34.8``````

CEIL (`dp` | `numeric`)
or
CEILING (`dp` | `numeric`)

Rounds a number up; returns the smallest integer that is greater than or equal to a given numeric expression. The return type has the same type precision and scale as the input type.

``````SELECT ceil(98.6) FROM dual;
[CEIL]
-------
99

SELECT ceiling(95.3) FROM dual;
[CEILING]
----------
96``````

DEGREES (`dp`)

``````SELECT degrees(0.5) FROM dual;
[DEGREES]
-----------------
28.64788975654116``````

FLOOR(`dp` | `numeric`)

Rounds a number down; returns the largest number that is less than or equal to the specified number. The return type has the same type precision and scale as the input type. If the input type is not a numeric type, If the input type is not a number, the type transition matrix is used for converting the input to a number. See Data Type Conversion for details on applied coercions.

``````SELECT floor (98.6) FROM dual;
[FLOOR]
--------
98``````

MOD(`dividend`, `divisor`)

Returns remainder of the division from two numeric values. See Data Type Conversion for details on how NuoDB computes.

``````SELECT MOD(20,3) FROM dual;
[%]
----
2``````

PI()

Returns a constant value for pi.

``````SELECT pi() FROM dual;
[PI]
-----------------
3.141592653589793``````

POWER(a, b)

Returns a double precision number representing `a` to the power `b`.

``````SELECT power(2, 3) FROM dual;
[POWER]
--------
8``````

``````SELECT radians (45.0) FROM dual;
------------------
0.7853981633974483``````

RAND()

Random double precision value in the range 0.0 ≤ x < 1.0.

``````SELECT rand() FROM dual;
[RAND]
------------------
0.4449284450040766``````

RAND(int)

A call to `RAND`(`int`) consistently, but arbitrarily maps integers to sequences of doubles in the range [0, 1). Calling `RAND()` with sequential values as its argument will yield a psuedo-random sequence of uniformly distributed numbers. Be aware that `RAND()` is not suitable for cryptographic purposes.

ROUND(`dp` | `numeric`)

Round to nearest integer. The return type preserves the type, precision and scale of the input argument.

``````SELECT round(42.4) FROM dual;
[ROUND]
--------
42``````

ROUND(`dp` | `numeric`, `s` )

Round `dp` (or `numeric`) to `s` decimal places. The return type preserves the type, precision and scale of the input argument.

``````SELECT round(42.4382,2) FROM dual;
[ROUND]
--------
42.4400``````

SQRT(`dp` | `numeric`)

Square root of the input value as a double precision number.

``````SELECT sqrt(2.0) FROM dual;
[SQRT]
-----------------
1.414213562373095``````

## Trigonometric Functions

Trigonometric functions are defined on a double precision input value and return a double precision value. NuoDB supports the following trigonometric functions:

Function Description

ACOS(`x`)

inverse cosine

ASIN(`x`)

inverse sine

ATAN(`x`)

inverse tangent

ATAN2(`y`, `x`)

inverse tangent of `y`/`x`

COS(`x`)

cosine

COT(`x`)

cotangent

SIN(`x`)

sine

TAN(`x`)

tangent

## Bitwise Operators

NuoDB supports the following bitwise operators:

Operator Description

`&`

Bitwise `AND`

`|`

Bitwise `OR`

`^`

Bitwise `XOR`

`~`

Bitwise `NOT`

Parameters to these operators must be of a type that can be converted to a `BIGINT`.

The result of any of the above operations is a 64-bit integer (a SQL `BIGINT`).