What are DDL, DML, and DCL in SQL?

Data Definition Language, shortly termed DDL, is a subset of SQL commands that define the structure or schema of the database.

Data Manipulation Language, shortly termed DML, is an element in SQL language that deals with managing and manipulating data in the database.

Data Control Language, shortly termed DCL, is comprised of those commands in SQL that deal with controls, rights, and permission in the database system.

What are DDL, DML, and DCL Commands?

DDL Commands

Data Definition Language (DDL) commands are SQL commands used to define and alter the structure of the database. The following is the list of DDL commands in SQL:

• The CREATE command creates database objects, tables, and triggers as part of the database schema.

• ALTER command alters the database structure by adding, deleting, and modifying columns of already existing tables, updating schema objects such as names, data types, and column sizes.

• The DROP command removes the defined table with all the table data, associated indexes, constraints, triggers, and permission specifications, and can also delete database structures as part of structural SQL changes.

• The TRUNCATE command deletes all the data and records from an existing table, including the allocated spaces for the records. Unlike the DROP command, it does not delete the table from the database, so data definition language ddl still manages database structures without removing the table itself. It works similarly to the DELETE statement without a WHERE clause.

• The RENAME command changes the table name when the user or administrator wants to give a more relevant name to an object existing in the database.

Transform Your Career

Choose from our industry-leading programs designed for career success

NSDC Certified

Modern Software and AI Engineering Program

Master full-stack development with AI integration

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

Modern Data Science and ML with specialisation in AI

Advanced data science techniques with AI specialization

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

Advanced AIML with Specialisation in Agentic AI

Deep dive into AIML with focus on Agentic systems

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

DevOps, Cloud & AI Platform Engineering

Build and manage AI-powered cloud infrastructure

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

AI Engineering Advanced Certification by IIT-Roorkee

Premier AI engineering certification from IIT-Roorkee

3 MonthsDuration

AI-LedCurriculum

Career SupportSupport

Go to Program

DML Commands

Data Manipulation Language (DML), or data manipulation language dml, includes SQL commands used to manipulate data and modify data when data present in tables needs to be changed, added, removed, or read.

• The SELECT command belongs to dql data query language, or data query language, and is used to retrieve data based on the query passed. It can perform a data query across two or more tables and use clauses like WHERE, ORDER BY, and GROUP BY, along with aggregate functions, to organize results; DQL compiles the output into a temporary result set similar to a temporary table.

• The INSERT command in dml data manipulation language is used to insert data by adding one or more new records into a table in the SQL database.

• The UPDATE command is used to modify existing records in a table in the SQL database.

• The DELETE command removes records from a table in the SQL database based on specified conditions. We can use the WHERE clause with the AND or OR operators to delete selected rows from the database.

• The MERGE command deals with insertion, updation, and deletion in the same SQL statement.

• The CALL command calls or invokes a stored procedure.

• The EXPLAIN PLAN command describes the access path to the data. It returns the execution plans for the statements like INSERT, UPDATE, and DELETE in the readable format for users to check the SQL Queries.

• The LOCK TABLE command ensures the consistency, atomicity, and durability of database transactions like reading and writing operations.

DCL Commands

DCL data control language commands are SQL commands that belong to the database security and control side and manage rights, permissions, and other controls in the database system.

• The GRANT command gives users access privileges or permissions like ALL, SELECT, and EXECUTE to perform specific actions on database objects like views, tables, etc, in SQL.

• The REVOKE command withdraws access privileges or permissions from users that were previously granted with the GRANT command.

Example of DDL, DML, and DCL in SQL

DDL Example

We can use the CREATE DDL command to define the database schema and create a new database in SQL. Let’s see it through an example.

The above SQL query creates a database named book in the SQL database.

We can also use the CREATE DDL command to create a new table in SQL, which is one of the schema objects within the database schema.

Let’s create a table named student with the given five columns in the SQL database.

The DROP command in SQL removes a database from the SQL database.

The above SQL statement removes the database named books from the SQL database.

The DROP command in SQL also removes an existing table from the SQL database.

DML Example

Let’s take the example of INSERT, which is a DML command. The INSERT command inserts data into the table in the SQL database.

Let us consider a Student table in the SQL database having fields names as s_idname, and age.

Student Table:

We can insert data as a new record into the Student table using the INSERT command.

The above SQL query inserts a row of data into the Student table with values 105, Nisha, and 30, and INSERT adds new records, while UPDATE is used to modify existing records. The resulting table looks like this:

Updated Student Table:

| s_id | name | age | |:-----:-|:---------:|:-----:| | 101 | Esha | 20 | | 102 | Suman | 21 | | 103 | Durgesh | 20 | | 104 | Amish | 22 | | 105 | Nisha | 30 | Let’s see another example of a DML command. i.e., the UPDATE command.

Let us consider a Product table having the following data:

Product Table:

Here, we must update the Product_Price of the product whose Product_Id is P201. We can do this with the help of the DML UPDATE command. The UPDATE statement for updating the product price is:

In the above SQL Query, we have used the WHERE clause to find out the row having Product_Id equal to P201. The SET command sets the Product_Price of the returned row to 80 to modify data already data present in the table.

We can also use the UPDATE command to update multiple fields of a record in the database table. Let us consider a Student table with the following set of records.

Student Table:

Let us update the Stu_MarksStu_Age of the student having Stu_Id as 103 and 202. This can be done using the DML UPDATE command using the code:

The WHERE clause separates the records of the student having their student id as 103 and 202. It results in the records of the students’ names Anuj and Sumit. The UPDATE command then updates the Student table with the new field values having Stu_Marks as 80 and Stu_Age as 21.

The updated table after executing the above SQL Query is:

DCL Commands

In the above SQL Query, we use the GRANT command to give permission. Using this command, the user gets the SELECT and UPDATE privileges. Here, the SELECT command gives access to view the record, and UPDATE gives access to modify the record. The control works on the table Student, which is the object here. Nisha is the username of the person getting permission to view and modify the Student table.

In the above SQL Query, we use the REVOKE command to revoke the SELECT and UPDATE permissions from the user named Nisha

What are the Differences Between DDL, DML, and DCL Commands?

Learn More

• Difference between DDL and DML