my notes...

View: A view is a logical table based on a table or another view. It is like a window through which data from tables can be viewed or changed. It is stored as a SELECT statement in the data dictionary. It's definition can be viewed in the USER-VIEWS data dictionary table.

Advantages of Views & its purpose :

• To restrict data access.

• Makes complex queries easy.

• Provide data independence. One view can be used to obtain data from several tables .

• Obtain different views of the same data.

• Can be removed without effecting underlying data.

Views options :

• Simple view based on one table

• complex view based on more than 1 table or containing groups of functions

• can replace other views with the same name

• can contain a check constraint

• can be read only

SYNTAX for creating a VIEW:

CREATE [ OR REPLACE ] [ FORCE | NOFORCE ] VIEW viewname [(column alias[, column alias]...)]

AS subquery

[WITH CHECK OPTION] [CONSTRAINT constraint_name]

[WITH READ ONLY] [CONSTRAINT constraint_name];

Controlling column names by including aliases after the Create statement & prior to the subquery:

CREATE OR REPLACE VIEW empvw80 (id_number, name, sal, department_id)

AS

SELECT employee_id id_number, first_name||' '||last_name name , salary sal ,

department_id department_id

FROM employees

WHERE department_id=80;

Controlling column names by including aliases within the subquery:

CREATE OR REPLACE VIEW empvw80

AS

SELECT employee_id id_number, first_name||' '||last_name name , salary sal ,

department_id department_id

FROM employees

WHERE department_id=80;

Note : Using CREATE OR REPLACE a view can be created even when it already exists, thus replacing an existing one. This means that the view can be altered without dropping, re-creating and re-granting object privileges.

Creating complex views:

CREATE OR REPLACE VIEW dept_sum_vu (name, minimum, maximum,average)

AS

SELECT d.department_name, MIN(e.salary),Max(e.salary), AVG(e.salary)

FROM employees e, departments d

WHERE e.department_id=d.department_id

GROUP BY d.department_name;

Query a view just like any table :

SELECT * FROM empvw80;

Steps undertaken to executing a view based query:

• Retrieve view definition from USER_VIEWS

• check access privileges

• convert the view query into an equivalent operation on underlying base table(s).

DML operations on a VIEW:

Can't remove or modify or add a row if a view contains the following:

• GROUP funcions

• GROUP BY clause

• DISTINCT keyword

• Pseudocolumn ROWNUM keyword

Can't modify or add a row if

• columns defined by an expression (eg. Salary * 2)

Can't add a row if

• NOT NULL columns in the base table are not selected by the view

Data can be added into a view unless it contains any of the above listed items or there are NOT NULL columns without default values in the base table that are not selected by the view. All required values must be present in the view.

WITH CHECK OPTION :

• to ensure that DML operations performed on the view remain within the domain of the view.

Can be used for the following:

1. referential integrity checks

2. enforce constraints at database level

3. can also be used to protect data integrity but its use is very limited.

WITH READ ONLY : Denying DML operations.

• Attempt to delete using view throws Oracle Server Error: ORA-01752 : cannot delete from view without exactly one key-preserved table.

• Attempt to insert or update row using view throws Oracle Server Error :ORA-01733 :virtual column not allowed here.

SYNTAX for dropping a VIEW: DROP VIEW view-name;

Inline Views :

• An inline view is a sub-query with an alias.

• A named query in the FROM clause is an example of inline view.

• An inline view is not a schema object.

SELECT a.last_name, a.salary, a.department_id, b.maxsal
FROM employees a, (SELECT department_id, max(salary) maxsal FROM employees GROUP BY department_id) b
WHERE a.department_id=b.department_id
AND a.salary

SYNTAX for TOP-N Analysis: Used to display only the n top most or the n bottom-most records from a table based on a condition. These queries use a consistent nested query structure with following elements :

• a subquery or inline view to generate the sorted list of data. Use of ORDER BY clause to ensure that the ranking is in the desired order. For results retrieving the largest values a DESC parameter is needed.

• An outer query to limit the number of rows in the final result set. It includes a ROWNUM pseudocolumn which assigns a sequential value starting from 1 to each of the rows returned from the subquery. A WHERE clause, which specifies the n rows to be returned. The outer WHERE must use a < or <= operator.

SELECT [column list...], ROWNUM

FROM (SELECT [column list...]

FROM tablename

ORDER BY Top-N-column)

WHERE ROWNUM<=N;

eg:

SELECT ROWNUM as RANK, last_name,salary
FROM (SELECT last_name,salary from employees ORDER BY salary DESC)
WHERE ROWNUM<=3;

SET LONG 600;

SELECT * FROM user_views;