All employees whose salary is less than $40000 are eligible for an across the board pay increase.
IF your salary is less than … | AND you have greater than this many years of service … | THEN you receive this percentage salary increase … |
---|---|---|
$30000.00 | 8 | 15 |
$35000.00 | 10 | 10 |
$40000.00 | 5 |
The following SELECT statement uses a CASE expression to produce a report showing all employees making under $40000, displaying the first 15 characters of the last name, the salary amount (formatted with $and punctuation), the number of years of service based on the current date (in the column named On_The_Job) and which of the four categories they qualify for: '15% Increase', '10% Increase', '05% Increase' or 'Not Qualified'.
SELECT CAST(last_name AS CHARACTER(15)) ,salary_amount (FORMAT '$,$$9,999.99') ,(date - hire_date)/365.25 (FORMAT 'Z9.99') AS On_The_Job ,CASE WHEN salary_amount < 30000 AND On_The_Job > 8 THEN '15% Increase' WHEN salary_amount < 35000 AND On_The_Job > 10 THEN '10% Increase' WHEN salary_amount < 40000 AND On_The_Job > 10 THEN '05% Increase' ELSE 'Not Qualified' END AS Plan WHERE salary_amount < 40000 FROM employee ORDER BY 4;
The result of this query appears in the following table:
last_name | salary_amount | On_The_Job | Plan |
---|---|---|---|
Trader | $37,850.00 | 20.61 | 05% Increase |
Charles | $39,500.00 | 18.44 | 05% Increase |
Johnson | $36,300.00 | 20.41 | 05% Increase |
Hopkins | $37,900.00 | 19.99 | 05% Increase |
Morrissey | $38,750.00 | 18.44 | 05% Increase |
Ryan | $31,200.00 | 20.41 | 10% Increase |
Machado | $32,300.00 | 18.03 | 10% Increase |
Short | $34,700.00 | 17.86 | 10% Increase |
Lombardo | $31,000.00 | 20.11 | 10% Increase |
Phillips | $24,500.00 | 19.95 | 15% Increase |
Rabbit | $26,500.00 | 18.03 | 15% Increase |
Kanieski | $29,250.00 | 20.11 | 15% Increase |
Hoover | $25,525.00 | 20.73 | 15% Increase |
Crane | $24,500.00 | 19.15 | 15% Increase |
Stein | $29,450.00 | 20.41 | 15% Increase |