DDL, DML, DQL, DCL, TCL in SQL

Data Definition Language (DDL)

The commands of SQL that are used to create database objects, alter the structure of the database objects and delete database objects from database are collectively called as DDL. Examples include Create, Alter , Drop, Truncate, Rename and Comment Commands.

Create

Create command is used to create database and its Objects like tables, index, stored procedure, views , triggers, functions and etc.

Example

To create Employee table.

  1. create table tblEmployee(  
  2.    Id int primary key identity(1,1) not null,  
  3.    Name nvarchar(50) ,  
  4.    Gender nvarchar(50) ,  
  5.    Salary int ,  
  6.    DepartmentId int ,  
  7. )  
Alter

Alter command is used to create database and its Objects.

Drop

Drop command is used to delete objects from database.

Truncate

Trunctae Table command is used to remove all records from a table, including all spaces allocated for records are removed.

Rename

It is used to rename the objects.

Comment

// -> Single line Comments, /* --Multi Line Comments-- */ used to comment the sql statements.

Data Manipulation Language (DML)

The commands of SQL that are used to insert data into the database, modify the data of the database and to delete data from the database are collectively called as DML. Examples include Insert, Update and Delete.

Insert

To insert date into a table.

Update

To update the existing data in a table.

Delete

delete all records from a table.

Data Query Language (DQL)

The commands of SQL that are used to retrieve data from the database are collectively called as DQL. So all Select statements comes under DQL.

Select

To retreive data from the database table.

Data Control Language (DCL)

The commands of SQL that are used to control the access to data stored in the database are collectively called as DCL and examples include Grant and Revoke.

Grant

All users access previleges to database.

Revoke

Withdraw users access previleges given by using the Grant command.

Transaction Control Language (TCL)

The commands of SQL that are used to control the transactions made against the database are collectively called as TCL and examples include Commit, Rollback and Savepoint.

Commit

Commit is used for the permanent changes. When we use Commit in any query then the change made by that query will be permanent and visible. We can't Rollback after the Commit.

Rollback

Rollback is used to undo the changes made by any command but only before a commit is done. We can't Rollback data which has been committed in the database with the help of the commit keyword.

Save point

creates points within groups of transactions in which to ROLLBACK.