ad

Views

Ø  It is logical table based on one or more tables or views.
Ø  A view in practically contains no data by itself.
Ø  The tables upon which a view is based are called as base tables.
Prerequisites:
Ø  Should have create view or create any view system privilege.
Ø  To create a sub view, we need under any view system privilege or under object privilege on the super view.
Ø  The owner of the schema should have select, insert, update and delete rows from all tables or views on which the view is based.
Ø  The above privileges must be granted through privileges directly, rather than a role.
Syntax:
Create [OR Replace] [{Force/Noforce}] View ViewName
            [(AliasName[,AliasName … ])]
            As SubQuery
            [With { CHECK OPTION / Read Only}]
            [Constraint ConstraintName];
OR Replace:
Ø  Specifies the view has to be replaced lf already existing.
Force:
Ø  Specifies the view has to be created even if the base tables do not exist.
Noforce:
Ø  Specifies the view not be created if the base table does not exist, which is default.

Aliasname:
Ø  Specifies the name of an alias for an expression in the sub query.
With Check Option:
Ø  Specifies that only rows that would be retrieved by the sub query can only be inserted, updated or deleted.
Constraint:
Ø  Constraint_name specifies the name of the constraint as with check option or read only constraint.
With Read Only:
Ø  Specifies that rows must only be read from the base tables.
Restrictions:
Ø  If a view has instead of triggers, then all sub views created on it must have instead of triggers, even if the views are inherently updatable.
Ø  An alias cannot be specified when creating an object view.
Types of Views:
Ø  Simple views
Ø  Complex views
Simple Views:
Ø  Which contains a sub query that retrieves data from only one base table.
Complex Views:
Ø  Contains a sub query that can perform any of these actions.
Ø  Retrieving from multiple base tables.
Ø  Groups rows using a group by or distinct clause.
Ø  Contains a function call. 

Simple Views:
Ex:
  • Create view Employees As Select
            Empno “ID Number”,
            Ename Name,
            Sal “Basic Salary”,
            Job Designation
                        From Emp;
Selecting Data from a View:

  • Select Name, Designation From Employees;
  • Select “ID Number”, Name, “Basic Salary” * 12 From Employees;
  • Select “ID Number”, Name, to_char(“Basic Salary”,’99,99,999.99’) Monthly, “Basic Salary” * 12 Annual From Employees where “Basic Salary” > 2500;