Views in SQL


   Ø View is a subset from one or more tables.

   Ø View is a virtual table, views contains rows and Columns.

   Ø We can present logical subsets or combination of data by creating views of tables.

   Ø A view is a logical table based on a table or another view.

 Advantages of views:

                   ·       Restricted Access to specific columns of a table there by                                                           providing additional security 

·       Oracle stores the definition of the view only

·       It simplifies queries

·       It avoids data redundancy

·       To hide the data complexity


  1.  Create view View_Name as

      Select clm1, clm2 ..clmn

      From Table_Name

      Where <\Condition>

Some types of views are,

1.    Simple view

2.    Join view

3.    Read only view

4.    Updatable view 

5.    Reasonably view

Simple view:

·       The definition of the view will contain the column names given in the query.


  1.   Create view <viewname> as query


  1. Create view empview


    Select ename, deptno, sal

    From emp;

Join View:

·       Joining of tables results in creation of join views. A join view is a view that is created using a join condition. There are certain in join views.


  1. Create view join_view


    Select empno,ename.dept.deptno

    From emp e, dept d

    Where e.deptno = d.deptno;


   Ø A view creation cannot contain on order by clause

   Ø If a view is defined with check option, a row cannot be inserted into or updated in the base table.

 Renaming the columns of view:

·       We can able to rename the column of the view but the column name should be meaningful.


  1.           Create view view_name (column1,column2,…)


              Select column1,column2

              From base_table

              Where condition;


  1.           Create view employee_view (e_no, e_name)


              Select empno,ename    

              From emp;

Describe the view:

  1. Desc employee_view;

 Read only views:

·       Certain views can be created only for selection purpose. Such views will not permit the user or the owner of the view to perform any DML operations.


  1. Create view read_view


    Select * from dept with read only;

Updatable views :

·       Views that allow data manipulation (insert, update, delete) are called updatable views.

Criteria for updatable views:

1.    The view must be created over a single base table.

2.    The view must include the primary key.

3.    The view should not contain any aggregate functions.

4.    The view should not contain any sub query in its select statement.

5.    The view should not contain DISTINCT, HAVING and GROUP BY clause in the SELECT statement.

6.    If the view is constructed over another view than the base view must be updated.

7.    The view should not contain any constants, strings and expressions in the SELECT statement.

8.    The view must contain all the NOT NULL columns of the base table.

Reasonably views:

·       Views that do not allow data manipulation are called reasonably views.

 Destroying views:

·       Views can be destroyed using the DROP view command.


  1. DROP view <view name>;


  1. DROP view empview;






No comments:

Post a Comment

Popular Posts

Search This Blog

Powered by Blogger.

Blog Archive

Service Support

Need our help to Learn or Post New Concepts Contact me

Blog Archive

Recent Posts

Service Support

Need our help to Learn or Post New Concepts Contact me