5 Types of SQL Commands: DML, DDL, DCL, TCL, DQL with Query Example

Last updated on December 23rd, 2022 at 01:40 am

There are five SQL commands supported in the SQL programming language to perform various database operations such as DML, DDL, DCL, TCL, and DQL commands.

SQL Commands

• SQL commands are instructions that are used to communicate with the database. They are also used to perform specific tasks, functions, and queries on the data.
• SQL commands allow you to do things like create a table, add data to tables, drop the table, modify the table, and set permissions for users.

Types of SQL Commands

The following is the list of five widely used SQL Commands.

• DDL (Data Definition Language)
• DML (Data Manipulation Language)
• DCL (Data Control Language)
• TCL (Transaction Control Language)
• DQL (Data Query Language)

Important SQL commands in DBMS.

What is SQL?

 SQL  stands for Structured Query Language. SQL is a language used to store, retrieve, and manipulate data in a Relational Database Management System (RDBMS) such as SQL Server, Oracle, MySQL, etc. SQL allows a user to communicate with the database using commands.

Features of Structured Query Language (SQL)

• SQL is used to create, truncate, and drop the database and table using SQL commands.
• SQL can be used to execute queries against the database to perform various operations.
• SQL is used to create a function, stored procedure, trigger, and view in a database.
• It provides security and allows users to set permissions on the tables, stored procedures, and views in the database.
• In SQL Server, Transaction Control Language(TCL) is an important element or feature of DBMS to control the transactions, TCL is used with the commands like commit, rollback, and savepoint.
• Security and authentication: SQL provides a mechanism to control and measure database security that includes authentication, the process of verifying if a user’s credentials match those stored in your database, and permitting only authenticated users access to your data, networks, and database platform.
• Limitation of access: A primary feature of database security is the effective limitation of access rights of your data to authenticated and legitimate users or applications.
  Limitations include what a user can access in your database. Access includes designing and granting appropriate user attributes and roles and limiting administrative privileges or access rights to the database.

What is an SQL Server database?

A database is a place to store data in an electronic format. It is an organized collection of database objects such as tables, views, functions, stored procedures, triggers, and so on.
It allows a user to easily access or manipulate the data.

01. Data Definition Language (DDL)

• DDL is the set of commands used for creating or modifying a database/table structure and schema.
• All DDL commands are auto-committed, which means they save all changes to the database permanently.

The following are some commands that fall under DDL:

• CREATE
• ALTER
• SP_RENAME
• TRUNCATE
• DROP

CREATE Command In SQL Server

CREATE command in SQL Server is used to create a new database object. It can be used to create a new table, view, function, stored procedure, etc. in the database.

Syntax:

CREATE TABLE Table_Name (
Column_Name1 Data_Type (Size),
Column_Name2 Data_Type (Size),
Column_NameN Data_Type (Size)
);

Example:

The following Create command will create a new Employee table in the database.

CREATE TABLE Employee
( 
    Id INT PRIMARY KEY,
    Name  VARCHAR(50), 
    Salary DECIMAL(18, 2) 
);

ALTER Command In SQL Server

ALTER command in SQL is used to add, modify, or delete, columns or constraints in an existing table.
This command is used to change the structure of an existing table in the database.

Syntax:

Alter syntax to add a new column in an existing table:

ALTER TABLE Table_Name ADD New_Column_Name Data_Type (Size);

Example:

The following Alter command is used to add a new column in the Employee table.

ALTER TABLE Employee ADD City VARCHAR(20) 

How to change the data type and size of an existing column?

Syntax:

ALTER TABLE Table_Name ALTER COLUMN Column_Name New_Data_Type (New_Size)

Example:

The following Alter statement is used to change the data type and size of an existing column named City.

ALTER TABLE Employee ALTER COLUMN City NVARCHAR (100);

How to delete existing column from a table using Alter command?

Syntax:

ALTER TABLE Table_Name DROP COLUMN Column_Name

Example:

The following Alter statement is used to drop an existing column City from the table.

ALTER TABLE Employee DROP COLUMN City;

SP_RENAME Command In SQL Server

In SQL Server SP_RENAME is basically a system-defined stored procedure, which can be used to change the column and table name.

How to rename the existing column name?

Syntax:

SP_RENAME 'Table_Name.Old_Column_Name','New_Column_Name' ;

Example:

The following SP_RENAME statement is used to change the existing column name from a table.

SP_RENAME 'Employee.Name','EmployeeName';

How to change the existing table name?

Example:

The following SP_RENAME statement is used to change the old table name to the new name.

SP_RENAME 'Employee','EmployeeDetails' ;

Truncate Command in SQL Server

