UPDATE without WHERE (danger) in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to update the salary of all employees to 50000.

SQL

UPDATE employees SET salary = [1];

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

A50000

BNULL

Csalary + 50000

D'50000'

Attempts:

3 left

💡 Hint

Common Mistakes

Putting the number 50000 in quotes, which makes it a string.

Using an expression like salary + 50000 which increases salary instead of setting it.

The correct value to set the salary to 50000 is the number 50000 without quotes.

2fill in blank

medium

Complete the code to update the status of all orders to 'shipped'.

SQL

UPDATE orders SET status = [1];

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

Ashipped

BSHIPPED

C'shipped'

D"shipped"

Attempts:

3 left

💡 Hint

Common Mistakes

Not using quotes around the text value.

Using double quotes instead of single quotes.

Text values in SQL must be enclosed in single quotes, so 'shipped' is correct.

3fill in blank

hard

Fix the error in the code to update all products' price to 10.99.

SQL

UPDATE products SET price = [1];

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

A'10.99'

B'10.99';

C10.99;

D10.99

Attempts:

3 left

💡 Hint

Common Mistakes

Including the semicolon inside the value.

Putting the number in quotes making it a string.

The semicolon should not be inside the value. The value should be a number without quotes and the semicolon ends the statement outside.

4fill in blank

hard

Fill both blanks to update the quantity of all items to 100 and the status to 'available'.

SQL

UPDATE inventory SET quantity = [1], status = [2];

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

A100

B'available'

Cavailable

D100.0

Attempts:

3 left

💡 Hint

Common Mistakes

Putting numbers in quotes.

Not putting text values in quotes.

Quantity is a number so use 100 without quotes. Status is text so use 'available' with single quotes.

5fill in blank

hard

Fill all three blanks to update the employees table: set salary to 60000, department to 'HR', and active to true.

SQL

UPDATE employees SET salary = [1], department = [2], active = [3];

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

A60000

B'HR'

CTRUE

D'true'

Attempts:

3 left

💡 Hint

Common Mistakes

Putting boolean values in quotes.

Not quoting text values.

Putting numbers in quotes.

Salary is a number so 60000 without quotes. Department is text so 'HR' with quotes. Active is a boolean so TRUE without quotes.

Practice

1.

What happens if you run an UPDATE statement without a WHERE clause?

easy

A. All rows in the table will be updated.

B. Only the first row will be updated.

C. No rows will be updated.

D. The query will cause a syntax error.

Solution

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

   The WHERE clause limits which rows are updated. Without it, no filter is applied.

2. Step 2: Effect of missing WHERE clause

   Without WHERE, the update affects every row in the table.

3. Final Answer:

   All rows in the table will be updated. -> Option A

4. Quick Check:

   UPDATE without WHERE updates all rows [OK]

Hint: No WHERE means update every row in the table [OK]

Common Mistakes:

• Thinking only one row updates without WHERE
• Assuming syntax error occurs without WHERE
• Believing no rows are updated without WHERE

2.

Which of the following is the correct syntax to update the salary column to 5000 for all employees in the employees table?

UPDATE employees SET salary = 5000;

easy

A. UPDATE employees SET salary = 5000 WHERE employee_id = 1;

B. UPDATE SET salary = 5000 FROM employees;

C. UPDATE employees salary = 5000;

D. UPDATE employees SET salary = 5000;

Solution

1. Step 1: Identify correct UPDATE syntax

   The correct syntax is UPDATE table_name SET column = value; to update all rows.

2. Step 2: Check each option

   UPDATE employees SET salary = 5000; matches correct syntax without WHERE, updating all rows. Others have syntax errors or extra clauses.

3. Final Answer:

   UPDATE employees SET salary = 5000; -> Option D

4. Quick Check:

   UPDATE table SET column = value; updates all rows [OK]

Hint: UPDATE table SET column=value updates all rows without WHERE [OK]

Common Mistakes:

• Omitting SET keyword
• Using WHERE when not needed for all rows
• Incorrect order of clauses

3.

Given the table products with 3 rows:

id | name    | price
---+---------+-------
1  | Pen     | 10
2  | Pencil  | 5
3  | Eraser  | 3

What will be the result after running this query?

UPDATE products SET price = price + 2;

medium

A. Only the first product's price increases by 2.

B. All products' prices increase by 2.

C. No prices change because WHERE is missing.

D. Syntax error due to missing WHERE.

Solution

1. Step 1: Understand UPDATE without WHERE effect

   Without a WHERE clause, the update applies to all rows in the table.

2. Step 2: Apply the price update

   Each product's price increases by 2, so prices become 12, 7, and 5 respectively.

3. Final Answer:

   All products' prices increase by 2. -> Option B

4. Quick Check:

   UPDATE without WHERE updates all rows [OK]

Hint: No WHERE means all rows updated, so all prices change [OK]

Common Mistakes:

• Thinking only one row updates
• Assuming missing WHERE causes no update
• Expecting syntax error without WHERE

4.

Consider this query intended to update only employees in department 10:

UPDATE employees SET salary = salary + 1000;

Why might this be a problem?

medium

A. It will update salaries for all employees, not just department 10.

B. It will cause a syntax error because WHERE is missing.

C. It will update no rows because no condition is specified.

D. It will only update the first employee's salary.

Solution

1. Step 1: Identify missing WHERE clause

   The query lacks a WHERE clause to filter employees by department.

2. Step 2: Effect of missing WHERE

   Without filtering, the update applies to all employees, not just those in department 10.

3. Final Answer:

   It will update salaries for all employees, not just department 10. -> Option A

4. Quick Check:

   Missing WHERE updates all rows, causing unintended changes [OK]

Hint: Always use WHERE to limit UPDATE scope [OK]

Common Mistakes:

• Assuming syntax error without WHERE
• Thinking no rows update without WHERE
• Believing only one row updates by default

5.

You want to increase the stock by 10 only for products with stock less than 5. Which query correctly avoids the danger of updating all rows?

hard

A. UPDATE products SET stock = stock + 10;

B. UPDATE products stock = stock + 10 WHERE stock < 5;

C. UPDATE products SET stock = stock + 10 WHERE stock < 5;

D. UPDATE products SET stock = stock + 10 WHERE stock > 5;

Solution

1. Step 1: Identify the condition to limit update

   We want to update only rows where stock < 5, so a WHERE clause is needed.

2. Step 2: Check query correctness

   UPDATE products SET stock = stock + 10 WHERE stock < 5; uses correct syntax and condition to update only matching rows. UPDATE products SET stock = stock + 10; (no WHERE) updates all rows. UPDATE products stock = stock + 10 WHERE stock < 5; (missing SET) has syntax error. UPDATE products SET stock = stock + 10 WHERE stock > 5; updates wrong rows (opposite condition).

3. Final Answer:

   UPDATE products SET stock = stock + 10 WHERE stock < 5; -> Option C

4. Quick Check:

   Use WHERE to limit UPDATE to desired rows [OK]

Hint: Use WHERE to update only targeted rows [OK]

Common Mistakes:

• Omitting WHERE and updating all rows
• Using wrong comparison operator
• Syntax errors by missing SET keyword