SQL Statements Part3 (DRL,TCL and DCL)....

In this post I want to discuss about DRL, TCL and DCL statements.

DRL ::

   DRL means Data Retrieval Language. This will be used for the retrieval of the data from the database.

In order to see the data present in the database, we will use DRL statement. We have only one DRL

statement.

             SELECT is the only DRL statement in SQL.

SELECT ::

   Select statement is used to see the data present in the database. Syntax for writing this statement is,

    SELECT col_list FROM tab_name;

 If we want to see the data present in all the columns of the table,then we can use following syntax,

    SELECT * FROM tab_name;

 Ex ::

 To check data present in all the columns of Emp table,

 SELECT * FROM Emp;

To check data present in specific columns of the table, we will use first syntax.

 Ex ::

  SELECT empno,ename FROM Emp;

In this I am just checking data present in empno and ename columns.

TCL ::

    TCL means Transaction Control Language. These statements are used to control the transactions made by the DML statements to the database. Any change which is made to the database by a DML statement will be called as a transaction. We need to control these transactions made by DML statements.

    Whatever change made to the database by a DML statement will not reside permanently in the database, unless auto commit option enabled in the session. Those changes will reside in temporary memory. So, we need to handle these changes.

    TCL statements will be used for this purpose. We have 3 TCL statements available.

                      1 COMMIT
                      2 ROLLBACK
                      3 SAVEPOINT

 1. COMMIT ::

     Commit will be used to make the changes made by DML statements permanent to the database. After performing DML operations , if we issue commit then those changes will be made permanent to the database. If Auto commit option for session is enabled, then this is not required as commit takes place automatically.

Note : DDL statements will carry implicit commit. So, if we issue DDL statement against the database, after doing some DML operations , then due to implicit commit of DDL, previous DML statements also will be commited as commit of DDL applies for the session till that point.

 COMMIT;

2. ROLLBACK ::


     Rollback will be used to discard the changes made by DML statements to the database. If auto commit option enabled in the session , then there is no use with Rollback statements as transactions will be commited automatically.

    So, If we give Rollback then any pending changes to the database which are made by DML statements will be discarded.

  ROLLBACK;

3. SAVEPOINT ::

     To Rollback the changes to a certain transaction point, we will use SAVEPOINT. I will discuss about this in future posts while discussing PL/SQL.

DCL ::


    DCL means Data Control Language. These statements are used for controlling the access to database objects for users. Generally these statements will be used by DBA people to control the access of users to the database.

   We have 2 DCL statements available in SQL.

         1. GRANT
         2. REVOKE

1. GRANT ::


      Grant will be used for giving permissions on the database to users. Using this we can give any type of permissions to the users on the database.

 For ex if we want to give Select permission on all the database tables to a user,

  GRANT SELECT ANY TABLE TO user_name;

If we want to give select permission on specific table then

  GRANT SELECT ON tab_name TO user_name;

If we want to give Create table permission then,

   GRANT CREATE TABLE TO user_name;

Like this we can give any privilege to the users on the database.

2. REVOKE ::

     Revoke will be used to remove the permissions which are granted previously to the users on the database.

   IF we want to remove permissions given in the above statements, then we can use following statements.

    REVOKE SELECT ANY TABLE FROM user_name;

    REVOKE SELECT ON tab_name FROM user_name;

    REVOKE CREATE TABLE FROM user_name.

Like this using DCL statements we can control the access of users to the database.


Difference Between DELETE and TRUNCATE ::


---> Delete is DML where as Truncate is DDL.
---> Delete requires transaction control as it is DML. Truncate does not require transaction control as it is
        DDL
---> If we remove data using DML, then we can get back it by giving Rollback (if commit not done). But
        we cannot get the data removed by Truncate (auto commit).
---> Using Delete we can remove specific rows (using where clause) or total rows also. But using Truncate
        we can remove entire rows only. (we cannot use where clause with truncate).
---> Truncate is faster than Delete.