UPDATE single column in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Update a single column for specific rows

Given a table Employees with columns id, name, and salary, what will be the result of this query?

UPDATE Employees SET salary = salary + 500 WHERE id = 3;

Assume the original salary for id = 3 is 3000.

SQL

SELECT id, name, salary FROM Employees ORDER BY id;

A [{"id":1,"name":"Alice","salary":4000},{"id":2,"name":"Bob","salary":4000},{"id":3,"name":"Charlie","salary":3500}]

B [{"id":1,"name":"Alice","salary":4000},{"id":2,"name":"Bob","salary":3500},{"id":3,"name":"Charlie","salary":3000}]

C [{"id":1,"name":"Alice","salary":3500},{"id":2,"name":"Bob","salary":3500},{"id":3,"name":"Charlie","salary":3500}]

D [{"id":1,"name":"Alice","salary":4000},{"id":2,"name":"Bob","salary":3500},{"id":3,"name":"Charlie","salary":3500}]

💡 Hint

Only the row with id = 3 will have its salary increased by 500.

The UPDATE statement changes the salary of the employee with id = 3 by adding 500 to the current salary of 3000, resulting in 3500. Other rows remain unchanged.

📝 Syntax

intermediate

Identify the syntax error in UPDATE statement

Which option contains a syntax error in updating a single column in the Products table to set price to 20 where id is 5?

A UPDATE Products SET price = 20 WHERE id = 5;

B UPDATE Products SET price = 20 WHERE id = 5

C UPDATE Products SET price 20 WHERE id = 5;

D UPDATE Products SET price = 20 WHERE id == 5;

💡 Hint

Check for missing operators or incorrect syntax in the SET clause.

Option C is missing the '=' sign between the column name and the new value, causing a syntax error.

❓ optimization

advanced

Optimizing UPDATE for large tables

You want to increase the stock column by 10 for all products in category 'Books' in a large Inventory table. Which query is the most efficient?

A UPDATE Inventory SET stock = stock + 10 WHERE category = 'Books';

B UPDATE Inventory SET stock = stock + 10;

C UPDATE Inventory SET stock = stock + 10 WHERE category LIKE '%Books%';

D UPDATE Inventory SET stock = stock + 10 WHERE category IN (SELECT category FROM Inventory WHERE category = 'Books');

💡 Hint

Focus on limiting the update only to relevant rows without unnecessary overhead.

Option A directly updates only rows where category equals 'Books', which is efficient. Option A updates all rows unnecessarily. Option A uses LIKE which may be slower and less precise. Option A uses a subquery that is redundant and less efficient.

🔧 Debug

advanced

Why does this UPDATE not change any rows?

Given the table Users with columns id, username, and active, why does this query not update any rows?

UPDATE Users SET active = 1 WHERE username = 'john_doe';

Assume there is a user with username 'John_Doe' (capital J).

A Because the table Users does not have a column named active.

B Because the WHERE clause is case-sensitive and 'john_doe' does not match 'John_Doe'.

C Because the UPDATE statement is missing a semicolon at the end.

D Because the active column is already 1 for all users.

💡 Hint

Think about how string comparisons work in SQL by default.

Most SQL databases treat string comparisons as case-sensitive by default, so 'john_doe' does not match 'John_Doe'. This causes no rows to be updated.

🧠 Conceptual

expert

Effect of UPDATE without WHERE clause

What happens if you run this query on the Orders table?

UPDATE Orders SET status = 'shipped';

A All rows in the Orders table will have their status set to 'shipped'.

B Only the first row in the Orders table will be updated.

C No rows will be updated because WHERE clause is missing.

D The query will cause a syntax error due to missing WHERE clause.

💡 Hint

Consider what happens when UPDATE is run without any filter.

Without a WHERE clause, UPDATE affects every row in the table, setting the status column to 'shipped' for all orders.

Practice

1. What does the SQL statement UPDATE employees SET salary = 5000 WHERE id = 3; do?

easy

A. It changes the salary to 5000 for the employee with id 3.

B. It adds a new employee with salary 5000 and id 3.

C. It deletes the employee with id 3.

D. It shows the salary of employee with id 3.

Solution

1. Step 1: Understand the UPDATE statement

   The UPDATE command changes existing data in a table.

2. Step 2: Analyze the SET and WHERE clauses

   SET salary = 5000 changes the salary column, WHERE id = 3 limits the change to the employee with id 3.

3. Final Answer:

   It changes the salary to 5000 for the employee with id 3. -> Option A

