UPDATE with subquery preview in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to update the salary of employees to 50000 where their department is 'Sales'.

SQL

UPDATE employees SET salary = [1] WHERE department = 'Sales';

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

ANULL

B50000

Csalary + 50000

D'50000'

Attempts:

3 left

💡 Hint

Common Mistakes

Putting 50000 in quotes, which makes it a string.

Using salary + 50000 which adds instead of setting.

The salary should be set to the number 50000 without quotes because salary is a numeric column.

2fill in blank

medium

Complete the code to update the salary of employees to the average salary of their department.

SQL

UPDATE employees SET salary = (SELECT AVG(salary) FROM employees WHERE department = [1]);

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

Adepartment

Bsalary

C'department'

D'Sales'

Attempts:

3 left

💡 Hint

Common Mistakes

Using quotes around department, making it a string instead of a column.

Using salary instead of department.

The subquery compares the department column of the outer query with the department column inside the subquery, so no quotes are needed.

3fill in blank

hard

Fix the error in the code to update employees' salary to the maximum salary in their department.

SQL

UPDATE employees SET salary = (SELECT MAX(salary) FROM employees WHERE department = [1]);

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

Adepartment

B'Sales'

C'department'

Dsalary

Attempts:

3 left

💡 Hint

Common Mistakes

Using quotes around department.

Using a fixed string like 'Sales' instead of the column.

The subquery must compare the department column of the outer query to the inner query's department column without quotes.

4fill in blank

hard

Fill both blanks to update employees' bonus to 10% of their salary where their department is 'Sales'.

SQL

UPDATE employees SET bonus = salary * [1] WHERE department = [2];

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

A0.1

B'Sales'

Cdepartment

D10

Attempts:

3 left

💡 Hint

Common Mistakes

Using 10 instead of 0.1 for percentage.

Not quoting the department string.

The bonus is 10% so 0.1 is used. The department is a string, so it needs quotes.

5fill in blank

hard

Fill all three blanks to update employees' salary to the average salary of their department only if their current salary is below that average.

SQL

UPDATE employees SET salary = (SELECT AVG(salary) FROM employees WHERE department = [1]) WHERE salary < (SELECT AVG(salary) FROM employees WHERE department = [2]) AND department = [3];

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

Adepartment

B'HR'

C'Sales'

Dsalary

Attempts:

3 left

💡 Hint

Common Mistakes

Quoting the department column in subqueries.

Not quoting the department string in the WHERE clause.

The subqueries compare the department column, so use the column name without quotes. The final department filter is a string, so it needs quotes.

Practice

1. What does an UPDATE statement with a subquery do in SQL?

easy

A. It changes values in a table based on data from another table.

B. It deletes rows from a table using a subquery.

C. It creates a new table from a subquery.

D. It selects rows without changing any data.

Solution

1. Step 1: Understand the purpose of UPDATE with subquery

   UPDATE modifies existing rows in a table. When combined with a subquery, it uses data from another table to decide new values.

2. Step 2: Identify the effect of the subquery

   The subquery provides values to update the target table, ensuring related data stays consistent.

3. Final Answer:

   It changes values in a table based on data from another table. -> Option A

4. Quick Check:

   UPDATE with subquery = change data using other table [OK]

Hint: UPDATE with subquery changes data using another table's values [OK]

Common Mistakes:

• Thinking UPDATE deletes rows
• Confusing UPDATE with SELECT
• Believing UPDATE creates new tables

2. Which of the following is the correct syntax for updating a table using a subquery in SQL?

easy

A. UPDATE table1 SET (col1) = (SELECT col2 FROM table2);

B. UPDATE table1 FROM (SELECT col2 FROM table2) SET col1 = col2;

C. UPDATE table1 SET col1 = SELECT col2 FROM table2 WHERE id = table1.id;

D. UPDATE table1 SET col1 = (SELECT col2 FROM table2 WHERE id = table1.id);

Solution

1. Step 1: Review correct UPDATE with subquery syntax

   The correct syntax uses SET column = (SELECT ...), with the subquery in parentheses returning a single value.

2. Step 2: Check each option

   UPDATE table1 SET col1 = (SELECT col2 FROM table2 WHERE id = table1.id); correctly uses SET col1 = (SELECT ... WHERE ...). Options B, C, and D have syntax errors or missing parentheses.

3. Final Answer:

   UPDATE table1 SET col1 = (SELECT col2 FROM table2 WHERE id = table1.id); -> Option D

4. Quick Check:

   SET col = (SELECT ...) is correct syntax [OK]

Hint: Use SET col = (SELECT ...) with parentheses for subquery [OK]

Common Mistakes:

