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).

Practice

1. What does the SQL statement UPDATE employees SET salary = salary + 500 do?

easy

A. Sets all salaries to 500

B. Increases the salary of all employees by 500

C. Deletes employees with salary 500

D. Creates a new employee with salary 500

Solution

1. Step 1: Understand the UPDATE statement

   The statement updates the 'salary' column in the 'employees' table.

2. Step 2: Analyze the expression used

   It adds 500 to the current salary value for every row because no WHERE clause limits the rows.

3. Final Answer:

   Increases the salary of all employees by 500 -> Option B

4. Quick Check:

   salary = salary + 500 [OK]

Hint: No WHERE means update all rows with the expression [OK]

Common Mistakes:

• Thinking it sets salary to 500 instead of adding
• Confusing UPDATE with DELETE
• Assuming it adds 500 only to some rows without WHERE

2. Which of the following is the correct syntax to increase the price by 10% only for products in category 'Books'?

easy

A. UPDATE products SET price = price * 1.10 WHERE category = 'Books';

B. UPDATE products SET price = price + 10% WHERE category = 'Books';

C. UPDATE products SET price = price + 0.10 WHERE category = 'Books';

D. UPDATE products SET price = price * 10 WHERE category = 'Books';

Solution

1. Step 1: Identify correct expression for 10% increase

   Multiplying price by 1.10 increases it by 10%.

2. Step 2: Check WHERE clause for category filtering

   WHERE category = 'Books' limits update to only books.

3. Final Answer:

   UPDATE products SET price = price * 1.10 WHERE category = 'Books'; -> Option A

4. Quick Check:

   price * 1.10 with WHERE category = 'Books' [OK]

Hint: Use multiplication by 1.10 for 10% increase [OK]

Common Mistakes:

• Using + 10% which is invalid syntax
• Adding 0.10 instead of 10%
• Multiplying price by 10 instead of 1.10

3. Given the table accounts with columns id, balance, what will be the balances after running:
UPDATE accounts SET balance = balance - 100 WHERE balance >= 500;
Initial balances: 400, 500, 600

medium

A. [400, 600, 700]

B. [300, 400, 500]

C. [400, 500, 600]

D. [400, 400, 500]

Solution

1. Step 1: Identify which rows meet the WHERE condition

   Only balances 500 and 600 are >= 500, so only these rows update.

2. Step 2: Subtract 100 from qualifying balances

   500 - 100 = 400, 600 - 100 = 500; 400 remains unchanged.

3. Final Answer:

   [400, 400, 500] -> Option D

4. Quick Check:

   Only balances >= 500 reduced by 100 [OK]

Hint: Only rows matching WHERE get updated [OK]

Common Mistakes:

• Updating all rows ignoring WHERE
• Subtracting 100 from all balances
• Confusing >= with >

4. What is wrong with this SQL statement?
UPDATE orders SET total = total + discount WHERE discount;

medium

A. The SET expression cannot use other columns

B. The UPDATE statement is missing a table name

C. The WHERE clause is invalid because it lacks a comparison

D. The total column cannot be updated

Solution

1. Step 1: Analyze the WHERE clause

   WHERE discount is incomplete; it needs a condition like discount > 0.

2. Step 2: Check other parts of the statement

   Table name and SET expression are correct; updating with another column is valid.

3. Final Answer:

   The WHERE clause is invalid because it lacks a comparison -> Option C

4. Quick Check:

   WHERE must have a condition, not just a column name [OK]

Hint: WHERE needs a condition, not just a column name [OK]

Common Mistakes:

• Assuming column names alone are valid WHERE conditions
• Thinking SET cannot use other columns
• Ignoring missing comparison in WHERE

5. You want to increase the salary by 5% for employees whose current salary is below the average salary. Which SQL statement correctly does this?

hard

A. UPDATE employees SET salary = salary * 1.05 WHERE salary < (SELECT AVG(salary) FROM employees);

B. UPDATE employees SET salary = salary + 5 WHERE salary < AVG(salary);

C. UPDATE employees SET salary = salary * 1.05 WHERE salary > (SELECT AVG(salary) FROM employees);

D. UPDATE employees SET salary = salary * 0.95 WHERE salary < (SELECT AVG(salary) FROM employees);

Solution

1. Step 1: Use a subquery to get average salary

   The subquery (SELECT AVG(salary) FROM employees) returns the average salary.

2. Step 2: Apply 5% increase only to salaries below average

   WHERE salary < average filters employees below average; salary * 1.05 increases salary by 5%.

3. Final Answer:

   UPDATE employees SET salary = salary * 1.05 WHERE salary < (SELECT AVG(salary) FROM employees); -> Option A

4. Quick Check:

   Use subquery in WHERE and multiply salary by 1.05 [OK]

Hint: Use subquery in WHERE and multiply salary by 1.05 [OK]

Common Mistakes:

• Using AVG(salary) without subquery
• Adding 5 instead of 5%
• Updating salaries above average instead of below
• Multiplying by 0.95 which decreases salary