The TRUNCATE command in SQL Server is to delete all the records from a table, but not the table itself. It doesn’t support the ‘where’ clause, that’s why we can’t delete a specific record.

Syntax:

TRUNCATE TABLE Table_Name

Example:

The following TRUNCATE statement is used to delete all the rows from a table.

TRUNCATE TABLE EmployeeDetails

DROP Command in SQL Server

The DROP command in SQL Server is used to remove an object from the database.
If we use the drop command on a table, the table including all the records and constraints will be removed from the database.

Syntax:

DROP TABLE Table_Name

Example:

The following DROP statement is used to delete the EmployeeDetails table from the database.

DROP TABLE EmployeeDetails ;

02. Data Manipulation Language (DML)

DML commands in SQL Server are used to manipulate or modify the data stored in the database. These commands can be used to insert, update, or delete the records from the database.

• INSERT
• UPDATE
• DELETE

INSERT Command In SQL Server

The INSERT command in SQL Server is used to add a new record in the table.

Syntax:

INSERT INTO Table_Name (Column_Name1, Column_Name2, ColumnN)
VALUES (Value1, Value2, ValueN);

Example:

The following INSERT statement is used to add a new row in the EmployeeDetails table.

INSERT INTO EmployeeDetails( Id, EmployeeName, Salary)
VALUES (101, 'Shekh Ali', 70000);

UPDATE Command In SQL Server

The UPDATE command in the SQL server is used to change or modify the existing records in a table.

Syntax:

--  Command to update the specific records based on where condition.

UPDATE Table_Name
SET Column_Name1 = Value1, Column_Name2 = Value2, ColumnN = ValueN
WHERE condition;

-- To update all the records for the particular columns

UPDATE Table_Name
SET Column_Name1 = Value1, Column_Name2 = Value2, ColumnN = ValueN;

Example:

The following UPDATE statement is used to modify an existing record in the EmployeeDetails table.

-- Update Salary based on Id
UPDATE EmployeeDetails SET Salary = 80000
WHERE Id = 101;

When updating records in a table, you should be extremely careful.
because if you run the update query without the where clause, the table will be updated with all of the records for the selected columns.

DELETE Command In SQL Server

The DELETE command in SQL Server is used to delete one or more existing records from a table.

Syntax:

-- To delete specic records

DELETE FROM Table_Name WHERE condition;

-- OR

-- To delete all the records

DELETE FROM Table_Name ;

Example:

The following DELETE statement is used to delete a specific record from the EmployeeDetails table.

DELETE FROM EmployeeDetails WHERE Id = 101 ;

The following DELETE statement will delete all the records from the EmployeeDetails table.

DELETE FROM EmployeeDetails ;

03. Data Control Language (DCL)

The DCL commands in SQL Server mainly deal with the right, permissions, and other security-related issues. It allowed a user to access the information from a database.

A user can grant various privileges on single or multiple tables. These permissions may be applicable for a user to use the commands such as SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, or ALL on a table.

Following is the list of the DCL Commands.

• GRANT
• REVOKE

GRANT Command In SQL Server

Grant command in SQL server is used for granting a user access privileges or other privileges to the database.

Syntax:

GRANT privileges ON object TO user;

For example, If we want to grant a user shekhali for the SELECT, INSERT, UPDATE, and DELETE privileges on a table called EmployeeDetails, we should run the following GRANT statement.

GRANT SELECT, INSERT, UPDATE, DELETE ON EmployeeDetails TO shekhali ;

• Grant user to create any table

GRANT CREATE ANY TABLE TO user

• Grant user to drop any table

GRANT DROP ANY TABLE TO user

REVOKE Command In SQL Server

REVOKE command is used to withdraw or take back some or all the user’s access privileges to the database given by using the GRANT command.

Syntax:

REVOKE privileges ON object FROM user;

Example:

REVOKE SELECT, INSERT, UPDATE, DELETE ON EmployeeDetails FROM shekhali ;

Following is the command to withdraw privileges from the user.

REVOKE CREATE, DROP TABLE FROM user

04. Transaction Control Language (TCL)

The TCL commands in SQL Server are used to manage the transaction and the changes made by DML statements like INSERT, UPDATE, and DELETE in a table.

The following are the TCL Commands:

• COMMIT
• ROLLBACK
• SAVEPOINT

COMMIT Command In SQL Server

Commit command in SQL Server is used to save all the changes or transactions permanently into the database.

After saving all the changes, Commit ends the current transaction and releases the transaction locks acquired on the tables.

Syntax:

COMMIT;

Example:

Let’s insert some records in the table.

BEGIN TRANSACTION

