Arithmetic operators are used to perform mathematical functions in SQLthe same as in most other languages. There are four conventional operators for mathematical functions: + (addition) - (subtraction) * (multiplication) / (division) Addition Addition is performed through the use of the plus (+) symbol. Example | Meaning | SELECT SALARY + BONUS FROM EMPLOYEE_PAY_TBL; | The SALARY column is added with the BONUS column for a total for each row of data | SELECT SALARY FROM EMPLOYEE_PAY_TBL WHERE SALARY + BONUS > '40000'; | Returns all rows that are greater than the total of the SALARY and BONUS columns | Subtraction Subtraction is performed using the minus (-) symbol. Example | Meaning | SELECT SALARY - BONUS FROM EMPLOYEE_PAY_TBL; | The BONUS column is subtracted from the SALARY column for the difference | SELECT SALARY FROM EMPLOYEE_PAY_TBL WHERE SALARY - BONUS > '40000'; | Returns all rows where the SALARY minus the BONUS is greater than 40000 | Multiplication Multiplication is performed by using the asterisk (*) symbol. Example | Meaning | SELECT SALARY * 10 FROM EMPLOYEE_PAY_TBL; | The SALARY column is multiplied by 10 | SELECT SALARY FROM EMPLOYEE_PAY_TBL WHERE SALARY * 10 > '40000'; | Returns all rows where the product of the SALARY multiplied by 10 is greater than 40000 | The pay rate in the following example is multiplied by 1.1, which increases the current pay rate by 10 percent: SELECT EMP_ID, PAY_RATE, PAY_RATE * 1.1 FROM EMPLOYEE_PAY_TBL WHERE PAY_RATE IS NOT NULL; EMP_ID PAY_RATE PAY_RATE*1.1 ----------- -------- ------------ 442346889 14.75 16.225 220984332 11 12.1 443679012 15 16.5 3 rows selected. Division Division is performed through the use of the slash (/) symbol. Example | Meaning | SELECT SALARY / 10 FROM EMPLOYEE_PAY_TBL; | The SALARY column is divided by 10 | SELECT SALARY FROM EMPLOYEE_PAY_TBL WHERE SALARY / 10 > '40000'; | Returns all rows that are greater than the SALARY | SELECT SALARY FROM EMPLOYEE_PAY_TBL WHERE SALARY / 10 > '40000'; | Returns all rows where the salary divided by 10 is greater than 40000 | Arithmetic Operator Combinations The arithmetic operators can be used in combinations with one another. Remember the rules of precedence in basic mathematics. Multiplication and division operations are performed first, and then addition and subtraction operations. The only way the user has control over the order of the mathematical operations is through the use of parentheses. Parentheses surrounding an expression causes that expression to be evaluated as a block. | Precedence is the order in which expressions are resolved in a mathematical expression or with embedded functions in SQL. | Expression | Result | 1 + 1 * 5 | 6 | (1 + 1) * 5 | 10 | 10 - 4 / 2 + 1 | 9 | (10 - 4) / (2 + 1) | 2 | In the following examples, notice that the placement of parentheses in an expression does not affect the outcome if only multiplication and division are involved. Precedence is not a factor in these cases. Although it may not appear to make sense, it is possible that some implementations of SQL do not follow the ANSI standard in cases like this, however unlikely . Expression | Result | 4 * 6 / 2 | 12 | (4 * 6) / 2 | 12 | 4 * (6 / 3) | 12 | The following are some more examples: SELECT SALARY * 10 + 1000 FROM EMPLOYEE_PAY_TBL WHERE SALARY > 20000; SELECT SALARY / 52 + BONUS FROM EMPLOYEE_PAY_TBL; SELECT (SALARY - 1000 + BONUS) / 52 * 1.1 FROM EMPLOYEE_PAY_TBL; The following is a rather wild example: SELECT SALARY FROM EMPLOYEE_PAY_TBL WHERE SALARY < BONUS * 3 + 10 / 2 - 50; Because parentheses are not used, mathematical precedence takes effect, altering the value for BONUS tremendously for the condition. | When combining arithmetic operators, remember to consider the rules of precedence. The absence of parentheses in a statement could render inaccurate results. Although the syntax of a SQL statement is correct, a logical error may result. | |