DDL stands for Data Definition Language. These are the SQL commands that build and change the structure of your database: the tables, the columns, the constraints. They do not touch the rows inside those tables, which is what DML does. There are five of them in common use, and most of a data analyst’s day involves only two.
If you are preparing for an interview, the distinction that gets asked most is DROP versus TRUNCATE versus DELETE. That one is at the end of this article, with the answer that actually gets you the mark.
The five DDL commands at a glance
Each row below gives the command, what it does to the database, the general syntax, and a working example. The examples use a single scenario throughout: a Bengaluru electronics retailer tracking orders, so you can follow one table as it changes shape.
| Command | What it does | Syntax | Example |
|---|---|---|---|
| CREATE | Makes a new table, database, view or index | CREATE TABLE name (column datatype, …) | CREATE TABLE orders (order_id INT PRIMARY KEY, customer_name VARCHAR(100), amount DECIMAL(10,2)) |
| ALTER | Changes the structure of an existing table | ALTER TABLE name ADD column datatype | ALTER TABLE orders ADD city VARCHAR(50) |
| DROP | Deletes the table and its structure entirely | DROP TABLE name | DROP TABLE orders |
| TRUNCATE | Removes every row but keeps the empty table | TRUNCATE TABLE name | TRUNCATE TABLE orders |
| RENAME | Changes a table’s name | RENAME TABLE old TO new | RENAME TABLE orders TO customer_orders |
Two things to notice. DROP and TRUNCATE both destroy data, and neither asks you to confirm. And ALTER is the one you will actually use most often, because real tables change shape constantly as a business starts tracking new things.
CREATE: building the table
CREATE defines what a table holds before any data goes into it. You name each column and give it a data type, and the database enforces that type from then on.
| Element | What it does | Syntax | Example |
|---|---|---|---|
| Basic table | Defines columns and types | CREATE TABLE name (col type, col type) | CREATE TABLE orders (order_id INT, amount DECIMAL(10,2)) |
| PRIMARY KEY | Marks the column that uniquely identifies a row | col type PRIMARY KEY | order_id INT PRIMARY KEY |
| NOT NULL | Refuses rows where this column is empty | col type NOT NULL | customer_name VARCHAR(100) NOT NULL |
| DEFAULT | Fills a value when none is supplied | col type DEFAULT value | status VARCHAR(20) DEFAULT ‘pending’ |
| CREATE DATABASE | Makes a new database | CREATE DATABASE name | CREATE DATABASE retail_analytics |
The data type matters more than beginners expect. VARCHAR(100) reserves space for up to 100 characters; INT holds whole numbers; DECIMAL(10,2) holds a number with ten digits total and two after the decimal point, which is what you want for money. Storing a rupee amount as a floating-point type is a classic mistake, because floats introduce rounding errors that show up as one-paisa discrepancies in a monthly total.
ALTER: changing a table that already exists
ALTER is the workhorse. A business decides it wants to track delivery city, or a column was sized too small, or a field that should never have been optional needs a constraint. ALTER does all of that without rebuilding the table.
| Operation | What it does | Syntax | Example |
|---|---|---|---|
| ADD column | Appends a new column | ALTER TABLE t ADD col type | ALTER TABLE orders ADD city VARCHAR(50) |
| DROP column | Removes a column and its data | ALTER TABLE t DROP COLUMN col | ALTER TABLE orders DROP COLUMN city |
| MODIFY column | Changes a column’s data type or size | ALTER TABLE t MODIFY col newtype | ALTER TABLE orders MODIFY customer_name VARCHAR(150) |
| RENAME column | Renames a column in place | ALTER TABLE t RENAME COLUMN old TO new | ALTER TABLE orders RENAME COLUMN amount TO order_value |
| ADD constraint | Adds a rule the data must satisfy | ALTER TABLE t ADD CONSTRAINT name type (col) | ALTER TABLE orders ADD CONSTRAINT pk_order PRIMARY KEY (order_id) |
One syntax warning that costs people marks in interviews and time at work: MODIFY is MySQL and Oracle. SQL Server uses ALTER COLUMN, and PostgreSQL uses ALTER COLUMN with a TYPE keyword. The concept is identical across all of them; only the keyword changes. Say which database you mean when you answer, and you will sound like someone who has used more than one.
Shrinking a column is where ALTER bites. Going from VARCHAR(150) down to VARCHAR(50) on a table that already holds a 90-character name will either error out or silently truncate, depending on the database and its settings. Check your longest existing value before you shrink anything.
DROP, TRUNCATE and DELETE: the three ways to remove things
This is the most-asked DDL question in analyst interviews, and the reason is that the three commands look similar and behave very differently.
| Command | Type | What it removes | Can you roll it back | Speed on a large table |
|---|---|---|---|---|
| DELETE | DML | Chosen rows, or all rows with no WHERE | Yes, inside a transaction | Slow; logs every row |
| TRUNCATE | DDL | Every row, structure stays | Usually no | Fast; deallocates pages |
| DROP | DDL | Rows, structure, constraints, indexes | No | Fast |
The way to hold it in your head: DELETE removes data, TRUNCATE empties the table, DROP removes the table. After DELETE and TRUNCATE you can still run a SELECT against the table and get zero rows back. After DROP, that SELECT errors, because there is no table.
Two follow-ups interviewers like. First, TRUNCATE usually resets an auto-increment counter back to its starting value while DELETE does not, so a table truncated and refilled starts its IDs at 1 again. Second, DELETE can carry a WHERE clause and the other two cannot, which is the practical reason you reach for DELETE when you only want some of the rows gone.
DDL versus DML, in one table
Interviewers often open with this because it sorts out who has actually written SQL from who has read about it.
| DDL | DML | |
|---|---|---|
| Full name | Data Definition Language | Data Manipulation Language |
| Works on | Structure: tables, columns, constraints | Data: the rows inside tables |
| Commands | CREATE, ALTER, DROP, TRUNCATE, RENAME | SELECT, INSERT, UPDATE, DELETE |
| Auto-commit | Yes, in most databases | No, needs an explicit COMMIT |
| Rollback | Generally not possible | Possible before COMMIT |
That auto-commit row is the one worth remembering, because it explains why DDL is dangerous. When you run an UPDATE without a WHERE clause you can often roll it back. When you run a DROP TABLE, most databases have already committed it by the time you see the error in your own face.
Mistakes that cost people real data
Running DROP when you meant TRUNCATE. You wanted an empty table to reload; you now have no table, and every view, index and foreign key that pointed at it is gone too.
Assuming TRUNCATE can be rolled back. In some databases inside some transaction settings it can. Do not rely on it. Treat TRUNCATE as permanent and take a backup first.
Adding a NOT NULL column to a populated table. The existing rows have no value for it, so the database refuses. Add the column as nullable, populate it, then apply the constraint with a second ALTER.
Testing on production because “it is only a structural change”. Structural changes are exactly the ones you cannot undo. Every DDL statement should run against a copy first.
Where DDL sits in an analyst’s actual job
Being honest about this: if your job title is data analyst, you will spend most of your time in SELECT, not in DDL. You are usually querying tables somebody else designed. DDL matters for you in three situations, and they are worth knowing well rather than exhaustively.
You will CREATE tables when you build a staging area for your own analysis. You will ALTER them constantly as your analysis evolves. And you will be asked about DROP versus TRUNCATE versus DELETE in almost every interview, because it is a quick test of whether you understand that structure and data are different things.
If you are aiming at data engineering rather than analysis, the weighting flips and DDL becomes central. That is worth knowing before you choose which way to specialise.
Frequently asked questions
What does DDL stand for in SQL?
DDL stands for Data Definition Language. It is the subset of SQL used to define and change the structure of a database, including its tables, columns, indexes and constraints. The main DDL commands are CREATE, ALTER, DROP, TRUNCATE and RENAME.
Is TRUNCATE a DDL or DML command?
TRUNCATE is classified as DDL, even though it removes data rather than structure. The reason is that it works by deallocating the data pages the table uses rather than deleting rows one at a time, and it auto-commits like other DDL commands. This is why it is fast and why it generally cannot be rolled back.
What is the difference between DROP and TRUNCATE?
TRUNCATE removes every row but leaves the empty table in place, so you can still query it and insert into it afterwards. DROP removes the table itself along with its rows, structure, indexes and constraints, so any query against it will error. Use TRUNCATE to empty a table you intend to refill, and DROP only when you want the table gone.
Can DDL commands be rolled back?
In most databases, no. DDL statements auto-commit, meaning the change is saved as soon as it runs and there is no pending transaction to reverse. PostgreSQL is a notable exception and supports transactional DDL, so a CREATE or ALTER inside a transaction block can be rolled back there. Never assume this behaviour without checking your specific database.
Which DDL command changes an existing table?
ALTER. It adds columns, removes them, changes a column’s data type or size, renames columns, and adds or drops constraints. The exact keyword for changing a column type varies by database: MySQL and Oracle use MODIFY, while SQL Server and PostgreSQL use ALTER COLUMN.
Do I need to know DDL to become a data analyst?
You need to understand it, but you will not write it every day. Analysts spend most of their SQL time on SELECT queries against tables that already exist. DDL becomes relevant when you build your own staging tables, and it comes up in interviews as a test of whether you understand the difference between a database’s structure and its contents.
Learn SQL properly, not in fragments
DDL is one hour of a real SQL curriculum. The parts that take longer, and that actually decide whether you can do the job, are joins across several tables, window functions, and query performance on tables large enough to be slow.
SkilloVilla’s SQL: Beginner to Advanced course covers the language end to end with live teaching and mentor support, at ₹33,110. If you want SQL as part of a full analytics path rather than on its own, the Data Analytics with Python track at ₹71,999, currently ₹58,999 places it alongside Python, statistics and the placement support that follows.
Fees and ratings last checked August 2026; confirm current numbers with the provider before enrolling.
