Complex Views:
Ex:
- Create View EmpInfo As Select
E.Empno
EmployeeID,
E.Ename
Name,
D.Deptno
DepartmentID,
D.Dname
DepartmentName,
From
Emp E, Dept D
Where
D.Deptno = E.Deptno
Order
by D.Deptno;
- Create View EmpGrade As Select
E.Ename
Name,
E.Sal
Basic, S.Grade Grade
From
Emp E,Salgrade S
Where
E.Sal Between S.Losal and S.Hisal
Order
by S.Grade;
- Create OR Replace View
EmpManagers As Select
RowNum
SerialNo,
Initcap(E.Ename)||’works
under’||M.Ename “Employee and Managers”
From
Emp E, Emp M
Where
E.Mgr = M.Empno;
- Create OR Replace View
EmpAccounts As Select
Ename,
Deptno, Sal Monthly, Sal * 12 Annual
From
Emp
Where
Deptno = (Select Depnto From Dept where Dname = ’ACCOUNTING’)
Order
by Annual;
- Create OR Replace View CumSum
As Select
B.Sal,
Sum(A.Sal) Cum_Sal
From
Emp A, Emp B
Where A.RowID
< = B.RowID
Group by B.RowID, B.Sal;
Group by B.RowID, B.Sal;
- Create OR Replace View
OrgDesignations As
Select
Job From Emp Where Deptno = 10
UNION
Select
Job From Emp Where Deptno IN(20,30);
Data Access Using Views:
Ø The steps or operations performed by
the oracle server, when data is accessed using a view are
o
Retrieves
the view definition from the data dictionary table USER_VIEWS.
o
Checks
the access privileges for views base table.
o
Converts
the view query into an equivalent operation on the underlying base table or tables.
Views in Data Dictionary:
Ø Once the view has been created, we
can query upon the data dictionary table called USER_VIEWS to see the name and
definition of the view.
Ø The text of the select statement
that constitutes the view is stored in a long column.
- Select
View_Name, Text From User_Views;
Modifying a View:
Ø Create or Replace option is used to
modify an existing view with a new definition.
Ø A view can be altered without dropping,
Recreating, and Re-granting object privileges.
Ø The Assigned column aliases in the
create view clause, are listed in the same order as the columns in the sub
query.
Creating Views with Columns Declarations:
Ø When a view is being created, we can
specify the names of the columns that it can project, along with the view’s
definition.
Ø The view in this case totally hides
the original names from the base table.
Ex:
- Create View DeptSalSummary
(
DepartmentName,
MinimumSalary,
MaxSalary,
AverageSalary,
SalarySum
)
As Select
D.Dname,
Min(E.Sal),
Max(E.Sal),
Avg(E.Sal),
Sum(E.Sal),
From
Emp E, Dept D
Where
E.Deptno = Deptno
Group
by D.Dname;
Using Views to create on the fly tables:
Ex:
- Create view insertDept10
As
Select * from emp where Deptno = 10;
Creating a table with data using a view:
Ex:
- Create table Dept10
As
Select * From insertDept10;
- Create view EmpGradeIns
As
Select Ename, Job, Sal, Grade From Emp E, SalGrade S
Where
E.Sal Between S.Losal and S.hisal;
- Create Table EmpGrades
(
Employee,
Designation,
BasicSalary,
Grade
)
As
Select
* From EmpGradeIns;
Dropping a View:
Ø The Drop view statement is used to
remove a view permanently.
Ø Dropping a view has no effect on the
tables upon which the view is created.
Ø Views or applications based on
deleted views become invalid.
Ø We need Drop any view privilege to
remove the views from data dictionary.
Syntax:
- Drop view ViewName;
Example:
- Drop view InsertDept;
Inline Views:
Ø An Inline view is a sub query with
an alias (Called as correlation name), that can be used within a SQL statement.
Ø An Inline view is similar to using a
named sub query in from clause of the main query.
Ø An Inline view is not a schema
object.
Ø An Inline view in from clause of a
select statement defines a data source for the select statement.
Ex:
- Select E1.Ename, E1.Sal,
E1.Deptno, E2.Maxsal From Emp E1,
(Select
Deptno, Max(sal) maxsal From Emp Group by Deptno ) E2
Where
E1.Deptno = E2.Deptno And E1.Sal < E2.Maxsal;