You want to raise the salary for each employee in department 600 by $200 for each year spent with the company (up to a maximum of $2500 per month).
To determine who is eligible, and the new salary, enter the following statement:
SELECT Name, (Salary+(YrsExp*200))/12 AS Projection FROM Employee WHERE Deptno = 600 AND Projection < 2500 ;
This statement returns the following response:
Name Projection -------- ---------- Newman P 2483.33
The statement uses parentheses to perform the operation YrsExp * 200 first. Its result is then added to Salary and the total is divided by 12.
The parentheses enclosing YrsExp * 200 are optional, but the parentheses enclosing Salary + (YrsExp * 200) are necessary. Without them, the operation YrsExp * 200 is divided by 12 and the result is added to Salary, producing an erroneous value.
The phrase "AS Projection" in this example associates the arithmetic expression (Salary + (YrsExp * 200)/12) with Projection. Using the AS phrase allows you to use the name Projection in the WHERE clause to refer to the entire expression.
The result is formatted without a comma separating thousands from hundreds.