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

# 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.

What is the difficulty level of this exercise?

﻿