Chapter 06 of 41

Database Languages (DDL, DML, DCL, TCL)

So far we have seen what a DBMS is, how it is structured, and how data models describe the shape of data. But how do we actually talk to a DBMS? How do we tell it "create a table for students", "add this new student", "give the intern read-only access" or "undo the last three changes"?

We do it through database languages. Every DBMS offers a set of commands, and these commands are grouped by the kind of job they do. In this chapter we will look at the four big groups you will hear about in every exam and interview: DDL, DML, DCL and TCL.

A database language is a set of commands that users and applications use to define the database structure, manipulate the data, control access to it, and manage transactions.

In practice, all four groups are part of one language: SQL. The DDL/DML/DCL/TCL split is a way of classifying SQL commands by purpose, not four separate languages you have to install.


Why Do We Need Different Kinds of Commands?

Think about running a college library.

  • First, someone has to design the shelves: which sections exist, what each label means, how many books fit. (Structure)

  • Then, every day, staff add, issue, return and remove books. (Data)

  • Only some people are allowed to enter the rare-books room. (Access)

  • When a student returns a book, the "returned" entry and the "fine paid" entry must both happen, or neither. (Transactions)

These are four very different jobs, done by different people, at different frequencies. A DBMS groups its commands the same way:

Group

Full Form

Deals With

Main Commands

DDL

Data Definition Language

Structure (schema)

CREATE, ALTER, DROP, TRUNCATE, RENAME

DML

Data Manipulation Language

The data inside tables

SELECT, INSERT, UPDATE, DELETE

DCL

Data Control Language

Permissions

GRANT, REVOKE

TCL

Transaction Control Language

Groups of changes

COMMIT, ROLLBACK, SAVEPOINT, START TRANSACTION

Some books and interviewers also talk about DQL (Data Query Language), which contains only SELECT. Others keep SELECT inside DML. Both views are common, so it helps to mention both in an exam answer.

All the examples in this chapter were run on MySQL 8.4, using the sample company database (departments, employees, customers, orders) that the SQL tutorial on this site also uses.


1. DDL – Data Definition Language

DDL commands define and change the schema: the tables, their columns, data types and constraints. Remember from Three-Schema Architecture & Data Independence that the schema is the "blueprint" of the database. DDL is how you draw and redraw that blueprint.

DDL is the part of the language used to create, modify and delete database objects such as tables, views, indexes and schemas.

An important detail: when you run a DDL command, the DBMS stores the new structure in its data dictionary (also called the system catalog), which is "data about data" (metadata). The DBMS reads this catalog every time it checks or runs a query.

CREATE

Let's create a table to store company projects:

