Questions: DELETE Statements: Removing Rows with Conditions

Question 1

A developer runs: DELETE FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE status = 'inactive'). The orders table has a foreign key referencing the order_items table with ON DELETE CASCADE. What happens?

• A. The statement fails because you cannot use a subquery in a DELETE WHERE clause
• B. Only the orders rows are deleted; order_items rows must be deleted separately
• C. Both the matching orders rows and all their associated order_items rows are deleted automatically
• D. The foreign key constraint blocks the delete until order_items rows are removed first

Answer: (C)

ON DELETE CASCADE means that when a parent row is deleted, all child rows referencing it are automatically deleted too. So deleting orders rows cascades to delete all matching order_items rows — the developer may not realize they are deleting far more data than intended. Option D describes the behavior of ON DELETE RESTRICT (the default), which blocks the parent delete if children exist. Understanding cascade behavior is critical before executing any DELETE in a system with foreign key relationships.

Question 2

What does a bare DELETE FROM employees statement (no WHERE clause) do?

• A. It prompts for confirmation before executing since no filter is provided
• B. It deletes only the most recently inserted rows as a safety measure
• C. It deletes every row in the table, leaving the table structure intact
• D. It raises a syntax error because WHERE is required by the SQL standard

Answer: (C)

DELETE FROM employees with no WHERE clause removes every single row from the table — it is equivalent to 'delete all.' The table structure (columns, constraints, indexes) remains. No confirmation prompt is issued and no syntax error occurs; it is perfectly legal SQL. This is why the best practice is to always write and test the WHERE clause as a SELECT first — running SELECT * FROM employees (no WHERE) shows you that you would be selecting all rows, which is your warning to add a condition.

Question 3

Running SELECT first with the same WHERE clause you plan to use in DELETE is a best practice because it lets you preview exactly which rows will be removed before committing to the deletion.

• T. True
• F. False

Answer: True

Since DELETE is irreversible (absent a transaction rollback), previewing with SELECT catches mistakes before data is lost. The SELECT and DELETE WHERE clause use identical syntax, so if SELECT returns the rows you expect, DELETE will remove exactly those rows. This habit is especially important when the WHERE clause involves subqueries, joins, or complex conditions where the result set may not be obvious.

Question 4

If a DELETE statement's WHERE clause matches zero rows, the database returns an error indicating no rows were affected.

• T. True
• F. False

Answer: False

DELETE simply reports zero rows affected — it does not raise an error when no rows match. This is consistent with how UPDATE and other DML behaves: the statement succeeds, it just changes nothing. This can be a subtle bug: a typo or wrong value in the WHERE clause silently does nothing, leaving you to believe the deletion succeeded when it did not. Always verify by checking row counts or re-running SELECT afterward.

Question 5

Why is batched deletion (deleting in chunks) preferred over a single large DELETE when removing millions of rows from a production table?

Model answer: A single DELETE of millions of rows holds locks on those rows for the entire duration of the transaction, blocking reads and writes from other queries for potentially minutes. It also fills the transaction log rapidly, which can exhaust disk space. Batching — deleting 1,000–10,000 rows at a time and committing between batches — keeps each transaction short, releases locks frequently, and lets other operations interleave. The cost is more total elapsed time, but the system remains responsive.

Large transactions are a common source of production incidents. Beyond lock contention, the redo/undo log for a multi-million-row delete can grow very large, making crash recovery slower. Batch deletion is a standard operational pattern for large-scale data cleanup in live systems.