Attached in the zip is the the sample database ITD640_B from the assignment. The sample database represents a company with employees and different stores. Each employee works at just one store. The employees have various jobs in different promotional activities (promotions) and may work on more than one promotion at a time.
database is attached
The following are the tables and data in the ITD640_B database:
Using the sample database, write the scripts in a file called ITD640_P3.SQL to create the following views. Remember to include a USES clause at the top of your script file to use the ITD640_B database. Also include code that checks if the view already exists. If it does, it should be dropped and recreated.
Create a view named v_stocker showing the employee number, promotion number, and start date where the job is "stocker."
Create a view called v_no_budget with all the columns of the promotion table except the budget column.
Create a view called v_count that shows the number of employees that are working on each promotion. The view should have columns for the promotion number and the count.
The solution contains SQL script file with script to create the 3 requested views.
SQL Code - Working with Additional Database Objects
See attached. Please show both the SQL code and results.
Working with Additional Database Objects
• 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.View Full Posting Details