This paper circulates around the core theme of a) Write a function (called total_cost) that returns the total cost of an item after the tax has been added to the product. together with its essential aspects. It has been reviewed and purchased by the majority of students thus, this paper is rated 4.8 out of 5 points by the students. In addition to this, the price of this paper commences from £ 99. To get this paper written from the scratch, order this assignment now. 100% confidential, 100% plagiarism-free.
Question 1
a) Write a function (called total_cost) that returns the total cost of an item after the tax has been added to the product. The function has two IN parameters Customer ID and Item ID and returns a number as a result of the function execution. If a Customer ID, Product ID or STATE does not exist in the relevant tables the function should return NULL as the result. You need to return the NULL result explicitly and not as a part of the cost calculation. The function calculates cost of an item based on the percentage charged for the state the product is shipped to.
b) Write a stored function called calculated_tax (which take in take a single IN parameter Product ID. The function returns the sum of tax that all customer had to pay for that product.
c) Write a stored procedure called total_product_customers which takes in a single IN OUT parameter. The procedure finds the total number customer who have purchased that item.
d) Write a stored procedure (called products_report) that takes a single IN parameter Product ID and creates a report to the screen as follows. For every customer in the table that purchased that Product ID create a report using the DBMS_OUTPUT module to print the Customer Last Name, First Name, State, Product Description, Product Cost, Tax, total cost by making use of relevant procedures and functions defined previously.
After processing all the customers display the following information.
Total number of customers who purchased product XX is YY and total tax calculated for sales is $XX.YY.
Call the procedure products_report from an anonymous block and capture the results.
