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.
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