SQL View

View

Views:-View is logical table. It never store data. Only query of view store in data dictionary. A view is a result of a query and treats it as a table. Therefore, a view can be thought of as a  stored query or a virtual table.

Creation of View:-Views can be created for a table,view or synonym only.

Syntax:- create or replace view as ;

Example:- create or replace view vemp as select empno,ename,job,sal,deptno from emp;

Example:-EMP table has several columns and thousands of records. If you want users to see only five of these columns or only specific rows,
         then you can create a view of that table for other users to access.

select * from emp; --records on base table
create or replace view vemp as select empno,ename,job,deptno from emp;--view created
select * from vemp; --information on view

Base Table:-View drived from some tables,those tables are called base tables.

Note:-you can define views with up to 1000 columns, just like a table. You can query views, and with some restrictions
      you can update, insert into, and delete from views. All operations performed on a view actually affect data in some base table of the view and are subject to the integrity constraints and triggers of the base tables.

Note:-You cannot explicitly define integrity constraints and triggers on views, but you can define them for the underlying base tables referenced by the view.

Storage for Views:-No need to allocate storage for view as view does not contain data.
                   view is defined by a query that extracts or derives data from the tables that the view references.
                   Views are very powerful because they allow you to tailor the presentation of data to different types of users.

Views are often used:-1) To provide an additional level of table security by restricting access to a predetermined set of rows or columns of a table
   Example:-STAFF view does not show the SAL or COMM columns of the base table EMP then need to create view.
2) To hide data complexity
   Example:- View might be defined with join of multiple tables. However, the view hides the fact that information actually originates from several tables.
3)To simplify statements for the user
  Example:- Views allow users to select information from multiple tables without actually knowing how to perform a join.
4) To present the data in a different perspective from that of the base table
  Example:- the columns of a view can be renamed without affecting the tables on which the view is based.
5) To isolate applications from changes in definitions of base tables
  Example:- if a view's defining query references three columns of a four column table, and a fifth column is added to the table, then the view's definition is not affected and all applications using the view are not affected.
6) To express a query that cannot be expressed without using a view
  Example:- View can be defined that joins a GROUP BY view with a table, or a view can be defined that joins a UNION view with a table.
7) To save complex queries
  Example:- Query can perform extensive calculations with table information. By saving this query as a view, you can perform the calculations each time the view is queried.

The Mechanics of Views :-Oracle stores a view's definition in the data dictionary as the text of the query that defines the view. When you reference a view in a SQL statement, Merges the statement
 that references the view with the query that defines the view .Parses the merged statement in a shared SQL area

Types of View:-
1) Updatable View :-We can perform DML operation on base table through this view.
2) Read only View :-We can't perform DML operation on base table through this view.

Read Only View:-Any view created with following clause, Set Operator, distinct,group by,with check option,decode,case will be read only view.

Note:- If all mandatory column (not null columns) are not selected in query then view will be read only.               

Example:-
SQL>create or replace view vemp as select deptno,count(*) count from emp group by deptno;
SQL> create or replace view vemp1 as select empno,ename,job,
                                            decode(deptno,10,'ACCOUNTING',20,'HR',30,'SALES','SUPPORT') Department  from emp;       
 SQL>create or replace view vemp2 as select * from emp where deptno=20 with check option;

Note:- any updateable view can be created as a read only by option "read_only"
SQL> create or replace view vemp3 as select * from emp read_only;

Updatable View:-Any view that has not satisfied above conditions is called updateable view as we can perform DML operation on base table through this view.
Example:- create or replace view vemp4 as select * from emp where sal>2000;

Complex view:-View, that is created with complex query is called complex view. most of the complex view are the read only views.

Force View:-We can create a view for those objects (table,view,synonym) which are not exists with help of "force" clause.This type of view is called force view.

Example:- 
SQL>create or replace force view fview as select * from xyz;           

Drop a View:-By drop statement we can drop a view.
Example:- 
SQL>drop view vemp;

Alter a View:-There is no alter statement on view ,only "or replace" clause can save new query for same view.        

Few restrictions of DML operations on Views are :-

We cannot DELETE/UPDATE/INSERT a row if the View contains the following :

1. Group Functions
2. A Group By clause
3. The Distinct Keyword
4. The Pseudo column ROWNUM Keyword

Practice Question :-
Q1 :- What is View ?
Q2 :- Why we are using View ?
Q3 :- What type of view available in Oracle ?
Q4 :- Can we create a View from a View or from Synonym ?
Q5 :- What is a Force View ?
Q6 :- What is the Check option View ?
Q7 :- What will be result, if base table dropped and Querying a View ?
Q8 :- Can we change query attached with View ?