12 creating views
This presentation is the property of its rightful owner.
Sponsored Links
1 / 25

12 Creating Views PowerPoint PPT Presentation


  • 59 Views
  • Uploaded on
  • Presentation posted in: General

12 Creating Views. Important Legal Notice: Materials on this lecture are from a book titled “Oracle Education” by Kochhar, Gravina, and Nathan (1999), published by Oracle Corp. For further information, visit www.oracle.com

Download Presentation

12 Creating Views

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


12 creating views

12 Creating Views

Important Legal Notice:

Materials on this lecture are from a book titled “Oracle Education” by Kochhar, Gravina, and Nathan (1999), published by Oracle Corp.

For further information, visit www.oracle.com

This presentation must be used for only education purpose for students at Central Washington University which is a member of Oracle Academic Initiatives (OAI) and has used Oracle systems for HRIS & Accounting Systems as a database platform for its PeopleSoft ERP system, since 1999.


Objectives

Objectives

After completing this lesson, you should be able to do the following:

  • Describe a view

  • Create a view

  • Retrieve data through a view

  • Alter the definition of a view

  • Insert, update, and delete data through a view

  • Drop a view


Objectives1

Objectives

After completing this lesson, you should be able to do the following:

  • Describe an inline view

  • Perform “Top-N” Analysis


Database objects

Database Objects


What is a view

What Is a View?

EMP Table

EMPNO ENAME JOBMGR HIREDATE SALCOMM DEPTNO ----------- ----------- --------------- ------- ---------------- ------------- --------- ----------

20

30

30

30

30

30

30

7900 JAMES CLERK 7698 03-DEC-81 950 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 30

EMPVU10 View

EMPNO ENAME JOB ---------- ---------- ----------------- 7739KING PRESIDENT 7782CLARK MANAGER 7934MILLERCLERK


Why use views

Why Use Views?

  • To restrict data access

  • To make complex queries easy

  • To allow data independence

  • To present different views of the same data


Simple views and complex views

Simple Views and Complex Views


Creating a view

Creating a View

  • You embed a subquery within the CREATE VIEW statement.

  • The subquery can contain complex SELECT syntax.

  • The subquery cannot contain an ORDER BY clause.

CREATE [OR REPLACE] [FORCE/NOFORCE] VIEW view

[(alias[,alias]…)]

AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY];


Creating a view1

Creating a View

  • Create a view, EMPVU10, that contains details of employees in department 10.

  • Describe the structure of the view by using the SQL*Plus DESCRIBE command.

SQL> CREATE VIEWempvu10

2AS SELECTempno, ename, job

3FROMemp

4WHEREdeptno = 10;

View created.

SQL> DESCRIBE empvu10


Creating a view2

Creating a View

  • Create a view by using column aliases in the subquery.

  • Select the columns from this view by the given alias names.

SQL> CREATE VIEWsalvu30

2AS SELECTempno EMPLOYEE_NUMBER, ename NAME,

3sal SALARY

4FROMemp

5WHEREdeptno=30;

View created.


Retrieving data from a view

Retrieving Data from a View

SQL> SELECT *

2FROMsalvu30;

EMPLOYEE_NUMBERNAMESALARY

7698BLAKE2850

7654MARTIN1250

7499ALLEN1600

7844TURNER1500

4900JAMES950

7521WARD1250


Querying a view

Querying a View

SQL*Plus

SELECT*

FROMempvu10;

7839 KING PRESIDENT

7782 CLARK MANAGER

7934 MILLER CLERK

USER_VIEWS

EMPVU10

SELECTempno, ename, job

FROMemp

WHEREdeptno-10;

EMP


Modifying a view

Modifying a View

  • Modify the EMPVU10 view by using CREATE OR REPLACE VIEW clause. Add an alias for each column name.

  • Column aliases in the CREATE VIEW clause are listed in the same order as the columns in the subquery.

SQL>CREATE OR REPLACE VIEW EMPVU10

2(employee_number, employee_name, job_title)

3AS SELECTempno, ename, job

