Ø 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;