Chapter 13 Views (up to p.495)

1. Chapter 13Views (up to p.495) Jason C. H. Chen, Ph.D. Professor of MIS School of Business Gonzaga University Spokane, WA 99258 USA chen@jepson.gonzaga.edu

2. 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

3. 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

4. A database view is a logical (virtual) table based on a query. Database Views Table-1 Table-2 Table-N It does not store data, but presents it in a format different from the one in which it is stored in the underlying tables. With the database view, you can view database records, but you can’t insert new records or modify or delete exiting records. database Single view table Database view Query Output: Report, Graphs

5. Views • Permanent objects (logical or virtual table) that store queries but no data • Based on a source query that: • can specify a subset of a single table’s fields or records • can join multiple tables • Two purposes • Reduce complex query requirements • Restrict users’ access to sensitive data (enforce security; user has access to view but not underlying table)

6. Creating and Using Database Views • Views can be updateable if: • SELECT clause contains only fieldnames, no functions or calculations • cannot contain the ORDER BY, DISTINCT, or GROUP BY clauses, group functions, or set operators • search condition cannot contain a nested query • Views are used like tables for selecting, inserting, updating and deleting data (only updatable views can be modified)

7. Database View (cont.) • SELECT view_name FROM user_views; • SELECT view_name FROM ALL_VIEWS WHERE owner=‘SYSTEM’; • DROP VIEW<view_name>; • CREATE ORREPLACE VIEW <view_name> AS <view query specification>;

8. Creating a Simple View • Only references one table – no group functions, GROUP BY clause, or expressions -- chapter 13, Figure 13-4; p.477 CREATE VIEW inventory AS SELECT isbn, title, retail price FROM books WITH READ ONLY; SELECT * FROM inventory; -- chapter 13, Figure 13-4(b); p.477 CREATE VIEW books_inventory_vu AS SELECT isbn, title, retail price FROM books WITH READ ONLY; SELECT * FROM books_inventory_vu; Figure 13-4 Selecting all records from the INVENTORY view What happen to “Database” after “DROP” view? DROP VIEW inventory; SELECT * FROM inventory; SELECT * FROM books; -- REcreate inventory view

9. CREATE OR REPLACE VIEW book_vu AS SELECT isbn, title, category FROM books WHERE category = 'COOKING'; SELECT * FROM book_vu;

10. Figure 13-1 View Processing

11. Types of Views Table 13-1 Overview of View Concepts

12. I. Creating a View • You use the CREATE VIEWkeywords 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 • 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 Figure 13-2 Syntax of the CREATE VIEW command

13. Database View • CREATE VIEW <view_name> AS <view query specification>; e.g., SELECT view_name FROM user_views; CREATEOR REPLACE VIEWcustomers_vu AS SELECT customer#, lastname, firstname, region FROM customers; What are gains? • DELETING VIEWS DROP VIEW viewname; DROP VIEW customers_vu; --EXTRA for VIEW SELECT firstname || ‘ ‘ || lastname, order#, orderdate FROM customers_vu, orders WHERE customers_vu.customer# = orders.order#;

14. 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 -- chapter 13, Figure 13-5; p.478 CREATE VIEW region_ne AS SELECT * FROM customers WHERE region = 'NE'; SELECT customer#, lastname, city, state, region FROM region_ne;

15. Why Failed to Update? -- chapter 13, Figure 13-6; p.479 UPDATE inventory SET price = 45.96 WHERE title LIKE '%POEM'; SELECT * FROM inventory WHERE title LIKE '%POEM%'; UPDATE inventory SET title = 'THE SHORTEST POEMS' WHERE title LIKE '%POEM%'; Figure 13-6 Failed updates on the INVENTORY view

16. DML Operations on a Simple View (continued) -- chapter 13, Figure 13-7; p.480 CREATE OR REPLACE VIEW inventory AS SELECT isbn, title, retail price FROM books; SELECT * FROM inventory WHERE title LIKE '%BODYBUILD%'; -- chapter 13, Figure 13-8; p.481 UPDATE inventory SET price = 49.95 WHERE title LIKE '%BODYBUILD%'; SELECT * FROM inventory WHERE title LIKE '%BODYBUILD%'; SELECT * FROM books WHERE title LIKE '%BODYBUILD%';

17. Rule for DML operations • As long as the view isn’t created with the WITH READ ONLY option and any DML operation is allowed if it doesn’t violate an existing constraint on the underlying table. • You can add, modify, and even delete data in an underlying table as long as one of the following constraints doesn’t prevent the operation: • PRIMARY KEY - FOREIGN KEY • UNIQUE - NOT NULL • WITH CHECK OPTION Practice Figure 13-9 to 13-11 (pp.482-483)