• Omitting parentheses around subquery
• Placing SELECT outside SET
• Using FROM incorrectly in UPDATE

3. Given these tables:

Employees(emp_id, dept_id, salary)
Departments(dept_id, bonus)

What will this query do?

UPDATE Employees SET salary = salary + (SELECT bonus FROM Departments WHERE Departments.dept_id = Employees.dept_id);

medium

A. Delete employees whose salary is less than bonus.

B. Increase each employee's salary by their department's bonus amount.

C. Cause a syntax error due to subquery in UPDATE.

D. Set all salaries to the bonus amount of the first department.

Solution

1. Step 1: Understand the subquery's role

   The subquery selects the bonus from Departments matching the employee's dept_id, returning one value per employee.

2. Step 2: Analyze the UPDATE effect

   Each employee's salary is increased by their department's bonus, as the subquery returns the correct bonus per row.

3. Final Answer:

   Increase each employee's salary by their department's bonus amount. -> Option B

4. Quick Check:

   UPDATE with correlated subquery adds bonus per employee [OK]

Hint: Subquery returns one value per row to update correctly [OK]

Common Mistakes:

• Assuming subquery returns multiple rows causing error
• Thinking it sets salary to bonus instead of adding
• Confusing UPDATE with DELETE

4. Identify the error in this SQL statement:

UPDATE Products SET price = (SELECT discount FROM Discounts WHERE product_id = Products.id);

Assuming Discounts has multiple rows per product_id.

medium

A. Subquery returns multiple rows causing an error.

B. Missing WHERE clause in UPDATE.

C. Incorrect table alias usage.

D. UPDATE cannot use subqueries.

Solution

1. Step 1: Check subquery result for each row

   The subquery selects discount from Discounts where product_id matches. If multiple discounts exist, it returns multiple rows.

2. Step 2: Understand UPDATE with subquery requirement

   UPDATE expects the subquery to return a single value per row. Multiple rows cause an error.

3. Final Answer:

   Subquery returns multiple rows causing an error. -> Option A

4. Quick Check:

   Subquery must return one value per row [OK]

Hint: Subquery must return single value per row to avoid error [OK]

Common Mistakes:

• Ignoring multiple rows returned by subquery
• Assuming missing WHERE causes error here
• Believing UPDATE disallows subqueries

5. You want to update a Users table to set each user's status to 'VIP' if their total orders amount exceeds 1000. The Orders table has user_id and amount. Which query correctly uses UPDATE with a subquery to do this?

hard

A. UPDATE Users SET status = 'VIP' WHERE (SELECT amount FROM Orders WHERE user_id = Users.id) > 1000;

B. UPDATE Users SET status = 'VIP' WHERE EXISTS (SELECT 1 FROM Orders WHERE user_id = Users.id AND amount > 1000);

C. UPDATE Users SET status = 'VIP' WHERE id IN (SELECT user_id FROM Orders GROUP BY user_id HAVING SUM(amount) > 1000);

D. UPDATE Users SET status = (SELECT 'VIP' FROM Orders WHERE user_id = Users.id AND amount > 1000);

Solution

1. Step 1: Understand the requirement

   We must update Users whose total order amount exceeds 1000. This requires grouping Orders by user_id and summing amounts.

2. Step 2: Analyze each option

   UPDATE Users SET status = 'VIP' WHERE (SELECT amount FROM Orders WHERE user_id = Users.id) > 1000; uses a subquery in WHERE that returns multiple rows if user has multiple orders, causing an error. UPDATE Users SET status = (SELECT 'VIP' FROM Orders WHERE user_id = Users.id AND amount > 1000); tries to set status from subquery returning 'VIP' per order, which is incorrect. UPDATE Users SET status = 'VIP' WHERE EXISTS (SELECT 1 FROM Orders WHERE user_id = Users.id AND amount > 1000); checks if any order > 1000, not total sum. UPDATE Users SET status = 'VIP' WHERE id IN (SELECT user_id FROM Orders GROUP BY user_id HAVING SUM(amount) > 1000); correctly uses WHERE id IN with grouped subquery filtering users with sum(amount) > 1000.

3. Final Answer:

   UPDATE Users SET status = 'VIP' WHERE id IN (SELECT user_id FROM Orders GROUP BY user_id HAVING SUM(amount) > 1000); -> Option C

4. Quick Check:

   Use GROUP BY and HAVING in subquery for totals [OK]

Hint: Use GROUP BY and HAVING in subquery for conditional UPDATE [OK]

Common Mistakes:

• Using WHERE EXISTS without aggregation
• Setting column from subquery returning multiple rows
• Ignoring grouping for total sums