SQL: DML, DDL, DCL AND TCL

DML:

Data Manipulation Language-

  • This is required to access and manipulate data.
  • These statements do not implicitly commit the current transaction.

INSERT

UPDATE

DELETE

MERGE

SELECT

The SELECT statement is a limited form of DML statement in that it can only access data in the database. It cannot manipulate data in the database, although it can operate on the accessed data before returning the results of the query.

Make sure that you do explicit transactions unless you have a reason otherwise.

 

DDL:

Data Definition Language-

  • allows us to create, drop and modify objects.
  • Grant and revoke previleges and roles
  • Analyse information on a table, index or cluster
  • Makes structural changes to the data objects.

Examples,

CREATE

DROP

ALTER

TRUNCATE

COMMENT

RENAME

ALTER, CREATE AND DROP commands require exclusive access to the specified object. For example, an ALTER command will fail if the user has an open transaction on the specified table.

 

The GRANTREVOKEANALYZEAUDIT, and COMMENT commands do not require exclusive access to the specified object. For example, you can analyze a table while other users are updating the table.

Oracle Database implicitly commits the current transaction before and after every DDL statement.

 

TCS :

  • Transaction control statements manage changes made by DML statements.
  • USED TO GRANT PERMISSION AND ROLES
  • CREATING USERS
  • PRI

Transaction Control Statements 

Manage changes made by DML

COMMIT

ROLLBACK

SAVEPOINT

SET  TRANSACTION

Example of what the database does behind the scene with DDL:

For example, this is what happens when you drop a table:

COMMIT TRANSACTION

BEGIN TRANSACTION

DROP TABLE  TBL_STUDENTS

COMMIT TRANSACTION

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top