Types of SQL Commands

Hook: SQL command types are like labels on a toolbox: they tell you whether you are building the database, changing rows, reading data, giving access, or locking changes in place.

Question: What are the types of SQL commands?

Answer: SQL commands are usually grouped into five families: DDL for defining structure, DML for changing data, DQL for reading data, DCL for permissions, and TCL for transactions. A simple way to remember them is: create the shape, move the rows, look at the rows, control access, and decide whether to commit or undo.

Interview-Ready Answer: I usually group SQL commands into five types. DDL changes database structure, like CREATE and ALTER. DML changes table data, like INSERT, UPDATE, and DELETE. DQL reads data with SELECT. DCL controls permissions with GRANT and REVOKE. TCL manages transactions with COMMIT and ROLLBACK. One important interview detail is that some books treat SELECT as DML, but many interviews call it DQL, so I mention that classification can vary by source.

Big Picture: what each command family does

Think of a database like a city. Some commands build roads and buildings, some move people and goods, some let you look things up, some control who gets a key, and some decide whether the day’s work is saved or undone. The command type matters because it tells you the side effect: does it change structure, change rows, just read, change permissions, or manage a transaction?

How the database processes them under the hood

1. The parser reads the statement. The database first checks the syntax and identifies the command family. This is where it knows, for example, that CREATE TABLE is a schema change and SELECT is a read.
2. Permissions are checked. DCL and many DDL operations need special rights. For example, you usually need ownership or admin rights to alter a table or grant access.
3. DDL updates metadata. Metadata means data about data: table definitions, column types, indexes, and constraints. A CREATE TABLE or ALTER TABLE changes the catalog, and may also lock objects while the change is happening.
4. DML changes table rows. INSERT, UPDATE, and DELETE work on actual records. These operations often write to the transaction log, a durable record used for recovery.
5. DQL reads and optimizes. SELECT does not change data. The optimizer, meaning the part that picks the best plan, decides whether to use an index, scan a table, or join in a certain order.
6. TCL decides finality. COMMIT makes the work permanent. ROLLBACK undoes uncommitted changes. SAVEPOINT creates a partial undo point inside a transaction.

When and why to use each one

• Use DDL when the shape of the database changes: new tables, new columns, new constraints, or dropping old objects.
• Use DML when the structure is fine but the data needs to change: daily inserts, updates from a job, soft deletes, or cleanup.
• Use DQL when your goal is to inspect, report, or validate data.
• Use DCL when access rules change, such as giving analysts read-only access.
• Use TCL when several statements must succeed together or fail together.

Performance, locks, and real numbers

Performance is very different across categories. A simple SELECT with a good index can return in a few milliseconds on a table with millions of rows, while a full table scan can take seconds. DML usually scales with the number of rows changed, so updating 10 rows is cheap, but updating 10 million rows can take seconds or minutes. DDL can be cheap for small metadata changes, but it can also be expensive: some schema changes rewrite the whole table or take strong locks, which can block writes. For example, adding a nullable column is often fast in modern engines, while changing a column type or adding a default to a huge table may be much slower. TCL itself is usually small in code size, but COMMIT can wait on disk flushes, so transaction finalization may add latency. DCL changes are usually lightweight, but the new permission may not affect already-open sessions in the way beginners expect, depending on the database and connection pooling.

Important edge cases interviewers love

• SELECT classification varies. Some sources call it DQL, some call it part of DML. In interviews, say both and explain the difference politely.
• TRUNCATE is tricky. It removes many rows quickly, but different databases classify it differently. Conceptually, it behaves more like a DDL-style bulk operation than a normal row-by-row delete.
• Autocommit matters. Many clients run each statement in its own transaction by default, so a ROLLBACK only works when you explicitly started a transaction.
• DDL rollback differs by engine. Some databases allow transactional DDL; others auto-commit certain schema changes. Never assume every schema command can be undone the same way.

Memory Check: If you can say what the command changes — structure, rows, access, or transaction state — you can usually classify it correctly under pressure.

