In Oracle and MySQL, which command removes all records from a table while…
2026
In Oracle and MySQL, which command removes all records from a table while keeping the table structure intact, and cannot be undone with ROLLBACK because the command itself causes an implicit commit?
Answer: C. TRUNCATE — Concept — SQL’s removal verbs differ on two axes: the sub-language they belong to (DML or DDL), and the object they act on (the rows inside a table, or the…
- A.
DELETE
- B.
DROP
- C.
TRUNCATE
- D.
REMOVE
Attempted by 1126 students.
Show answer & explanation
Correct answer: C
Concept — SQL’s removal verbs differ on two axes: the sub-language they belong to (DML or DDL), and the object they act on (the rows inside a table, or the table object itself). In the Oracle and MySQL model this question names, a DML statement’s changes stay inside the enclosing transaction, so ROLLBACK can undo them. Certain DDL statements, by contrast, are listed by those engines as causing an implicit COMMIT: once such a statement has run, the transaction is already closed and ROLLBACK has nothing left to undo.
Application — The stem pins down three requirements; take them in order:
Every record must go, so the statement has to act on the table’s entire row set rather than on a filtered subset.
The structure must survive — columns, data types, constraints, indexes and permissions all remain — so the statement has to act on the rows, never on the table object.
Its effect must survive ROLLBACK, so the statement must be one the engine documents as causing an implicit commit, rather than logged DML whose removed rows sit in the still-open transaction.
One statement satisfies all three. TRUNCATE TABLE deallocates the data pages that hold the table’s rows in a single operation instead of logging each row, leaves the empty table object in the schema, and — in Oracle and MySQL/InnoDB, the defaults exam syllabi assume — commits implicitly.
Cross-check — the three real SQL statements against the same three requirements:
Statement | Sub-language | What it removes | Undone by ROLLBACK? |
|---|---|---|---|
| DML | rows, one at a time, each one logged | Yes — its rows stay in the open transaction until COMMIT |
| DDL | all rows, by deallocating data pages; table object stays | No — documented as an implicit-commit statement |
| DDL | the whole table object: rows plus its definition | No, implicit commit |
Dialect note — the stem names Oracle and MySQL for a reason. Both engines document TRUNCATE TABLE in their published list of statements that cause an implicit commit, while DELETE does not appear on that list — that documented statement-level difference, not the session’s autocommit setting, is the discriminator here. Two things are worth carrying forward. PostgreSQL and SQL Server also classify TRUNCATE as DDL, yet they permit it inside an explicit transaction, where ROLLBACK does restore the rows. And in any engine, a session left in autocommit commits every statement the moment it finishes, so nothing is recoverable afterwards — which is exactly why the comparison is drawn at the statement level rather than at the session level.
Answer — TRUNCATE.