Why UPDATE needs caution in SQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to update the salary of employees in the 'employees' table.

SQL

UPDATE employees SET salary = salary [1] 1000 WHERE department = 'Sales';

Drag options to blanks, or click blank then click option'

A+

B/

C*

D-

Attempts:

3 left

💡 Hint

Common Mistakes

Using '-' will decrease the salary instead of increasing it.

Using '*' or '/' will multiply or divide the salary, which is not intended here.

The '+' operator increases the salary by 1000 for employees in the Sales department.

2fill in blank

medium

Complete the code to update the status of orders to 'shipped' only for orders with status 'pending'.

SQL

UPDATE orders SET status = 'shipped' WHERE status [1] 'pending';

Drag options to blanks, or click blank then click option'

A>

B=

C!=

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '!=' will update orders that are not pending, which is incorrect.

Using '>' or '<' does not make sense for string comparison here.

The '=' operator selects orders where the status is exactly 'pending' to update them to 'shipped'.

3fill in blank

hard

Fix the error in the UPDATE statement that tries to set all product prices to 0 but accidentally omits the WHERE clause.

SQL

UPDATE products SET price = [1];

Drag options to blanks, or click blank then click option'

ANULL

B''

C0

D-1

Attempts:

3 left

💡 Hint

Common Mistakes

Using NULL or empty string may cause errors or unexpected results.

Forgetting the WHERE clause can update all rows unintentionally.

Setting price to 0 is valid, but omitting WHERE updates all rows. Use caution to avoid unintended mass updates.

4fill in blank

hard

Fill both blanks to update the 'quantity' by subtracting 1 only for items where quantity is greater than 0.

SQL

UPDATE inventory SET quantity = quantity [1] 1 WHERE quantity [2] 0;

Drag options to blanks, or click blank then click option'

A-

B+

C>

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Adding 1 instead of subtracting changes the quantity incorrectly.

Using '<=' allows zero or negative quantities, which may be invalid.

Subtract 1 from quantity only if quantity is greater than 0 to avoid negative values.

5fill in blank

hard

Fill all three blanks to update the 'status' to 'inactive' and set 'last_updated' to current date only for users with 'active' status and last login before 2023-01-01.

SQL

UPDATE users SET status = [1], last_updated = [2] WHERE status = [3] AND last_login < '2023-01-01';

Drag options to blanks, or click blank then click option'

A'inactive'

BCURRENT_DATE

C'active'

D'2023-01-01'

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong status values or missing quotes causes errors.

Not filtering by status or last_login can update unintended users.

This updates only active users who last logged in before 2023-01-01, setting their status to inactive and recording the update date.

Practice

1. Why should you be careful when using the UPDATE statement without a WHERE clause?

easy

A. Because it will only update the first row.

B. Because it will delete the table.

C. Because it will create a new table.

D. Because it will update all rows in the table, possibly causing unwanted changes.

Solution

1. Step 1: Understand the role of WHERE in UPDATE

   The WHERE clause limits which rows get updated. Without it, all rows are affected.

2. Step 2: Consider the effect of missing WHERE

   Without WHERE, every row in the table changes, which can cause data loss or errors.

3. Final Answer:

   Because it will update all rows in the table, possibly causing unwanted changes. -> Option D

4. Quick Check:

   Missing WHERE updates all rows [OK]

Hint: Always use WHERE to limit rows in UPDATE [OK]

Common Mistakes:

• Assuming UPDATE only changes one row by default
• Forgetting WHERE clause causes full table update
• Thinking UPDATE deletes data

2. Which of the following is the correct syntax to update the salary to 5000 for employees in department 10?

easy

A. UPDATE employees SET salary = 5000 department = 10;

B. UPDATE employees SET salary = 5000 WHERE department = 10;

C. UPDATE employees SET salary 5000 WHERE department = 10;

D. UPDATE employees salary = 5000 WHERE department = 10;

Solution

1. Step 1: Recall correct UPDATE syntax

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

2. Step 2: Check each option

   UPDATE employees SET salary = 5000 WHERE department = 10; matches the correct syntax exactly. Others miss keywords or punctuation.

3. Final Answer:

   UPDATE employees SET salary = 5000 WHERE department = 10; -> Option B

4. Quick Check:

   Correct UPDATE syntax uses SET and WHERE [OK]

Hint: UPDATE needs SET and WHERE for targeted changes [OK]

Common Mistakes:

• Omitting SET keyword
• Missing equal sign (=)
• Leaving out WHERE clause

3. Given the table products with rows:
{id:1, price:100}, {id:2, price:200}, {id:3, price:300}
What will be the result after running:
UPDATE products SET price = price + 50 WHERE id > 1;
Then selecting all rows?

medium

A. [{id:1, price:150}, {id:2, price:250}, {id:3, price:350}]

B. [{id:1, price:150}, {id:2, price:200}, {id:3, price:300}]

C. [{id:1, price:100}, {id:2, price:250}, {id:3, price:350}]

D. [{id:1, price:100}, {id:2, price:200}, {id:3, price:300}]

Solution

1. Step 1: Identify rows affected by WHERE clause

   The condition id > 1 affects rows with id 2 and 3 only.

2. Step 2: Calculate new prices for affected rows

   Price for id 2: 200 + 50 = 250; Price for id 3: 300 + 50 = 350; id 1 remains 100.

3. Final Answer:

   [{id:1, price:100}, {id:2, price:250}, {id:3, price:350}] -> Option C

4. Quick Check:

   Only rows with id > 1 updated [OK]

Hint: Check WHERE condition carefully to know which rows update [OK]

Common Mistakes:

• Updating all rows ignoring WHERE
• Updating wrong rows by misreading condition
• Changing values incorrectly

4. What is wrong with this SQL statement?
UPDATE employees SET salary = salary + 1000 WHERE;

medium

A. The WHERE clause is incomplete and causes a syntax error.

B. The SET keyword is missing.

C. The table name is incorrect.

D. The salary column cannot be updated.

Solution

1. Step 1: Check the WHERE clause syntax

   The WHERE clause must have a condition after it. Here it is empty, causing error.

2. Step 2: Verify other parts of the statement

   SET keyword and table name are correct; salary can be updated.

3. Final Answer:

   The WHERE clause is incomplete and causes a syntax error. -> Option A

4. Quick Check:

   Incomplete WHERE causes syntax error [OK]

Hint: Always provide a condition after WHERE [OK]

Common Mistakes:

• Leaving WHERE empty
• Forgetting condition after WHERE
• Assuming WHERE is optional but incomplete

5. You want to increase the price by 10% only for products priced below 100. Which UPDATE statement is safest to avoid changing other rows?

hard

A. UPDATE products SET price = price * 1.10 WHERE price < 100;

B. UPDATE products SET price = price * 1.10;

C. UPDATE products SET price = price + 10 WHERE price < 100;

D. UPDATE products SET price = price * 1.10 WHERE price > 100;

Solution

1. Step 1: Identify the condition to limit rows

   Only products with price less than 100 should be updated, so WHERE price < 100 is needed.

2. Step 2: Choose correct price increase method

   Increasing by 10% means multiplying price by 1.10, not adding 10.

3. Final Answer:

   UPDATE products SET price = price * 1.10 WHERE price < 100; -> Option A

4. Quick Check:

   Use WHERE and correct math for safe updates [OK]

Hint: Use WHERE to limit rows and correct math for updates [OK]

Common Mistakes:

• Forgetting WHERE and updating all rows
• Adding fixed amount instead of percentage
• Using wrong condition in WHERE