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:

UPDATE employees SET salary= CASE department_id 
 WHEN 40 THEN salary+(salary*.25) 
 WHEN 90 THEN salary+(salary*.15)
 WHEN 110 THEN salary+(salary*.10)
  ELSE salary
    END
 WHERE department_id IN (40,50,50,60,70,80,90,110);

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