SQl Math functions

SQL Functions - Math Functions

1.ABS Function:

SELECT ABS(-1) AS absolute;

Output:- Output:1

2.Trig Functions

The available trig functions are:

ACOS

ASIN

ATAN

TAN

COS

SIN

Example of COS:

SELECT COS(5.5) AS cosine;

Output:0.70866977429126

Example of SIN:

SELECT SIN(3.55) AS sine;

Output:-0.39714816728596

Example of Tangent:

SELECT TAN(3.5553) AS tangent;

Output: 0.43904601857096

3.Two functions are CEIL and CEILING.

Example of CEIL

SELECT CEIL(5.35) AS roundedUp;

Output:6

Example of CEILING:

SELECT CEILING(5.35) AS roundedUp;

OUTPUT:- 6

4.Floor Function

Example:

SELECT FLOOR(5.35) AS roundedDown;

The output:5

5.Rounding

The generic formula is:

ROUND(X,Y)

This states that we are going to round X to Y decimal places.

SELECT ROUND(5.334333,2);

The result is:5.33

6.Converting Radians to Degrees

Example:

SELECT DEGREES(0.4567) AS deg;

Output:26.1669825036247

7.Converting Degrees to Radians

Example:

SELECT RADIANS(26.1669825036247) AS rad;

The output is:0.4567

8.Exponents

Example:

SELECT POW(5,3) AS pow;

Output:125

9.Greatest and Max Functions

Example:

SELECT GREATEST(5,3,8.5,9,-2,5,63,323,34,333);

Output:333

10.Least and MIN Functions

Example of least:

SELECT LEAST(5.5,3.5,2,4,-1) AS smallest;

Output:-1

11.PI

Example:

SELECT PI();

Output:3.141593

12.Random Number

Example:

SELECT RAND()

Output:- 0.234685802119491

SELECT FLOOR(10*RAND())

output:2

SELECT FLOOR(10*RAND())+1

output:5

13.Total Values

SELECT SUM(age) FROM nums;

Output:8

14.Average

SELECT AVG(age) FROM nums;

Output:4.0000

SELECT SUM(age) / (SELECT COUNT(age) FROM nums) FROM nums;

Output:4.0000

15.Modulus Function

SELECT MOD(5,2);

Result:1