DBMS Views

 Views

A view is a virtual or logical table that allows to view or manipulate parts of the tables.To reduce REDUNDANT DATA to the minimum possible, Oracle allows the creation of an object called a VIEW. 
A View is mapped, to a SELECT sentence. The table on which the view is based is described in the FROM clause of the SELECT statement. 
Some Views are used only for looking at table data. Other Views can be used to Insert, Update and Delete table data as well as View data. If a View is used to only look at table data and nothing else the View is called a Read-Only View. A View that is used to look at table data as well as Insert, Update and Delete table data is called an Updateable View. 
The reasons why views are created are: 

  • When Data security is required .
  • When Data redundancy is to be kept to the minimum while maintaining data security .

Types of views :

  1. Read-only View : Allows only SELECT operations.
  2. Updateable View : Allows SELECT as well as INSERT , UPDATE and DELETE operations.


Creating a View :

The ORDER BY clause cannot be used while creating a view.  The columns of the table are related to the view using a one-to-one relationship.

Syntax:

CREATE <OR REPLACE> VIEW <ViewName> AS SELECT <ColumnName1 >, <ColumnName2> FROM <TableName> WHERE <ColumnName> = < Expression List> <WITH    READ ONLY> ;  

This statements creates a view based on query specified in SELECT statement.
OR REPLACE option recreates the view if it is already existing maintaning the privileges granted to view viewname.
WITH READ ONLY option creates readonly view.

Example :

Creating a view stu based on student table and than update it.
Run SQL Command Line
SQL>create view stu as select enroll,name from student;

View Created.

SQL>select * from stu;

ENROLL             NAME
-----------    ----------
       4866        ABCD
       4546        BDSG
 

Updateable Views :

Views can also be used for data manipulation .   Views on which data manipulation can be done are called Updateable Views.
When an updateable view name is given in an Insert Update, or Delete SQL statement, modifications to data in the view will be immediately passed to the underlying table.  
For a view to be updateable, it should meet the following criteria: 
  •  Views defined from Single table
  •  If the user wants to INSERT records with the help of a view, then the PRIMARY KEY column(s) and all the NOT NULL columns must be included in the view .
  •  The user can UPDATE, DELETE records with the help of a view even if the PRIMARY KEY column and NOT NULL column(s) are excluded from the view definition .

Example :

Run SQL Command Line
SQL>update stu set name='xyz' where enroll=4866;

1 Row updated.

SQL>select * from stu;

ENROLL             NAME
-----------    ----------
       4866        xyz
       4546        BDSG

Destroying a View :

The drop command drops the specified view.

Syntax :

DROP VIEW Viewname;

Example:

Run SQL Command Line
SQL>drop view stu;

View dropped.

Advantages of View :

  • Flexible enforcement of using Security.
  • Simplification of complex Query.


Share This Page on:


Ask Question

Advanced SQL