UPDATE with subquery preview in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of UPDATE with correlated subquery

Consider the tables employees and departments. The employees table has columns id, name, salary, and department_id. The departments table has columns id and budget.

What will be the salary of employee with id = 3 after running this query?

SQL

UPDATE employees e
SET salary = (
  SELECT budget / 10
  FROM departments d
  WHERE d.id = e.department_id
)
WHERE e.id = 3;

A 5000

B 10000

C NULL

D No change (salary remains the same)

💡 Hint

Think about how the subquery uses the department_id of the employee to find the department's budget.

The subquery selects the budget from the departments table where the id matches the employee's department_id. Then it divides that budget by 10 and sets it as the new salary for the employee with id = 3.

🧠 Conceptual

intermediate

Understanding UPDATE with subquery behavior

Which statement best describes what happens when you run an UPDATE statement with a subquery in the SET clause?

A The subquery is ignored if it returns more than one row.

B The subquery is executed only once before the update starts and its result is used for all rows.

C The subquery is executed once for each row being updated, using values from that row.

D The subquery must not reference the table being updated.

💡 Hint

Think about how the subquery can use values from the row being updated.

In an UPDATE with a correlated subquery, the subquery runs once per row, using that row's values to compute the new value.

📝 Syntax

advanced

Identify the syntax error in UPDATE with subquery

Which option contains a syntax error in the UPDATE statement using a subquery?

SQL

UPDATE products p
SET price = (SELECT AVG(price) FROM products WHERE category = p.category)
WHERE category = 'Electronics';

A UPDATE products SET price = (SELECT AVG(price) FROM products WHERE category = products.category) WHERE category = 'Electronics';

B UPDATE products p SET price = SELECT AVG(price) FROM products WHERE category = p.category WHERE category = 'Electronics';

C UPDATE products p SET price = (SELECT AVG(price) FROM products WHERE category = p.category);

D UPDATE products p SET price = (SELECT AVG(price) FROM products WHERE category = p.category) WHERE category = 'Electronics';

💡 Hint

Look carefully at the placement of parentheses and keywords.

Option B is missing parentheses around the subquery and has an extra WHERE clause outside the subquery, causing syntax error.

❓ optimization

advanced

Optimizing UPDATE with subquery for performance

You want to update the salary of all employees to the average salary of their department. Which query is more efficient?

A UPDATE employees e SET salary = (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

B UPDATE employees SET salary = (SELECT AVG(salary) FROM employees);

C UPDATE employees SET salary = (SELECT salary FROM employees WHERE id = employees.id);

D UPDATE employees SET salary = avg_salary FROM (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) d WHERE employees.department_id = d.department_id;

💡 Hint

Consider how many times the subquery runs in each option.

Option D calculates average salaries per department once in a derived table, then joins to update, reducing repeated subquery executions.

🔧 Debug

expert

Why does this UPDATE with subquery cause an error?

Given the query below, why does it cause an error?

SQL

UPDATE orders o
SET total = (SELECT SUM(amount) FROM order_items WHERE order_id = o.id)
WHERE o.id IN (SELECT id FROM orders WHERE status = 'pending');

A The subquery is valid and causes no error.

B The subquery returns multiple rows causing a runtime error.

C The subquery returns NULL for some rows causing the update to fail.

D The subquery references the same table being updated causing a conflict.

💡 Hint

Think about whether the subquery returns a single value per row.

The subquery returns a single sum value per order, so it is valid. The WHERE clause limits updates to pending orders only.