Change the design of a live table – Databases Practice

Tables change as requirements change. Do it without losing a single row.

What to build

Statements that add a column, fill it in, rename the table and show the result.

Requirements

Add a column level, a whole number with the default 1.
Set level to 2 for everyone with 5 or more years of service.
Rename the table to employees, then show every column, in order of id.

Tables

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

CREATE TABLE staff (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  years INTEGER NOT NULL
);

Output

Columns id, name, years and level, 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

The table may be empty, and years is never NULL.

Example

When a test starts with:

INSERT INTO staff VALUES (1, 'Asha', 2), (2, 'Bilal', 7);

your SQL should print:

id  name   years  level
--  -----  -----  -----
1   Asha   2      1
2   Bilal  7      2
(2 rows)

Why it works

ADD COLUMN gives every existing row the column's default, so only the senior staff need an UPDATE. Renaming keeps every row, so the final SELECT just uses the new name.

Examples

Example 1 · Input

CREATE TABLE staff (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  years INTEGER NOT NULL
);
INSERT INTO staff VALUES (1, 'Asha', 2), (2, 'Bilal', 7);

Expected output

id  name   years  level
--  -----  -----  -----
1   Asha   2      1
2   Bilal  7      2
(2 rows)

Example 2 · Input

CREATE TABLE staff (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  years INTEGER NOT NULL
);
INSERT INTO staff VALUES (1, 'Chen', 5);

Expected output

id  name  years  level
--  ----  -----  -----
1   Chen  5      2
(1 row)

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

`ALTER TABLE staff ADD COLUMN level INTEGER DEFAULT 1;` gives every existing row the default.

Hint 2

An UPDATE with `WHERE years >= 5` sets the senior staff to level 2.

Hint 3

`ALTER TABLE staff RENAME TO employees;` renames it, and the SELECT must then use the new name.

Solution

Show one correct solution (try it yourself first)

ALTER TABLE staff ADD COLUMN level INTEGER DEFAULT 1;
UPDATE staff SET level = 2 WHERE years >= 5;
ALTER TABLE staff RENAME TO employees;
SELECT * FROM employees ORDER BY id;

Lesson: Creating tables and adding rows →