UPDATE with WHERE condition in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output after this UPDATE?

Given the table Employees:

id | name   | salary
---+--------+--------
1  | Alice  | 5000
2  | Bob    | 4500
3  | Carol  | 5500

What will be the salary of Bob after running this query?

UPDATE Employees SET salary = salary + 500 WHERE name = 'Bob';

A 5000

B 4500

C 5500

D 500

💡 Hint

Only the row where name is 'Bob' is updated.

The query adds 500 to Bob's salary (4500 + 500 = 5000). Other rows remain unchanged.

❓ query_result

intermediate

How many rows are updated?

Consider the table Products:

product_id | name       | stock
-----------+------------+-------
1          | Pen        | 100
2          | Pencil     | 200
3          | Notebook   | 150
4          | Eraser     | 50

After running this query, how many rows will have their stock increased?

UPDATE Products SET stock = stock + 10 WHERE stock < 150;

A 2

B 1

C 3

D 4

💡 Hint

Check which rows have stock less than 150.

Rows with stock 100 (Pen) and 50 (Eraser) satisfy the condition, so 2 rows are updated.

📝 Syntax

advanced

Which UPDATE query is syntactically correct?

Choose the correct SQL UPDATE statement that increases price by 20 for products with category 'Books'.

A UPDATE Products price = price + 20 WHERE category = 'Books';

B UPDATE Products SET price + 20 WHERE category = 'Books';

C UPDATE Products SET price = price + 20 WHERE category = 'Books';

D UPDATE Products SET price = price + 20 category = 'Books';

💡 Hint

Remember the syntax: UPDATE table SET column = value WHERE condition;

Option C uses correct syntax with SET and WHERE clauses properly.

Options A, B, and D have syntax errors.

❓ optimization

advanced

Which query is more efficient to update salaries for employees in department 5?

Given a large Employees table with a department_id column, you want to increase salaries by 100 for all employees in department 5.

Which query is more efficient?

A UPDATE Employees SET salary = salary + 100;

B UPDATE Employees SET salary = salary + 100 WHERE department_id = 5;

C UPDATE Employees SET salary = salary + 100 WHERE department_id IN (SELECT department_id FROM Departments WHERE department_id = 5);

D UPDATE Employees SET salary = salary + 100 WHERE department_id > 0;

💡 Hint

Consider the condition that targets only department 5.

Option B directly filters employees in department 5, updating only needed rows efficiently.

Option B updates all rows unnecessarily.

Option B uses a subquery which is less efficient here.

Option B updates all employees with department_id > 0, which is broader than needed.

🔧 Debug

expert

What error does this UPDATE query raise?

Consider this query:

UPDATE Employees SET salary = salary + 100 WHERE name = 'John' AND;

What error will the database return?

A Constraint violation error

B No rows updated

C Type error on salary column

D Syntax error near 'AND'

💡 Hint

Look carefully at the WHERE clause syntax.

The WHERE clause ends abruptly with 'AND' without a condition after it, causing a syntax error.