DDL and DML commands in SQL with examples should include definitions and difference between DDL Commands and DML commands in sql and important points. Also, 2 more command types TCL and DCL will be explained.
DDL Commands:
DDL means Data Definition Language. It is used to create and modify the structure of database objects in SQL. As a DDL example, lets say we are creating a table, index and or removing a table from a database and modifying a table i.e. modifying columns etc. then we use DDL commands.
DDL Commands in SQL Examples:
- CREATE – Creates objects e.g. table in the database
- ALTER – Alters objects of the database. e.g. modifying a column of a table
- DROP – Deletes objects from the database. e.g. remove table from a sql database.
- TRUNCATE – Deletes all records from a table and resets table identity to initial value.
DML Command:
DML Command in SQL database stands for Data Manipulation Language. DML commands are used to retrieve, store, Modify, delete, insert and update data in database. In fact, we are manipulating data that’s why it is called data manipulation language.
DML Command in SQL Examples:
- SELECT- This command or statement is used to retrieves data from a table
- INSERT – Inserts new data into a table
- UPDATE – Updates or modifies existing data into a table
- DELETE – Deletes records from a table
NOTE:
An interviewer here can raise a point that SELECT statement cannot manipulate data in sql database then how SELECT clause is data manipulation language?
The answer is that the SELECT is considered as a limited version of the data manipulation language as it cannot change the data in the SQL database. But, SELECT statement can perform operations on data retrieved from the database before the results are returned to the calling function.
NOTES
2 more command types are used in SQL i.e. TCL and DCL commands.
TCL Command:
TCL Stands for Transaction Control Language. These commands are used to manage different transactions occurring within a database. For example, consider the case of money deposit / withdrawal at Bank ATM. We entered the information, processed and get cash successfully then our account record will be updated and committed.But, in between if some process fails and not able to get cash then all process will be rollback and there will be no update in your account.
TCL Commands in SQL Examples:
- COMMIT – Saves work done in transactions
- ROLLBACK – Restores database to original state since the last COMMIT command in transactions
- SAVE TRANSACTION – Sets a save point within a transaction.
NOTES:
One more command type is DCL that stands for Data Control Language, and it is used to create roles, permissions, and referential integrity as well. It is used to control access to database by securing it.
DCL Commands in SQL Examples:
- GRANT – Gives user’s access privileges to database
- REVOKE – Withdraws user’s access privileges to database given with the GRANT command