Develop a stored procedure to receive a product number and returns the total number product sold

Develop a stored procedure to receive a product number and year, and returns the total number product sold for that year

Develop a procedure that will receive a product number, a month, a year, and returns the sales for that year

Develop a driver program in PL/SQL to call the above procedures and produce the following reports

Product#, product_name, year, Number_Product_sold, total_sale

Q8: Advanced Query Processing: Using your database, answer the following queries (10%) Note: For every query, you should have the following format: query in English, sql code, and result.

Result should be readable, so you may need to use SQL plus commands to format it.

If the result is more than 20 lines, then show only 20 rows (selecting 20 rows should be part of your SQL program). Hint think about ROWNUM

8.a: List the top 25% percent of customers in terms of total number of orders

8.b: For each product (product number and product description),

List the top three customers purchased in term of net sales

8.c: For each product brand, List the top three states which customers have purchased the product brand. This should be done in term of gross sales (dont include tax or shipping charges)

8.d: List those customers that their orders included all of our products

8.e: List those employees that have sold all products

8..f: List every states with their highest total sale

Q9: Active databases: Using your database version

Sales requirements

If any product is sold to a customer and there is enough product on hand

Then the QOH column in product table should be updated.(You should think about creating a trigger).

else customer should be informed (email) that you are out of stock and products will be shipped on this date (to be calculated)

the quantity ordered should be placed on back orders

Order to vendor should be placed

All our vendor has 3 days no charge delivery and 5% charge for 1 Day delivery

Order date, expected receiving date, actual received date, shipper (similar shippers that we use for our sales

Orders recd from vendor should increase the QOH

Validate your development with your own test data and make sure it is posted in your report then rollback your changes

All orders should be shipped 5 days after order date.

Be careful, this should be done for the new orders not the old transaction.

Validate your development with your own test data and make sure it is posted in your report then rollback your changes