Views in SQL

Views:

   Ø 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

Syntax: 

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.

Syntax: 

1.   Create view <viewname> as query

Example:

1. Create view empview

   As

   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.

Example:

1. Create view join_view

   As 

   Select empno,ename.dept.deptno

   From emp e, dept d

   Where e.deptno = d.deptno;

Restrictions:

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

Syntax:

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

             As

             Select column1,column2

             From base_table

             Where condition;

Example:

1.           Create view employee_view (e_no, e_name)

   As

             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.

Example: 

1. Create view read_view

   As

   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.

Syntax:           

1. DROP view <view name>;

Example:          

1. DROP view empview;

 

 

 

 

Share: