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.