Real-World Story: Imagine a checkout service for an e-commerce app. A release adds a new coupon_code column to the orders table, so the team uses DDL for the migration. The payment worker uses DML to insert the order and decrease inventory. The fraud dashboard uses DQL to read order data. The analytics team gets read-only access through DCL. And the checkout flow wraps payment capture plus inventory update inside one transaction with TCL, so both succeed or both fail.

What goes wrong if someone misunderstands the types? A developer might forget the transaction and run payment capture, then fail on the inventory update. Users get charged, but the order is not fulfilled. In logs, you may see the payment row inserted, then an error on the update, followed by no rollback. Support sees complaints like “I was charged but my order disappeared,” which is a classic sign that the work was not treated as one atomic unit. Atomic means “all or nothing.” That is exactly why TCL exists.

Another common production bug is a risky DDL change during peak traffic. For example, adding a constraint or changing a column type on a huge table can block writes long enough to slow checkout for everyone. The symptom is not a crash; it is rising latency, lock waits, and a backlog of requests. That is why senior engineers separate schema changes from row changes and test them on real-sized data before release.

Follow-up & Tricky Questions:

• What is the difference between DDL and DML? DDL changes the database structure, such as tables and columns, while DML changes the rows inside those tables. A good shortcut is: DDL changes the container, DML changes the contents.
• Is SELECT DML or DQL? Many interviewers expect DQL because it reads data without changing it, but some books and vendors place it under DML. The safest answer is to mention the naming difference and then explain that SELECT is a read operation.
• What is the role of COMMIT and ROLLBACK? They belong to TCL. COMMIT makes all changes in the transaction permanent, while ROLLBACK discards uncommitted changes so the database returns to the earlier state.
• Why is GRANT considered DCL? Because it changes who is allowed to do what, not the data itself. It updates permissions, which is why it belongs to access control.
• Why is ALTER TABLE dangerous on large tables? Some changes require locks or even a full table rewrite. On a large production table, that can block writers and cause slow requests or outages.
• Is TRUNCATE the same as DELETE? No. DELETE removes rows one by one and can use a WHERE clause, while TRUNCATE removes all rows very quickly and behaves differently across databases.
• Does ROLLBACK undo every SQL statement? No. It only undoes work inside the current transaction, and some schema changes may auto-commit depending on the database. That vendor difference is a common trap.
• Does GRANT change table data? No. It changes access rules only. The rows stay exactly the same.

Tricky / gotcha questions:

• Can I classify TRUNCATE as DML? Not safely. Many people call it DDL-like because it acts on the whole table and may auto-commit or reset identity values depending on the system.
• Is a transaction needed for a single INSERT? Sometimes yes, sometimes the client’s autocommit mode already wraps it for you. The safe interview answer is that transactions matter most when multiple statements must succeed together.
• Can I always roll back ALTER TABLE? No. Some databases support transactional DDL, others do not, and even in transactional systems there are exceptions. Always check the engine’s behavior.

Common Mistakes:

• Mixing up DDL and DML. Correct it by remembering that DDL changes the schema, while DML changes the data inside the schema.
• Forgetting that SELECT is a read command. Even if a source calls it DML, the important idea is that it does not modify rows.
• Assuming every change can be rolled back. Some databases auto-commit certain DDL statements, so you should not assume ROLLBACK will undo everything.
• Using DELETE without WHERE accidentally. That removes all rows. Use a filter, or use transactions when doing risky maintenance.

Memory Hook: Think: Build, Move, Look, Lock, Undo — DDL builds, DML moves data, DQL looks at data, DCL locks down access, and TCL undoes or saves the work.

Cheat Sheet:

• DDL = structure: CREATE, ALTER, DROP
• DML = data changes: INSERT, UPDATE, DELETE
• DQL = read data: SELECT
• DCL = permissions: GRANT, REVOKE
• TCL = transactions: COMMIT, ROLLBACK, SAVEPOINT
• Always mention that classifications can vary slightly by source.

Practice Tasks:

• Create a small table, insert 3 rows, and run one SELECT to confirm them.
• Wrap an INSERT and an UPDATE in a transaction, then ROLLBACK and verify the data did not change.
• Write a schema change with ALTER TABLE and explain whether it is DDL or DML.