SQL statements : what are DDL, DML or DRL scripts ?

---

Managing a database is inevitably done by creating instructions. Those instructions allow you to create a table, insert or delete some data, update and so on. This is possible using the SQL language (Structured Query Language) which is the standard language for relational database management systems.

You need to know that the SQL statements are categorized into 5 types :

• A. Data Definition Language (DDL)
• B. Data Manipulation Language (DML)
• C. Data Retrieval Language (DRL)
• D. Data Control Language (DCL)
• E. Transaction Control Language (TCL)

---

A. Data Definition Language (DDL)

DDL commands are used to define, create and modify database objects like tables, tablespaces, schemas, views, stored procedures and so on. The main statements are CREATE, DROP, ALTER, RENAME, TRUNCATE, ANALYZE. Here are some explanations and examples of DDL commands.

• CREATE : Creates the object with the defined characteristics.

CREATE TABLE Employees (
    Id INTEGER,
    Firstname VARCHAR(255),
    Lastname VARCHAR(255)
);

• DROP : Drop/delete the object.

DROP TABLE Employees ;

• ALTER Modify the definition of an object like setting a nullable column to NOT NULL for example.

ALTER TABLE Employees 
    ALTER COLUMN Id INTEGER NOT NULL ;

• RENAME If an object is already defined, it will rename its name like a column or a table.

RENAME TABLE Employees TO Managers ;

• ANALYZE : This command is used to generate table statistics.

ANALYZE TABLE Employees ;

---

B. Data Manipulation Language (DML)

DML commands are dealing with the manipulation of the data itself on pre-defined tables and columns. It basically consists in interacting with the rows/records of a table. The main statements are INSERT, UPDATE and DELETE.

• INSERT : This command is used to add one or multiple records into a table.

INSERT INTO Employees VALUES ('4', 'John', 'Doe') ;

• DELETE It is used to delete one or multiples rows based on a condition using the WHERE keyword.

DELETE FROM Employees WHERE Firstname = 'John' ;

• UPDATE This command updates one or multiple records based on a condition using the WHERE keyword. It can also modify multiple column at once.

UPDATE Employees SET Firstname = 'Jean' WHERE Lastname = 'Dupont' ;

---

C. Data Retrieval Language (DRL)

DRL scripts are basically used for queries and data access. It relies on one essential command which is SELECT to retrieve data.

• SELECT : This command retrieves data from tables based on pre-defined conditions.

SELECT FROM Employees WHERE Firstname = 'John' ;

---

D. Data Control Language (DCL)

DCL commands are mainly used to manage rights, permissions and others controls on different objects in the database. It is composed of 2 mains commands which are GRANT and REVOKE.

• GRANT : This command will allow/grant the use of a specific operation or access. For example to define data access on a user regarding a specific table, we can manage the SELECT permission.

GRANT SELECT ON Employees TO user1 ;

• REVOKE : This command will disable/revoke the use of a specific operation or access.

REVOKE SELECT ON Employees FROM user1 ;

---

E. Transaction Control Language (TCL)

TCL commands are dealing with the transaction concept in a database. A transaction is the smallest unit of work executed in the database. It is defined by a sequence of statements. If you want more information about database transaction I recommend you to check my other post : https://codethekey.com/database-transaction-sql/. The main statements interacting with transactions are COMMIT, ROLLBACK and SAVEPOINT.

• COMMIT

DELETE FROM Employees WHERE Firstname = 'John' ;
COMMIT;

• ROLLBACK

DELETE FROM Employees WHERE Firstname = 'John' ;
ROLLBACK;

• SAVEPOINT

DELETE FROM Employees WHERE Firstname = 'John' ;
SAVEPOINT SP1;

---

If you already looked at some SQL scripts you probably know some of those mentionned statements in each category.

Then you may ask yourself some questions :

What is the difference between a SQL script and a DDL script ?

Can I just write the content of a DDL script and another DML script into a single SQL script ?

The answer to this question is simple, it is mainly for human readability.
In fact, when you see a DDL script you know it will only be about the creation, drop or renaming of an object. It can also increase security by allowing only certain operations in a type of file, removing possible errors. However a general SQL script can include all sorts of statements.