SQL statements are divided into 6 types. They are
- Data Definition Language (DDL) Statements
- Data Manipulation Language (DML) Statements
- Transaction Control Statements
- Data Control Language (DCL) Statements
- Session Control Statements
- 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.
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
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.
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.
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.
6.System Control Statement
The single system control statement is ALTER SYSTEM, dynamically manages the properties of an Oracle Database instance.