18. Exercises • Practice all the examples in the text. • A Script file is available on the Bb (file name: ch13Queries.sql) • After completing all examples, do the HW. • In-class Exercise • #1 (p.508)

19. Homework - Hands-On Assignments Email me with one attachment (Oracle_ch13_Spool_Lname_Fname.) to: chen@jepson.gonzaga.edu with subject title of Bmis441-01_Oracle_ch13 (or Bmis441-02_Oracle_ch13) Read and Practice all examples on Chapters 13 • 1. Read Oracle assignment and create a script file Oracle_ch13_Lname_Fname.sql for questions (#1-5 and 9,10; p.508) on “Hands-on Assignments”. • 2. Execute and test one problem at a time and make sure they are all running successfully. • 3. When you done, spool the script files (see next slide for spooling instructions) and email the file (Oracle_ch13_Spool_Lname_Fname.txt) to me by the midnight before the next class.

20. How to Spool your Script and Output Files After you tested the script file of Oracle_ch13_Lname_Fname.sql successfully, follow the instructions below to spool both script and output files: Step 0. Run the following script file from SQL*Plus (since you have created JLDB tables) • 1. type the following on SQL> • Spool c:\oradata\Oracle_ch13B_Spool_Lname_Fname.txt (make sure your name is entered) • 2. open Oracle_ch13_Lname_Fname.sql that you already tested • 3. copy and paste all the SQL commands (including all comments) to the SQL*PLUS • 4. type Spool Off on the SQL> The output should contain your personal information, all SQL commands and their solution on the .txt file and saved in C: drive (oradata\ folder) Email me with the spooled file (.txt) with attachment to: chen@jepson.gonzaga.edu with subject title of Bmis441-01_Oracle_ch13 (or Bmis441-02_Oracle_ch13)

21. PART II

22. II. 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

23. DML Operations on a Complex View with an Arithmetic Expression Figure 13-12 Create and update a complex view named prices

24. Rule#1: DML operations are not permitted if the view includes a column based on arithmetic expression Figure 13-14 Failed attempts to add a new book via the PRICES view -- chapter 13, Figure 13-15; p.488 INSERT INTO prices (isbn, title, cost, retail) VALUES(0202020202, 'A New Book', 8.95, 15.95);

25. Rule#2: DML operations are not permitted if they violate a constraint such as NOT NULL, UNIQUE etc. no pubid is inserted Figure 13-16 Constraint violation with an INSERT command via the PRICES view

26. DML Operations on a Complex View Containing Data from Multiple Tables • DML operations cannot be performed on non-key-preserved tables, but they are permitted on key-preserved tables Figure 13-17 PRICES view with a table join Practice: Figure 13-18

27. DML Operations on a Complex View Containing Data from Multiple Tables (continued) • Rule#3: DML operations can’t be performed on a non-key-preserved table. Figure 13-19 Failed attempt to update the publisher name via the PRICES view

28. DML Operations on a Complex View Containing Functions or Grouped Data • Rule#4: DML operations are not permitted if the view includes a group function or a GROUP BY clause, the DISTINCT keyword, or the ROWNUM pseudocolumn. -- chapter 13, Figure 13-21; p.492 CREATE VIEW balancedue AS SELECT customer#, order#, SUM(quantity * retail) Amtdue FROM customers JOIN orders USING (customer#) JOIN orderitems USING (order#) JOIN books USING (isbn) GROUP BY customer#, order#; SELECT * FROM balancedue; Figure 13-21 View including grouped data

29. SELECT * FROM balancedue; Figure 13-21 View including grouped data

30. DML Operations on a Complex View Containing Functions or Grouped Data (continued) Figure 13-22 Failed DELETE command on a view with grouped data

31. Dropping a View • Use DROP VIEW command Figure 13-25 Command to drop the PRICES view

32. III. 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

33. “TOP-N” Analysis • ORDER BY included to identify top values: • Descending for highest values • Ascending for lowest values • Extract data based on ROWNUM • Up to “N” rows returned Figure 13-26 Syntax of TOP-N analysis

34. “TOP-N” Analysis (continued) Figure 13-28 TOP-N analysis to identify the five most profitable books

35. Figure 13-28 TOP-N analysis to identify the five most profitable books Figure 13-28 TOP-N analysis to determine the three lest profitable books

36. PART III

37. Materialized Views • Replicate data • Store data retrieved from view query • Referred to as “snapshots”

38. Materialized Views (continued) Figure 13-30 Creating a materialized view named CUSTBAL_MV

39. Figure 13-31 Query a materialized view

40. Materialized Views (continued) Figure 13-32 Drop a materialized view

41. 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

42. 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

43. 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