Develop a stored procedure to receive a product number and returns the total num
ID: 3917812 • Letter: D
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 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 (don’t 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 rec’d 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
Explanation / Answer
To answer this question perfectly, I am taking the example of tables like Product which is having column name like product_no, product_name, product_qty, sale_date.
CREATE OR REPLACE PROCEDURE Num_Product_Sold(in_product_no NUMBER) IS
v_product_count NUMBER;
BEGIN
SELECT SUM(product_qty) AS Total_no_Product INTO v_product_count from Product where product_no = in_product_no;
DBMS_OUTPUT.PUT_LINE(v_product_count)'
END;
/
CREATE OR REPLACE PROCEDURE Num_Product_Sold_year(in_product_no NUMBER, in_year NUMBER) IS
v_product_count NUMBER;
BEGIN
SELECT SUM(product_qty) AS Total_no_Product INTO v_product_count from Product where product_no = in_product_no AND extract(year from to_date(sale_date,'dd/mm/yyyy'))=in_year;
DBMS_OUTPUT.PUT_LINE(v_product_count)'
END;
/
CREATE OR REPLACE PROCEDURE No_Product_Sold_month(in_product_no NUMBER, in_month NUMBER, in_year NUMBER) IS
v_product_count NUMBER;
BEGIN
SELECT SUM(product_qty) AS Total_no_Product INTO v_product_count from Product where product_no = in_product_no AND extract(month from to_date(sale_date,'dd/mm/yyyy'))=in_month AND extract(year from to_date(sale_date,'dd/mm/yyyy'))=in_year;
DBMS_OUTPUT.PUT_LINE(v_product_count)'
END;
/
Thanks.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.