SQL Arithmetic Expressions

Arithmetic Expressions

Create expressions with number and date data by using arithmetic operators.You may need to modify the way in which data is displayed, or you may want to perform calculations, or look at what-if scenarios. All these are possible using arithmetic expressions. An arithmetic expression can contain column names, constant numeric values, and the arithmetic operators.

Arithmetic Operators

The slide lists the arithmetic operators that are available in SQL. You can use arithmetic operators in
any clause of a SQL statement (except the FROM clause).
Note: With the DATE and TIMESTAMP data types, you can use the addition and subtraction operators only.

                                                   Operator                  Description

                                                       

                                                        +                             Add

                                                        -                              Subtract

                                                        *                             Multiply

                                                        /                              Divide

    

  Using Arithmetic Operators

                      SELECT last_name, salary, salary + 300 FROM employees;

The example uses the addition operator to calculate a salary increase of 300 for all employees. It also displays a SALARY+300 column in the output.

Note that the resultant calculated column, SALARY+300, is not a new column in the EMPLOYEES

table; it is for display only. By default, the name of a new column comes from the calculation that

generated it—in this case, salary+300.

Note: The oracle server ignores blank spaces before and after arithmetic operators.

Operator Precedence

If an arithmetic expression contains more than one operator, multiplication and division are evaluated

first. If operators in an expression are of the same priority, then evaluation is done from left to right.

You can use parentheses to force the expression that is enclosed by the parentheses to be evaluated

first.

Rules of Precedence:

• Multiplication and division occur before addition and subtraction.

• Operators of the same priority are evaluated from left to right.

• Parentheses are used to override the default precedence or to clarify the statement.

                SELECT last_name, salary, 12*salary+100 FROM employees;

               

The first example displays the last name, salary, and annual compensation of employees.

It calculates the annual compensation by multiplying the monthly salary with 12, plus a one-time

bonus of 100. Note that multiplication is performed before addition.

Using Parentheses

You can override the rules of precedence by using parentheses to specify the desired order in which

the operators are to be executed.

The second example displays the last name, salary, and annual compensation of employees. It calculates the annual compensation as follows: adding a monthly bonus of 100 to the monthly salary, and then multiplying that subtotal with 12. Because of the parentheses, addition takes priority over multiplication.

                 SELECT last_name, salary, 12*(salary+100) FROM employees;