DDL, DML, DCL, TCL In SQL

DDL – Data Definition Language: statements used to define the database structure or schema. Some examples:

·       CREATE – To create objects in the database

·       ALTER – Alters the structure of the database

·       DROP – Delete objects from the database

·       TRUNCATE – Remove all records from a table, including all spaces allocated for the records are removed

·       COMMENT – Add comments to the data dictionary

·       RENAME – Rename an object

DML – Data Manipulation Language: statements used for managing data within schema objects. Some examples:

·       SELECT – Retrieve data from the a database

·       INSERT – Insert data into a table

·       UPDATE – Updates existing data within a table

·       DELETE – Deletes all records from a table, the space for the records remain

·       MERGE – UPSERT Operation (insert or update)

·       CALL – Call a PL/SQL or Java subprogram

·       EXPLAIN PLAN – Explain access path to the data

·       LOCK TABLE – Controls concurrency

DCL – Data Control Language: Some examples:

·       GRANT – Gives user’s access privileges to database

·       REVOKE – Withdraw access privileges given with the GRANT command

TCL – Transaction Control Language: statements used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

·       COMMIT – save work done

·       SAVEPOINT – Identify a point in a transaction to which you can later roll back

·       ROLLBACK – Restore database to original since the last COMMIT

·       SET TRANSACTION – Change transaction options like isolation level and what rollback segment to use.

Note: DML are not auto-commit. i.e. you can roll-back the operations, but DDL are auto-commit