4FROMemp

5WHEREdeptno-10;

View created.


Creating a complex view

Creating a Complex View

SQL>CREATE VIEWdept_sum_vu

2(name, minsal, maxsal, avgsal)

3 AS SELECTde.dname, MIN(e.sal),MAX(e.sal),

4AVG(e.sal)

5FROMemp e, dept d

6WHERE e.deptno = d.deptno

7GROUP BYd.dname;

View created.

Create a complex view that contains group functions to display values from two tables.


Rules for performing dml operations on a view

Rules for Performing DML Operations on a View

  • You can perform DML operations on simple views.

  • You cannot remove a row if the view contains the following:

    • Group functions

    • A GROUP BY clause

    • The DISTINCT keyword

    • The pseudocolumn ROWNUM keyword


Rules for performing dml operations on a view continued

Rules for Performing DML Operations on a View (continued)

  • You cannot modify data in a view if it contains:

    • Any of the conditions mentioned in the previous slide

    • Columns defined by expressions

    • The ROWNUM pseudocolumn

  • You cannot add data if:

    • The view contains any of the conditions mentioned above or in the previous slide

    • There are NOT NULL columns in the base tables that are not selected by the view


Using the with check option clause

Using the WITH CHECK OPTION Clause

  • You can ensure that DML on the view stays within the domain of the view by using the WITH CHECK OPTION clause.

  • Any attempt to change the department number for any row in the view will fail because it violates the WITH CHECK OPTION constraint.

SQL> CREATE OR REPLACE VIEW empvu20

2AS SELECT*

3FROMemp

4WHEREdeptno = 20

5WITH CHECK OPTION CONSTRAINT empvu20_ck;

View created.


Denying dml operations

Denying DML Operations

  • You can ensure that no DML operations occur by adding the WITH READ ONLY option to your view definition.

  • Any attempt to perform a DML on any row in the view will result in Oracle Server error.

SQL>CREATE OR REPLACE VIEW empvu10

2(employee_number, employee_name, job_title)

3AS SELECTempno, ename, job

4FROMemp

5WHEREdeptno = 10

6WITH READ ONLY;

View created.


Removing a view

Removing a View

DROP VIEW view;

Remove a view without losing data because a view is based on underlying tables in the database.

SQL> DROP VIEW empvu10;

View dropped.


Inline views

Inline Views

  • An inline view is a subquery with an alias (correlation name) that you can use within a SQL statement.

  • An inline view is similar to using a named subquery in the FROM clause of the main query.

  • An inline view is not a schema object.


Top n analysis

“Top-N” Analysis

  • Top-N queries ask for the n largest or smallest values of a column.

    • What are the ten best selling products?

    • What are the ten worst selling products?

  • Both largest values and smallest values sets are considered Top-N queries.


Performing top n analysis

Performing “Top-N” Analysis

SQL>SELECT [column_list], ROWNUM

2 FROM (SELECT [column_list] FROM table

3 ORDER BY Top-N_column)

4WHERE ROWNUM <= N

The high-level structure of a Top-N analysis query is:


Example of top n analysis

Example of “Top-N” Analysis

SQL> SELECT ROWNUM as RANK, ename, sal

2FROM (SELECT ename, sal FROM emp

3 ORDER BY sal DESC)

4WHERE ROWNUM <= 3;

To display the top three earners names and salaries from the EMP table.

RANKENAMESAL

1KING5000

2SCOTT3000

3FORD3000


Summary

Summary

  • A view is derived from data in other tables or other views.

  • A view provides the following advantages:

    • Restricts database access

    • Simplifies queries

    • Provides data independence

    • Allows multiple views of the same data

    • Can be dropped without removing the underlying data

  • An inline view is a subquery with an alias name.

  • “Top-N” analysis can be done using :

    • Subquery

    • Outer query


Practice overview

Practice Overview

  • Creating a simple view

  • Creating a complex view

  • Creating a view with a check constraint

  • Attempting to modify data in the view

  • Displaying view definitions

  • Removing views


  • Login