About the Presentations - Mission College

Oracle 11g: SQL Chapter 13 Views Objectives Create a view by using CREATE VIEW command or the CREATE OR REPLACE VIEW command Employ the FORCE and NOFORCE options State the purpose of the WITH CHECK OPTION constraint

Explain the effect of the WITH READ ONLY option Update a record in a simple view Re-create a view Oracle 11g: SQL 2 Objectives (continued) Explain the implication of an expression in a view for DML operations

Update a record in a complex view Identify problems associated with adding records to a complex view Identify the key-preserved table underlying a complex view Drop a view Explain inline views and the use of ROWNUM to perform a TOP-N analysis Create a materialized view to replicate data Oracle 11g: SQL

3 Views Permanent objects that store no data Store a query Two purposes Reduce complex query requirements Restrict users access to sensitive data Oracle 11g: SQL

4 Types of Views Oracle 11g: SQL 5 Creating a View You use the CREATE VIEW keywords to create a view Use OR REPLACE if the view already exists

Use FORCE if the underlying table does not exist at the time of creation Provide new column names if necessary Oracle 11g: SQL 6 Creating a View (continued) WITH CHECK OPTION constraint if used, prevents data changes that will make the data

subsequently inaccessible to the view WITH READ ONLY prevents DML operations Oracle 11g: SQL 7 Creating a Simple View Only references one table no group functions, GROUP BY clause, or expressions

Oracle 11g: SQL 8 DML Operations on a Simple View Any DML operations are allowed through simple views unless created with WITH READ ONLY option DML operations that violate constraints on the underlying table are not allowed

Oracle 11g: SQL 9 Creating a Complex View A complex view may contain data from multiple tables or data created with the GROUP BY clause, functions, or expressions Type of DML operations allowed depends on various factors

Oracle 11g: SQL 10 DML Operations on a Complex View with an Arithmetic Expression Oracle 11g: SQL 11

DML Operations on a Complex View Containing Data from Multiple Tables DML operations cannot be performed on nonkey-preserved tables, but they are permitted on key-preserved tables Oracle 11g: SQL 12 DML Operations on a Complex View Containing Data from Multiple Tables

(continued) Oracle 11g: SQL 13 DML Operations on a Complex View Containing Functions or Grouped Data DML operations are not permitted if the view includes a group function or a GROUP BY

clause Oracle 11g: SQL 14 DML Operations on a Complex View Containing Functions or Grouped Data (continued) Oracle 11g: SQL

15 DML Operations on a Complex View Containing DISTINCT or ROWNUM DML operations on a view that contains the DISTINCT keyword or ROWNUM are not permitted Oracle 11g: SQL

16 Dropping a View Use DROP VIEW command Oracle 11g: SQL 17 Creating an Inline View An inline view is a temporary table created by using

a subquery in the FROM clause It can only be referenced while the command is being executed Most common usage TOP-N analysis Oracle 11g: SQL 18 TOP-N Analysis ORDER BY included to identify top values:

Descending for highest values Ascending for lowest values Extract data based on ROWNUM Oracle 11g: SQL 19 TOP-N Analysis (continued)

Oracle 11g: SQL 20 Materialized Views Replicate data Store data retrieved from view query Referred to as snapshots Oracle 11g: SQL

21 Materialized Views (continued) Oracle 11g: SQL 22 Materialized Views (continued) Oracle 11g: SQL

23 Summary A view is a temporary or virtual table that is used to retrieve data that exists in the underlying database tables The view query must be executed each time the view is used A view can be used to simplify queries or to restrict access to sensitive data

A view is created with the CREATE VIEW command A view cannot be modified; to change a view, it must be dropped and then re-created, or the CREATE OR REPLACE VIEW command must be used Oracle 11g: SQL 24 Summary (continued) Any DML operation can be performed on a simple query if it does not violate a constraint

A view that contains expressions or functions, or that joins multiple tables, is considered a complex view A complex view can be used to update only one table; the table must be a key-preserved table Data cannot be added to a view column that contains an expression DML operations are not permitted on non-key-preserved tables Oracle 11g: SQL 25

Summary (continued) DML operations are not permitted on views that include group functions, a GROUP BY clause, the ROWNUM pseudocolumn, or

the DISTINCT keyword Oracle 11g assigns a row number to every row in a table to indicate its position in the table; the row number can be referenced by the keyword ROWNUM A view can be dropped with the DROPVIEW command; the data is not affected, because it exists in the original tables An inline view can be used only by the current statement and can include an ORDER BY clause TOP-N analysis uses the row number of sorted data to determine a range of top values Materialized views physically store view query results

Oracle 11g: SQL 26

Recently Viewed Presentations

  • Personification - ednet.ns.ca

    Personification - ednet.ns.ca

    Personification Personification Personification is giving human qualities, feelings, action, or characteristics to inanimate (non-living) objects. Ex. The window winked at me. Winking is a human action. The window is the object being personified. Personification What is the human action? What...
  • Chapter 14: Small Business Organizations  2013 Cengage Learning.

    Chapter 14: Small Business Organizations 2013 Cengage Learning.

    An LP is a creature of state statute and must follow statutory requirements. Documents must be filed with the a secretary of state who will issue a . ... LP interests are considered securities and regulated by both federal and...
  • Coyote and the buffalo - Mr. Sadek's Classes

    Coyote and the buffalo - Mr. Sadek's Classes

    Trickster tales, like other forms of folk literature, offer readers insight into a society's way of life. What information about the following aspects of Okanogan culture did you glean from this tale? Traits or qualities the Okanogan admired as well...
  • Applying Motivational Interviewing to Job Corps Students and

    Applying Motivational Interviewing to Job Corps Students and

    Develop Discrepancy. problem. Positives things about making the change. Positives things about keeping things the way they are. Negative things about making the change. Negative things about keeping things the way they are. Have audience give an example of a...
  • Heat Transfer and The Human Head

    Heat Transfer and The Human Head

    Heat Transfer and The Human Head Brad Elmer John Pearson Introduction Problem Find the effects of wearing a beanie on a bald head Varying wind speeds in cold weather Assumptions Average Head Circumference 58cm (www.thehatesite.com) Radiation neglected (same for all...
  • The Role of Early Tertiary Underplating in Epeirogenesis on ...

    The Role of Early Tertiary Underplating in Epeirogenesis on ...

    Supported by the University of Leicester Teaching Enhancement Fund Department of Geology Student profile c. 70 students per year taking a variety of first degrees in Earth Sciences 15 % mature (22+) students 60% male 40% female 'Traditional' teaching Lectures...
  • media.luxoworks.com

    media.luxoworks.com

    ZUMTOBEL HELISSA D450 1/40W T16R EVG WH 60811737 Description Wall-/ceiling-mounted luminaire; lamp(s): 1/40W T16-R; control gear: with high frequency ballast ; uniformly illuminated polycarbonate diffuser; age-resistant diffuser for good light output ratio; lighting corona around luminaire for indirect accent lighting;...
  • The development of health economics teaching in the UK from a ...

    The development of health economics teaching in the UK from a ...

    The development of health economics teaching in the UK from a course director's perspective: Aqualitative study. Dr Emma Frew, Dr Paula Lorgelly and Dr Tracey Sach. Funded by the Economics Network. Health Economics Teaching in the UK.