Lesson 23 : VIEWS

Views

Views are virtual tables and also called as logical tables. It consists  the data of one of more tables which are called base tables. Views can be based on actual tables or another view also.

When we perform INSERT,UPDATE,DELETE operations on views, it actually affect the base table of the view. You can treat views same as any other table.

Note : It does not consume any space in the database as it always refers its base tables.

Syntax :

CREATE  OR REPLACE VIEW <viewname>  AS  <select query>;

Example :

SQL> create view sample_view as select * from emp where location=’NEW YORK’ ;

Here the sample_view is created which refers all employee data from emp table with their location is NewYork.

Example 2 :

SQL> create view cs_students as select * from students_master where branch='COMPUTER SCIENCE'

DROP VIEW

Syntax :

DROP VIEW <view name>;

Example :

SQL> drop view sample_view;

The following are the restrictions on performing DML operations in any view,

If a view is defined by a query that contains SET or DISTINCT operators, a GROUP BY clause, or a group function, then rows cannot be inserted into, updated in, or deleted from the base tables using the view.

If a view is defined with WITH CHECK OPTION, then a row cannot be inserted into, or updated in, the base table (using the view), if the view cannot select the row from the base table.
If a NOT NULL column that does not have a DEFAULT clause is omitted from the view, then a row cannot be inserted into the base table using the view.
If the view iscreated by using an expression, such as DECODE, then rows cannot be inserted into or updated in the base table using the view.

Examples :

The following are views inwhich we can not perform DML operations

SQL> Create view trans_view as select trans_id, count(*) from master group by trans_id;

SQL>  create view  atm_list as select distinct atm_details from bank_master;