Please see the attachments and follow the instructions very carefully. 

Assignment
Complete the Premiere Products Exercises (tasks 1-11, and 12b) on page 150.
Only submit the SQL commands required to perform each task, do not include explanations or results (deductions will be taken for submitting explanations or additional results)
Review the below modifications to the instructions:
For Tasks 1 and 2; only submit the SQL command to Create the View for the associated review question (2a and 3a), “and” include a Select clause (command) to “Display the data in the view”.
“Display the data…” means that you must include an SQL Select clause / command to “display” the results. DO NOT submit any “Results”, only the SQL command. Use the SQL syntax in the text as the guide.
For Task 9 delete the Street field from the Customer table
For Task 10 delete the Rep table
For Tasks 11 and 12 use && as the delimiter
Do not use Access as an SQL generator (it does not support all the needed SQL commands, and often provides more complex syntax than required, a deduction of ~15% will be taken if an SQL generator is used) – the objective is to practice writing basic SQL commands.
Use the task number of the Exercise to label each response
The following rubric will be used to grade the submission:
  Exercise tasks                                        85%
  Using APA style/reference                         5%
  Writing Org/Clarity/Spelling                     10%
               Total                                       100%

QUESTIONS

In the following exercises, you will use the data in the Premiere Products database shown in 

Figure 2-1

 in 

Chapter 2

. (SEPARATE ATTACHMENT)

· 1. Create the TopLevelCust view described in Review Question 2. Display the data in the view.

· 2. Create the PartOrder view described in Review Question 3. Display the data in the view.

· 3. Create a view named OrdTot. It consists of the order number and order total for each order currently on file.

(The order total is the sum of the number ordered multiplied by the quoted price on each order line for each order.) Display the data in the view.

· 4. Create the following indexes. If it is necessary to name the index in your DBMS, use the indicated name.

· a. Create an index named PartIndex1 on the PartNum field in the OrderLine table.

· b. Create an index named PartIndex2 on the Warehouse field in the Part table.

· c. Create an index named PartIndex3 on the Warehouse and Class fields in the Part table.

· d. Create an index named PartIndex4 on the Warehouse and OnHand fields in the Part table and list units on hand in descending order.

· 5. Drop the PartIndex3 index.

· 6. Assume the Part table has been created, but there are no integrity constraints. Create the necessary integrity constraint to ensure that the only allowable values for the Class field are AP, HW, and SG. Ensure that the PartNum field is the primary key and that the PartNum field in the OrderLine table is a foreign key that must match the primary key of the Part table.

· 7. Add a field named Allocation to the Part table. The allocation is a number representing the number of units of each part that have been allocated to each customer. Set all Allocation values to zero. Calculate the number of units of part number KV29 currently on order. Change the Allocation value for part number KV29 to this number. Display all the data in the Part table.

· 8. Increase the length of the Warehouse field in the Part table to two characters. Change the warehouse number for warehouse 1 to 1a. Display all the data in the Part table.

· 9. Delete the Allocation field from the Part table. Display all the data in the Part table.

· 10. What command would you use to delete the Part table from the Premiere Products database? (Do not delete the Part table.)

· 11. Write a stored procedure that will change the price of a part with a given part number. How would you use this stored procedure to change the price of part AT94 to $26.95?

· 12. Write the code for the following triggers following the style shown in the text.

· b. When updating a customer, add the difference between the new balance and the old balance multiplied by the sales rep’s commission rate to the commission for the corresponding sales rep.