Exercises

Objectives

This lesson introduces no new concepts. It’s a chance to practice everything from this module: renaming, adding and dropping columns, changing types, SET/DROP DEFAULT and NOT NULL, adding and dropping UNIQUE/CHECK/PRIMARY KEY/FOREIGN KEY, and constraint naming.

All exercises are runnable against a real PostgreSQL database. Start from this employees table:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(150) NOT NULL,
    department VARCHAR(50),
    salary NUMERIC(10,2) NOT NULL
);

INSERT INTO employees (first_name, last_name, email, department, salary) VALUES
    ('Erin', 'Castillo', 'erin.castillo@example.com', 'Engineering', 74000),
    ('Jordan', 'Blake', 'jordan.blake@example.com', NULL, 61000),
    ('Maya', 'Fischer', 'maya.fischer@example.com', 'Marketing', 67000);

Notice Jordan Blake’s department is deliberately NULL, later exercises depend on it.

Exercises

1. Renaming

a. Rename employees to staff_members, confirm the new name, then rename it back to employees.

b. Rename the department column to team_name, confirm the change, then rename it back to department.

2. Adding and Dropping Columns

a. Add a linkedin_url column of type VARCHAR(200).

b. Add an is_remote column, BOOLEAN NOT NULL DEFAULT false, and confirm all three existing employees show false.

c. Try adding a badge_code column as VARCHAR(20) NOT NULL with no default, and confirm you get an error, since the table already has rows.

d. Drop linkedin_url.

3. Types and Defaults

a. Add a bio column of type VARCHAR(100), then widen it to TEXT.

b. Set department’s default to 'General', then insert Priya Desai (priya.desai@example.com, salary 79000) without mentioning department at all, and confirm she ends up in 'General'.

c. Try SET NOT NULL on department, and confirm it fails, Jordan Blake’s row still has a NULL department at this point.

d. Update Jordan Blake’s department to 'General', then SET NOT NULL again and confirm it succeeds this time, then DROP NOT NULL to leave department optional again.

4. Constraints and Naming

a. Add a UNIQUE constraint named employees_email_key on email.

b. Add a CHECK constraint named employees_salary_check requiring salary > 0.

c. Create a departments table (id, name) with rows for 'Engineering', 'Marketing', and 'General', add a department_id column to employees, and add a foreign key named fk_employees_department from employees.department_id to departments.id.

d. Query employees’ constraints and confirm you see all three you just named, plus the auto-generated employees_pkey and the _not_null constraints PostgreSQL generates automatically for every NOT NULL column.

Recap

You can now rename tables and columns, add and remove columns safely, change a column’s type, adjust DEFAULT and NOT NULL after the fact, add and remove every major constraint type, and name constraints so they’re easy to find and drop later. Combined with Module 3, you can now shape a table’s entire structure, at creation and afterward.

Next module: Querying Data, where WHERE, ORDER BY, and LIMIT turn SELECT into a genuinely precise tool.