SQL SERVER – Example of DDL, DML, DCL and TCL Commands, difference between DDL, DML and DCL commands?

Data Manipulation Language (DML) statements are used for managing data within schema objects. It is used to retrieve, store, modify, delete, insert and update data in database.

SELECT – it is used to retrieve data from a table

INSERT - it is used to insert data into a table

UPDATE – it is used to update existing data into a table

DELETE –it is used to delete all records from a table

MERGE - UPSERT operation (insert or update)

CALL - call a PL/SQL or Java subprogram

EXPLAIN PLAN - explain access path to data

LOCK TABLE - control concurrency

Data Definition Language (DDL) statements are used to define the database structure or schema. It is used to create and modify the structure of database objects in database.

CREATE – it is used to creates objects in the database

ALTER – it is used to alter objects of the database

DROP – it is used to delete objects of the database

TRUNCATE – it is used to delete all records from a table and resets table identity to initial value.

COMMENT - add comments to the data dictionary

RENAME - rename an object

Data Control Language (DCL) statements. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

GRANT –it gives user’s access privileges to database

REVOKE –it Withdraws user’s access privileges to database given with the GRANT command

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

COMMIT – Saves work done in transactions

ROLLBACK – Restores database to original state since the last COMMIT command in transactions

SAVE TRANSACTION – Sets a savepoint within a transaction