4. Quick Check:

   UPDATE with SET and WHERE changes specified rows [OK]

Hint: UPDATE changes data; SET picks column; WHERE picks rows [OK]

Common Mistakes:

• Thinking UPDATE adds new rows
• Forgetting WHERE updates all rows
• Confusing UPDATE with SELECT

2. Which of the following is the correct syntax to update the price column to 20 in the products table for product with id 10?

easy

A. UPDATE products SET price = 20 WHERE id = 10;

B. UPDATE products price = 20 WHERE id = 10;

C. UPDATE products SET price WHERE id = 10 = 20;

D. UPDATE products SET price = 20;

Solution

1. Step 1: Check the UPDATE syntax

   The correct syntax is UPDATE table_name SET column = value WHERE condition;

2. Step 2: Validate each option

   UPDATE products SET price = 20 WHERE id = 10; matches the correct syntax with SET and WHERE clauses properly used.

3. Final Answer:

   UPDATE products SET price = 20 WHERE id = 10; -> Option A

4. Quick Check:

   Correct UPDATE syntax uses SET column = value and WHERE [OK]

Hint: Always use SET column = value and WHERE condition [OK]

Common Mistakes:

• Omitting SET keyword
• Placing WHERE before SET
• Missing semicolon at end

3. Given the table students with columns id, name, and grade, what will be the result of this query?

UPDATE students SET grade = 90 WHERE name = 'Alice';

Assuming the table before update:

id | name  | grade
1  | Alice | 85
2  | Bob   | 88
3  | Alice | 92

medium

A. No rows will be updated because name is not unique.

B. Only the first row with name 'Alice' will be updated.

C. Both rows with name 'Alice' will have grade updated to 90.

D. All rows in the table will have grade set to 90.

Solution

1. Step 1: Understand the WHERE clause effect

   WHERE name = 'Alice' matches all rows where name is exactly 'Alice'.

2. Step 2: Apply UPDATE to matching rows

   Both rows with name 'Alice' (id 1 and 3) will have their grade set to 90.

3. Final Answer:

   Both rows with name 'Alice' will have grade updated to 90. -> Option C

4. Quick Check:

   UPDATE affects all rows matching WHERE condition [OK]

Hint: WHERE filters all matching rows, not just one [OK]

Common Mistakes:

• Assuming only one row updates with non-unique WHERE
• Thinking UPDATE without WHERE updates one row
• Confusing UPDATE with SELECT

4. Identify the error in this SQL statement:

UPDATE orders SET status 'shipped' WHERE order_id = 101;

medium

A. Incorrect table name.

B. Missing equal sign (=) between column and value.

C. Using single quotes around column name.

D. Missing WHERE clause.

Solution

1. Step 1: Check SET clause syntax

   SET clause must have column = value format. Here, equal sign is missing.

2. Step 2: Verify other parts

   WHERE clause is present, table name looks correct, and no quotes around column name.

3. Final Answer:

   Missing equal sign (=) between column and value. -> Option B

4. Quick Check:

   SET requires column = value syntax [OK]

Hint: SET needs column = value, not just column value [OK]

Common Mistakes:

• Omitting equal sign in SET
• Forgetting WHERE causes all rows update
• Using quotes incorrectly around column names

5. You have a table inventory with columns item_id, quantity, and price. You want to increase the price by 10% only for items with quantity less than 5. Which SQL statement correctly updates the price column?

hard

A. UPDATE inventory SET price = price + 10 WHERE quantity < 5;

B. UPDATE inventory SET price = price + 0.10 WHERE quantity < 5;

C. UPDATE inventory SET price = price * 0.10 WHERE quantity < 5;

D. UPDATE inventory SET price = price * 1.10 WHERE quantity < 5;

Solution

1. Step 1: Understand the requirement

   Increase price by 10% means multiply price by 1.10.

2. Step 2: Check the WHERE condition

   Only update rows where quantity is less than 5.

3. Step 3: Evaluate each option

   UPDATE inventory SET price = price * 1.10 WHERE quantity < 5; correctly multiplies price by 1.10 with proper WHERE clause.

4. Final Answer:

   UPDATE inventory SET price = price * 1.10 WHERE quantity < 5; -> Option D

5. Quick Check:

   Multiply price by 1.10 to increase by 10% [OK]

Hint: Multiply price by 1.10 to increase by 10% [OK]

Common Mistakes:

• Adding 10 instead of 10%
• Multiplying by 0.10 reduces price
• Adding 0.10 increases price by 0.10 units only