SQL Numeric Functions

Some of the most commonly used numeric functions are:

ABS(n) returns the absolute value of n.
ex:SELECT ABS(-15) FROM DUAL;This gives and output of 15.

CEIL(n) returns smallest integer greater than or equal to n.
EX:SELECT CEIL(15.7) FROM DUAL;Output is 16.

FLOOR(n) returns largest integer equal to or less than n
EX:SELECT FLOOR(15.7) FROM DUAL;Output is 15.

MOD(m,n) returns the remainder of m divided by n. Returns m if n is 0.
SELECT MOD(11,4) FROM DUAL;Output is 3.

POWER(m,n) returns m raised to the nth power. The base m and the exponent n can be any numbers, but if m is negative, then n must be an integer.
EX:SELECT POWER(3,2) FROM DUAL;Output is 9

ROUND returns n rounded to integer places to the right of the decimal point. If you omit integer, then n is rounded to 0 places.The argument integer can be negative to round off digits left of the decimal point.
EX1:SELECT ROUND(15.193,1) FROM DUAL;Output is 15.2
EX2:SELECT ROUND(1.5) FROM DUAL;Output is 2.

TRUNC returns n truncated to m decimal places. If m is omitted, then n is truncated to 0 places. m can be negative to truncate (make zero) m digits left of the decimal point.
EX:SELECT TRUNC(15.79,1) FROM DUAL;Output is 15.7.

SIGN(n) returns the sign of n.
The sign is -1 if n is < n ="0"> 0.
EX:SELECT SIGN(-15) FROM DUAL;Output is -1.

SQRT(n) returns the square root of n.
EX:SELECT SQRT(16) FROM DUAL;Output is 4.

WIDTH_BUCKET lets you construct equiwidth parts, in which the part range is divided into intervals that have identical size.
EX: When u give WIDTH_BUCKET(credit_limit, 100, 5000, 10) in select statement,it divides the credit_limit into 10 equal parts and and all those credits that r below 500 fall under bucket 1 and from 500-1000 comes bucket 2 and from 1000 to 1500 comes bucket 3 and so on till bucket 10 to make a total credit_limit of 5000.