PostgreSQL Update Table: Increase the salary of employees under the department 40, 90 and 110 according to the company rules in the HR database

8. Write a SQL statement to increase the salary of employees under the department 40, 90 and 110 according to the company rules that, the salary will be increased by 25% of the department 40, 15% for department 90 and 10% of the department 110 and the rest of the department will remain same.

Sample table: employees

Sample Solution:

Code:

-- This SQL statement updates the 'salary' column in the 'employees' table based on the department_id.
-- It increases the salary for employees in specific departments by a certain percentage and leaves it unchanged otherwise.

UPDATE employees 
SET salary = CASE department_id 
                WHEN 40 THEN salary + (salary * 0.25)  -- Increase salary by 25% for department_id 40
                WHEN 90 THEN salary + (salary * 0.15)  -- Increase salary by 15% for department_id 90
                WHEN 110 THEN salary + (salary * 0.10) -- Increase salary by 10% for department_id 110
                ELSE salary  -- Keep salary unchanged for other department_ids
            END
WHERE department_id IN (40,50,50,60,70,80,90,110); -- Update salary for employees in specified department_ids

Explanation:

• The UPDATE statement is used to modify existing records in a table.
• employees is the name of the table being updated.
• The CASE expression evaluates different conditions (department_id) and performs corresponding actions (calculating new salary based on the condition).
• WHEN department_id THEN specifies the conditions for each department_id.
• THEN salary + (salary * 0.25) increases the salary by 25% if the department_id is 40, THEN salary + (salary * 0.15) increases the salary by 15% if the department_id is 90, and THEN salary + (salary * 0.10) increases the salary by 10% if the department_id is 110.
• ELSE salary specifies that the salary remains unchanged for department_ids not mentioned in the CASE expression.
• WHERE department_id IN (40,50,50,60,70,80,90,110) restricts the update operation to only those employees whose department_id is included in the list. It ensures that salary is updated only for employees in specified department_ids.

Output:

See the result before update. Only the effected rows have been displayed.

postgres=# SELECT * FROM emp WHERE department_id IN (40,90,110);
 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
(6 rows)

See the result. Only the effected rows have been displayed.

postgres=# SELECT * FROM emp WHERE department_id IN (40,90,110);

 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    | 27600.00 |           0.00 |          0 |            90
         101 | Neena      | Kochhar   | NKOCHHAR | 515.123.4568 | 1987-06-18 | AD_VP      | 19550.00 |           0.00 |        100 |            90
         102 | Lex        | De Haan   | LDEHAAN  | 515.123.4569 | 1987-06-19 | AD_VP      | 19550.00 |           0.00 |        100 |            90
         203 | Susan      | Mavris    | SMAVRIS  | 515.123.7777 | 1987-09-28 | HR_REP     |  8125.00 |           0.00 |        101 |            40
         205 | Shelley    | Higgins   | SHIGGINS | 515.123.8080 | 1987-09-30 | AC_MGR     | 13200.00 |           0.00 |        101 |           110
         206 | William    | Gietz     | WGIETZ   | 515.123.8181 | 1987-10-01 | AC_ACCOUNT |  9130.00 |           0.00 |        205 |           110
(6 rows)

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a SQL statement to change the job ID of the employee which ID is 118 to SH_CLERK if the employee belongs to a department which ID is 30 and the existing job ID does not start with SH.
Next: PostgreSQL Insert Records - Exercises, Practice, Solution