SQL | Numeric Functions

SQL provides a rich set of numeric functions that allow you to perform operations on data. These functions can be categorized under mathematical, trigonometric, and other specific types of operations depending on the database system. Below are some commonly used numeric functions across different databases:

1. Basic Mathematical Functions:

ABS(x): Returns the absolute value of x.
```
SELECT ABS(-25);  -- Returns 25
```
ROUND(x, d): Rounds the number x to d decimal places. If d is omitted, it rounds to the nearest whole number.
```
SELECT ROUND(123.4567, 2);  -- Returns 123.46
```
CEIL(x) or CEILING(x): Returns the smallest integer greater than or equal to x.
```
SELECT CEIL(123.01);  -- Returns 124
```
FLOOR(x): Returns the largest integer less than or equal to x.
```
SELECT FLOOR(123.99);  -- Returns 123
```
MOD(x, y): Returns the remainder of x divided by y.
```
SELECT MOD(10, 3);  -- Returns 1
```
POWER(x, y): Returns the value of x raised to the power of y.
```
SELECT POWER(2, 3);  -- Returns 8
```
SQRT(x): Returns the square root of x.
```
SELECT SQRT(9);  -- Returns 3
```

2. Trigonometric Functions:

Note: The arguments for trigonometric functions are typically in radians.

SIN(x): Returns the sine of x.
COS(x): Returns the cosine of x.
TAN(x): Returns the tangent of x.
ASIN(x), ACOS(x), ATAN(x): Return the arc sine, arc cosine, and arc tangent of x, respectively.
ATAN2(y, x): Returns the arc tangent of y/x.

3. Random Numbers:

RAND(): Returns a random float value between 0 and 1. Some databases allow a seed value as an argument.
```
SELECT RAND();  -- Might return 0.345298
```

4. Specific Database Extensions:

Different databases might have extended numeric functions to cater to specific needs. For instance:

Oracle: Functions like TO_NUMBER to convert strings to numeric types, LOG for logarithms, BIN_TO_NUM to convert binary to a number, etc.
SQL Server: Functions like LOG, LOG10, PI, RADIANS, DEGREES, etc.
PostgreSQL: Offers functions like CBRT for cube root, DEGREES to convert radians to degrees, FACTORIAL for factorial of a number, etc.

Note:

Always refer to your database's official documentation to know the exact list of supported numeric functions, their syntax, and any nuances or specific behaviors.

ABS() function in SQL:
- Returns the absolute value of a numeric expression.
```
SELECT ABS(-10) AS absolute_value;
```
ROUND() function in SQL:
- Rounds a numeric value to the nearest integer or to the specified number of decimal places.
```
SELECT ROUND(15.678, 2) AS rounded_value;
```
SQL numeric functions for mathematical operations:
- Basic arithmetic operations like addition, subtraction, multiplication, and division.
```
SELECT 10 + 5 AS addition,
       10 - 5 AS subtraction,
       10 * 5 AS multiplication,
       10 / 5 AS division;
```
CEIL() and FLOOR() functions in SQL:
- CEIL() returns the smallest integer greater than or equal to a specified number.
- FLOOR() returns the largest integer less than or equal to a specified number.
```
SELECT CEIL(15.3) AS ceil_value,
       FLOOR(15.8) AS floor_value;
```

Trigonometric functions in SQL (SIN, COS, TAN):

Perform trigonometric calculations.

SELECT SIN(30) AS sin_value,
       COS(60) AS cos_value,
       TAN(45) AS tan_value;

SQL EXP() and LOG() functions:
- EXP() returns the exponential value of a specified number.
- LOG() returns the natural logarithm of a specified number.
```
SELECT EXP(2) AS exp_value,
       LOG(10) AS log_value;
```
SQRT() function in SQL for square root:
- Returns the square root of a specified number.
```
SELECT SQRT(25) AS sqrt_value;
```
Power functions (POW, POWER) in SQL:
- Raise a number to the power of another.
```
SELECT POW(2, 3) AS pow_value,
       POWER(3, 2) AS power_value;
```
MOD() function for modulo in SQL:
- Returns the remainder of a division operation.
```
SELECT MOD(10, 3) AS modulo_value;
```

Aggregate numeric functions in SQL (SUM, AVG, COUNT):

Aggregate functions for numeric data.

SELECT SUM(column1) AS sum_value,
       AVG(column2) AS avg_value,
       COUNT(*) AS count_rows
FROM example_table;

Rounding decimal places with SQL numeric functions:
- Controlling the number of decimal places using ROUND() or CAST().
```
SELECT ROUND(123.456789, 2) AS rounded_value,
       CAST(123.456789 AS DECIMAL(5, 2)) AS casted_value;
```

Using CAST() and CONVERT() for numeric conversions in SQL:

Convert numeric types.

SELECT CAST('123' AS INT) AS casted_value,
       CONVERT(DECIMAL(10, 2), '45.678') AS converted_value;

Numeric functions with NULL values in SQL:
- Handle NULL values in numeric expressions.
```
SELECT ABS(NULL) AS abs_null,
       ROUND(NULL, 2) AS round_null;
```