DCL & Alias

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 sudha;         -- you can give individual privilege

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

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

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

     SQL> select * from saketh.student;

     The sudha 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 sudha with grant option;             

     Now sudha 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 sudha; -- you can revoke individual privilege

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

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

USING ALIASES

CREATE WITH SELECT

We can create a table using existing table [along with data].

Syntax:

    Create table <new_table_name> [col1, col2, col3 ... coln] as select * from

                                                               <old_table_name>;

Ex:

    SQL> create table student1 as select * from student;

    Creating table with your own column names.

    SQL> create table student2(sno, sname, smarks) as select * from student;

    Creating table with specified columns.

    SQL> create table student3 as select no,name from student;

    Creating table with out table data.

    SQL> create table student2(sno, sname, smarks) as select * from student where 1 = 2;

    In the above where clause give any condition which does not satisfy.

INSERT WITH SELECT

Using this we can insert existing table data to a another table in a single trip. But the table structure should be same.

Syntax:

     Insert into <table1> select * from <table2>;   

Ex:

     SQL> insert into student1 select * from student;

     Inserting data into specified columns

     SQL> insert into student1(no, name) select no, name from student;

COLUMN ALIASES

Syntax:

     Select <orginal_col> <alias_name> from <table_name>;

Ex:

     SQL> select no sno from student;

or

     SQL> select no “sno” from student;

TABLE ALIASES

If you are using table aliases you can use dot method to the columns.

Syntax:

     Select <alias_name>.<col1>, <alias_name>.<col2> … <alias_name>.<coln> from

                                                       <table_name> <alias_name>;

Ex:

     SQL> select s.no, s.name from student s;