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.
- create table tblEmployee(
- Id int primary key identity(1,1) not null,
- Name nvarchar(50) ,
- Gender nvarchar(50) ,
- Salary int ,
- DepartmentId int ,
- )
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.