What is SQL? Explain DQL, DML, DDL, DCL and TCL statements with examples.

Learn about SQL and categories of SQL commands like DQL, DML, DDL, DCL and TCL statements with specific command examples.

 

SQL: 

SQL stands for Structured Query Language. It is the language for database that helps retrieving, storing, and manipulation of data in a relational database.

All RDBMS like Oracle, SQL Server, MySQL, PostgresSQL, DB2 etc uses SQL as database language. NoSQL database don't use it.

SQL uses certain commands like Select, Create, Insert, Update, Drop, Truncate etc. to carry out the specific tasks in the database.

These SQL commands are primarily categorized into following categories:

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

Data Definition Language (DDL)

Data Definition Language or DDL commands are comprised of the SQL commands that are used to define the database schema and objects like tables, index, procedures, triggers etc. 

DDL commands are auto-committed, meaning that changes saved permanently in the database. 

Examples of DDL commands:

• CREATE, 
• DROP, 
• ALTER, 
• TRUNCATE, 
• COMMENT,
• RENAME 

Data Query Language (DQL) :

Data Query Language or DQL statements are used to retrieve data from database tables. It is also in some form works for inserting data.

Example of DQL commands:

• SELECT

 

Data Manipulation Language(DML): 

DML SQL commands are used to manipulate data in the database. 

DML commands are not auto-committed, meaning that changes have to committed explicitly and it can be roolback

Examples of DML commands:

• INSERT
• UPDATE
• DELETE

 

Data Control Language(DCL): 

DCL commands deal with the rights, permissions and other controls of the database system.

Examples of DCL commands:

• GRANT
• REVOKE

Transaction Control Language(TCL): 

TCL commands are used to manage the transaction at the database level. These operations are automatically committed in the database.

Examples of TCL commands:

• COMMIT
• ROLLBACK
• SAVEPOINT
• SET TRANSACTION