Create a simple view named CUST_VIEW using the book_customer table that will display the customer number, first and last name, and the state for every customer currently in the database. Now insert the following data into the book_customer TABLE using an INSERT statement. (Do not use the View for this insert.) CUSTOMERID - 1021, FIRSTNAME – EDWARD, LASTNAME – BLAKE, STATE – TX. Now query your view and display the new record.
Create a complex view named CUST_ORDER that will list the customer number, last name, and state from the BOOK_CUSTOMER table, in addition to the order number and order date from the BOOK_ORDER table. Insert the following data into this view (use the view for the insert statement): CUSTOMERID - 1022, LASTNAME – smith, STATE – KS, ORDERID - 1021, and ORDERDATE – 10-OCT-2004.
In your own words, explain why the insert statement for the view you created in Step 2 did not work.
Create a sequence that can be used to assign a publisher ID number to a new publisher. Define the sequence to start with seven, increment by two, and stop at 1000. Name the sequence PUBNUM_SEQ.
Insert two new publishers into the PUBLISHER table, one named Double Week with a contact name of Jennifer Close at 800-959-6321, and the second one named Specific House with a contact name of Freddie Farmore at 866-825-3200. Use your new sequence to create the PUBID for each record. Now, query your PUBLISHER table to see your two new records.
Using a single query, query the PUBNUM_SEQ to determine what both the current sequence number is and the next sequence number will be.
Create a unique index on the combined columns ORDERID and CUSTOMERID in the BOOK_ORDER table. Give the index a name of BOOK_ORDER_IDX.
Determine how many objects you currently own in your schema by querying the USER_OBJECTS view in the Data Dictionary. Your result set should list the different object types that you find and include a count by object type.