Instructions:

To do this homework you will first need to run the NorthwindTradersDB creation script script to create the appropriate database on your machine, and then the NorthwindTradersDB Data Script to insert data into the database.

Business Requirements for the project are as follows:

The user should be able to view all categories.

The user should be able to see all products in a category. The user will provide category name (or part of category name). They may also choose to not provide any value for category name.

The user should be able to see the details of a particular product when they search by product name (or part of the product name).

The user should be able to enter employee information in the employee table.

The user should be able to create a purchase order. The purchase order ID will be returned as an output parameter. (create five such orders in the table using your test snippet)

The user should be able to add a purchase order detail for a particular order. The appropriate columns would be provided as input parameters. The purchase order detail ID will be returned as an output parameter when the purchase order detail is added successfully. (For every order in the purchase order table enter at least TWO order details using the test snippet for this procedure).

The user should be able to find the product that has the highest quantity (combined total of quantity for that product) in purchase order details. For this you will have to look at the purchaseorderdetails table and sum the total quantity by product id. (This stored procedure should be created after completing #5 and #6).

The user should be able to delete a purchase order detail. The appropriate column would be provided as an input parameter.

The user should be able to update the approved date of an existing purchase order.

The user should be able to calculate the final price (quantity * unitcost) for an order detail for which an order detail ID has been provided.

The user should be able to calculate the entire total of an order by calculating the totals of all the order details for that order.

The user would like to see the most valuable supplier by finding which supplier provides the highest number of individual products to Northwind Traders. The results should show the company, the last name and the state of the supplier in addition to the number of individual products supplied by the supplier.