Mathematical functions in SQL

 Mathematical functions perform operations with numerical expressions and return a result, they operate with numeric data types.

Numeric functions accept numeric input parameters and return numeric values.

Oracle has some functions for working with numbers. Here are some of them.

- abs (x): returns the absolute value of the argument "x".

Example:

 select abs (-20) from dual; - returns 20.

The dual table is a virtual table that exists in all Oracle databases.

- ceil (x): rounds the argument "x" up to an integer.

Example:

 select ceil (12.34) from dual; - returns 13.

- floor (x): rounds the argument "x" down to integer.

select floor (12.34) from dual; --12

- mod (x, y): returns the remainder of the x / y division.

Example:

 select mod (10,3) from dual; - returns 1.

 select mod (10,2) from dual; - returns 0.

- power (x, y): returns the value of "x" raised to the "y" power. 

Example:

 select power (2,3) from dual; - returns 8.

- round (n, d): returns "n" rounded to decimal "d"; if the second argument is omitted, round all the decimal places. If the second argument is positive, the number of decimal places is rounded according to "d"; if it is negative, the number is rounded from the integer to the value of "d".

Examples:

 select round (123.456,2) from dual; - returns "123.46", that is, it rounds from the second decimal place.

 select round (123.456,1) from dual; - 123.5, that is, round from the first decimal place.

 select round (123.456, -1) from dual; - 120, rounds from the first integer value (to the left).

 select round (123.456, -2) from dual; - 100, rounds from the second integer value (to the left).

 select round (123.456) from dual; - 123.

- sign (x): if the argument is a positive value, it returns 1, if it is negative, it returns -1 and 0 if it is 0. 

Examples:

 select sign (-120) from dual; - returns -1

 select sign (120) from dual; - returns 1

- trunc (n, d): truncate a number to the number of decimal places specified by the second argument. If the second argument is omitted, all decimals are truncated. If "d" is negative, the number is truncated from the integer part.

Example:

 select trunc (1234.5678,2) from dual; - returns 1234.56

 select trunc (1234.5678, -2) from dual; - returns 1200

 select trunc (1234.5678, -1) from dual; - returns 1230

 select trunc (1234.5678) from dual; - returns 1234

- sqrt (x): returns the square root of the value sent as an argument. 

Example:

select sqrt (9) from dual; - returns 3

Oracle has trigonometric functions that return radians, calculate sine, cosine, inverses, etc .: aso, asin, atan, atan2, cos, cosh, exp, ln, log, sin, sinh, tan, tanh. We will not see them in detail.

Mathematical functions can be used by sending the name of a numeric field as an argument.