Oracle:Arithmetic Operators
Main Page >> Oracle and SQL >> Workbook >> Functions >> 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.
