Core CS · DBMS

Four families, and only one you can undo

DDL, DML, DCL and TCL are not four lists to memorise. They are four kinds of change, and which family a statement belongs to decides whether you can take it back.

Run a script and watch a rollback fail →

Two statements can empty the same table. Undo the DELETE before you commit and every row comes back. Undo the TRUNCATE and, in Oracle or MySQL, nothing happens at all — because it belongs to a different family. This lesson is about that difference and what it costs you.

01 The idea

Four kinds of change, not four lists to memorise

SQL is one language on paper. In practice a statement does one of four different kinds of work, and every textbook splits SQL into sub-languages by which one. CREATE TABLE invents a place to put rows. INSERT puts rows into it. GRANT decides who else may. COMMIT decides whether any of it survives. Four different targets: the structure, the data, the permissions, and the fate of the change.

The split earns its keep because of one consequence. The family decides whether you are allowed to change your mind. Run DELETE FROM student; inside an open transaction and you can still type ROLLBACK; and get every row back, because nothing was permanent yet. Run TRUNCATE TABLE student; and there is nothing left to roll back, because in Oracle and MySQL that statement committed your transaction before it did the work. Both statements emptied the same table. Only one of them is reversible, and the reason is the family it belongs to.

Where the DBMS writes the change down differs too, and it is the cleanest way to keep the families apart. DDL rewrites the data dictionary, the catalog tables from the first lesson that record which columns exist. DML rewrites the rows in the table’s own data pages. DCL rewrites the privilege catalog. TCL writes no new data at all; it decides what happens to writes that have already been made.

Ask what the statement changes: structure, rows, permissions, or the fate of the change. That single question gives you the family, and the family tells you whether ROLLBACK can undo it.

Sub-languageA grouping of SQL statements by what they change. SQL itself has no such keyword and no such command — the split into DDL, DML, DCL and TCL is a teaching and examining convention, which is why sources disagree at the edges.

Data dictionaryThe DBMS’s own tables recording which tables, columns, types, constraints and privileges exist. Every DDL statement is a write to it, and you read it back with an ordinary SELECT.

Auto-commitWhen a statement ends the current transaction and makes everything before it permanent, without you typing COMMIT. In Oracle and MySQL a DDL statement does this even inside an open transaction; a DML statement never does. MySQL also sits in autocommit mode until you turn it off, which is a separate trap.

02 Worked example

The life of one table, in four families

One table and three students, carried through the whole lesson. The rows are the first three students from lesson one: Asha 78, Ravi 65, Meera 35. Follow the table from the moment it does not exist to the moment a change you thought was provisional turns out to be permanent.

sid	name	marks
`S1`	Asha	78
`S2`	Ravi	65
`S3`	Meera	35

Five steps built and changed that table, covering all four families, with DDL appearing twice. Section 04 steps through this same script with four more statements woven in. The order is what matters, and the highlighted step is where students lose marks.

CREATE TABLEDDL · three columns written into the data dictionary, zero rows

→

GRANT SELECTDCL · one entry in the privilege catalog: reader may now read, and reading is all reader got

→

INSERT × 3DML · the three rows above, provisional — ROLLBACK would still empty the table

→

UPDATE, SAVEPOINTDML puts Meera on 45, then TCL marks that state — still provisional

→

ALTER TABLEDDL again · adds a column, and in Oracle and MySQL commits everything still pending on the way in

The first four nodes behave the way their names suggest. The fifth is the one that catches people. ALTER TABLE student ADD email VARCHAR(40); looks like a change to the structure and nothing else. In Oracle and MySQL it fires an implicit COMMIT before it starts and another after it finishes. Meera’s 45, provisional a moment ago, is now permanent. A ROLLBACK typed on the next line reports no error and does nothing.

That behaviour is the reason to learn the four as families rather than as four lists of keywords. You are not memorising that ALTER is DDL for its own sake. You are memorising it so that when you meet it in the middle of a script you know your transaction has already ended, whether you wanted it to or not.

03 Mechanics

Which family, what it writes, and whether you can undo it

Four families, four different places the change is written down, and one column that decides whether you may change your mind. Read the last column first; that is the one interviews are testing.

