w3resource logo


PostgreSQL exercises

PostgreSQL Update Table - Exercises, Practice, Solution

Secondary Nav

PostgreSQL Update Table [ 8 exercises with solution]

1. Write a sql statement to change the email column of employees table with 'not available' for all employees.

Sample table : employees

UPDATE employees SET email='not available';

See the result. Only two rows have been displayed.

postgres=# SELECT * FROM employees LIMIT 2;
 employee_id | first_name | last_name |     email     | phone_number | hire_date  | job_id  |  salary  | commission_pct | manager_id | department_id
-------------+------------+-----------+---------------+--------------+------------+---------+----------+----------------+------------+---------------
         100 | Steven     | King      | not available | 515.123.4567 | 1987-06-17 | AD_PRES | 24000.00 |           0.00 |          0 |            90
         101 | Neena      | Kochhar   | not available | 515.123.4568 | 1987-06-18 | AD_VP   | 17000.00 |           0.00 |        100 |            90

2. Write a sql statement to change the email and commission_pct column of employees table with 'not available' and 0.10 for all employees.

Sample table : employees

UPDATE employees SET email='not available',
commission_pct=0.10;

See the result. Only two rows have been displayed.

postgres=# SELECT * FROM employees LIMIT 2;
 employee_id | first_name | last_name |     email     | phone_number | hire_date  |  job_id  | salary  | commission_pct | manager_id | department_id
-------------+------------+-----------+---------------+--------------+------------+----------+---------+----------------+------------+---------------
         128 | Steven     | Markle    | not available | 650.124.1434 | 1987-07-15 | ST_CLERK | 2200.00 |           0.10 |        120 |            50
         129 | Laura      | Bissot    | not available | 650.124.5234 | 1987-07-16 | ST_CLERK | 3300.00 |           0.10 |        121 |            50

3. Write a sql statement to change the email and commission_pct column of employees table with 'not available' and 0.10 for those employees whose department_id is 110.

Sample table : employees

UPDATE employees 
SET email='not available',
commission_pct=0.10 
WHERE department_id=110;

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

postgres=# SELECT * FROM employees WHERE department_id=110;
 employee_id | first_name | last_name |     email     | phone_number | hire_date  |   job_id   |  salary  | commission_pct | manager_id | department_id
-------------+------------+-----------+---------------+--------------+------------+------------+----------+----------------+------------+---------------
         205 | Shelley    | Higgins   | not available | 515.123.8080 | 1987-09-30 | AC_MGR     | 12000.00 |           0.10 |        101 |           110
         206 | William    | Gietz     | not available | 515.123.8181 | 1987-10-01 | AC_ACCOUNT |  8300.00 |           0.10 |        205 |           110
(2 rows)

4. Write a sql statement to change the email column of employees table with 'not available' for those employees whose department_id is 80 and gets a commission is less than .20%

Sample table : employees

UPDATE employees 
SET email='not available'
WHERE department_id=80 
AND commission_pct<.20;

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

postgres=# SELECT * FROM  employees WHERE email='not available' AND department_id=80 AND commission_pct<.20;
 employee_id | first_name | last_name |     email     |    phone_number    | hire_date  | job_id | salary  | commission_pct | manager_id | department_id
-------------+------------+-----------+---------------+--------------------+------------+--------+---------+----------------+------------+---------------
         155 | Oliver     | Tuvault   | not available | 011.44.1344.486508 | 1987-08-11 | SA_REP | 7000.00 |           0.15 |        145 |            80
         163 | Danielle   | Greene    | not available | 011.44.1346.229268 | 1987-08-19 | SA_REP | 9500.00 |           0.15 |        147 |            80
         164 | Mattea     | Marvins   | not available | 011.44.1346.329268 | 1987-08-20 | SA_REP | 7200.00 |           0.10 |        147 |            80
         165 | David      | Lee       | not available | 011.44.1346.529268 | 1987-08-21 | SA_REP | 6800.00 |           0.10 |        147 |            80
         166 | Sundar     | Ande      | not available | 011.44.1346.629268 | 1987-08-22 | SA_REP | 6400.00 |           0.10 |        147 |            80
         167 | Amit       | Banda     | not available | 011.44.1346.729268 | 1987-08-23 | SA_REP | 6200.00 |           0.10 |        147 |            80
         171 | William    | Smith     | not available | 011.44.1343.629268 | 1987-08-27 | SA_REP | 7400.00 |           0.15 |        148 |            80
         172 | Elizabeth  | Bates     | not available | 011.44.1343.529268 | 1987-08-28 | SA_REP | 7300.00 |           0.15 |        148 |            80
         173 | Sundita    | Kumar     | not available | 011.44.1343.329268 | 1987-08-29 | SA_REP | 6100.00 |           0.10 |        148 |            80
         179 | Charles    | Johnson   | not available | 011.44.1644.429262 | 1987-09-04 | SA_REP | 6200.00 |           0.10 |        149 |            80

5. Write a sql statement to change the email column of employees table with 'not available' for those employees who belongs to the 'Accouning' department.

Sample table : employees

Sample table : departments

UPDATE employees 
SET email='not available'
WHERE department_id=(
SELECT department_id 
FROM departments 
WHERE department_name='Accounting');

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

SELECT * FROM employees 
WHERE email='not available' AND department_id=
(SELECT department_id 
FROM dep 
WHERE department_name='Accounting');


 employee_id | first_name | last_name |     email     | phone_number | hire_date  |   job_id   |  salary  | commission_pct | manager_id | department_id
-------------+------------+-----------+---------------+--------------+------------+------------+----------+----------------+------------+---------------
         205 | Shelley    | Higgins   | not available | 515.123.8080 | 1987-09-30 | AC_MGR     | 12000.00 |           0.00 |        101 |           110
         206 | William    | Gietz     | not available | 515.123.8181 | 1987-10-01 | AC_ACCOUNT |  8300.00 |           0.00 |        205 |           110
(2 rows)

6. Write a sql statement to change salary of employee to 8000 whose ID is 105, if the existing salary is less than 5000.

Sample table : employees

UPDATE employees SET SALARY = 8000 WHERE employee_id = 105 AND salary < 5000;

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

postgres=# SELECT * FROM  employees WHERE  SALARY = 8000 AND employee_id = 105;

 employee_id | first_name | last_name |  email  | phone_number | hire_date  | job_id  | salary  | commission_pct | manager_id | department_id
-------------+------------+-----------+---------+--------------+------------+---------+---------+----------------+------------+---------------
         105 | David      | Austin    | DAUSTIN | 590.423.4569 | 1987-06-22 | IT_PROG | 8000.00 |           0.00 |        103 |            60
(1 row)

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

Sample table : employees

UPDATE employees SET JOB_ID= 'SH_CLERK' 
WHERE employee_id=118 
AND department_id=30 
AND NOT JOB_ID LIKE 'SH%';

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

postgres=# SELECT * FROM  employees WHERE employee_id=118 AND department_id=30 AND JOB_ID LIKE 'SH%';

 employee_id | first_name | last_name |  email  | phone_number | hire_date  |  job_id  | salary  | commission_pct | manager_id | department_id
-------------+------------+-----------+---------+--------------+------------+----------+---------+----------------+------------+---------------
         118 | Guy        | Himuro    | GHIMURO | 515.127.4565 | 1987-07-05 | SH_CLERK | 2600.00 |           0.00 |        114 |            30
(1 row)

8. Write a sql 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.

Sample table : employees

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);

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)