Types Of SQL Statements With Examples

What is SQL?

SQL refers to Structured Query Language, as it is the special purpose domain specific language for querying the data in Relational Database Management System (RDBMS).

Microsoft SQL Server, MySQL, Oracle etc. use SQL for querying with slight syntax differences.



Types of SQL statements

SQL statements are categorized into four different type of statements, which are

1. DML (DATA MANIPULATION LANGUAGE)
2. DDL (DATA DEFINITION LANGUAGE)
3. DCL (DATA CONTROL LANGUAGE)
4. TCL (TRANSACTION CONTROL LANGUAGE)

Let's see one by one.



DML

In Data Manipulation Language(DML), we have four different SQL statements.

1. SELECT
   
   Select statement is used to select the collection of records from the table, which is based on some condition.
   
   E.g. select * from student – Get all the records of student table.
   
   Select * from student where rank>5 – Get the records with the condition where students' rank is greater than 5.
   
   
2. INSERT
   
   Insert statement is used to insert the set of values into the table.
   
   E.g. Insert into Student (Rank, StudentName, Mark) Values(1,’Kumar’,450)
   
   
3. UPDATE
   
   Update statement is used to update the existing values in the table, which is based on some condition.
   
   E.g., update student set StudentName=’Manoj’ where StudentName=’Kumar’
   
   The query given above will update the studentName from Manoj to Kumar where student Name Kumar.
   
   
4. DELETE
   
   Delete statement is used to delete the existing record in the table, which is based on some condition.
   
   Eg., Delete from Student where StudentName=’Manoj’
   
   The query given above will delete the record which has StudentName Manoj.

DDL

In Data Definition Language (DDL), we have three different SQL statements.

1. CREATE
   
   Create statement is used to create the new table in an existing database.
   
   Eg., Create Table Student (Rank Int,StudentName varchar(50),Mark Float)
   
   
2. ALTER
   
   Alter statement can add a column, modify a column, drop a column, rename a column or rename a table.
   
   Eg., Alter Table Student Add (StudentAddress varchar (100))
   
   
3. DROP
   
   SQL DROP TABLE statement is used to remove a table definition and all the data, indexes, triggers, constraints and permission specifications for the table.
   
   Eg, Drop Student

DCL

In Data Control Language(DCL), it defines the control over the data in the database. We have two different commands, which are

1. GRANT
   
   Grant is allowed to do the specified user to the specified tasks.
   
   Syntax
   
   GRANT privilege_name
   ON object_name
   TO {user_name |PUBLIC |role_name}
   [WITH GRANT OPTION];
   
   
2. REVOKE
   
   It is used to cancel previously granted or denied permissions.
   
   Syntax
   
   REVOKE privilege_name
   ON object_name
   FROM {user_name |PUBLIC |role_name}

TCL

In Transaction Control Language (TCL), the commands are used to manage the transactions in the database. These are used to manage the changes made by DML statements. It also allows the statements to be grouped together into logical transactions.

1. COMMIT
   
   Commit command is used to permanently save any transaction into the database.
   
   Syntax Commit;
   
   
2. ROLLBACK
   
   Rollback command is used to restore the database for the last committed state. It’s also used with save point to jump to the save point.
   
   Syntax
   
   Rollback to save point name
   
   
3. SAVEPOINT
   
   Save point command is used to temporarily save a transaction, so that you can roll back to that point whenever necessary.
   
   Syntax
   
   savepointsavepoint-name;
   

These are the different types of statements in SQL language. I hope, this will be helpful for the readers. Thanks for reading.