SQL Mathematical Functions

Summary -

In this topic, we described about the Mathematical Functions with detailed example.

SQL has many built in system functions. Mathematical Functions are one of them. Mathematical Functions performs mathematical operation on input data as provided on arguments and returns numeric data in the result set based on the operation. Mathematical Functions perform operations on numeric data like INTEGER, FLOAT, DECIMAL. Mathematical Functions deals with Trigonometry, calculus, and geometry.

The types of mathematical Functions are explained as follows –

Scientific and Trigonometric Functions
Rounding Functions
Sign Functions
Random number Functions

Scientific and Trigonometric Functions -

Function	Description	Syntax
ACOS ()	It returns specified cosine value into radians i.e. angle in radians. The cosine value is in FLOAT and it returns FLOAT values in the output. It returns output with in -1 to 1 range otherwise it returns An invalid floating point operation occurred.	ACOS(float_expression);
ASIN ( )	It returns specified sine value in to radians i.e. angle in radians. The sine value is in FLOAT and it returns FLOAT values in the output. It returns output within -1 to 1 range otherwise it returns An invalid floating point operation occurred.	ASIN(float_expression);
ATAN ( )	It returns specified tangent value in radians i.e. angle in radians. The tangent value is in FLOAT and it returns FLOAT values in the output.	ATAN(float_expression);
ATN2 ( )	It returns specified two tangent values in radians i.e. angle in radians. The tangent values are in FLOAT and it returns FLOAT values in the output.	ATN2(float_expression,float_expression);
COS ( )	It returns cosine value for the specified angle in radians in specified expression. The value specified is in FOAT and it returns FLOAT value.	COS(float_expression);
COT ( )	It returns cotangent value for the specified angle in radians in specified expression. The value specified is in FOAT and it returns FLOAT value.	COT(float_expression);
TAN ( )	It returns Tangent value for the specified angle in radians in specified expression. The value specified is in FOAT and it returns FLOAT value.	TAN(float_expression);
SIN ( )	It returns Sine value for the specified angle in radians in specified expression. The value specified is in FOAT and it returns FLOAT value.	SIN(float_expression);
DEGREES()	It returns value in degree for specified value of angle in radians. It returns numeric value in result set i.e. FLOAT type.	DEGREES(number);
RADIANS()	It returns value in radians for specified value of angle in degrees. It returns numeric value in result set i.e. FLOAT type.	RADIANS(number);
EXP ( )	It returns the exponential value for the specified FLOAT value. specified value is in FLOAT or it implicitly convert to that type FLOAT. It uses the constant e (2.718281…) as the base.	EXP (float_expression);
LOG ( )	It returns the natural logarithm of specified number or logarithm of a required number to specified base.	LOG(number,base);
LOG10 ( )	It returns the natural logarithm of specified number to the base of 10. It returns FLOAT value in the result set.	LOG10 (float_expression);
PI ( )	It returns the constant value of PI. Return type of PI is FLOAT.	PI ( );
POWER ( )	It returns the value of one value to the power of another value. The value and returned value is either any datatype but not bit datatype.	POWER (X, Y );
SQRT ( )	It returns the square root value of specified value. It returns either INT or FLOAT datatype based on the specified value. The specified value should be positive number.	SQRT ( number );
SQUARE()	It returns the square value of specified number. It returns either INT or FLOAT datatype based on the specified value.	SQUARE (number);

The Scientific and Trigonometric Mathematical Functions that are used in SQL was as shown in below –

Function

Example

output

ACOS ()

SELECT ACOS(-0.25)AS A,AOS(1)AS B;

Now we will get corresponding radians values as follows -

A	B
1.82347658193698	0

ASIN ( )

SELECT ASIN(0.25)AS A,AOS(0.5)AS B;

Now we will get corresponding radians values as follows -

A	B
-0.252680255142079	0.523598775598299

ATAN ( )

SELECT ATAN(1.25)AS A,ATAN (45)AS B;

Now we will get corresponding radians values as follows -

A	B
0.896055384571344	1.54857776146818

ATN2 ( )

SELECT ATN2(45,125)AS A;

Now we will get corresponding radians values as follows -

A
0.345555580581712

COS ( )

