CTE with INSERT, UPDATE, DELETE (writable CTEs) in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to insert a new user into the users table using a writable CTE.

PostgreSQL

WITH new_user AS (INSERT INTO users (name, email) VALUES ('Alice', '[e-mail]') RETURNING [1]) SELECT * FROM new_user;

Drag options to blanks, or click blank then click option'

A*

Bid

Cname

Demail

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting RETURNING clause causes no rows to be returned.

Using RETURNING without specifying columns returns an error in some databases.

The RETURNING * clause returns all columns of the inserted row, which is useful to see the full inserted record.

2fill in blank

medium

Complete the code to update the email of a user with id 5 using a writable CTE.

PostgreSQL

WITH updated_user AS (UPDATE users SET email = '[e-mail]' WHERE id = 5 RETURNING [1]) SELECT * FROM updated_user;

Drag options to blanks, or click blank then click option'

Aemail

Bname

C*

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Not using RETURNING causes no rows to be returned.

Returning only one column may miss other updated data.

Using RETURNING * returns all columns of the updated row, showing the full updated record.

3fill in blank

hard

Fix the error in the DELETE statement to return the deleted rows using a writable CTE.

PostgreSQL

WITH deleted_users AS (DELETE FROM users WHERE last_login < '2023-01-01' RETURNING [1]) SELECT * FROM deleted_users;

Drag options to blanks, or click blank then click option'

Alast_login

Bid

Cemail

D*

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting RETURNING clause causes no output.

Returning only one column may not show full deleted data.

Using RETURNING * returns all columns of the deleted rows, which is useful to see what was removed.

4fill in blank

hard

Fill both blanks to insert multiple products and return their ids and names.

PostgreSQL

WITH inserted_products AS (INSERT INTO products (name, price) VALUES ('Pen', 1.5), ('Notebook', 3.0) RETURNING [1], [2]) SELECT * FROM inserted_products;

Drag options to blanks, or click blank then click option'

Aid

Bname

Cprice

Ddescription

Attempts:

3 left

💡 Hint

Common Mistakes

Returning columns not in the table causes errors.

Forgetting commas between columns in RETURNING.

Returning id and name shows the unique identifier and the product name of inserted rows.

5fill in blank

hard

Fill all three blanks to update user status, return id, status, and email of updated users.

PostgreSQL

WITH updated_status AS (UPDATE users SET status = 'active' WHERE last_login > '2024-01-01' RETURNING [1], [2], [3]) SELECT * FROM updated_status;

Drag options to blanks, or click blank then click option'

Aid

Bstatus

Cemail

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Returning columns not updated or irrelevant.

Missing commas between columns in RETURNING.

Returning id, status, and email shows key details of the updated users.