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 …|
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: