Views:
views are called as virtual or logical table for the base table It will create with the help of "select statement"
If we have no privilege's to run the views then we need to grant them as follows:
syntax:
grant <role name> to <username>;
role name --- connect
resource
create view
Types of Views:
Simple view or updatable view:
when we access the required data from a single base table .it can support all DML Operations(Insert,update,delete)
Syntax:
create view < view name> as select * from <Table name>
Example:
SQL> create view rkemp as select eno,ename from emp420;
View created.
SQL> select * from rkemp;
ENO ENAME
---------- -----
11 raju
22 rani
33 vani
44 kani
Subquery:
SQL> select * from emp420;
ENO ENAME EADD
---------- ----- -----
11 raju vsp
22 rani hyd
33 vani hyd
44 kani vzm
SQL> select * from dept420;
DNO DNAME ENO
--- ----------- ----------
d1 hr 11
d2 it 22
d3 mrk 44
SQL> select eno from dept420 where dname='hr';
ENO
----------
11
SQL> select ename from emp420 where eno=(select eno from dept420 where dname='hr');
ENAME
-----
raju
How to change the column field size and display page of a given table:
column field size:
SQL> desc emp420;
Name Null? Type
----------------------------------------- -------- ----------------------------
ENO NUMBER(2)
ENAME VARCHAR2(5)
EADD VARCHAR2(5)
SQL> ALTER TABLE emp420 RENAME COLUMN eadd TO eaddress;
Table altered.
SQL> select * from emp420;
ENO ENAME EADDR
---------- ----- -----
11 raju vsp
22 rani hyd
33 vani hyd
44 kani vzm
SQL> column eaddress format a10;
SQL> select * from emp420;
ENO ENAME EADDRESS
---------- ----- ----------
11 raju vsp
22 rani hyd
33 vani hyd
44 kani vzm
display page size :
SQL> select * from emp420,dept420;
ENO ENAME EADDRESS DNO DNAME ENO
---------- ----- ---------- --- ----- ----------
11 raju vsp d1 hr 11
22 rani hyd d1 hr 11
33 vani hyd d1 hr 11
44 kani vzm d1 hr 11
11 raju vsp d2 it 22
22 rani hyd d2 it 22
33 vani hyd d2 it 22
44 kani vzm d2 it 22
11 raju vsp d3 mrk 44
22 rani hyd d3 mrk 44
33 vani hyd d3 mrk 44
ENO ENAME EADDRESS DNO DNAME ENO
---------- ----- ---------- --- ----- ----------
44 kani vzm d3 mrk 44
12 rows selected.
SQL> set pagesize 24;
SQL> select * from emp420,dept420;
ENO ENAME EADDRESS DNO DNAME ENO
---------- ----- ---------- --- ----- ----------
11 raju vsp d1 hr 11
22 rani hyd d1 hr 11
33 vani hyd d1 hr 11
44 kani vzm d1 hr 11
11 raju vsp d2 it 22
22 rani hyd d2 it 22
33 vani hyd d2 it 22
44 kani vzm d2 it 22
11 raju vsp d3 mrk 44
22 rani hyd d3 mrk 44
33 vani hyd d3 mrk 44
44 kani vzm d3 mrk 44
12 rows selected.
No comments:
Post a Comment