Friday, March 29, 2013

What are the difference between DDL, DML DCL and TCL commands?

Following difference between DDL, DML DCL and  TCL commands



Data definition language(DDl)
DDL statements are used to build and modify the structure of your tables and other objects in the database. When you execute a DDL statement, it takes effect immediately.

  1. CREATE - to create objects in the database
  2. ALTER - alters the structure of the database
  3. DROP - delete objects from the database
  4. TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
  5. COMMENT - add comments to the data dictionary
  6. RENAME - Rename an object/Table

Data Manipulation Language(DML)
DML statements are used to work with the data in tables. When you are connected to most multi-user databases (whether in a client program or by a connection from a Web page script), you are in effect working with a private copy of your tables that can’t be seen by anyone else until you are finished (or tell the system that you are finished). You have already seen the SELECT statement; it is considered to be part of DML even though it just retreives data rather than modifying it.

  1. SELECT - retrieve data from the a database
  2. INSERT - insert data into a table
  3. UPDATE - updates existing data within a table
  4. DELETE - deletes all records from a table, the space for the records remain
  5. MERGE - UPSERT operation (insert or update)
  6. CALL - call a PL/SQL or Java subprogram
  7. EXPLAIN PLAN - explain access path to data
  8. LOCK TABLE - control concurrency
Data Control Language (DCL)
DCL is Stand for  Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

  1. GRANT - gives user's access privileges to database
  2. REVOKE - withdraw access privileges given with the GRANT command

Transactional Control Language(TCL)
TCL is Stand for Transactional Control Language. It is used to manage different transactions occurring within a database.
  1. COMMIT - save work done
  2. SAVEPOINT - identify a point in a transaction to which you can later roll back
  3. ROLLBACK - restore database to original since the last COMMIT
  4. SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use 
 ROLLBACK is used for revoking the transactions until last commit.
COMMIT is used for commiting the transactions to the database.
Once we commit we cannot rollback. Once we rollback we cannot commit.
Commit and Rollback are generally used to commit or revoke the transactions that are with regard to DML commands.



NOTE:
DML commands can't be rollback when a DDL command is executed immediately after a DML. DDL after DML means "auto commit". The changes will return on disk not on the buffer. If the changes return on the buffer it is possible to rollback not from the disk


1 comment: