Views

Simple Views and Complex Views

• A simple view
  Derives data from only one table
  No functions or groups
  DML operations through the view
  
• A complex view
  more than one table
  contains functions and groups
  DML operation might be limited
  

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

FORCE : creates the view regardless of whether or not the base table exist.
NOFORCE : create the view only if the base table exist. (default)
WITH CHECK OPTION : specifies that only those rows that are accessible to the view can be inserted or updated.
constraint : is the name assigned to the CHECK OPTION constraint
WITH READ ONLY : ensure that no DML operations can be performed on this view

CREATE VIEW empvu80
AS
SELECT employee_id, last_name, salary
FROM    employees
WHERE  department_id = 80;

- the subquery can contain complex SELECT syntax, including joins, groups, and subqueries.
- default name of the constraint is Sys_Cn
- REPLACE : drop and re-create.


Creating a view by using column alias in the subquery.

CREATE VIEW salvu50
AS
SELECT employee_id ID_NUMBER, last_name NAME,
            salary*12 ANN_SALARY
FROM    employees
WHERE  department_id = 50;

or

CREATE VIEW salvu50 (ID_NUMBER, NAME, ANN_SALARY)
AS
SELECT employee_id , last_name,
            salary*12
FROM    employees
WHERE  department_id = 50;