Types of SQL Commands

Structured Query Language is abbreviated as SQL. It is used to communicate with the database server through the database management system software in the form of queries.

SQL is a query language used to modify and retrieve desired information from the database table.

There are five types of SQL commands they are,

1. DDL,

2. DML,

3. DCL,

4. DQL and,

5. TCL

DDL (Data Definition Language)

DDL commands are used to define the schema (structure) of a table or a database.

DDL Commands are,

Create: This command is used to create a database or a table.

Syntax,

CREATE table table_name (column1 datatype, column2 datatype, column3 datatype...);

eg,

CREATE table Employee(Id int, Name varchar(50), Departmrnt varchar(10));

Here, Id, Name, and Department are the column or attributes of the table Employee and int, varchar(50), and varchar(10) are the corresponding datatypes of the attributes.

Alter: This command is used to add columns or modify the existing columns.

With the help of ALTER command,

• We can add a new column,

• We can modify the existing column properties.

Syntax,

ALTER table table_name ADD column_name datatype;  //To add a new column.
ALTER table table_name MODIFY column_name datatype;  //To modify the existing column.

eg,

ALTER table Employee ADD Salary int;
ALTER table Employee MODIFY Salary float;

Truncate: This command is used to delete the content of the entire table leaving the structure of the table.

Syntax,

TRUNCATE table table_name;

eg,

TRUNCATE table Employee;

Drop: This command is used to delete the entire table or a database including the structure of the table.

Syntax,

DROP table table_name;

eg,

DROP table Employee;

DML (Data Manipulation Language)

These commands are used to Manipulate the properties of a database table. That is, they are used to make changes within the table.

DML Commands are,

Insert: This command is used to insert data into the database table.

Syntax,

INSERT into table_name values (value1, value2, value3...);   (or)
INSERT into table_name (column1, column2, column3...) values (value1, value2, value3...);

eg,

INSERT into Employee values (1, Robert, Developer);    (or)
INSERT into Employee (Id, Name, Department) values (1, Robert, Developer);

Delete: This command is used to delete the data from the database table.

Syntax,

DELETE from table_name where condition;

eg,

DELETE from Employee where Id = 3;

Note: Not specifying the where clause condition in the delete query will delete all the records of the table.

Update: Used to update existing data from the database table.

Syntax,

UPDATE table_name SET column1 = value1, column2 = value2...) where condition;

eg,

UPDATE Employee SET Name = "Tony" WHERE Id = 2; 

Note: Not specifying the where condition will change all the records.

SET command is used to specify which column should be updated to which value.

DCL (Data Control Language)

These commands are used to give access to users with specific privileges, to access and query the database.

DCL commands are important in security perspective it deals with access control and giving certain permissions and privileges to users.

DCL commands are,

Grant: This command is used to give access to the database for specific users.

Syntax,

GRANT privilege_name on object_name
to {user_name | public | role_name} 

eg,

GRANT insert, delete, update,
select ON Employee to Parker;    

The above command gives Parker access to insert, delete, update, and query the Employee table.

Revoke: This command is used to revoke the access that is granted to the users.

Syntax,

REVOKE privilege_name on object_name
from {user_name | public | role_name}

eg,

REVOKE update, delete,
on  Employee from Parker;

The above command revokes or removes access to delete and update the records of the Employee table from Parker.

DQL (Data Query Language)

This command is used to query the database in order to display the desired data from the database table.

eg,

SELECT * from Employee;  // Used to display the entire table.

SELECT Id, Name from Employee where department = "Sales";  
//Used to display the Id and Name of employees who are in the sales department. 

TCL (Transaction Control Language)

These commands deals with the transaction and the commands used are,

Commit: It is a transactional command. The commit command is used to save the changes to the database. It saves all the transactions to the database.

COMMIT;

Savepoint: It is a point in a transaction in which we can roll the transaction back to a

certain point without rolling back the entire transaction.

We use the savepoint to roll back to the previous transactions in case of any error in the current transaction without affecting the entire transaction.

SAVEPOINT Savepoint_Name;

Rollback: It is the transactional command which goes to the previous state or it undoes the transactions that have already been made.

ROLLBACK;

Note: Once we commit the transaction we would not be able to roll back to the previous transaction state.

There are still a few SQL commands that are not listed here that are out of scope for this tutorial, but these are the most important ones. Comment below any other SQL command that you would like to add!

Do check our other posts on Cybersecurity and AI here.

Follow us on Instagram for the latest updates in Cybersecurity and AI.