DDL on Views

Complex Views:
  • 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;

  • Create OR Replace View OrgDesignations As
            Select Job From Emp Where Deptno = 10
            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.

  • Create View DeptSalSummary
            (           DepartmentName,
As        Select
            From Emp E, Dept D
            Where E.Deptno = Deptno
            Group by D.Dname;
Using Views to create on the fly tables:
  • Create view insertDept10
            As Select * from emp where Deptno = 10;
Creating a table with data using a view:
  • 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
            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.
  • Drop view ViewName;
  • 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.
  • 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;