DELETE with WHERE condition in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to delete rows where the age is 30.

SQL

DELETE FROM users WHERE age [1] 30;

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

A>

B!=

C<

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '!=' deletes rows where age is not 30, which is the opposite.

Using '>' or '<' deletes rows with age greater or less than 30, not equal.

The = operator checks for equality. This deletes rows where age equals 30.

2fill in blank

medium

Complete the code to delete rows where the status is 'inactive'.

SQL

DELETE FROM accounts WHERE status [1] 'inactive';

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

A=

BLIKE

C!=

DIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using '!=' deletes rows where status is not 'inactive'.

Using 'IN' requires a list of values, not a single string.

The = operator matches the exact value 'inactive'.

3fill in blank

hard

Fix the error in the code to delete rows where score is less than 50.

SQL

DELETE FROM results WHERE score [1] 50;

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

A<=

B<

C>

D==

Attempts:

3 left

💡 Hint

Common Mistakes

Using '==' causes a syntax error in SQL.

Using '>' deletes rows with score greater than 50, not less.

The correct operator for 'less than' is <. The == operator is not valid in SQL.

4fill in blank

hard

Fill both blanks to delete rows where the city is 'Paris' and age is greater than 25.

SQL

DELETE FROM customers WHERE city [1] 'Paris' [2] age > 25;

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

A=

B!=

CAND

DOR

Attempts:

3 left

💡 Hint

Common Mistakes

Using '!=' deletes rows where city is not Paris.

Using 'OR' deletes rows where either condition is true, not both.

The = operator checks city equals 'Paris'. The AND combines both conditions so both must be true.

5fill in blank

hard

Fill all three blanks to delete rows where the product is 'Book' and quantity is less than 10.

SQL

DELETE FROM orders WHERE product [1] 'Book' [2] quantity [3] 10;

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

A=

B<

CAND

DOR

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'OR' deletes rows if any condition is true, not all.

Using '>' instead of '<' for quantity changes the condition.

Use = to check product equals 'Book'. Use AND to combine conditions. Use < to check quantity less than 10.

Practice

1. What does the DELETE statement with a WHERE condition do in SQL?

easy

A. Removes only the rows that match the condition specified in WHERE.

B. Removes all rows from the table regardless of the condition.

C. Updates the rows that match the condition.

D. Creates a new table with rows matching the condition.

Solution

1. Step 1: Understand DELETE statement

   The DELETE statement removes rows from a table.

2. Step 2: Effect of WHERE condition

   When a WHERE condition is present, only rows matching that condition are deleted.

3. Final Answer:

   Removes only the rows that match the condition specified in WHERE. -> Option A

4. Quick Check:

   DELETE with WHERE removes matching rows only [OK]

Hint: DELETE with WHERE deletes matching rows only, not all [OK]

Common Mistakes:

• Thinking DELETE always removes all rows
• Confusing DELETE with UPDATE
• Assuming WHERE creates a new table

2. Which of the following is the correct syntax to delete rows where the column age is greater than 30 from a table named employees?

easy

A. REMOVE FROM employees WHERE age > 30;

B. DELETE employees WHERE age > 30;

C. DELETE FROM employees WHERE age > 30;

D. DELETE * FROM employees WHERE age > 30;

Solution

1. Step 1: Identify correct DELETE syntax

   The correct syntax starts with DELETE FROM followed by the table name.

2. Step 2: Check WHERE clause usage

   The WHERE clause filters rows to delete; it must follow the table name.

3. Final Answer:

   DELETE FROM employees WHERE age > 30; -> Option C

4. Quick Check:

   DELETE FROM + table + WHERE condition [OK]

Hint: Use DELETE FROM table WHERE condition; to delete specific rows [OK]

Common Mistakes:

• Omitting FROM keyword
• Using REMOVE instead of DELETE
• Adding * after DELETE which is invalid

3. Given the table products with rows:
{id:1, name:'Pen', stock:10}, {id:2, name:'Pencil', stock:0}, {id:3, name:'Eraser', stock:5}
What will be the result of this query?

DELETE FROM products WHERE stock = 0;

medium

A. Deletes all products because stock = 0 is ignored.

B. Deletes the product with id 2 only.

C. Deletes no rows because stock = 0 is false for all.

D. Causes a syntax error.

Solution

1. Step 1: Identify rows matching condition

   Only the product with stock = 0 is the one with id 2 (Pencil).

2. Step 2: Effect of DELETE with WHERE

   DELETE removes only rows matching the condition, so only id 2 is deleted.

3. Final Answer:

   Deletes the product with id 2 only. -> Option B

4. Quick Check:

   DELETE WHERE stock=0 removes matching rows only [OK]

Hint: DELETE removes only rows matching WHERE condition [OK]

Common Mistakes:

• Assuming all rows are deleted
• Thinking condition is ignored
• Expecting syntax error for valid query

4. What is wrong with this SQL statement?

DELETE FROM orders WHERE;

medium

A. It updates rows instead of deleting.

B. It deletes all rows because WHERE is empty.

C. It creates a new table instead of deleting.

D. Missing condition after WHERE causes syntax error.

Solution

1. Step 1: Check syntax after WHERE

   WHERE clause must have a condition; empty WHERE is invalid syntax.

2. Step 2: Result of missing condition

   SQL parser throws syntax error because condition is missing.

3. Final Answer:

   Missing condition after WHERE causes syntax error. -> Option D

4. Quick Check:

   WHERE must have a condition, else syntax error [OK]

Hint: WHERE must always have a condition, never empty [OK]

Common Mistakes:

• Leaving WHERE empty thinking it deletes all
• Confusing DELETE with UPDATE
• Expecting query to run without error

5. You have a table users with columns id, name, and status. You want to delete all users whose status is either 'inactive' or 'banned'. Which query correctly deletes these users?

hard

A. DELETE FROM users WHERE status IN ('inactive', 'banned');

B. DELETE FROM users WHERE status = 'inactive' OR 'banned';

C. DELETE FROM users WHERE status = 'inactive' AND status = 'banned';

D. DELETE FROM users WHERE status = 'inactive', 'banned';

Solution

1. Step 1: Understand condition for multiple values

   To delete rows where status is 'inactive' or 'banned', use OR or IN clause.

2. Step 2: Compare options

   DELETE FROM users WHERE status = 'inactive' OR 'banned'; -- OR 'banned' (literal string) evaluates to true, deleting all rows.
   DELETE FROM users WHERE status IN ('inactive', 'banned'); -- correctly matches either value.
   DELETE FROM users WHERE status = 'inactive' AND status = 'banned'; -- AND condition is impossible, deletes none.
   DELETE FROM users WHERE status = 'inactive', 'banned'; -- invalid comma syntax.

3. Final Answer:

   DELETE FROM users WHERE status IN ('inactive', 'banned'); -> Option A

4. Quick Check:

   Use WHERE status IN (...) for multiple values [OK]

Hint: Use WHERE column IN (values) to delete multiple matches [OK]

Common Mistakes:

• Using AND instead of OR for multiple conditions
• Incorrect syntax with commas in WHERE
• Not using IN for multiple values