INSERT INTO EmployeeDetails(Id,EmployeeName,Salary) VALUES(102,'ALI',50000);
INSERT INTO EmployeeDetails(Id,EmployeeName,Salary) VALUES(103,'Klara',60000);

if(@@ERROR > 0)
BEGIN
  ROLLBACK TRANSACTION;
END
BEGIN
   COMMIT TRANSACTION;
END

Let’s run the above transactions to insert the records.

Let’s see the result by using the SELECT statement.

ROLLBACK Command In SQL Server

The Rollback command in SQL Server is used to undo or restore the database to the last committed state in case of any error.
After the rollback is done, it releases the transaction locks acquired on the tables.

Syntax:

ROLLBACK ;

Example:

The following transaction will Rollback due to a violation of the primary key.

Let’s insert some records with the same Id key that already exists in the table.

BEGIN TRANSACTION

INSERT INTO EmployeeDetails(Id,EmployeeName,Salary) VALUES(104,'Sam',30000);
INSERT INTO EmployeeDetails(Id,EmployeeName,Salary) VALUES(103,'David',40000);

if(@@ERROR > 0)
BEGIN
  ROLLBACK TRANSACTION;
END
BEGIN
   COMMIT TRANSACTION;
END

Save Transaction Command In SQL Server

The Savepoint command in SQL Server is used to save a transaction temporarily. So, that we can Rollback to a certain state or point whenever required.

Following is the syntax of the savepoint command.

SAVE TRANSACTION Savepoint_Name ;

Example:

BEGIN TRANSACTION 
INSERT INTO EmployeeDetails VALUES(104,'Sameer',30000);

 -- Create a savepoint after the first INSERT 
SAVE TRANSACTION A;

Update EmployeeDetails SET EmployeeName ='Priya' WHERE ID=104;

 -- Create a savepoint after the Update 
SAVE TRANSACTION B;

INSERT INTO EmployeeDetails VALUES(105,'David',40000);

 -- Create a savepoint after the Insert
SAVE TRANSACTION C;

INSERT INTO EmployeeDetails VALUES(106,'John',70000);

 -- Create a savepoint after the Insert
SAVE TRANSACTION D;

-- Rollback to the savepoint A
   ROLLBACK TRANSACTION A;

   COMMIT;

Let’s run the above transaction and see the result by using the SELECT Statement.

In the above result, we can see that the only record for the first insert statement is only committed because the transaction was Rollback to savepoint ‘A’ before the rest of the statements.

05. Data Query Language (DQL)

DQL (Data query language) is used to fetch the data from the database using a SELECT statement.

• The SELECT command is used to extract data from a database.
• It is used to select the attribute depending on the condition described by the WHERE clause.

Syntax:

SELECT expressions FROM Table_Name
WHERE conditions;

Example:

-- To show all the records
SELECT * FROM EmployeeDetails;

-- To show the records based on where condition
SELECT * FROM EmployeeDetails WHERE ID=101;

Example 2:

SELECT name,  
FROM student  
WHERE age > 15;  

Conclusion:

• SQL Server provides various DDL (Data Definition Language) commands to create and manipulate the database objects such as CREATE, ALTER, TRUNCATE, and DROP
• Using these commands a user can change the structure of the database objects.
• The DML (Data Manipulation Language) commands in SQL Servers are used to manipulate or modify the data stored in the database.
• These commands can be used to INSERT, UPDATE, or DELETE the records from the database.
• The DCL (Data Control Language) commands in SQL Server mainly deal with the right, permissions, and other security-related jobs. These commands are GRANT and REVOKE.

I hope you enjoyed it and found it helpful. Please leave a comment and share this post with others.

References: SQL Commands

Recommended Articles:

• Types of Joins in SQL Server
• Function vs Stored procedure in SQL Server
• Stored Procedure in SQL Server With Examples
• Create, Alter, and Drop Database In SQL Server
• SQL Comparison Operators (Less than, Greater than, Equal, Not Equal operators)
• Primary Key Constraint In SQL Server
• View In SQL | Types Of Views In SQL Server
• SQL Server Trigger Update, Insert, Delete Examples

• Author
• Recent Posts

Shekh Ali

Meet Shekh Ali, a skilled Software engineer and passionate blogger with an MCA degree and 8+ years of experience in the field. He enjoys sharing his expertise in C#, Java, SQL, Design Patterns, and other related topics through his writing. When he's not coding, Shekh Ali can often indulge in his passions for reading books and fitness training.

Latest posts by Shekh Ali (see all)

• SQL Server Indexing: Clustered vs Non Clustered Index Explained - March 26, 2023
• Mastering Database Normalization: Best Practices and Techniques - March 25, 2023
• CRM Databases: The Key to Enhanced Customer Engagement and Sales - March 23, 2023