SELECT COS(45)AS A;

Now we get cosine value in specified expression as follows –

A
0.52532198881773

COT ( )

SELECT COT(45)AS A;

Now we get cotangent value in specified expression as follows -

A
0.617369623783555

TAN ( )

SELECT TAN(145)AS A;

Now we get Tangent value in specified expression as follows –

A
0.529205277601419

SIN ( )

SELECT SIN(45)AS A;

Now we get cosine value in specified expression as follows –

A
0.850903524534118

DEGREES()

SELECT DEGREES(51)AS A;

Now we get specified angle in radians into degrees as follows –

A
2922

RADIANS()

SELECT RADIANS(45)AS A;

Now we get specified angle in degrees into radians as follows –

A
0

EXP ( )

SELECT EXP(8)AS A;

Now we can get exponential value for specified value as follows –

A
2980.95798704173

LOG( )

SELECT LOG (2)AS A,LOG (2,4) AS B;

Now we can get natural algorithm of 2 and algorithm of base 4 of 2 was as follows -

A	B
0.693147180559945	0.5

LOG10( )

SELECT LOG10(5)AS A;

Now we can get logarithm of 2 of base 10 was as follows –

A
0.698970004336019

PI ( )

SELECT PI()AS A,PI() 2 AS B;

Now we can get the constant value of PI in FLOAT and it returns the value that is multiplied with 2. The output was as follows-

A	B
3.14159265358979	6.28318530717959

POWER ( )

SELECT POWER(1.25, 0.2)AS A,POWER(2.5,5)AS B;

Now we can get the power values of 0.25,0.5 was as follows –

A	B
1.05	1525.9

SQRT()

SELECT SQRT(0.22)AS A,SQRT(2)AS B,SQRT(9) AS C;

Now we can ger the square root values of specified values was as follows –

A	B	c
0.469041575982343	1.4142135623731	3

SQUARE()

SELECT SQUARE(0.5)AS A,SQUARE(6)AS B,SQUARE(-3)AS C;

Now we can ger the square values of specified values was as follows –

A	B	c
0.25	36	9

Rounding Functions -

Function	Description	Syntax
CEILING()	It returns the next highest integer value for the specified decimal value. Specified value is in either positive or negative.	CEILING (number);
FLOOR ()	It returns the next lowest integer value for the specified decimal value. Specified value is in either positive or negative.	FLOOR (number );
ROUND()	It returns the nearest round value for the specified decimal values.	ROUND (number);

The Round Mathematical Functions that are used in SQL was as shown in below –

Function

Description

Syntax

CEILING()

SELECT CEILING(23.20)AS A,CEILING(-123.20)AS B;

Now we can get the next highest integer value was as follows –

A	B
24	-123

FLOOR()

SELECT FLOOR(23.20)AS A,FLOOR (-123.20)AS B;

Now we can get the next highest integer value was as follows –

A	B
23	-124

ROUND()

SELECT ROUND(25.36,1)AS A,ROUND(-59.36,2)AS B;

Now we get the nearest round values based on specified decimal values was as follows –

A	B
25.40	-59.36

SIGN Functions -

Function	Description	Syntax
ABS ()	This function returns absolute value of a number.If number is negative it returns positive number.If number is positive Then no change occur.	ABS(number);
SIGN()	This function returns +1 if the specified value is positive. returns -1 if the specified value is negative.returns 0 if the specified value is zero.	SIGN(number);

The Sign Mathematical Functions that are used in SQL was as shown in below -

Function

Example

output

ABS()

SELECT ABS(2.65) AS A,ABS (2) AS

Now we will get absolute values in result set.The output was as follows -

A	B
2.65	2

SIGN ()

SELECT SIGN ( 25 ) AS A, SIGN ( AS B, SIGN(0) AS C ;

Now we will get 1, -1, 0 values based on specified values.The output was as follows -

A	B	c
1	-1.00	0

Random Functions -

RAND ()

This function returns a random number in the result set.

RAND(Seed);

The Random Mathematical Functions that are used in SQL was as shown in below -

RAND()

SELECT RAND(23)AS A, RAND()AS B;

Now we will get random number for specified value in result set was as follows -

A	B
0.714001918580604	0.87499527410411