MySQL Update Table Statement Exercises: Increase the salary of employees of department_id 40, 90, and 110 by 25%, 15% and 10% respectively and the rest will remain same
MySQL Update Table Statement: Exercise-8 with Solution
Write a MySQL statement to increase the salary of employees under the department 40, 90 and 110 according to the company rules that, salary will be increased by 25% for the department 40, 15% for department 90 and 10% for the department 110 and the rest of the departments will remain same.
Here is the sample table employees.
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | 100 | Steven | King | SKING | 515.123.4567 | 1987-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90 | | 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 1987-06-18 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 1987-06-19 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 1987-06-20 | IT_PROG | 9000.00 | 0.00 | 102 | 60 | | 104 | Bruce | Ernst | BERNST | 590.423.4568 | 1987-06-21 | IT_PROG | 6000.00 | 0.00 | 103 | 60 | | 105 | David | Austin | DAUSTIN | 590.423.4569 | 1987-06-22 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 1987-06-23 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 1987-06-24 | IT_PROG | 4200.00 | 0.00 | 103 | 60 | | 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 1987-06-25 | FI_MGR | 12000.00 | 0.00 | 101 | 100 | ......... | 206 | William | Gietz | WGIETZ | 515.123.8181 | 1987-10-01 | AC_ACCOUNT | 8300.00 | 0.00 | 205 | 110 | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
-- This SQL query updates the 'salary' column in the 'employees' table based on a CASE statement.
-- It adjusts the salary for employees in specific departments according to different percentage increases.
UPDATE employees
-- Use a CASE statement to determine the new salary based on the department_id.
SET salary =
CASE department_id
WHEN 40 THEN salary + (salary * 0.25) -- Increase salary by 25% for department 40.
WHEN 90 THEN salary + (salary * 0.15) -- Increase salary by 15% for department 90.
WHEN 110 THEN salary + (salary * 0.10) -- Increase salary by 10% for department 110.
ELSE salary -- Keep the current salary for other departments.
END
-- Specify the condition for updating rows: department_id should be one of the specified values.
WHERE department_id IN (40, 50, 50, 60, 70, 80, 90, 110);
Let execute the above code in MySQL command prompt
See the result before update. Only the effected rows have been displayed.
+-------------+------------+-----------+----------+--------------+------------+------------+----------+----------------+------------+---------------+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | +-------------+------------+-----------+----------+--------------+------------+------------+----------+----------------+------------+---------------+ | 100 | Steven | King | SKING | 515.123.4567 | 1987-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90 | | 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 1987-06-18 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 1987-06-19 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 203 | Susan | Mavris | SMAVRIS | 515.123.7777 | 1987-09-28 | HR_REP | 6500.00 | 0.00 | 101 | 40 | | 205 | Shelley | Higgins | SHIGGINS | 515.123.8080 | 1987-09-30 | AC_MGR | 12000.00 | 0.00 | 101 | 110 | | 206 | William | Gietz | WGIETZ | 515.123.8181 | 1987-10-01 | AC_ACCOUNT | 8300.00 | 0.00 | 205 | 110 | +-------------+------------+-----------+----------+--------------+------------+------------+----------+----------------+------------+---------------+
Explanation:
Here's a brief explanation of the above MySQL code:
- UPDATE employees: This part of the statement indicates that you want to update records in the "employees" table.
- SET salary = CASE department_id ... END: This uses a CASE statement to conditionally determine the new salary for each employee based on their 'department_id'. It adjusts the salary differently for employees in departments 40, 90, and 110, and keeps the current salary for employees in other departments.
- WHERE department_id IN (40, 50, 50, 60, 70, 80, 90, 110): This specifies the condition for updating rows. It includes only rows where 'department_id' is one of the specified values (40, 50, 60, 70, 80, 90, 110).
Go to:
PREV : Write a SQL statement to change job ID of employee which ID is 118, to SH_CLERK if the employee belongs to department, which ID is 30 and the existing job ID does not start with SH.
NEXT : Write a SQL statement to increase the minimum and maximum salary of PU_CLERK by 2000 as well as the salary for those employees by 20% and commission percent by .10.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?