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

DDL commands are SQL commands used to modify or 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.
• ALTER command alters the database structure by adding, deleting, and modifying columns of the already existing tables, like renaming and changing the data type and size of the columns.
• The DROP command deletes the defined table with all the table data, associated indexes, constraints, triggers, and permission specifications.
• 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. 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 it.

DML Commands

DML commands are SQL commands that perform operations like storing data in database tables, modifying and deleting existing rows, retrieving data, or updating data.

• The SELECT command fetches data or records from one or more tables in the SQL database. The retrieved data gets displayed in a result table known as the result set.
• The INSERT command inserts one or more new records into the table in the SQL database.
• The UPDATE command updates or changes the existing data or records in a table in the SQL database.
• The DELETE command deletes all the existing records and the allocated spaces from a table in the SQL database. 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 commands are SQL commands that perform operations like giving and withdrawing database access from the user.

• The GRANT command gives access privileges or permissions like ALL, SELECT, and EXECUTE to the database objects like views, tables, etc, in SQL.
• The REVOKE command withdraws access privileges or permissions given with the GRANT command.

Example of DDL, DML, and DCL in SQL

DDL Example

We can use the CREATE DDL command to 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.

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_id, name, and age.

Student Table:

We can insert 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. The resulting table looks like this:

Updated Student Table:

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.

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_Marks and Stu_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