Saturday, April 15, 2006

Oracle - Views & Top-N Analysis

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 :

Views options :

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:

DML operations on a VIEW:

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

Can't modify or add a row if

Can't add a row if

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 :

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.

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

Inline Views :

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 :

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;


Comments: Post a Comment



<< Home
Advertisements

eBay.in - The World's Online Marketplace

ARCHIVES
  • March 2006
  • April 2006
  • May 2006
  • June 2006
  • July 2006
  • August 2006
  • September 2006

This page is powered by Blogger. Isn't yours?

Recent Postings

Google