IT358 Unit 6 Views
Outcomes addressed in this activity:
Create a view, using CREATE
VIEW command or the CREATE OR REPLACE VIEW command
Explain the effect of the WITH READ ONLY option
Demonstrate the ability to update records in a simple view
Demonstrate the ability to re-create a view
Identify problems associated with adding records to a complex view
Identify the key-preserved table underlying a complex view
In this project, you will write joins and subqueries:
1. Complete the Review Questions for numbers 1–5 from Chapter 13.
2. Complete the Hands-on Assignments #1-10 from Chapter 13.
Oracle 11G SQL Chapter 13 Hands-On assignment Solution
1. Create a view that will list the name of the contact person at each publisher and the person’s phone number. Do not include the publisher’s ID in the view. Name the view CONTACT.
2. Change the CONTACT view so that no users can accidentally perform DML operations on the view.
3. Create a view called HOMEWORK13 that will include the columns named Col1 and Col2 from the FIRSTATTEMPT table. Make certain the view will be created even if the FIRSTATTEMPT table does not exist.
4. Attempt to view the structure of the HOMEWORK13 view.
5. Create a view that will list the ISBN and title for each book in inventory along with the name and telephone number of the individual to contact in the event the book needs to be reordered. Name the view REORDERINFO.
6. Try to change the name of one of the individuals in the REORDERINFO view to your name. Was there an error message displayed when performing this step? If so, what was the cause of the error message?
7. Select one of the books in the REORDERINFO view and try to change the ISBN of the book. Was there an error message displayed when performing this step? If so, what was the cause of the error message?
8. Delete the record in the REORDERINFO view that contains your name (if that step was not performed successfully, then delete one of the contacts already listed in the table).Was there an error message displayed when performing this step? If so, what was the cause of the error message?
9. Issue a rollback command to undo any changes made with any previous DML operations.
10. Delete the REORDERINFO view.