# SQL Mathematical Functions and Operators

You can use mathematical operators in the target list, in the `WHERE` clause of a `SELECT` statement, or anywhere else a numeric result may be appropriate. This sometimes will include the `ORDER BY` clause, a `JOIN` qualifier, or a `GROUP BY` clause.

## 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.
````SELECT ceil(98.6) FROM dual;`
ceiling
--------
99

`SELECT ceiling(95.3) FROM dual;`
ceiling
--------
96```
DEGREES (`dp`) Radians to degrees
````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.
````SELECT floor (98.6) FROM dual;`
floor
-----
98```
MOD(`dividend`, `divisor`) Returns remainder of the division from two integer values.
```SELECT MOD(20,3) FROM dual;
-
2```
PI()

Returns a constant value for pi.

````SELECT pi() FROM `
pi
-----------------
3.141592653589793```
POWER(a, b) Takes `a` to the power `b`.
````SELECT power(2, 3) FROM dual;`
power
------
8```

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

RAND() Random value in the range 0.0 <= x < 1.0.
````SELECT rand() FROM dual;`
rand
------------------
0.1933957548781278```
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.
````SELECT round(42.4) FROM dual;`
round
------
42```
ROUND(`dp``numeric`, `s` ) Round `dp` (or `numeric`) to `s` decimal places.
````SELECT round(42.4382,2) FROM dual;`
round
------
42.44```
SQRT(`dp` | `numeric`) Square root of `dp` (or `numeric`) up to 15 decimal points.
````SELECT sqrt(2.0) FROM dual;`
sqrt
-----------------
1.414213562373095```

## Trigonometric Functions

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`). It is displayed in the format of the value on the left hand side of the operator. That is, if the value on the left hand side of the operator is `BINARY`, the result will be displayed as `BINARY` and if the result on the left hand side of the operator is an `INTEGER`, the result will be displayed as an `INTEGER`.

### Examples

Insert the values: integer 79 and binary 0x18, represented in binary as:
```0100 1111 0001 1000```

``````CREATE TABLE testtab (int_col INTEGER, bin_col BINARY(8));
INSERT INTO testtab VALUES (79, 0x18);

SELECT int_col | bin_col FROM testtab;```
--
95

`SELECT int_col & bin_col FROM testtab;`
-
8
```
SELECT int_col ^ bin_col FROM testtab;```
--
87
```
SELECT bin_col | int_col FROM testtab;```
--
5F

`SELECT bin_col  & int_col FROM testtab;`
--
08
```
SELECT bin_col  ^ int_col FROM testtab;```
--
57```