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