SQL Code - Working with Additional Database Objects
Not what you're looking for?
See attached. Please show both the SQL code and results.
Working with Additional Database Objects
Deliverables:
• SQL code and the results (7 queries and 1 explanation in it).
1. 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. Define the view so that it will list the customer last names by ascending order within the states in descending order. Now insert the following data into the book_customer table using an INSERT statement. CUSTOMER# - 1021, FIRSTNAME - EDWARD, LASTNAME - BLAKE, STATE - TX. Now query your view and display the new record.
2. Create a complex view named CUST_ORDER that will list the customer number, last name, and state from the BOOK_CUSTOMER table and the order number and order date from the BOOK_ORDER table. Insert the following data into this view: CUSTOMER# - 1022, LASTNAME - smith, STATE - Kansas, ORDER# - 1021, and ORDERDATE - 10-OCT-2004.
3. Explain why the insert statement for the view you created in #2 did not work.
4. Create a sequence that can be used to assign a publisher ID number to a new publisher. Define the sequence to start with 7, increment by two and stop at 1000. Name the sequence PUBNUM_SEQ.
5. Insert two new publishers into the PUBLISHER table, one named Double Week with a contact name Jennifer Close at 800-959-6321, and the second one named Specific House with a contact name 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.
6. Using a single query, query the PUBNUM_SEQ to determine what both the current sequence number is and the next sequence number will be.
7. Create a unique index on the combined columns ORDER# and CUSTOMER# in the BOOK_ORDER table. Give the index a name of BOO_ORDER_IDX.
8. 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.
Purchase this Solution
Solution Summary
This solution follows the SQL code instructions and contains both the results and the SQL code.
Purchase this Solution
Free BrainMass Quizzes
C# variables and classes
This quiz contains questions about C# classes and variables.
Inserting and deleting in a linked list
This quiz tests your understanding of how to insert and delete elements in a linked list. Understanding of the use of linked lists, and the related performance aspects, is an important fundamental skill of computer science data structures.
C++ Operators
This quiz tests a student's knowledge about C++ operators.
Word 2010: Table of Contents
Ever wondered where a Table of Contents in a Word document comes from? Maybe you need a refresher on the topic? This quiz will remind you of the keywords and options used when working with a T.O.C. in Word 2010.
Basic UNIX commands
Use this quiz to check your knowledge of a few common UNIX commands. The quiz covers some of the most essential UNIX commands and their basic usage. If you can pass this quiz then you are clearly on your way to becoming an effective UNIX command line user.