Family	Statements	What it changes	Where the DBMS writes it	Undone by a later ROLLBACK?
DDL Data Definition	`CREATE, ALTER, DROP, TRUNCATE, RENAME`	the structure: tables, columns, constraints, indexes	the data dictionary	`No in Oracle and MySQL — it commits first`
DML Data Manipulation	`INSERT, UPDATE, DELETE`	the rows inside a table	the table’s data pages, plus one undo record per row	`Yes, until you COMMIT`
DQL or DML, see below	`SELECT`	nothing at all	nothing	`nothing to undo`
DCL Data Control	`GRANT, REVOKE`	who may run which statement on which object	the privilege catalog	`No in Oracle, which commits; yes in PostgreSQL`
TCL Transaction Control	`COMMIT, ROLLBACK, SAVEPOINT`	which DML changes become permanent	the transaction’s own state	`it is the undo`

DCL deserves one more sentence, because it is the family students describe most vaguely. A GRANT names an action, an object, and a grantee — and optionally a column list, so GRANT UPDATE (marks) ON student TO teacher; hands over one column and not the rest. It can also carry WITH GRANT OPTION, which lets the grantee pass the privilege on to somebody else. REVOKE takes a privilege back, and what happens to anyone the grantee passed it on to is where products differ: Oracle cascades the withdrawal to those onward grants for you, PostgreSQL refuses the REVOKE unless you write CASCADE, and MySQL does not cascade at all. Neither statement reads or writes a single row of data.

Now the trip-up. Two statements empty the same table, and every difference below follows from one of them being DML and the other being DDL.

	DELETE FROM student;	TRUNCATE TABLE student;
Family	`DML`	`DDL`
How the rows go	one logged row deletion at a time	the table’s data pages are deallocated whole
WHERE clause	`allowed`	`not allowed — every row or none`
Row-level DELETE triggers	`fire, once per row`	`do not fire`
Cost on a million rows	`grows with the row count`	`roughly constant`
What ROLLBACK has to work with	`one undo record per row`	`nothing per row was written`
ROLLBACK on the next line	`restores every row`	`Oracle, MySQL: already committed. PostgreSQL, SQL Server: works`
Identity or AUTO_INCREMENT counter	`keeps its current value`	`reset in MySQL and SQL Server`
Privilege you need	`DELETE on the table`	`a table-level right, not DELETE`

TRUNCATE being DDL and not DML is the single most common trip-up on this topic. The reason is the row labelled what ROLLBACK has to work with. DELETE writes one undo record per row, and that is precisely what ROLLBACK reads to put the rows back. TRUNCATE writes no per-row record, because it never touches rows. It throws away the pages the rows were sitting in and records a structural change instead. In Oracle and MySQL, where the statement also commits, that leaves nothing to reverse. PostgreSQL and SQL Server keep it inside your transaction instead — PostgreSQL writes to a fresh file and holds the old one until you commit, SQL Server logs the page deallocations — so a ROLLBACK there puts the table back whole rather than row by row.

One more split, and this one sits inside DML rather than between families. It gets asked as “is SQL procedural or non-procedural?”, and the answer is a sentence rather than a word.

	Non-procedural DML	Procedural DML
What you write	what you want	what you want, and the order in which to get it
Unit of work	`a set of rows at once`	`one record at a time`
Our example	`UPDATE student SET marks = 45 WHERE sid = 'S3';`	open a cursor, FETCH each row, test it, update it, loop
Who picks the access path	`the query optimiser`	`you do`
Where you meet it	plain SQL, every day	PL/SQL and T-SQL blocks, cursors, SQL embedded in C, and the older CODASYL network-model DMLs
Cost of getting it wrong	`a slow plan the optimiser may later fix`	`a loop nobody can optimise for you`

SQL is non-procedural DML. You describe the rows you want and the optimiser decides how to reach them — index or scan, which order, which join method. Procedural DML is what you fall back to when a set-based statement cannot express the logic, and it is slower for the same reason it is more flexible: you took the access path away from the optimiser and did it by hand.

Which leaves the question sources genuinely disagree on. Is SELECT part of DML, or a fifth family of its own called DQL?

The ISO SQL standard has no DQL. It groups SELECT, INSERT, UPDATE and DELETE together as SQL-data statements, kept separate from SQL-schema statements (the DDL) and SQL-transaction statements (the TCL). Many Indian university syllabi and placement question banks do list DQL as a fifth family, with SELECT as its only member. Both groupings follow from a definition of DML: one reads it as “statements that operate on the data”, which includes retrieval, and the other reads it as “statements that modify the data”, which does not.

Give the definition, then the answer. “If DML means statements that operate on data, SELECT is DML, and that is how the SQL standard groups it. If DML means statements that modify data, SELECT is retrieval only, and many syllabi call that DQL.” That answer is correct under either marking scheme, and it shows you know why the disagreement exists.

05 Cheat sheet

Which statements survive a rollback

One row per statement you will be asked about, and the answer that gets marked. The vendor named in the last column is the exception worth saying out loud, because leaving it out is what invites the correction.

