Monday 3 December 2012

Examlpes For Complex Views

Here are few examples of complex views in Oracle,
a)Create Or Replace View Dept_Sum_Vu (Name,Minsal,Maxsal,Avgsal)
As Select D.Dname,Min(E.Sal),Max(E.Sal),Avg(E.Sal) From Emp E,Dept D Where E.Deptno=D.Deptno Group By D.Dname Order By D.Dname; 
View created.
Select * From Dept_Sum_Vu;
NAME              MINSAL    MAXSAL    AVGSAL
-------------- --------- --------- ---------
ACCOUNTING          1300      5000 2916.6667
RESEARCH             800      3000      2175
SALES                950      2850 1566.6667

b)Create Or Replace View Dept_Sum_Vu (Name) As Select D.Dname From Dept D Order By D.Dname;
View created.
Select * From Dept_Sum_Vu;
NAME
--------------
ACCOUNTING
OPERATIONS
RESEARCH
SALES

c)Select * From Dept_Sum_Vu,Dept Where Dept_Sum_Vu.Name=Dept.Dname; (This Is A Join, Of View And Table)
NAME              DEPTNO DNAME          LOC
-------------- --------- -------------- -------------
ACCOUNTING            10 ACCOUNTING     NEW YORK
OPERATIONS            40 OPERATIONS     BOSTON
RESEARCH              20 RESEARCH       DALLAS
SALES                 30 SALES          CHICAGO

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...

ShareThis