What is View ?

VIEW:

• It is just window for a table
• It is the logical object for the physical collection of tables

Mainly we use views for

• Limiting the no of rows and columns
• reducing network
• better security and encapsulation

Restrictions on view:

• An index cannot be created on a view
• A view cannot be contain on ORDER BY clause
• Derived and aggregated columns must be assigned a alias name
• A view cannot be used for UPDATE operations, if it contains
• Data from more then one table (join view)
• The same column specified twice
• Derived columns (sal/12)
• A Distinct clause
• A group by clause

Creating View : 
CREATE VIEW <view_name> AS 
SELECT <column-name> [AS <alias-name>]
           [....,<column-name> [AS <alias-name>]]
           FROM <table-name>
           [WHERE <conditional-tests>]
           [WITH CHECK OPTION];
OR
CREATE VIEW may be abbreviated as CV
CV <view-name> [(<alias-name>, <alias-name>,...)]  AS
SELECT <column-name> [AS <alias-name>]
           [....,<column-name> [AS <alias-name>]]
           FROM <table-name>
           [WHERE <conditional-tests>]
           [WITH CHECK OPTION];
The employee table is used to demonstrate the use of views
Emp table - contains 13 rows.

empno	ename	job	mgr	hiredate	sal	comm	deptno
7788	SCOTT	ANALYST	7566	19/04/1987	3000	0	20
7782	CLARK	MANAGER	7839	09/06/1981	2450	0	10
7521	WARD	SALESMAN	7698	22/02/1981	1250	500	30
7698	BLAKE	MANAGER	7839	01/05/1981	2850	0	30
7902	FORD	ANALYST	7566	03/12/1981	3000	0	20
7839	KING	PRESIDENT	0	17/09/1981	5000	0	10
7900	JAMES	CLERK	7698	03/12/1981	950	0	30
7654	MARTIN	SALESMAN	7698	28/09/1981	1250	1400	30
7934	MILLER	CLERK	7782	23/01/1982	1300	0	10
7566	JONES	MANAGER	7839	02/04/1981	2975	0	20
7844	TURNER	SALESMAN	7698	08/09/1981	500	0	30
7499	ALLEN	SALESMAN	7698	20/02/1981	1600	300	30
7876	ADAMS	CLERK	7788	23/05/1987	1100	0	20

The following creates a view to return the emp table in deptno 20. It limits the view to and empno,ename,and sal. This view restricts columns that is job,mgr,hiredate,comm and rows not for deptno 20.

CREATE VIEW emp_20_v as 

SELECT empno as empolyeenumber,ename as employeename,sal*12 as "Annual Salary" from emp where deptno=20

empolyeenumber	employeename	Annual Salary
7788	SCOTT	36000
7566	JONES	35700
7902	FORD	36000
7876	ADAMS	13200

In the above view creation, we written alias name all columns in select statement. But this is another way to written alias names in crate view statement. Let us see how we write,

CREATE VIEW EMP_10_V (EMP_NO,EMP_NAME,EMP_SAL) AS

SELECT EMPNO,ENAME,SAL FROM EMP 

WHERE DEPTNO=10 
These two tables are used in the following examples

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17/12/1980	800	0	20
7499	ALLEN	SALESMAN	7698	20/02/1981	1600	300	30
7521	WARD	SALESMAN	7698	22/02/1981	1250	500	30
7566	JONES	MANAGER	7839	02/04/1981	2975	0	20
7654	MARTIN	SALESMAN	7698	28/09/1981	1250	1400	30
7698	BLAKE	MANAGER	7839	01/05/1981	2850	0	30
7782	CLARK	MANAGER	7839	09/06/1981	2450	0	10
7788	SCOTT	ANALYST	7566	19/04/1987	3000	0	20
7839	KING	PRESIDENT	0	17/11/1981	5000	0	10
7844	TURNER	SALESMAN	7698	08/09/1981	1500	0	30
7876	ADAMS	CLERK	7788	23/05/1987	1100	0	20
7900	JAMES	CLERK	7698	03/12/1981	950	0	30
7902	FORD	ANALYST	7566	03/12/1981	3000	0	20
7934	MILLER	CLERK	7782	23/01/1982	1300	0	10

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

The following view performs the join
create view emp_dept_v as

select ename as emp_name

,sal as emp_sal

,dname as dept_name

,loc as dept_loc from emp inner join dept

on emp.deptno=dept.deptno

The next select references the view to perform the join 

select * from emp_dept_v order by emp_sal

emp_name	emp_sal	dept_name	dept_loc
TURNER	500	SALES	CHICAGO
JAMES	950	SALES	CHICAGO
ADAMS	1100	RESEARCH	DALLAS
WARD	1250	SALES	CHICAGO
MARTIN	1250	SALES	CHICAGO
MILLER	1300	ACCOUNTING	NEW YORK
ALLEN	1600	SALES	CHICAGO
CLARK	2450	ACCOUNTING	NEW YORK
BLAKE	2850	SALES	CHICAGO
JONES	2975	RESEARCH	DALLAS
FORD	3000	RESEARCH	DALLAS
SCOTT	3000	RESEARCH	DALLAS
KING	5000	ACCOUNTING	NEW YORK

Another common use for views is to summarize data. Instead of creating an actual table and storing the data twice.

The following creates a view to perform the aggregation of emp by each department.

CREATE VIEW AGG_V AS
SELECT DEPTNO
,SUM(SAL) TOTALSAL
,AVG(SAL) AVARAGESAL
,MIN(SAL) MINIMUMSAL
,MAX(SAL) MAXIMUSAL 
FROM EMP
GROUP BY DEPTNO
The view can then be used to aggregate the columns that are created as a result of an aggregate,as seen below
SEL SUM(TOTALSAL) FROM AGG_V
SEL AVG(AVARAGESAL) FROM AGG_V
Deleting View : 
DROP VIEW [<database-name>]<View-name>
It removes the view name, column names and SELECT from the DD
Modifying View:
The syntax of the REPLACE VIEW follows
REPLACE VIEW [<database-name>]<view-name>
AS SELECT <column-name>
                 [...<column-name>]
FROM <table-name>
[WHERE <conditional-tests>]
[WITH CHECK OPTION];
REPLACE VIEW AGG_V AS
SELECT DEPTNO
,SUM(SAL) TOTALSAL
,AVG(SAL) AVARAGESAL
,MIN(SAL) MINIMUMSAL
,MAX(SAL) MAXIMUSAL
FROM EMP
GROUP BY DEPTNO
WHERE DEPTNO BETWEEN 10 AND 20
Notice that the keyword REPLACE appears instead of the original CREATE and the WHERE clause is changed form the original CREATE VIEW statement.