Returning modified rows with RETURNING in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to update the salary of employees and return the updated rows.

PostgreSQL

UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales' [1] id, salary;

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

AFETCH

BRETURNING

CSELECT

DOUTPUT

Attempts:

3 left

💡 Hint

Common Mistakes

Using OUTPUT instead of RETURNING, which is not valid in PostgreSQL.

Trying to use SELECT after UPDATE without RETURNING.

The RETURNING clause in PostgreSQL allows you to get the rows affected by the UPDATE statement. Here, it returns the id and salary of updated employees.

2fill in blank

medium

Complete the code to delete employees from the Marketing department and return their names.

PostgreSQL

DELETE FROM employees WHERE department = 'Marketing' [1] name;

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

ARETURNING

BSELECT

CFETCH

DOUTPUT

Attempts:

3 left

💡 Hint

Common Mistakes

Using OUTPUT which is not supported in PostgreSQL.

Trying to use SELECT after DELETE without RETURNING.

In PostgreSQL, the RETURNING clause after DELETE returns the specified columns of the deleted rows. Here, it returns the name of deleted employees.

3fill in blank

hard

Fix the error in the code to insert a new employee and return their id and name.

PostgreSQL

INSERT INTO employees (name, department, salary) VALUES ('Alice', 'HR', 50000) [1] id, name;

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

AOUTPUT

BFETCH

CSELECT

DRETURNING

Attempts:

3 left

💡 Hint

Common Mistakes

Using OUTPUT which is not valid in PostgreSQL.

Trying to use SELECT after INSERT without RETURNING.

The RETURNING clause after INSERT returns the specified columns of the newly inserted row(s). Here, it returns id and name of the new employee.

4fill in blank

hard

Fill both blanks to update the price of products and return their id and new price.

PostgreSQL

UPDATE products SET price = price [1] 1.2 WHERE category = 'Books' [2] id, price;

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

A*

B+

CRETURNING

DOUTPUT

Attempts:

3 left

💡 Hint

Common Mistakes

Using '+' instead of '*' to increase price by percentage.

Using OUTPUT instead of RETURNING.

The price is increased by multiplying by 1.2 using the * operator. The RETURNING clause returns the id and updated price of the products.

5fill in blank

hard

Fill all three blanks to insert a new order and return its id, customer_id, and total.

PostgreSQL

INSERT INTO orders (customer_id, total) VALUES ([1], [2]) [3] id, customer_id, total;

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

A123

B250.75

CRETURNING

DOUTPUT

Attempts:

3 left

💡 Hint

Common Mistakes

Using OUTPUT instead of RETURNING.

Putting quotes around numeric values.

The values 123 and 250.75 are inserted for customer_id and total. The RETURNING clause returns the id, customer_id, and total of the new order.

Practice

1. What does the RETURNING clause do in a PostgreSQL UPDATE statement?

easy

A. It delays the update until a commit is issued.

B. It prevents the update from modifying any rows.

C. It returns the rows that were updated immediately after the update.

D. It returns the number of rows in the table before the update.

Solution

1. Step 1: Understand the purpose of RETURNING

   The RETURNING clause is used to get the data of rows affected by an INSERT, UPDATE, or DELETE immediately.

2. Step 2: Apply to UPDATE statement

   When used with UPDATE, RETURNING shows the updated rows without needing a separate SELECT query.

3. Final Answer:

   It returns the rows that were updated immediately after the update. -> Option C

4. Quick Check:

   RETURNING shows changed rows = B [OK]

Hint: RETURNING shows changed rows right after modification [OK]

Common Mistakes:

• Thinking RETURNING delays changes
• Assuming RETURNING prevents updates
• Confusing RETURNING with row count only

2. Which of the following is the correct syntax to return all columns of updated rows in PostgreSQL?

easy

A. UPDATE table_name SET col = val RETURNING ALL COLUMNS;

B. UPDATE table_name SET col = val RETURNING *;

C. UPDATE table_name SET col = val RETURNING ALL;

D. UPDATE table_name SET col = val RETURN *;

Solution