Statement	Family	What it touches	Undone by a later ROLLBACK?
`CREATE, ALTER, DROP, RENAME`	DDL	the data dictionary	`No in Oracle and MySQL — it committed first`
`TRUNCATE TABLE`	DDL	data pages, deallocated whole	`No in Oracle and MySQL; yes in PostgreSQL and SQL Server`
`INSERT, UPDATE, DELETE`	DML	rows, plus one undo record each	`Yes, until you COMMIT`
`SELECT`	DML by the standard, DQL in many syllabi	nothing	`nothing to undo`
`GRANT, REVOKE`	DCL	the privilege catalog	`Oracle commits; PostgreSQL rolls it back`
`COMMIT`	TCL	ends the transaction	`No — it is the point of no return`
`SAVEPOINT sp1`	TCL	marks a point inside the transaction	`changes no data`
`ROLLBACK TO SAVEPOINT sp1`	TCL	undoes back to the mark; the transaction stays open	`it is the undo`
`ROLLBACK`	TCL	undoes everything since the last commit	`it is the undo`

The one-question testWhat does this statement change — structure, rows, permissions, or the fate of the change? Structure is DDL, rows are DML, permissions are DCL, fate is TCL. Apply it and you never have to recall a list, which matters when you are handed a statement the list never mentioned.

Auto-commit is the whole examNearly every question here is really asking which statements end your transaction. DDL does, in Oracle and MySQL. DML does not. That is why DELETE is reversible and TRUNCATE is not, and it is why one DDL statement in the middle of a script quietly makes everything above it permanent.

The rule belongs to Oracle, not to SQLPostgreSQL, SQL Server and SQLite wrap DDL in the transaction like anything else, so BEGIN; CREATE TABLE ...; ROLLBACK; leaves no table behind. Say the auto-commit rule, then name this as the exception. “DDL can never be rolled back” is the version that gets corrected.

06 Where & why

Where the rule holds, and where it does not

“DDL auto-commits” is a statement about specific products, not about SQL. It is true in the two databases most syllabi were written around and false in three others you have probably installed. Naming which is which is the difference between reciting a rule and understanding it.

Oracle

Where the textbook rule comes from

Every DDL statement fires an implicit COMMIT before it runs and another after, and so does GRANT. This is the behaviour every “DDL cannot be rolled back” answer describes. Oracle softens the worst case with a recycle bin, so FLASHBACK TABLE ... TO BEFORE DROP can bring back a dropped table — but that is a recovery feature, not a rollback, and it will not undo a committed UPDATE.

MySQL · InnoDB

Auto-commit is on before you start

The default session has autocommit = 1, so every single INSERT is its own committed transaction and ROLLBACK does nothing until you run START TRANSACTION or turn autocommit off. DDL causes an implicit commit on top of that. TRUNCATE also resets the AUTO_INCREMENT counter, which DELETE does not.

PostgreSQL

Transactional DDL, and it changes how you deploy

BEGIN; ALTER TABLE student ADD email ...; ROLLBACK; and the column is gone. TRUNCATE, CREATE TABLE and GRANT are all transactional here. This is exactly why migration tools like it: a migration that fails on step seven leaves the schema untouched instead of half-applied.

SQLite

DDL rolls back, and DCL does not exist

Everything, schema changes included, runs inside the same transaction, so a ROLLBACK undoes a CREATE TABLE. There is no GRANT at all: SQLite has no users and no privilege catalog, so the DCL family is absent altogether and the operating system’s file permissions do that job instead.

Name the vendor. “DDL auto-commits, so you cannot roll it back” is right in Oracle and MySQL and wrong in PostgreSQL, SQL Server and SQLite. Give the rule, then give the exception in the same breath, and the follow-up question never arrives.

07 Interview questions

What they actually ask

This topic is asked early, because it is quick to mark and it separates people who have run SQL from people who have read about it. Expect a list question first, then one that turns on auto-commit. Answer each of these out loud in about twenty seconds.

What are the sub-languages of SQL?

Four, grouped by what the statement changes. DDL changes the structure and writes the data dictionary: CREATE, ALTER, DROP, TRUNCATE, RENAME. DML changes the rows: INSERT, UPDATE, DELETE. DCL changes who may do what: GRANT, REVOKE. TCL decides whether the change becomes permanent: COMMIT, ROLLBACK, SAVEPOINT. SQL itself has no keyword for any of this — the grouping is a convention, which is why the edges are argued about.

Which statements are DDL, and what do they have in common?

