UPDATE with expressions in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of UPDATE with arithmetic expression

Given a table products with columns id, price, and stock, what will be the price of the product with id = 2 after running this query?

UPDATE products SET price = price + 10 WHERE id = 2;

SQL

SELECT price FROM products WHERE id = 2;

A The price increases by 10 for product with id 2

B The price decreases by 10 for product with id 2

C The price remains the same for product with id 2

D Syntax error in the UPDATE statement

💡 Hint

Think about how the expression modifies the current price value.

The UPDATE statement adds 10 to the current price of the product where id equals 2, increasing its price by 10.

❓ query_result

intermediate

Result of UPDATE with string concatenation

Consider a table users with columns id and username. What will be the new username for the user with id = 1 after executing?

UPDATE users SET username = username || '_2024' WHERE id = 1;

SQL

SELECT username FROM users WHERE id = 1;

A The username will have '_2024' prepended at the start

B The username will be replaced entirely by '_2024'

C The username will have '_2024' appended at the end

D The query will cause a syntax error

💡 Hint

The operator || concatenates strings in SQL.

The UPDATE appends '_2024' to the existing username for the user with id 1.

📝 Syntax

advanced

Identify the syntax error in UPDATE with expression

Which option contains a syntax error in the UPDATE statement that tries to increase salary by 5% for employees in department 10?

A UPDATE employees SET salary = salary * 5% WHERE department_id = 10;

B UPDATE employees SET salary = salary + salary * 0.05 WHERE department_id = 10;

C UPDATE employees SET salary = salary + (salary * 0.05) WHERE department_id = 10;

D UPDATE employees SET salary = salary * 1.05 WHERE department_id = 10;

💡 Hint

Check how percentages are expressed in SQL arithmetic.

Option A uses '5%' which is not valid syntax in SQL arithmetic expressions; percentages must be expressed as decimals.

🔧 Debug

advanced

Why does this UPDATE not change any rows?

Given a table orders with columns order_id, status, and quantity, why does this query not update any rows?

UPDATE orders SET quantity = quantity + 1 WHERE status = 'shipped' AND quantity < 0;

A The quantity column cannot be updated because it is a primary key

B No rows have status 'shipped' with quantity less than 0

C The syntax of the UPDATE statement is incorrect

D The WHERE clause is missing parentheses causing a logic error

💡 Hint

Think about the data values that satisfy the WHERE condition.

The condition requires quantity to be less than 0, but no such rows exist with status 'shipped', so no rows are updated.

🧠 Conceptual

expert

Effect of multiple expressions in UPDATE

What will be the final values of score and level columns after running this query on a table players with one row where score = 100 and level = 2?

UPDATE players SET score = score + 50, level = level * 2 WHERE level = 2;

SQL

SELECT score, level FROM players WHERE level = 4;

A score = 50 and level = 4

B score = 100 and level = 4

C score = 150 and level = 2

D score = 150 and level = 4

💡 Hint

Both columns are updated in the same statement; the WHERE filters rows before update.

The row with level 2 matches the WHERE clause, so score increases by 50 (100 + 50 = 150) and level doubles (2 * 2 = 4).