SQL - VIEWS

VIEWS

VIEW is a logical representation of another table or combination of tables. A view derives its data from the tables on which it is based. These tables are called base tables. Base tables might in turn be actual tables or might be views themselves. All operations performed on a view actually affect the base table of the view.

A view can be thought of as a stored query or a virtual table. You can use views in most places where a table can be used.
For example, the employees table has several columns and numerous rows of information. 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.

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.

You cannot explicitly define triggers on views, but you can define them for the underlying base tables referenced by the view. Oracle does support definition of logical constraints on views.

Unlike a table, a view is not allocated any storage space, nor does a view actually contain data. Rather, a view is defined by a query that extracts or derives data from the tables that the view references. These tables are called base tables. Base tables can in turn be actual tables or can be views themselves (including materialized views). Because a view is based on other objects, a view requires no storage other than storage for the definition of the view (the stored query) in the data dictionary.

USES OF VIEWS

1) Provide an additional level of table security by restricting access to a predetermined set of rows or columns of a table.

2) Hide data complexity
For example, a single view can be defined with a join, which is a collection of related columns or rows in multiple tables. However, the view hides the fact that this information actually originates from several tables.

3) Simplify statements for the user
For example, views allow users to select information from multiple tables without actually knowing how to perform a join.

4) Present the data in a different perspective from that of the base table
For example, the columns of a view can be renamed without affecting the tables on which the view is based.

5) Save complex queries
For example, a 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.

Creating view

Syntax:

CREATE [OR REPLACE] [FORCE] [NOFORCE] VIEW view_nameAS subquery[WITH CHECK OPTION [CONSTRAINT constraint]]

FORCE : creates the view regardless of whether the view's base tables exist or the owner of the schema containing the view has privileges on them.

NOFORCE : creates the view only if the base tables exist and the owner of the schema containing the view has privileges on them. The default is NOFORCE.

PREREQUISITES:
To create a view in your own schema, you must have CREATE VIEW system privilege. To create a view in another user's schema, you must have CREATE ANY VIEW system privilege.

Example:

To create a view CSStudent (computer science students) from the Student table, enter:

SQL> CREATE OR REPLACE VIEW CSStudent AS SELECT * FROM Student WHERE Major = 'CS';

Eg2:
The following statement creates a view on a subset of data in the emp table:

CREATE VIEW sales_staff AS SELECT empno, ename, deptno FROM emp WHERE deptno = 10 WITH CHECK OPTION CONSTRAINT sales_staff_cnst;

Creating Views with check option

The query that defines the sales_staff view references only rows in department 10. Furthermore, the CHECK OPTION creates the view with the constraint (named sales_staff_cnst) that INSERT and UPDATE statements issued against the view cannot result in rows that the view cannot select. For example, the following INSERT statement successfully inserts a row into the emp table by means of the sales_staff view, which contains all rows with department number 10:

INSERT INTO sales_staff VALUES (7584, 'OSTER', 10);

However, the following INSERT statement returns an error because it attempts to insert a row for department number 30, which cannot be selected using the sales_staff view:

INSERT INTO sales_staff VALUES (7591, 'WILLIAMS', 30);

The view could optionally have been constructed specifying the WITH READ ONLY clause, which prevents any updates, inserts, or deletes from being done to the base table through the view. If no WITH clause is specified, the view, with some restrictions, is inherently updatable.

Different types of VIEWS

1) Simple Views

2) Complex Views

Simple Views:

The view which is created by selecting only one table.A Simple view does not contain functions.You can always perform DML operations through Simple views.

2)Complex Views:

The view which is created by selecting more than one tables. A Complex view contains functions.You cannot always perform DML through Complex views.

Rules for performing DML Operations on a view:

1) You can perform DML operations on Simple views.

2) You cannot always perform DML through Complex views.

3) You cannot delete a row if the view contains the following:

Group functions such as SUM, MIN, MAX, AVG and ......A GROUP BY clause.The DISTINCT keyword.

4) You cannot update data in a view if it contains,
Group functions such as SUM, MIN, MAX, AVG and ......A GROUP BY clause.The DISTINCT keyword.Columns defined by expressions such as SALARY*1.2The ROWNUM pseudo column.

5) You cannot insert data in a view if it contains,
Group functions such as SUM, MIN, MAX, AVG and ......A GROUP BY clause.The DISTINCT keyword.Columns defined by expressions such as SALARY*1.2The ROWNUM pseudo column.There are NOT NULL columns in the base tables that are not selected by the view.


Updating a Join View

An updatable join view (also referred to as a modifiable join view) is a view that contains more than one table in the top-level FROM clause of the SELECT statement, and is not restricted by the WITH READ ONLY clause.

The rules for updatable join views are as follows.

1) Any INSERT, UPDATE, or DELETE operation on a join view can modify only one underlying base table at a time.

2) All updatable columns of a join view must map to columns of a key-preserved table. If the view is defined with the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are not updatable.

3) Rows from a join view can be deleted as long as there is exactly one key-preserved table in the join.

4) An INSERT statement must not explicitly or implicitly refer to the columns of a non-key-preserved table.