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.
- DDL (Data Definition Language)
- DQL (Data Query Language)
- DML (Data Manipulation Language)
- DCL (Data Control Language)
- TCL (Transaction Control Language)
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.
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.
The CREATE command is used to create a new database(s) or its objects.
The DROP command is used to delete the database objects.
The ALTER command is used to restructure the database tables, such as to change the columns, their data types, and index keys.
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.
Data Dictionary includes comments. The COMMENT command is used to add these comments in the Data Dictionary.
The RENAME command is used to rename the database objects.
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.
The SELECT Statement is used to select the desired column values from the Database and its tables.
It is also the type of SELECT command that is used to select only the unique table records.
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.
The INSERT command is used to insert new records in the database tables.
The UPDATE command is used to update existing database table records.
The DELETE command is used to delete the records from a database table.
The LOCK command is used to lock the privileges of reading or writing to a table.
The MERGE command is used to merge the records of a database table.
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.
The GRANT command is used to give particular rights or privileges to the database user(s).
The REVOKE command is used to withdraw the rights or privileges from the database user(s).
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.
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.
The ROLLBACK command is used to re-do the changes performed by a transaction in case of an anomaly or error.
The SAVEPOINT adds a checkpoint to the transaction process.
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!