CREATE TABLE projects (
  project_id INT PRIMARY KEY,
  title      VARCHAR(100) NOT NULL,
  dept_id    INT,
  budget     DECIMAL(12,2),
  FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

DESCRIBE projects;

Output:

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| project_id | int           | NO   | PRI | NULL    |       |
| title      | varchar(100)  | NO   |     | NULL    |       |
| dept_id    | int           | YES  | MUL | NULL    |       |
| budget     | decimal(12,2) | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

Notice that we did not insert any data. We only described the shape of the data: column names, types, a primary key, a NOT NULL rule and a foreign key. That is exactly what DDL is about.

ALTER

Requirements change. Suppose we now also need a project start date, and titles can be longer:

ALTER TABLE projects ADD COLUMN start_date DATE;
ALTER TABLE projects MODIFY title VARCHAR(150) NOT NULL;

DESCRIBE projects;

Output:

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| project_id | int           | NO   | PRI | NULL    |       |
| title      | varchar(150)  | NO   |     | NULL    |       |
| dept_id    | int           | YES  | MUL | NULL    |       |
| budget     | decimal(12,2) | YES  |     | NULL    |       |
| start_date | date          | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

ALTER changes an existing structure without throwing away the data already in the table.

DROP, TRUNCATE and RENAME

DROP TABLE projects;                -- removes the table: structure + data
TRUNCATE TABLE projects;            -- removes all rows, keeps the empty table
RENAME TABLE projects TO initiatives;  -- changes the table's name
  • DROP deletes the object completely. The table no longer exists.

  • TRUNCATE empties the table but keeps its structure, so you can insert into it again.

  • RENAME just changes the name.

You might wonder: "TRUNCATE removes rows, so why is it DDL and not DML?" Because internally it does not delete row by row. In MySQL it effectively drops and re-creates the table. We will see the practical consequences of this in the TCL section below.


2. DML – Data Manipulation Language

DML commands work on the data stored inside the tables. These are the commands an application runs thousands of times a day.

DML is the part of the language used to retrieve, insert, update and delete data in the database.

INSERT

INSERT INTO projects (project_id, title, dept_id, budget, start_date) VALUES
 (1, 'Mobile App Revamp',   10, 1200000.00, '2024-02-01'),
 (2, 'Festive Sales Drive', 20,  300000.00, '2024-09-15'),
 (3, 'Hiring Portal',       30,  150000.00, NULL);

SELECT * FROM projects;

Output:

+------------+---------------------+---------+------------+------------+
| project_id | title               | dept_id | budget     | start_date |
+------------+---------------------+---------+------------+------------+
|          1 | Mobile App Revamp   |      10 | 1200000.00 | 2024-02-01 |
|          2 | Festive Sales Drive |      20 |  300000.00 | 2024-09-15 |
|          3 | Hiring Portal       |      30 |  150000.00 | NULL       |
+------------+---------------------+---------+------------+------------+

UPDATE and DELETE

Engineering gets a 10% budget increase, and the Hiring Portal project is cancelled:

UPDATE projects SET budget = budget * 1.10 WHERE dept_id = 10;
DELETE FROM projects WHERE project_id = 3;

SELECT * FROM projects;

Output:

+------------+---------------------+---------+------------+------------+
| project_id | title               | dept_id | budget     | start_date |
+------------+---------------------+---------+------------+------------+
|          1 | Mobile App Revamp   |      10 | 1320000.00 | 2024-02-01 |
|          2 | Festive Sales Drive |      20 |  300000.00 | 2024-09-15 |
+------------+---------------------+---------+------------+------------+

Notice the WHERE clause. Without it, UPDATE would change every row and DELETE would remove every row. This is one of the most common (and painful) real-world mistakes.

SELECT (sometimes called DQL)

SELECT name, salary
FROM employees
WHERE dept_id = 10
ORDER BY salary DESC, name;

Output:

+--------------+-----------+
| name         | salary    |
+--------------+-----------+
| Aarav Sharma | 150000.00 |
| Priya Iyer   |  95000.00 |
| Rohan Mehta  |  95000.00 |
+--------------+-----------+

SELECT only reads data; it never changes it. That is why some people put it in its own group, DQL.

Procedural vs Non-Procedural DML

This is a classic theory question. DMLs come in two styles:

Procedural DML

Non-Procedural (Declarative) DML

You say what data you want and how to get it, step by step.

You say only what data you want. The DBMS decides how.

Usually works one record at a time (e.g., loop through records, follow pointers).

Works on whole sets of rows at once.

Example: relational algebra (a sequence of operations), record-at-a-time navigational DMLs of old network/hierarchical systems.

Example: SQL, relational calculus.

Programmer must think about efficiency.

The query optimizer picks an efficient plan.

In the SQL query above, we never told MySQL to "open the employees file, read row 1, check dept_id, ...". We just described the result we wanted. That is why SQL is called declarative, and why the same query can become faster just by adding an index, without changing a single word of it.


3. DCL – Data Control Language

A company database is shared by many people. The HR team should see salaries; a summer intern probably should not. The finance app may update payments; the analytics dashboard should only read. DCL handles this.

DCL commands give and take away privileges (permissions) on database objects.

The two DCL commands are:

  • GRANT – gives a privilege to a user or role.

  • REVOKE – takes a privilege back.

-- run by an administrator
CREATE USER 'intern'@'localhost' IDENTIFIED BY 'Intern@123';

GRANT SELECT ON company_db.employees TO 'intern'@'localhost';

-- later, when the internship ends
REVOKE SELECT ON company_db.employees FROM 'intern'@'localhost';

After the GRANT, the intern can run SELECT on employees, but if they try to change data, MySQL refuses with an error of this form:

ERROR 1142 (42000): UPDATE command denied to user 'intern'@'localhost' for table 'employees'

And a user with no privileges at all on a database cannot even open it:

ERROR 1044 (42000): Access denied for user 'intern'@'localhost' to database 'company_db'

A few points worth knowing:

  • Privileges can be given at different levels: whole server, one database, one table, even specific columns.

  • To pass a privilege on to others, a user needs it WITH GRANT OPTION. Simply having SELECT does not let you grant SELECT to someone else.

  • Modern systems (including MySQL 8) support roles: you grant privileges to a role like analyst, then grant the role to many users. This is much easier to manage than per-user grants.

  • CREATE USER and CREATE ROLE are not GRANT/REVOKE, but they are usually discussed together with DCL because they belong to security administration.

Why it matters: DCL is how a DBMS enforces the principle of least privilege: every user gets only the access they really need. This limits the damage from mistakes and from stolen passwords.


4. TCL – Transaction Control Language

A transaction is a group of operations that must be treated as one unit: either all of them happen, or none of them do. The classic example is a bank transfer: debit one account and credit another. We will study transactions properly in Transactions & ACID Properties; here we only look at the commands.

TCL commands control transactions: they make changes permanent, undo them, or mark points to undo back to.

Command

What It Does

START TRANSACTION (or BEGIN)

Starts a new transaction

COMMIT

Makes all changes of the transaction permanent

ROLLBACK

Undoes all changes of the transaction

SAVEPOINT name

Marks a point inside the transaction

ROLLBACK TO SAVEPOINT name

Undoes only the changes made after that point

Example: SAVEPOINT and Partial Rollback

Vikram (emp 5) and Ananya (emp 6) currently earn 62,000 and 58,000. We start giving both a raise, then change our mind about Ananya:

START TRANSACTION;
UPDATE employees SET salary = salary + 5000 WHERE emp_id = 5;
SAVEPOINT after_vikram;
UPDATE employees SET salary = salary + 5000 WHERE emp_id = 6;

SELECT emp_id, name, salary FROM employees WHERE emp_id IN (5, 6);
+--------+--------------+----------+
| emp_id | name         | salary   |
+--------+--------------+----------+
|      5 | Vikram Singh | 67000.00 |
|      6 | Ananya Das   | 63000.00 |
+--------+--------------+----------+
ROLLBACK TO SAVEPOINT after_vikram;

SELECT emp_id, name, salary FROM employees WHERE emp_id IN (5, 6);
+--------+--------------+----------+
| emp_id | name         | salary   |
+--------+--------------+----------+
|      5 | Vikram Singh | 67000.00 |
|      6 | Ananya Das   | 58000.00 |
+--------+--------------+----------+

Only Ananya's change was undone. The transaction is still open. Now:

COMMIT;

Vikram's raise is now permanent, and nothing can roll it back anymore.

Step by step:

START TRANSACTION
   │
   ├── UPDATE emp 5  (+5000)        kept
   │
   ├── SAVEPOINT after_vikram  ◄────────────┐
   │                                        │
   ├── UPDATE emp 6  (+5000)        undone ─┘ ROLLBACK TO after_vikram
   │
COMMIT  →  emp 5 = 67000 saved, emp 6 = 58000 unchanged

Autocommit

By default, MySQL runs in autocommit mode: every single statement you run outside an explicit transaction is committed immediately. That is why a careless DELETE without WHERE cannot be undone in a normal session. START TRANSACTION switches autocommit off until the next COMMIT or ROLLBACK.


An Important Trap: DDL Causes an Implicit Commit

In MySQL (and Oracle), most DDL statements automatically commit the current transaction. Watch what happens here:

START TRANSACTION;
INSERT INTO projects VALUES (4, 'Market Survey', 40, 90000.00, NULL);
CREATE TABLE temp_notes (id INT);   -- DDL!
ROLLBACK;

SELECT project_id, title FROM projects;

Output:

+------------+---------------------+
| project_id | title               |
+------------+---------------------+
|          1 | Mobile App Revamp   |
|          2 | Festive Sales Drive |
|          4 | Market Survey       |
+------------+---------------------+

The ROLLBACK did not remove project 4. The CREATE TABLE silently committed the INSERT before it ran, so by the time we said ROLLBACK, there was nothing left to undo.

Why do databases do this? Changing the structure of tables while other uncommitted changes depend on the old structure is hard to make safe, so many systems simply close the transaction first. (PostgreSQL is a notable exception: most of its DDL can be rolled back inside a transaction.)


DELETE vs TRUNCATE vs DROP

This is one of the most frequently asked interview questions on this topic. Let's prove the key difference first.

CREATE TABLE logs (id INT AUTO_INCREMENT PRIMARY KEY, msg VARCHAR(50));
INSERT INTO logs (msg) VALUES ('a'), ('b'), ('c');

START TRANSACTION;
DELETE FROM logs;          -- DML
ROLLBACK;

SELECT COUNT(*) AS rows_after_delete_rollback FROM logs;
+----------------------------+
| rows_after_delete_rollback |
+----------------------------+
|                          3 |
+----------------------------+

The DELETE was rolled back; all 3 rows are back. Now TRUNCATE:

TRUNCATE TABLE logs;       -- DDL: commits, cannot be rolled back
INSERT INTO logs (msg) VALUES ('fresh');
SELECT * FROM logs;
+----+-------+
| id | msg   |
+----+-------+
|  1 | fresh |
+----+-------+

The new row got id = 1: TRUNCATE also reset the AUTO_INCREMENT counter. A plain DELETE does not do that; the next id would have been 4.

Point

DELETE

TRUNCATE

DROP

Category

DML

DDL

DDL

Removes

Selected rows (or all)

All rows

Rows + table structure

WHERE clause

Allowed

Not allowed

Not applicable

Rollback (MySQL)

Yes, inside a transaction

No (implicit commit)

No (implicit commit)

Speed on big tables

Slower (row by row, logged)

Very fast

Very fast

Fires DELETE triggers

Yes

No

No

AUTO_INCREMENT

Not reset

Reset

Gone with the table

Table usable afterwards?

Yes

Yes (empty)

No


Other Language Terms You May See

Older textbooks (especially those following the three-schema architecture strictly) mention a few more names:

  • SDL – Storage Definition Language: defines the internal (physical) schema, like how files are stored. In modern systems this is mostly hidden or done through options such as tablespaces and index types.

  • VDL – View Definition Language: defines external schemas (user views). In SQL, CREATE VIEW plays this role, and it is usually counted as DDL.

  • Host language and embedded SQL: SQL statements used from inside a general-purpose language like Java, Python or C. The general language is called the host language, and the SQL part is the data sublanguage.

In real-life SQL, all of these are just more SQL statements. The names matter mostly for theory exams.


Common Mistakes

  • Calling TRUNCATE a DML command. It removes data, but it is DDL. Remember: no WHERE, no rollback in MySQL, resets AUTO_INCREMENT.

  • Thinking ROLLBACK can undo anything. It can only undo the current, uncommitted transaction. Once committed (explicitly, by autocommit, or implicitly by DDL), the change is permanent.

  • Mixing DDL into a transaction and expecting it to roll back together with the DML around it.

  • Forgetting WHERE in UPDATE/DELETE. Good habit: write the SELECT ... WHERE first, check the rows, then turn it into UPDATE/DELETE.

  • Saying "SQL = DML". SQL contains DDL, DML, DCL and TCL together.

  • Giving everyone full privileges "to keep things simple". It defeats the whole purpose of DCL.


Interview Questions

1. What are DDL, DML, DCL and TCL? Give two commands of each.

DDL defines structure (CREATE, ALTER, DROP, TRUNCATE). DML works with data (SELECT, INSERT, UPDATE, DELETE). DCL controls access (GRANT, REVOKE). TCL controls transactions (COMMIT, ROLLBACK, SAVEPOINT).

2. Is SELECT a DML or a DQL command?

Both classifications are used. Many textbooks put it under DML (data retrieval is a form of manipulation); others create a separate DQL group because SELECT only reads data. Mention both.

3. Why is TRUNCATE a DDL command even though it removes rows?

Because it works on the table as a whole object instead of row by row. In MySQL it is implemented like drop-and-recreate, causes an implicit commit, cannot be rolled back, does not fire DELETE triggers and resets AUTO_INCREMENT.

4. What is the difference between procedural and non-procedural DML?

Procedural DML specifies what data is needed and how to get it (step by step, often one record at a time). Non-procedural (declarative) DML, like SQL, specifies only what is needed; the DBMS optimizer chooses how.

5. What is an implicit commit?

A commit the DBMS performs automatically, without you typing COMMIT. In MySQL, most DDL statements (CREATE, ALTER, DROP, TRUNCATE, RENAME) implicitly commit the current transaction before running.

6. What does a SAVEPOINT do?

It marks a point inside a transaction. ROLLBACK TO SAVEPOINT undoes only the changes made after that point, while keeping the transaction open.

7. What is WITH GRANT OPTION?

It allows the user who receives a privilege to grant that same privilege to other users. Without it, a user can use the privilege but cannot pass it on.

8. Where does the DBMS store the result of DDL commands?

In the data dictionary (system catalog), which holds metadata such as table definitions, column types, constraints, users and privileges.


Summary

  • Database languages are the commands we use to talk to a DBMS. In practice, they are all parts of SQL.

  • DDL (CREATE, ALTER, DROP, TRUNCATE, RENAME) defines and changes structure; results go into the data dictionary.

  • DML (SELECT, INSERT, UPDATE, DELETE) works with the data. SQL is a non-procedural DML: you say what, not how.

  • DCL (GRANT, REVOKE) manages who may do what.

  • TCL (START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT) groups changes into all-or-nothing units.

  • In MySQL, DDL statements cause an implicit commit, so they cannot be rolled back. This is why TRUNCATE and DROP are irreversible, while DELETE inside a transaction is not.

Next, we move from talking to the database to designing it. In ER Model, we will learn how to turn a real-world problem into a clear picture of entities and relationships before writing a single CREATE TABLE.