UPDATE with expressions in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to increase the salary by 1000 for all employees.

SQL

UPDATE employees SET salary = salary [1] 1000;

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.

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

The plus sign (+) adds 1000 to the current salary value.

2fill in blank

medium

Complete the code to set the bonus to 10% of the current salary.

SQL

UPDATE employees SET bonus = salary [1] 0.10;

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

A+

B*

C-

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Using '+' will add 0.10 instead of calculating 10%.

Using '-' or '/' will not calculate the percentage correctly.

The asterisk (*) multiplies the salary by 0.10 to calculate 10% bonus.

3fill in blank

hard

Fix the error in the code to decrease the stock quantity by 5.

SQL

UPDATE inventory SET quantity = quantity [1] 5 WHERE product_id = 101;

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

A-

B+

C*

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Using '+' will increase the quantity instead of decreasing.

Using '*' or '/' will multiply or divide the quantity, causing errors.

The minus sign (-) subtracts 5 from the current quantity.

4fill in blank

hard

Fill both blanks to double the price and set the discount to 20%.

SQL

UPDATE products SET price = price [1] 2, discount = price [2] 0.20;

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

A*

B+

C-

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Using '+' will add instead of multiply.

Using '-' or '/' will subtract or divide values incorrectly.

Price is doubled by multiplying by 2. Discount is set to 20% of price by multiplying by 0.20.

5fill in blank

hard

Fill the blanks to update the total as quantity times price minus discount.

SQL

UPDATE sales SET total = (quantity [1] price) [2] discount WHERE sale_id = 500;

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

A*

B+

C-

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Using '+' instead of '-' will add discount incorrectly.

Using '*' or '/' for subtraction will cause errors.

Total is calculated by multiplying quantity and price, then subtracting discount.

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