Views, Oracle

What is View?
A view is a logical table based on a table or another view. A view contains no data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called base tables. The view is stored as a SELECT statement in the DD.

Advantages of views;

->View restrict access to data bcz the view can display selective columns from the table.
->View can be used to make simple queries to retrieve the results of complicated queries.For example, view can used to query information from multiple tables without the user knowing how to write a join statement.
-> Views can be used to retrieve data from several tables.

There are two types:
simple views
Complex views

A simple view is

- You can retrieve the data one table;
- don't contains functions
- you can perform DML operations through the view

A complex view

- Retrieve the data many tables
- it can be contains functions
- Doesn't allow the DML operations through the view.

syntax for view

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[,alias]...)]
as subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];

in this syntax

REPLACE - re create the view if it already exists
FORCE - create the view regardless of whether or not the base table exist
NOFORCE - create the view only if the base table exist(this automatically default)
alias - you can use alias in your subquery
check - condition
with READ only - it is ensures that no DML operation can be performed on this view.

e.g CREATE VIEW EMP10 AS SELECT EMPNO,ENAME,SAL FROM EMP WHERE DEPTNO =10;

Using ALIAS names in subquery

CREATE VIEW EMP10 AS SELECT EMPNO EMP_ID, ENAME EMPLOYEE, SAL*12 A_SAL FORM EMP WHERE DEPTNO=10;

Creating Complex View;

CREATE VIEW DEP_SUM (DNAME,MINSAL,MAXSAL) AS SELECT D.DNAME,MIN(E.SAL),MAX(SAL),AVG(SAL) FROM EMP E,DEPT D WHERE
E.DEPTNO=D.DEPTNO GROUP BY D.DNAME;


We can't remove a row if the view contains the Group functons,Group by clause, DISTINCT keyword, pseudocolumn keyword.

Removing view

Drop VIEW view;