What Are DDL, DML, DQL, and DCL in SQL?

We explore the separation of SQL into its four principal sublanguages and explain the meaning behind each of them.

When you’re learning SQL – perhaps through LearnSQL.com’s very own comprehensive SQL Basics course – you may have come across the terms DDL, DML, DQL, and DCL. And maybe you’ve gotten confused by all the acronyms. In this article, we will explain what these terms stand for and how they correspond to sublanguages that neatly divide the SQL language into four parts that function together.

Dividing SQL Into Sublanguages

Structured Query Language, or SQL, is a comprehensive language for manipulating databases. It is the language used in database systems such as PostgreSQL, MySQL, Microsoft SQL Server, and Oracle Database. Each of these systems have their own implementations of the dozens of commands in the SQL language (i.e. their own dialects), which can be overwhelming at first.

SQL can do many different things: create database tables, insert or change records, add indexes, retrieve information, and so on. So it can be useful to divide SQL into several sublanguages; this helps us wrap our heads around all the different operations that can be performed on an SQL database. These sublanguages are:

• Data Query Language (DQL) - The Data Query Language is the sublanguage responsible for reading, or querying, data from a database. In SQL, this corresponds to the SELECT
• Data Manipulation Language (DML) - The Data Manipulation Language is the sublanguage responsible for adding, editing or deleting data from a database. In SQL, this corresponds to the INSERT, UPDATE, and DELETE
• Data Definition Language (DDL) - The Data Definition Language is the sublanguage responsible for defining the way data is structured in a database. In SQL, this corresponds to manipulating tables through the CREATE TABLE, ALTER TABLE, and DROP TABLE
• Data Control Language (DCL) - The Data Control Language is the sublanguage responsible for the administrative tasks of controlling the database itself, most notably granting and revoking database permissions for users. In SQL, this corresponds to the GRANT, REVOKE, and DENY commands, among others.

In the sections below we will go through these sublanguages one by one and explore their meanings and the commands they represent. If you want a deeper dive on these concepts, follow our interactive SQL Basics course on LearnSQL.com. It contains 129 hands-on exercises that will help you learn SQL through practice. With each exercise solved, you’ll gain confidence in your SQL skills.

Data Query Language (DQL)

The Data Query Language, or DQL for short, is the group of commands responsible for querying data from a database. The principal DQL command in SQL is the SELECT command, which retrieves data from one or more tables.

Let’s take the following table. It contains bank account details and is named Accounts:

If we wanted to retrieve the names of all users with a bank account, we would use this SELECT command:

SELECT Name 
FROM Accounts

The above command is a typical statement from the DQL sublanguage. What if, however, we wanted to get the names of all users with a balance greater than $1500? In that case, we need to add the WHERE clause to the statement:

SELECT Name 
FROM Accounts 
WHERE Balance > 1500

Take a look at our SQL Basics Cheat Sheet for details on the SELECT command (and much more).

Data Manipulation Language (DML)

The Data Manipulation Language, or DML for short, is the group of commands responsible for manipulating data in a database; this generally entails inserting, editing, or deleting rows in SQL tables.

The SQL command for inserting a new row in a table is the INSERT command. If we want to add a row to the Accounts table for a user called Evan Johnson with a balance of $3200, we would do something like this:

INSERT INTO Accounts (Name, Balance) 
VALUES (‘Evan Johnson’, 3200)

If, at some point in the future, Evan made a withdrawal of $200, we would need to update his balance to $3000. To edit Evan’s bank account record, we need to use the UPDATE command:

UPDATE Accounts SET Balance = 3000 
WHERE Name = ‘Evan Johnson’

Finally, if Evan decided to close his account at our bank, we would need to delete his entry in the Accounts table. To accomplish this, we need to use the DELETE command:

DELETE FROM Accounts 
WHERE Name = ‘Evan Johnson’

The commands described above (INSERT, UPDATE, and DELETE) represent the main SQL operations for data manipulation and thus make up the Data Manipulation Language. To learn more about these commands, you can read the SQL INSERT, SQL UPDATE, SQL DELETE – Oh My! article or take the How to INSERT, UPDATE, and DELETE Data in SQL course right here on LearnSQL.com.

Data Definition Language (DDL)

The Data Definition Language, or DDL, is made up of the commands responsible for creating, editing and deleting SQL tables. These commands are CREATE TABLE, ALTER TABLE, and DROP TABLE.

In the examples above, we were working with the Accounts table, which already had a structure and records. But how would we go about creating this table in the first place? We’d need to use the CREATE TABLE command:

CREATE TABLE Accounts (
	ID INT,
	Name VARCHAR(255),
	Balance INT
)

What if we then wanted to edit the table so it had a column storing account holders’ phone numbers? We’d use the ALTER TABLE command:

ALTER TABLE Accounts
ADD Phone VARCHAR(255)

Finally, if we want to delete the Accounts table altogether, we’d use the DROP TABLE command:

DROP TABLE Accounts

These commands have far more features than listed above and their specific syntax tends to vary among SQL dialects. If you want to learn more, take our Creating Database Structure track or read What Is a SQL Dialect, and Which One Should You Learn? to get a feel of the different SQL implementations available.

Data Control Language (DCL)

The Data Control Language, or DCL for short, is responsible for all sorts of administrative tasks around the database itself. The most notable one is setting permissions for database users, which is done chiefly through the GRANT, REVOKE, and DENY commands.

GRANT is the SQL command responsible for granting permissions to a database user. If we wanted to grant the permission to query the Accounts table to a user named Mark, we’d do something like this:

GRANT SELECT TO ‘mark’@’localhost’

To explicitly deny the permission to query the table for a database user, use the DENY command as follows:

DENY SELECT FROM ‘mark’@’localhost’

Note that explicitly denying a permission prevents it from being granted in the future. If we simply want to reverse a GRANT or DENY command, we should use the REVOKE command:

REVOKE SELECT FROM ‘mark’@’localhost’

The above command reverses any particular GRANT and DENY permissions assigned to the user, essentially resetting them to their default state.

Note that these commands differ greatly between SQL dialects, so the above examples are assuming you’re running Microsoft SQL Server. There are many more commands that deal with database administration, but these differ so much between platforms that it would be overwhelming to deal with them here. Consult your database documentation for more details.

Learn More About the SQL Language

To sum up, the SQL language can be divided into four sublanguages:

1. Data Query Language (DQL) for querying data.
2. Data Manipulation Language (DML) for editing data.
3. Data Definition Language (DDL) for structuring data.
4. Data Control Language (DCL) for administering the database.

If you want to learn more about SQL, join our SQL Basics course, where we cover all this and more. You can also take a look at our SQL Basics Cheat Sheet for a quick syntax refresher on any command.