Home » SQL

SQL | DDL, DML, TCL, and DCL Commands

DDL, DML, TCL, and DCL Commands in SQL: In this tutorial, we are going to learn about the DDL, DML, TCL, and DCL Commands in SQL (Structured Query Language).
Submitted by Abhishek Goel, on March 20, 2020

The most commonly used language to create, operate, update, manipulate and communicate with the database, usually a relational database, is called SQL (Structured Query Language). It is pronounced as "ees-que-el'' or "sequel". It is a standard language adopted for all RDBMS with 4GL(Fourth Generation Language) capability. It caters to most of the commonly used queries by the user on the database.

MySQL

MySQL is an open-source and freely available Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). It provides excellent features for creating, storing, maintaining, and accessing data, stored in the form of databases and their respective tables.

Being open-source software, MySQL can be freely and easily downloaded from www.mysql.org. It is fully secured, reliable and fast, and possesses far better functionalities than many other commercial RDBMSs available in the market.

Classification of SQL Statements

1. Data Definition Language (DDL) Commands
2. Data Manipulation Language (DML) Commands
3. Transaction Control Language (TCL) Commands
4. Data Control Language (DCL) Commands

1) Data Definition Language (DDL) Commands

A language that helps a user to define a database schema or database structure by a set of definitions is termed as Data Definition Language. It contains the necessary statements for creating, manipulating, altering and deleting the table. The compilation of DDL statements results in a set of tables that are stored in a special file known as Data Dictionary.

The DDL provides a set of definitions to specify the storage structure and access methods used by the database system. DDL Performs the following functions:
It defines the different types of database elements such as data-item, segment record, table, and databases.

• DDL provides data elements such as data-item-type, record-type, file-type, database, etc., with a unique name.
• It defines proper and relevant data types.
• It defines and specifies how the records are logically related to each other.

Data Definition Language (DDL) commands allow us to perform the tasks related to data definition or data structure.

These commands include:

• CREATE: This command is used to create database objects such as a database, table, view, etc.
• ALTER: This command is used to change or modify database objects such as a database, table, etc.
• DROP: This command is used to remove or delete database objects such as a database, table, etc.

2) Data Manipulation Language (DML) Commands

A Data Manipulation Language (DML) is a part of SQL that helps a user to access or manipulate data. The DML statements are executed in the form of queries which are handled by the DML compiler. It contains a set of statements to:

• Retrieve data from the tables of the database.
• Insert data into the tables of the database.
• Delete data from the tables of the database.
• Update data among the rows/records in the tables of the database.

DML commands carry out query processing operations and manipulate data in the database objects. Several DML commands available here:

• SELECT statement: To extract information from the table may or may not be on the basis of certain conditions/criteria.
• Insert INTO statement: To insert new data (record) into a table.
• UPDATE statement: To modify or change the data (tuple) in a table (not modifying the data type of column).
• DELETE: To delete data (tuple) from a table (not deleting a column).

DML can be further classified into two types:

1. Procedural DMLs: These commands provide the procedures for information retrieval, i.e., the user has to specify "what data" is needed and "how-to" fetch it.
2. Non-Procedural DMLs: These commands require a user to indicate "what to do" and not "how to do" as it is done by the DBMS through its own set of procedures.

3) Transaction Control Language (TCL) Commands

TCL is a set of commands used to control and manage the transaction control statements. It includes commands for specifying the beginning and ending of transactions. These commands keep a check on other commands and their effect on the database. These commands can nullify the changes made by other commands by rolling back to the original state. It can also make the changes permanent.

Transaction Control is done in four states:

• COMMIT: Whenever transaction is done in the table is permanent, i.e., changes are permanently saved in the tables/databases.
• ROLLBACK: It allows you to undo the transaction,i.e.,undo the changes.
• SAVEPOINT: To save the changes made by a transaction temporarily until commit commands get executed.
• SET TRANSACTION: Establishes properties for the current transactions. Thus, transactional language controls logical units of work.

4) Data Control Language (DCL) Commands

It contains the commands used for controlling and specifying user access to the data in relations and views. It also provides a command to grant and take back access rights/authority. These are also termed as Authorization checks.

For example, commands like GRANT, REVOKE, etc., are available in DCL.