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.

SQL Language

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.

SQL Language

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.

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now