PLEASE ANSWER ALL QUESTIONS

PLEASE INCLUDE EXECUTE COMMANDS

REFER TO DATA INCLUDED

Create stored procedures required for the following business requirements. Submit ONE document with the create commands in it (file name: LastName_HW2.sql). This file would also hold a test snippet (execute command) for EACH procedure. Make sure to comment out the test snippets (using multi-line comments).

Business Requirements for the project are as follows:

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.

60 E 1= = and