SQL Command Types – DDL, DQL, DML, DCL & TCL

SQL Command Types

SQL Commands are categorized into DDL, DQL, DML, DCL & TCL. Learn the commands included in each of these categories with their purpose and explanation.

SQL Command Types

Structured Query Language (SQL) is used to create a database(s) and perform different operations on it. There is a huge list of operations that can be performed on databases and their tables. For each operation is there is some unique SQL command or a set of commands. For the sake of simplicity, these commands are categorized into a different set of commands on the basis of the specificity of their purpose.

These categories are listed below.

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

SQL Command Types – DDL, DQL, DML, DCL & TCL

Let’s discuss each of these categories in detail and also find out what particular SQL commands are included in each of these categories.

Note. The objects of a database include tables, functions, indexes, triggers, views, functions, store-procedures, etc. So, wherever I use the word objects, please keep in mind, it refers to these things.

1. DDL

Data Definition Language, as the name suggests is used to define the database. So, this category includes all the commands that are not just used to define the database but also to perform several other things that are related to the definition of the database. The operations belonging to the database schema comes into this category. This includes the following commands.

CREATE

The CREATE command is used to create a new database(s) or its objects.

DROP

The DROP command is used to delete the database objects.

ALTER

The ALTER command is used to restructure the database tables, such as to change the columns, their data types, and index keys.

TRUNCATE

The TRUNCATE command is used to empty a database table. The data of a table completely vanish but there is no effect on the structure of the database table.

COMMENT

Data Dictionary includes comments. The COMMENT command is used to add these comments in the Data Dictionary.

RENAME

The RENAME command is used to rename the database objects.

2. DQL

Data Query Language is used to query the information from the database. So, it includes the commands that are used to fetch the information from the database. There is particularly only one command that is used to fetch information from the database and its objects and that is the SELECT command.

SELECT

The SELECT Statement is used to select the desired column values from the Database and its tables.

SELECT DISTINCT

It is also the type of SELECT command that is used to select only the unique table records.

3. DML

Data Manipulation Language is used to manipulate the database and its objects. So, it includes all the commands that are used to add new data or modify existing database data. DML includes the following listed SQL commands.

INSERT

The INSERT command is used to insert new records in the database tables.

UPDATE

The UPDATE command is used to update existing database table records.

DELETE

The DELETE command is used to delete the records from a database table.

LOCK

The LOCK command is used to lock the privileges of reading or writing to a table.

MERGE

The MERGE command is used to merge the records of a database table.

4. DCL

Data Control Language is used to control the database rights from different database users. So, using the DCL commands one can manage which database and its objects should be accessible or modifiable by which users. These are so sophisticated that the database administrator can assign which particular SQL Statements a user can execute or not. Therefore, these commands allow database management with atomic level security. There are basically two DCL commands that are listed below.

GRANT

The GRANT command is used to give particular rights or privileges to the database user(s).

REVOKE

The REVOKE command is used to withdraw the rights or privileges from the database user(s).

5. TCL

Again as the name suggests, Transaction Control Language is used to control everything related to the transaction happening in accordance with the database(s). There are several different SQL commands that are used for transactions and they are listed below.

COMMIT

The COMMIT command is used to commit the transaction. In other, this command is used to store the changes performed by the transaction into the database.

ROLLBACK

The ROLLBACK command is used to re-do the changes performed by a transaction in case of an anomaly or error.

SAVEPOINT

The SAVEPOINT adds a checkpoint to the transaction process.

SET TRANSACTION

The different characters of a transaction can be defined using the SET TRANSACTION command.

I hope you find this guide useful. If so, do share it with others who are willing to learn SQL. If you have any questions related to this article, feel free to ask them in the comments section.

Also, don’t forget to Subscribe to WTMatter!

Subscribe To WTMatter!

Receive updates of our latest articles via email. Enter your email address below to get started.

Leave a Reply

Your email address will not be published. Required fields are marked *