1. DML (Data Manipulation Language):
These are used to manage records in the table. It includes the basic operations carried out on the tabular data like selecting few records, inserting new ones, deleting the unnecessary ones, and updating/modifying the existing ones. Following are different DML statements available in SQL:
- <SELECT> – retrieve data from the database
- <INSERT> – to insert data into a table
- <UPDATE> – it updates existing data within a table
- <DELETE> – to delete all records from a table
- <MERGE> – UPSERT operation (insert or update)
- <CALL> – to call a PL/SQL or Java subprogram
- <EXPLAIN PLAN> – define access path to data
- <LOCK TABLE> – control concurrency
2. DDL (Data Definition Language):
DDL statements are used to alter/modify a database or table structure and schema. These statements handle the design and storage of database objects. Following are different DDL statements available in SQL:
- <CREATE> – to create objects in the database
- <ALTER> – alters the structure of the database
- <DROP> – to delete objects from the database
- <TRUNCATE> – remove all records from a table. It also frees all the space allocated to them.
- <COMMENT> – add comments to the data dictionary
- <RENAME> – to rename an object.
3. DCL (Data Control Language):
DCL statements control the level of access that users have to the database objects. Following are different DCL statements available in SQL:
- <GRANT> – it gives access privileges to the user for the database
- <REVOKE> – to withdraw the access privileges given by GRANT command.
4. TCL (Transaction Control Language):
It allows you to control and manage transactions to maintain the integrity of data within SQL statements. Following are different TCL statements:
- <COMMIT> – to save the work
- <SAVEPOINT> – identify a point in a transaction to which you can rollback at a later point in time when required
- <ROLLBACK> – restore the database to original since the last COMMIT
- <SET TRANSACTION> – Change transaction options like isolation level and what rollback segment to use.