1. Step 1: Recall correct RETURNING syntax

   PostgreSQL uses RETURNING followed by column names or * to return all columns.

2. Step 2: Identify correct option

   Only UPDATE table_name SET col = val RETURNING *; uses RETURNING * which is valid syntax to return all columns.

3. Final Answer:

   UPDATE table_name SET col = val RETURNING *; -> Option B

4. Quick Check:

   RETURNING * returns all columns [OK]

Hint: Use RETURNING * to get all columns after update [OK]

Common Mistakes:

• Using RETURN instead of RETURNING
• Writing RETURNING ALL instead of RETURNING *
• Adding extra keywords like ALL COLUMNS

3. Given the table users(id INT, name TEXT, age INT) with rows:
(1, 'Alice', 30), (2, 'Bob', 25)
What will this query return?

UPDATE users SET age = age + 1 WHERE id = 2 RETURNING id, age;

medium

A. (2, 26)

B. (2, 25)

C. (1, 31)

D. (1, 30)

Solution

1. Step 1: Identify updated row

   The WHERE clause updates only the row with id = 2 (Bob).

2. Step 2: Calculate new age and returned columns

   Bob's age increases from 25 to 26. RETURNING id, age returns (2, 26).

3. Final Answer:

   (2, 26) -> Option A

4. Quick Check:

   Updated row with id=2 returns new age 26 [OK]

Hint: RETURNING shows updated values immediately [OK]

Common Mistakes:

• Returning old values instead of updated
• Confusing which row is updated
• Ignoring WHERE clause effect

4. What is wrong with this query?

UPDATE products SET price = price * 1.1 RETURNING;

medium

A. RETURNING cannot be used with UPDATE statements.

B. The multiplication operator * is invalid in UPDATE.

C. Missing WHERE clause causes syntax error.

D. RETURNING clause requires columns or * to specify what to return.

Solution

1. Step 1: Check RETURNING syntax

   RETURNING must be followed by column names or *; empty RETURNING is invalid syntax.

2. Step 2: Validate other parts

   Multiplication and UPDATE without WHERE are valid; RETURNING with no columns is the error.

3. Final Answer:

   RETURNING clause requires columns or * to specify what to return. -> Option D

4. Quick Check:

   RETURNING needs columns or * [OK]

Hint: Always specify columns or * after RETURNING [OK]

Common Mistakes:

• Leaving RETURNING empty
• Thinking WHERE is mandatory for syntax
• Believing RETURNING disallowed in UPDATE

5. You want to increase the salary by 10% for employees in department 5 and get their id and new salary. Which query correctly does this and returns the updated rows?

hard

A. UPDATE employees SET salary = salary * 1.1 WHERE department_id = 5 RETURNING id, salary;

B. UPDATE employees SET salary = salary + 10 WHERE department_id = 5 RETURNING *;

C. UPDATE employees SET salary = salary * 1.1 RETURNING id, salary WHERE department_id = 5;

D. UPDATE employees SET salary = salary * 1.1 WHERE department_id = 5 RETURN id, salary;

Solution

1. Step 1: Identify correct UPDATE with WHERE and RETURNING

   The query must update only department 5 employees and return id and salary.

2. Step 2: Check syntax correctness

   UPDATE employees SET salary = salary * 1.1 WHERE department_id = 5 RETURNING id, salary; uses correct syntax: UPDATE ... SET ... WHERE ... RETURNING id, salary;

3. Step 3: Eliminate incorrect options

   Adding a fixed 10 instead of multiplying by 1.1 doesn't implement a 10% increase. Placing RETURNING before WHERE is invalid syntax. Using RETURN instead of RETURNING is wrong.

4. Final Answer:

   UPDATE employees SET salary = salary * 1.1 WHERE department_id = 5 RETURNING id, salary; -> Option A

5. Quick Check:

   Correct syntax with WHERE and RETURNING columns [OK]

Hint: RETURNING follows WHERE clause with columns listed [OK]

Common Mistakes:

• Placing RETURNING before WHERE
• Using RETURN instead of RETURNING
• Adding fixed amount instead of percentage