Oracle:Functions

Main Page >> Oracle and SQL >> Workbook >> Arithmetic Operators and Functions

Arithmetic Operators

Arithmetic expressions can be included in an SQL command. The following operators may be used in arithmetic expressions:

+ plus

- minus

* multiply

/ divide

Parentheses may be used in the arithmetic expressions.

Some Example Queries Involving Arithmetic Expressions

To calculate total monthly compensation (salary plus commission) for every salesperson, ordered by compensation:

SELECT  ENAME,  SAL,  COMM,  SAL + COMM AS total_sal
FROM  EMP  
WHERE  JOB  =  'SALESMAN'  
ORDER BY total_sal DESC;

Note, when creating new columns, i.e., the sum of SAL+COMM is creating a temporary new column, the column does not have an actual name. If you wish to refer to this column in an order by clause then you can either repeat the expression again, give the new column an alias as seen above or refer to the column's position in the select list, which in this case is the fourth one.

To list employees whose commission is greater than 25% of their salary:

SELECT  ENAME,  SAL,  COMM FROM  EMP 
WHERE  COMM  >  0.25  *  SAL;

To calculate total annual compensation for all salespeople:

SELECT  ENAME,  SAL,  COMM,  12  *  (SAL  +  COMM) AS compensation
FROM  EMP  
WHERE  JOB  =  'SALESMAN';

Arithmetic Functions

The following arithmetic functions can be used in SQL commands:

Function	Returns
ABS(X)	absolute value of X
CEILING(X)	smallest integer > X
FLOOR(X)	largest integer < X
MOD(X,N)	remainder of X divided by N
ROUND(X,N)	X rounded to N places
SIGN(X)	if X<0, -1; if X=0, 0; if X>0, 1
SQRT(X)	square root of X

where X is an attribute name and N is an integer.

Note that when an expression or individual function refers to a column that contains a null value, the result is also null.

Next Step

Group Functions.