CTE with INSERT, UPDATE, DELETE (writable CTEs) in PostgreSQL - Mini Project: Build & Apply

Managing Employee Records with Writable CTEs in PostgreSQL

📖 Scenario: You are managing a small company's employee database. You need to add new employees, update existing employee salaries, and remove employees who have left the company. Using writable Common Table Expressions (CTEs) in PostgreSQL will help you perform these operations cleanly and efficiently.

🎯 Goal: Build a PostgreSQL script that uses writable CTEs to insert a new employee, update an existing employee's salary, and delete an employee who left the company, all in one query.

📋 What You'll Learn

Create a table called employees with columns id (integer primary key), name (text), and salary (integer).

Insert initial employee data into the employees table.

Define a writable CTE to insert a new employee.

Define a writable CTE to update the salary of an existing employee.

Define a writable CTE to delete an employee by id.

Select all remaining employees after the changes.

💡 Why This Matters

🌍 Real World

Writable CTEs help database administrators and developers perform multiple data modifications in a single, readable query, improving maintainability and reducing errors.

💼 Career

Understanding writable CTEs is valuable for roles like database developer, data engineer, and backend developer working with PostgreSQL or similar SQL databases.

1

Create the employees table and insert initial data

Create a table called employees with columns id as primary key integer, name as text, and salary as integer. Then insert these exact rows: (1, 'Alice', 50000), (2, 'Bob', 60000), and (3, 'Charlie', 55000).

PostgreSQL

-- Create the employees table and insert initial data
-- Your code here

Need a hint?

Use CREATE TABLE to define the structure, then INSERT INTO to add the rows.

2

Add a writable CTE to insert a new employee

Add a writable CTE named new_employee that inserts a new employee with id = 4, name = 'Diana', and salary = 62000 into the employees table.

PostgreSQL

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

INSERT INTO employees (id, name, salary) VALUES
    (1, 'Alice', 50000),
    (2, 'Bob', 60000),
    (3, 'Charlie', 55000);

WITH new_employee AS (
    -- Insert new employee here
    -- Your code here
)

Need a hint?

Use WITH new_employee AS (INSERT INTO ... RETURNING *) to create the writable CTE.

3

Add writable CTEs to update and delete employees

Extend the query by adding two more writable CTEs: updated_salary that updates Bob's salary to 65000, and deleted_employee that deletes the employee with id = 3 (Charlie). Use UPDATE and DELETE statements with RETURNING * inside the CTEs.

PostgreSQL

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

INSERT INTO employees (id, name, salary) VALUES
    (1, 'Alice', 50000),
    (2, 'Bob', 60000),
    (3, 'Charlie', 55000);

WITH new_employee AS (
    INSERT INTO employees (id, name, salary)
    VALUES (4, 'Diana', 62000)
    RETURNING *
),
updated_salary AS (
    -- Update Bob's salary here
    -- Your code here
),
deleted_employee AS (
    -- Delete Charlie here
    -- Your code here
)

Need a hint?

Use UPDATE employees SET salary = 65000 WHERE name = 'Bob' RETURNING * and DELETE FROM employees WHERE id = 3 RETURNING * inside CTEs.

4

Select all employees after the changes

Complete the query by adding a final SELECT statement that retrieves all columns from the employees table after the insert, update, and delete operations.

PostgreSQL

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

INSERT INTO employees (id, name, salary) VALUES
    (1, 'Alice', 50000),
    (2, 'Bob', 60000),
    (3, 'Charlie', 55000);

WITH new_employee AS (
    INSERT INTO employees (id, name, salary)
    VALUES (4, 'Diana', 62000)
    RETURNING *
),
updated_salary AS (
    UPDATE employees
    SET salary = 65000
    WHERE name = 'Bob'
    RETURNING *
),
deleted_employee AS (
    DELETE FROM employees
    WHERE id = 3
    RETURNING *
)

SELECT * FROM employees;
-- Your code here

Need a hint?

Use SELECT * FROM employees; after the CTEs to see the final table state.