SQL Arithmetic functions, slides presentation

Transcript

SQL Arithmetic Functions

SQL ABS() function

The SQL ABS() returns the absolute value of a number passed as argument.

Syntax : ABS(Expression)

Example : SELECT ABS(-17.36) FROM dual;

Output : 17.36

SQL CEIL() function

The SQL CEIL() will rounded up any positive or negative decimal value within the function upwards

Syntax : CEIL(Expression)

Example : SELECT CEIL(17.36) FROM dual;

Output : 18

Example : SELECT CEIL(-17.36);

Output : -17

SQL FLOOR() function

The SQL FLOOR() rounded up any positive or negative decimal value down to the next least integer value. SQL DISTINCT along with the SQL FLOOR() function is used to retrieve only unique value after rounded down to the next least integer value depending on the column specified.

Syntax : FLOOR(Expression)

Example : SELECT FLOOR(17.36) FROM dual;

Output : 17

Example : SELECT FLOOR(-17.36) FROM dual;

Output : -18

SQL EXP() function

The SQL EXP() returns e raised to the n-th power(n is the numeric expression), where e is the base of natural algorithm and the value of e is approximately 2.71828183.

Syntax : EXP(Expression)

Example : SELECT EXP(2) AS e_to_2s_power FROM dual;

Output : 7.3890560989306502272304274605750078132

SQL LN() function

The SQL LN() function returns the natural logarithm of n, where n is greater than 0 and its base is a number equal to approximately 2.71828183.

Syntax : LN(Expression)

Example : SELECT LN(65) "natural_log of 65" FROM dual;

Output : 4.17438726989563711065424677479150624431

SQL MOD() function

The SQL MOD() function returns the remainder from a division. The SQL DISTINCT command along with the SQL MOD() function is used to retrieve only unique records depending on the specified column or expression.

Syntax : MOD(dividend , divider )

Example : SELECT MOD(25 , 7) FROM dual;

Output : 4

Example : SELECT MOD(-25 , 7) FROM dual;

Output : -4

SQL POWER() function

The SQL POWER() function returns the value of a number raised to another, where both of the numbers are passed as arguments. The SQL DISTINCT command along with the SQL POWER() function can be used to retrieve only unique data depending on a specified expression.

Syntax : POWER( base, exponent )

Example : SELECT POWER(2 , 3) FROM dual;

Output : 8

SQL SQRT() function

The SQL SQRT() returns the square root of given value in the argument.

Syntax : SQRT( expression )

Example : SELECT SQRT(36) FROM dual;

Output : 6