CTE with INSERT, UPDATE, DELETE (writable CTEs) in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of UPDATE using writable CTE

Given the table employees with columns id, name, and salary, what will be the output of the following query?

WITH updated AS (
  UPDATE employees
  SET salary = salary * 1.1
  WHERE id = 2
  RETURNING id, salary
)
SELECT * FROM updated;

PostgreSQL

WITH updated AS (
  UPDATE employees
  SET salary = salary * 1.1
  WHERE id = 2
  RETURNING id, salary
)
SELECT * FROM updated;

A [{"id": 1, "salary": 50000}]

B []

C [{"id": 2, "salary": 55000}]

D [{"id": 2, "salary": 50000}]

💡 Hint

The CTE returns rows affected by the UPDATE with new salary values.

The UPDATE affects only the employee with id=2, increasing salary by 10%. The RETURNING clause outputs the updated row with new salary.

❓ query_result

intermediate

Result of DELETE with writable CTE

Consider the table products with columns product_id and stock. What will this query return?

WITH deleted AS (
  DELETE FROM products
  WHERE stock = 0
  RETURNING product_id
)
SELECT COUNT(*) FROM deleted;

PostgreSQL

WITH deleted AS (
  DELETE FROM products
  WHERE stock = 0
  RETURNING product_id
)
SELECT COUNT(*) FROM deleted;

A SyntaxError

B 0

C 1

D 3

💡 Hint

The CTE returns all deleted rows, then counts them.

The DELETE removes all products with stock=0 and RETURNING outputs those rows. COUNT(*) counts how many were deleted.

📝 Syntax

advanced

Identify syntax error in writable CTE with INSERT

Which option contains a syntax error in this writable CTE that inserts a new row?

WITH ins AS (
  INSERT INTO orders (order_id, amount)
  VALUES (101, 250)
  RETURNING order_id
)
SELECT * FROM ins;

PostgreSQL

WITH ins AS (
  INSERT INTO orders (order_id, amount)
  VALUES (101, 250)
  RETURNING order_id
)
SELECT * FROM ins;

A WITH ins AS (INSERT INTO orders order_id, amount VALUES (101, 250) RETURNING order_id) SELECT * FROM ins;

B WITH ins AS (INSERT INTO orders (order_id, amount) VALUES (101, 250) RETURNING order_id) SELECT * FROM ins;

C ;sni MORF * TCELES )di_redro GNINRUTER )052 ,101( SEULAV )tnuoma ,di_redro( sredro OTNI TRESNI( SA sni HTIW

D WITH ins AS (INSERT INTO orders (order_id, amount) VALUES (101, 250) RETURNING order_id) SELECT order_id FROM ins;

💡 Hint

Check the syntax of the INSERT statement inside the CTE.

Option A misses parentheses around columns in INSERT INTO clause, causing syntax error.

❓ optimization

advanced

Best practice for updating multiple rows with writable CTE

You want to increase salary by 5% for all employees in department 10 and get their updated salaries. Which query is most efficient and correct?

Options:
A) Use a writable CTE with UPDATE and RETURNING, then select from it.
B) Use a subquery to select employees, then update outside.
C) Use two separate queries: one UPDATE, one SELECT.
D) Use a writable CTE with DELETE and INSERT to replace rows.

A WITH updated AS (UPDATE employees SET salary = salary * 1.05 WHERE department_id = 10 RETURNING id, salary) SELECT * FROM updated;

B UPDATE employees SET salary = salary * 1.05 WHERE id IN (SELECT id FROM employees WHERE department_id = 10); SELECT id, salary FROM employees WHERE department_id = 10;

C UPDATE employees SET salary = salary * 1.05 WHERE department_id = 10; SELECT id, salary FROM employees WHERE department_id = 10;

D WITH replaced AS (DELETE FROM employees WHERE department_id = 10 RETURNING *) INSERT INTO employees SELECT * FROM replaced;

💡 Hint

Writable CTEs can update and return rows in one query.

Option A uses writable CTE to update and return updated rows efficiently in one step.

🧠 Conceptual

expert

Effect of writable CTE on transaction visibility

In PostgreSQL, when using a writable CTE with DELETE and RETURNING inside a transaction, what happens if you try to SELECT the deleted rows outside the CTE but before committing?

Choose the correct behavior:

A The deleted rows remain visible in all SELECTs until commit, including inside the CTE.

B The deleted rows are visible in the CTE but not visible in a separate SELECT until commit.

C The deleted rows are immediately removed from all SELECTs, including inside the CTE, even before commit.

D The DELETE inside the writable CTE does not affect visibility until the transaction commits.

💡 Hint

Think about how RETURNING works and transaction isolation in PostgreSQL.

Writable CTE's RETURNING shows deleted rows immediately inside the CTE, but outside SELECTs still see old data until commit.