What is DDL, DML, DCL and TCL in the SQL language

In this material I will tell you about DDL, DML, DCL and TCL in SQL language. If you do not know what these incomprehensible letter sets mean and you work with SQL language, you should read this material.

What is DDL, DML, DCL and TCL in SQL

For starters, let’s remember what SQL is and what it is for.

SQL – Structured Query Language

Structured Query Language (SQL) – a language of structured queries, using it to write special queries (SQL instructions) to the database in order to obtain these data from the database and to manipulate these data.

SQL is a standard, it is implemented in all relational databases, but each DBMS has an extension of this standard, has its own language for working with data, it is usually called a dialect of SQL, which, of course, is based on SQL, but provides more opportunities for full programming, in addition, this internal language allows you to get system information and simplify SQL queries.

Here are some dialects of the SQL language:

• Transact-SQL (short for T-SQL) – used in Microsoft SQL Server;
• PL/SQL (Procedural Language / Structured Query Language) – used in Oracle Database;
• PL/pgSQL (Procedural Language/PostGres Structured Query Language) – used in PostgreSQL.

Thus, it depends on the DBMS on which extension you will write SQL instructions. If we talk about simple SQL queries, for example:

SELECT ProductId, ProductName
FROM Goods

then, of course, such queries will work in all DBMS, because SQL is a standard.

Note: This is a simple SQL query for selecting data from a single table, two columns are displayed.

However, if you are going to program, use all internal DBMS capabilities (develop procedures, use built-in functions, get system information, etc.), then you need to learn a specific SQL dialect and practice it accordingly in the DBMS that uses this dialect. This is important, because the syntax of many constructions differs in the same way as the features and much more. And if, say, you run an SQL instruction that uses the capabilities of a particular SQL extension to another DBMS, then such an instruction, of course, will not be executed.

In other words, the SQL language is needed to work with databases.

In terms of implementation, the SQL language is a set of operators that are divided into certain groups and each group has its own purpose. In short, these groups are called DDL, DML, DCL and TCL.

Thus, these incomprehensible letters represent an abbreviation for names of groups of operators of SQL language.

DDL – Data Definition Language

Data Definition Language (DDL) is a group of data definition operators. In other words, with the help of operators included in this group, we define the structure of the database and work with the objects of this database, ie create, modify and delete them.

This group includes the following operators:

• CREATE – used to create database objects;
• ALTER – used to modify database objects;
• DROP – used to delete database objects.

DML – Data Manipulation Language

Data Manipulation Language (DML) is a group of operators for data manipulation. With these operators we can add, modify, delete and unload data from the database, i.e. manipulate them.

This group includes the most common SQL language operators:

• SELECT – performs data sampling;
• INSERT – adds new data;
• UPDATE – changes the existing data;
• DELETE – deletes data.

DCL – Data Control Language

Data Control Language (DCL) – a group of operators defining access to data. In other words, these are operators for managing permissions, with their help we can allow or deny certain operations on database objects.

This includes:

• GRANT – gives the user or group permissions to perform certain operations on the object;
• REVOKE – revokes the granted permissions;
• DENY – sets a ban that has priority over the permission.

TCL – Transaction Control Language

Transaction Control Language (TCL) – a group of operators to manage transactions. A transaction is a command or block of commands (instructions) that are successfully completed as a whole, with all the changes made in the database being fixed permanently or cancelled, i.e. all the changes made by any command included in the transaction will be cancelled.

TCL operator group is designed just for the implementation and management of transactions. It can be referred to here:

• BEGIN TRANSACTION – serves to determine the beginning of the transaction;
• COMMIT TRANSACTION – applies the transaction;
• ROLLBACK TRANSACTION – rollback all changes made in the context of the current transaction;
• SAVE TRANSACTION – sets the intermediate saving point within the transaction.

DDL, DML, DCL & TCL statements in SQL (Database basics)