Question

Suppose all the employees got promoted on January 1, 2000. Write an SQL query that will output first name, last name, salary, department and date (in a specific format) columns from the Employees table. The first column will be the employee’s first name, the second column will be the employee’s last name, the third column will be the current salary and the fourth column will show the 'Updated salary' of the employees. The updated salary column will be calculated as per the below format and condition. The salary will be doubled if an employee has been associated with the company for more than 10 years (01-JAN-2000 - hire date). If the employee has been associated with the company for more than five years but less than ten years, the salary is increased by 50 percent. Otherwise, the salary is increased by 20 percent. Order the rows as per the department ID keeping the null values on the top of the output.

Answer 1

If you have any doubts, please give me comment...

SELECT FirstName, LastName, Salary AS "Current Salary", CASE

WHEN EXTRACT(YEAR FROM HireDate) - EXTRACT(YEAR FROM TO_DATE('01-JAN-2000', 'DD-MON-YYYY')) >10 THEN Salary*2

WHEN EXTRACT(YEAR FROM HireDate) - EXTRACT(YEAR FROM TO_DATE('01-JAN-2000', 'DD-MON-YYYY')) THEN Salary*1.5

WHEN EXTRACT(YEAR FROM HireDate) - EXTRACT(YEAR FROM TO_DATE('01-JAN-2000', 'DD-MON-YYYY')) THEN Salary*1.2

END AS "Updated Salary", DepartmentID, HireDate

FROM Employees

ORDER BY DepartmentID;