A pay rise and a clear-out – Databases Practice

Change some rows, remove others, and check the result.

What to build

An UPDATE, a DELETE and a SELECT that apply two staff decisions.

Requirements

Add 1000 to the salary of everyone in IT and HR.
Remove everyone in Temp.
Show every column of the table, in order of id.

Tables

Each test fills these tables with its own rows, then runs your SQL:

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  salary INTEGER NOT NULL
);

Output

Columns id, name, dept and salary, in order of id. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

Department names are written exactly as IT, HR, Sales and Temp.

Example

When a test starts with:

INSERT INTO employees VALUES (1, 'Farhana', 'Sales', 40000), (2, 'Imran', 'IT', 65000), (3, 'Joya', 'HR', 45000), (4, 'Kamal', 'Temp', 20000);

your SQL should print:

id  name     dept   salary
--  -------  -----  ------
1   Farhana  Sales  40000
2   Imran    IT     66000
3   Joya     HR     46000
(3 rows)

Why it works

Each statement's WHERE limits it to the right rows, so other departments keep their salaries. Without the WHERE, the UPDATE would raise everyone's pay.

Examples

Example 1 · Input

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  salary INTEGER NOT NULL
);
INSERT INTO employees VALUES (1, 'Farhana', 'Sales', 40000), (2, 'Imran', 'IT', 65000), (3, 'Joya', 'HR', 45000), (4, 'Kamal', 'Temp', 20000);

Expected output

id  name     dept   salary
--  -------  -----  ------
1   Farhana  Sales  40000
2   Imran    IT     66000
3   Joya     HR     46000
(3 rows)

Example 2 · Input

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  salary INTEGER NOT NULL
);
INSERT INTO employees VALUES (1, 'A', 'Temp', 1), (2, 'B', 'Temp', 2);

Expected output

id  name  dept  salary
--  ----  ----  ------
(0 rows)

Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.

Hints

Hint 1

`WHERE dept IN ('IT', 'HR')` picks both departments in one UPDATE.

Hint 2

A separate DELETE removes the `Temp` staff.

Solution

Show one correct solution (try it yourself first)

UPDATE employees SET salary = salary + 1000 WHERE dept IN ('IT', 'HR');
DELETE FROM employees WHERE dept = 'Temp';
SELECT * FROM employees ORDER BY id;

Lesson: Changing and deleting rows →