CREATE, ALTER, DROP, TRUNCATE and RENAME. What they share is the target: every one of them writes to the data dictionary rather than to the rows of a table. The consequence that gets asked about is that in Oracle and MySQL each of them fires an implicit COMMIT before and after itself, so a DDL statement ends your transaction whether or not you meant it to.

Is SELECT DML or DQL?

It depends on the definition of DML you are using, so give the definition first. If DML means statements that operate on data, SELECT is DML, and that is how the ISO SQL standard groups it — SELECT, INSERT, UPDATE and DELETE are all SQL-data statements and the standard has no DQL. If DML means statements that modify data, SELECT is retrieval only, and many syllabi split it out as DQL. Say both and you are correct under either marking scheme.

Why is TRUNCATE classified as DDL and not DML?

Because it does not operate on rows. DELETE removes rows one at a time and writes one undo record for each, which is what ROLLBACK reads to put them back. TRUNCATE deallocates the table’s data pages wholesale and records a structural change instead — in Oracle it resets the table’s high-water mark. Nothing per row was logged, so there is nothing per row to reverse. That is also why it cannot take a WHERE clause and why it is roughly constant-time.

Difference between DELETE, TRUNCATE and DROP?

DELETE is DML: it removes rows you select with a WHERE clause, fires row triggers, and is undone by ROLLBACK. TRUNCATE is DDL: it removes every row by deallocating pages, fires no row triggers, and takes no WHERE. DROP is DDL too, and it removes the table itself — structure, indexes, constraints and privileges go with the rows. After DELETE and TRUNCATE the table still exists and is empty. After DROP it does not exist.

Which statements can you not roll back?

DDL, in Oracle and MySQL, because the statement commits before it starts. GRANT and REVOKE too, in Oracle. And COMMIT itself, which is the point of it. Then name the exception in the same breath: PostgreSQL, SQL Server and SQLite have transactional DDL, so there you can BEGIN, ALTER TABLE, ROLLBACK, and the column is gone. Saying "DDL can never be rolled back" without the exception is the answer that gets corrected.

What is a SAVEPOINT, and how does ROLLBACK TO SAVEPOINT differ from ROLLBACK?

A SAVEPOINT is a named marker inside an open transaction. It changes no data. ROLLBACK TO SAVEPOINT sp1 undoes only the work done after that marker and leaves the transaction open, so anything you did before sp1 is still pending and still undoable. A plain ROLLBACK undoes everything since the last commit and ends the transaction. Savepoints are how you retry one step of a long procedure without abandoning the rest.

Does COMMIT release savepoints?

Yes, all of them. Once the transaction ends there is nothing left inside it to roll back to, so every savepoint it held is discarded. The part people miss is that an auto-committing DDL statement discards them the same way, silently. A SAVEPOINT taken before a DDL statement is worthless the moment that statement runs.

I have a DDL statement in the middle of my script. What happens to the DML above it?

In Oracle and MySQL it is committed, permanently, by the DDL statement’s implicit COMMIT. Your uncommitted inserts and updates cross to the permanent side without you asking, and a ROLLBACK after the DDL statement reports no error and undoes nothing, because there is nothing left to undo. So the question to ask of any script is not how many statements it has, but where its last commit sits.

Procedural or non-procedural DML: which is SQL, and what is the difference?

SQL is non-procedural, also called declarative. You describe the rows you want and the optimiser chooses the access path — index or scan, join order, join method. Procedural DML makes you state the order of operations yourself, one record at a time: a cursor that fetches a row, tests it and updates it inside a loop. You reach for it when a set-based statement cannot express the logic, and you pay for it, because you have taken the plan away from the optimiser.

What exactly do GRANT and REVOKE change?

Hard

Committed state: S1 78, S2 65, S3 35. On Oracle, in one session: (1) UPDATE student SET marks = 45 WHERE sid = 'S3'; (2) SAVEPOINT a; (3) INSERT INTO student VALUES ('S4','Kabir',82); (4) SAVEPOINT b; (5) DELETE FROM student WHERE marks < 50; (6) ROLLBACK TO SAVEPOINT a; (7) CREATE INDEX idx_marks ON student(marks); (8) ROLLBACK;. Give the final table as your session sees it and as a second session reading only committed data sees it. Then name the one line you would move, and where to, so that line 8 restores the original three rows.

Follow-up

Keep two columns, not one: the session view and the committed view, and update only the one each line actually touches. Exactly one of the eight lines moves anything into the committed column, and it is not a COMMIT. The two savepoints do not mark the same state, and only one of them is ever used.

Show the hint

Work out which state each savepoint is a marker for before you reach line 6, and write that state down.