Question
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
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
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started