CMPT 354 (00-3): Assignment 2

(100 marks)

Submission Requirements

Due time and date: 9:30 on December 1

Please hand in your assignment to the instructor.

Part 1: Exercise Assignment (75 points)

Part 2: programming part (25 points)

a. Using NorthWind database offered by Microsoft SQLServer 7.0, create a stored procedure to increase the discount rate and calculate and print the discounted price for each product in each order. The parameter to pass in is the percentage of the discount to increase.

(Hint:  This is not to update your database at all.   Read SQLServer manual on Stored Procedures.  Use SQLServer Query Analyzer to create a stored procedure and do the test).
For example, unit price = 100, original discount=10%, the percentage you is 10%, discounted price is 80. when you run the stored procedure, pass 10 as parameter(as 10%)

Sample output:
CompanyName OrderID ProductID DiscountPrice
------------------------------------ ---------------- ---------------- ----------------
Alfreds Futterkiste 10643 28 29.64

b. Create a stored procedure using cursor to calculate the discount rate based on the original price.
 
Original Range  Discount Rate 
10.00 - 19.99  5% 
20.00 - 49.99  10% 
50.00 - 99.99  15% 
> 100.00  20% 

Print company name, product id, sale price, quantity, and total amount.
Hint: you can use a print function, check the print function in SQL book online

Sample output:

Company: Vins et alcools Chevalier
Product: 11 , Sale Price: $13.30 , Quantity: 12 , Total: 159.60

Solution to written part
Solution to programming part