Types of SQL statements

     SQL statements are divided into 6 types. They are 

  1. Data Definition Language (DDL) Statements
  2. Data Manipulation Language (DML) Statements
  3. Transaction Control Statements
  4. Data Control Language (DCL) Statements
  5. Session Control Statements
  6. System Control Statement
1.Data Definition Language (DDL) Statements

   DDL statements are used for creating, modifying, and dropping the structure of database objects. Below are DDL statements.

  • CREATE :  Create new database/table.
  • ALTER  :   Modifies the structure of database/table.
  • DROP : Deletes a database/table.
  • TRUNCATE : Remove all records including allocated table spaces in table.
  • RENAME : Rename the database/table.

DDL Statements

2.Data Manipulation Language (DML) Statements

   DML statements are used for managing data within schema objects like retrieving, inserting, update and delete etc. . Below are DML statements.

  • SELECT : Retrieve data from table
  • INSERT : Insert data into a table
  • UPDATE : Update existing data within a table.
  • DELETE – Delete all records from a table. But not the allocated space, the space remain exists.
  • MERGE (UPSERT) : Used to INSERT new records or UPDATE existing records depending on condition matches or not.
  • LOCK TABLE – Lock one or more tables in a specified mode. Table access denied to a other users until your table operation completed.
  • CALL : Call a stored procedure.
  • EXPLAIN PLAN – Explain access path to data

DML Statements

3.Transaction Control Statements

   Transaction control statements manage changes made by DML statements i.e apply the changes permanently or restore to original. Below are Transaction Control Statements.

  • COMMIT : To apply the changes permanently.
  • ROLLBACK : Restore the changes since to last commit.
  • SAVEPOINT : Create SAVEPOINT for later use ROLLBACK.
  • SET TRANSACTION : Used to establish the current transaction as read-only or read/write, establish its isolation level, or assign it to a specified rollback segment.

TCL Statements

4.Data Control Language (DCL) Statements

   DCL statements control the level of access that users have on database objects. Below are DCL statements.

  • GRANT : Used to grant privileges to user on databases objects.
  • REVOKE : Used to take back privileges of  user on database objects.

DCL Statements

5.Session Control Statements

   Session control statements used to manage user session. Below are Session control statements.

  • ALTER SESSION : Used to modify parameters and conditions that are affect to your database connection.
  • SET ROLE : Used to enable or disable the roles for current session.

Session control statements

6.System Control Statement

   The single system control statement is ALTER SYSTEM, dynamically manages the properties of an Oracle Database instance.

System Control Statement