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 nameDROPdeletes the object completely. The table no longer exists.TRUNCATEempties the table but keeps its structure, so you can insert into it again.RENAMEjust 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
SELECTdoes not let you grantSELECTto 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 USERandCREATE ROLEare 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 |
|---|---|
| Starts a new transaction |
| Makes all changes of the transaction permanent |
| Undoes all changes of the transaction |
| Marks a point inside the transaction |
| 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 unchangedAutocommit
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 VIEWplays 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 ... WHEREfirst, 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.