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
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;
MERGE
You can use merge command to perform
insert and update in a single command.
Ex:
SQL> Merge into student1 s1
Using
(select *From student2) s2
On(s1.no=s2.no)
When
matched then
Update set
marks = s2.marks
When not
matched then
Insert
(s1.no,s1.name,s1.marks)
Values(s2.no,s2.name,s2.marks);
In the above the two tables are with the same structure but
we can merge different structured tables also but the datatype of the columns
should match.
Assume that student1 has columns like no,name,marks and
student2 has columns like no,
name, hno, city.
SQL> Merge into student1 s1
Using
(select *From student2) s2
On(s1.no=s2.no)
When
matched then
Update set
marks = s2.hno
When not
matched then
Insert
(s1.no,s1.name,s1.marks)
Values(s2.no,s2.name,s2.hno);
We have table called DEPT with the following columns and
data
DEPTNO DNAME LOC
-------- -------- ----
10 accounting new york
20 research
dallas
30 sales
Chicago
40 operations boston
a) CREATE STUDENT TABLE
SQL> Create table student(no
number(2),name varchar(2),marks number(3));
b) MULTI
INSERT WITH ALL FIELDS
SQL> Insert all
Into
student values(1,’a’,100)
Into
student values(2,’b’,200)
Into
student values(3,’c’,300)
Select *
from dept where deptno=10;
-- This inserts
3 rows
c) MULTI INSERT WITH SPECIFIED FIELDS
SQL> insert all
Into
student (no,name) values(4,’d’)
Into
student(name,marks) values(’e’,400)
Into
student values(3,’c’,300)
Select
*from dept where deptno=10;
-- This inserts
3 rows
d) MULTI INSERT WITH DUPLICATE ROWS
SQL> insert all
Into
student values(1,’a’,100)
Into
student values(2,’b’,200)
Into
student values(3,’c’,300)
Select
*from dept where deptno > 10;
-- This inserts
9 rows because in the select statement retrieves 3 records (3 inserts for
each row
retrieved)
e) MULTI INSERT WITH CONDITIONS BASED
SQL> Insert all
When
deptno > 10 then
Into
student1 values(1,’a’,100)
When
dname = ‘SALES’ then
Into
student2 values(2,’b’,200)
When loc
= ‘NEW YORK’ then
Into
student3 values(3,’c’,300)
Select
*from dept where deptno>10;
-- This inserts 4 rows because the first condition
satisfied 3 times, second condition
satisfied
once and the last none.
f) MULTI INSERT WITH CONDITIONS BASED AND ELSE
SQL> Insert all
When
deptno > 100 then
Into
student1 values(1,’a’,100)
When
dname = ‘S’ then
Into
student2 values(2,’b’,200)
When loc
= ‘NEW YORK’ then
Into
student3 values(3,’c’,300)
Else
Into
student values(4,’d’,400)
Select
*from dept where deptno>10;
-- This inserts
3 records because the else satisfied 3 times
g) MULTI INSERT WITH CONDITIONS BASED AND FIRST
SQL> Insert first
When
deptno = 20 then
No comments:
Post a Comment