Mastering SQL for Data Science: A Comprehensive Guide Day-7

Mastering SQL for Data Science: A Comprehensive Guide Day-7

DCL & TCL Commands

v DCL (Data Control Language):-

      Data Control Language provides users with privilege commands. The owner of the database object has the authority over them. Following commands are categorized as data control language commands;

1. Grant Command: When we create any database object we are the owner of that object. The grant command gives the facility so that we can share an object with privileges. We can restrict other users to perform certain operations on your object. Objects are logical storage structure like tables, views, sequences, indexes, synonyms etc.

Syntax: Grant <privileges>  on <object_name>  to <user_name>;

We can specify privileges like select, update, insert, delete. We can specify all to grant all privileges.

E.g:  grant insert, update, delete, select on emp to smith;

2. Revoke Command:

Revoke command is used to withdraw the privileges that have been granted to a user using    grant command.

Syntax: Revoke <privileges>  on <object_name>  from <user_name>;

E.g:  revoke insert, update,  select on emp from smith;

• TCL(Transactional Control Language):

All the changes made to the database can be referred as a transaction. A transaction begins with DML commands but ends explicitly with transactional control language.

1. Commit:

This command is used at an end of any transaction. Only with the help of commit commands transactions changes can be made permanent to the database. This command also erases all the save point and releasing transaction locks.

Syntax: Commit

E.g: Commit

Remember that all the DDL commands are also auto committed but you have to commit all the DML commands explicitly.

2. Save point:

Save point command is used to mark very lengthy also transaction into smaller parts. They are used to identify a point a transaction to which we can later rollback.

Syntax: Save point <Save point1>;

E.g: Save point s1;

3. Rollback:

Rollback command undoes the work done in the current transaction from the starting or last commit. When we perform rollback it will rollback up to last commit or specified save point.

Syntax: Rollback or Rollback to save point <save point_name>;

E.g: Rollback to save point s1;

It wills rollback transaction up to save point s1. It will roll backed all the transactions from save point to last transaction.