SQL All DDL, DML and TCL Commands with examples

USING DML

USING UPDATE

This can be used to modify the table data.

Syntax:

     Update <table_name> set <col1> = value1, <col2> = value2 where <condition>;

Ex:

     SQL> update student set marks = 500;

     If you are not specifying any condition this will update entire table.

     SQL> update student set marks = 500 where no = 2;

     SQL> update student set marks = 500, name = 'Venu' where no = 1;

USING DELETE

This can be used to delete the table data temporarily.

Syntax:

    Delete <table_name> where <condition>;

Ex:

     SQL> delete student;

     If you are not specifying any condition this will delete entire table.

     SQL> delete student where no = 2;

USING DDL

USING ALTER

This can be used to add or remove columns and to modify the precision of the datatype.

a) ADDING COLUMN

    Syntax:

        alter table <table_name> add <col datatype>;

    Ex:

        SQL> alter table student add sdob date;

b) REMOVING COLUMN

    Syntax:

        alter table <table_name> drop <col datatype>;

    Ex:

         SQL> alter table student drop column sdob;

c) INCREASING OR DECREASING PRECISION OF A COLUMN

    Syntax:

          alter table <table_name> modify <col datatype>;

    Ex:

          SQL> alter table student modify marks number(5);

          * To decrease precision the column should be empty.

d) MAKING COLUMN UNUSED

    Syntax:

         alter table <table_name> set unused column <col>;

    Ex:

         SQL> alter table student set unused column marks;

   

         Even though the column is unused still it will occupy memory.

d) DROPPING UNUSED COLUMNS

    Syntax:

        alter table <table_name> drop unused columns;

    Ex:

        SQL> alter table student drop unused columns; 

        * You can not drop individual unused columns of a table.

e) RENAMING COLUMN

    Syntax:

        alter table <table_name> rename column <old_col_name> to <new_col_name>;

    Ex:

        SQL> alter table student rename column marks to smarks; 

USING TRUNCATE

This can be used to delete the entire table data permanently.

Syntax:

      truncate table <table_name>;

Ex:

     SQL> truncate table student;

USING DROP

This will be used to drop the database object;

Syntax:

     Drop table <table_name>;

Ex:

     SQL> drop table student;

USING RENAME

This will be used to rename the database object;

Syntax:

     rename <old_table_name> to <new_table_name>;

Ex:

     SQL> rename student to stud;

USING TCL

USING COMMIT

This will be used to save the work.

Commit is of two types.

Ø  Implicit

Ø  Explicit

a) IMPLICIT

     This will be issued by oracle internally in two situations.

Ø  When any DDL operation is performed.

Ø  When you are exiting from SQL * PLUS.

b) EXPLICIT

     This will be issued by the user.

     Syntax:

                Commit or commit work;

              * When ever you committed then the transaction was completed.

USING ROLLBACK

This will undo the operation.

This will be applied in two methods.

Ø  Upto previous commit

Ø  Upto previous rollback

Syntax:

Roll or roll work;

Or

Rollback or rollback work;

* While process is going on, if suddenly power goes then oracle will rollback the transaction.

USING SAVEPOINT

You can use savepoints to rollback portions of your current set of transactions.

Syntax:

     Savepoint <savepoint_name>;

Ex:

     SQL> savepoint s1;

     SQL> insert into student values(1, ‘a’, 100);

     SQL> savepoint s2;

     SQL> insert into student values(2, ‘b’, 200);

     SQL> savepoint s3;

      SQL> insert into student values(3, ‘c’, 300);

     SQL> savepoint s4;

      SQL> insert into student values(4, ‘d’, 400);

    

     Before rollback

     SQL> select * from student;

        NO NAME      MARKS

        ---  -------     ----------

         1          a         100

         2          b         200

         3          c         300

         4          d         400

          

     SQL> rollback to savepoint s3;

                                    Or

     SQL> rollback to s3;

    

    This will rollback last two records.

     SQL> select * from student;

        NO NAME      MARKS

        ---  -------     ----------

         1          a         100

         2          b         200

 

 USING DCL

DCL commands are used to granting and revoking the permissions.

USING GRANT

This is used to grant the privileges to other users.

Syntax:

     Grant <privileges> on <object_name> to <user_name> [with grant option];

Ex:

     SQL> grant select on student to Rama;          -- you can give individual privilege

      SQL> grant select, insert on student to Rama;        -- you can give set of privileges

     SQL> grant all on student to Rama;                -- you can give all privileges

    

     The Rama user has to use dot method to access the object.

     SQL> select * from Prasad.student;

     The Rama user can not grant permission on student table to other users. To get this type of

     option use the following.

     SQL> grant all on student to Rama with grant option;             

     Now Rama user also grant permissions on student table.

USING REVOKE

This is used to revoke the privileges from the users to which you granted the privileges.

Syntax:

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

Ex:

     SQL> revoke select on student form Rama;  -- you can revoke individual privilege

     SQL> revoke select, insert on student from Rama; -- you can revoke set of privileges

      SQL> revoke all on student from Rama;       -- you can revoke all privileges