Premiere Products Exercises

763 Words Nov 22nd, 2015 4 Pages
1. Create the TopLevelCust view described in Review Question 2. Display the data in the view.

CREATE VIEW TopLevelCust AS
SELECT CustomerNum,CustomerName, Street, City, State, Zip, CreditLimit
FROM Customer

2. Create the PartOrder view described in Review Question 3. Display the data in the view. CREATE VIEW PartOrder AS
SELECT Part.PartNum, Part.Description, Part.Price, OrderLine.OrderNum, OrderLine.NumOrdered, OrderLine.QuotedPrice, Orders.OrderDate
FROM Orders INNER JOIN (Part INNER JOIN OrderLine ON Part.[PartNum] = OrderLine.[PartNum]) ON Orders.[OrderNum] = OrderLine.[OrderNum];

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
…show more content…
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.)
DROP TABLE Part
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?
CALL Change _Price (‘AT94’, 26.95)

12. Write the code for the following triggers following the style shown in the text.
a. When adding a customer, add the customer's balance times the sales rep's commission rate to the commission for the corresponding sales rep.
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

More about Premiere Products Exercises