CS348 - Project 2 PL/SQLProject Due in Blackboard: 11:59PM, Thursday April 5, 2018. Any Late Day will be penalized by 10% of the grade until a maximum of 5 days, after which the project will not be accepted. Overview: In this project, you will perform. some common database operations. The schema is slightly modified from the one used in Project 1, so make sure to use the scripts provided with this package. You MUST use PL/SQL for this project. You should use your Purdue Oracle account to create and test the queries. Submit your answers via Blackboard. Note: The numbers should be accurate to 2 decimal places. (For instance, 90, 90.5 and 90.50 are all acceptable. But 90.555 is unacceptable.) Customer(CustomerId, FirstName, LastName, Address, PhoneNo, Age) Supplier(SupplierId, FirstName, LastName, Address, PhoneNo) Product(ProductId, ProductName, Category, SupplierId, ProductDescription) Inventory(ProductId, TotalStock, LastUpdatedOn)Orders(OrderId, CustomerId, OrderDate, Status) OrderItems(OrderId, ProductId, Quantity, UnitPrice, Discount) Step 1: Drop the tables if existing: SQL > @droptales.sql Step 2 : Then create the new tables using script. tables.sql SQL > @tables.sqlStep 3 : Then, add the data to the database using Script. data.sql. SQL > @data.sqlStep 4: PL/SQL Procedures: In this part, you are going to use PL/SQL(Oracle’s procedural extension to SQL) to write procedures to process data.Create a file named procedures.sql. The first line of this file should be : set serveroutput on size 32000 Your file should contain code to create and run six procedures: Your file should look something like this: /* create the procedure */
create or replace procedure Pro_order_status as
/* declarations */ begin
/* code */ end;
/
/* actually run the procedure */ begin
Pro_order_status; end;
/
create or replace procedure Pro_prod_report as begin
/*code*/ end;
/
begin
Pro_prod_report; end; / Procedures1. Pro_order_status : Generate the report based on state of the orders for last month and last year. Find the total number of orders in delayed and shipped states over last month (last 30 days) and last year(last 12 months). These dates are matched with orderdate in orders table. The output should be as follows : DURATION DELAYED_ORDERS SHIPPED_ORDERS------------------------------------------------------------------------------------------------LAST_MONTH560100LAST_YEAR1187658 2. Pro_prod_report: Generate a report based on inventory of the products into 3 categoriesa. Available (more than 10 units available)b. Critical (10 or less units available)c. Out of stock.Then, print the product with supplier details of out of stock products (sorted by product name ascending order). The output should be as follows : INVENTORY REPORTAVAILABLE CRITICAL OUT OF STOCK ----------------------------------------------------------------------------------450327Out of stock products Product Id Product Name Supplier Name Supplier Phone No.-----------------------------------------------------------------------------------------------------------------1IphoneABC89128999114PixelXYZ0987179987 3. Pro_age_categ: Find the top category ordered by customers of different age groups. This represents the hit category for each age group and helps in targeted advertising. Top category can be calculated by comparing the number of product units (total quantity sold) of each category ordered. Consider bins of size 10 for different ages. For example, a customer of age 45 will fall in bin {>40, 30, 40, 50, 60, 30, 40, 50, 60, <=70------------------------------------------------------------------------------------------------------Electronics Clothing Home Appliances Clothing 4. Pro_category_info: Generate a report to display the details about each category. These details include the number of units of that category ordered, the average unit price of the products in that category, the average discounted amounts given. Notice that the discounts given in the data is in percentage while the report wants the average discount in whole amount. The average prices and average discounts should be formatted to 2 decimal places. Order the result by the total units sold in descending order. The computation of the averages should take the quantity into account. CATEGORY REPORTCATEGORY TOTAL_UNITS AVG_PRICE AVG_DISCOUNT---------------------------------------------------------------------------------------------------------------Clothing13098.006.45Electronics32459.0045.00 5. Pro_search_customer: Given a customerid, find the details about that customer. Enter CustomerID : 5 (User Input ‘5’ given)Customer Name : CustFN CustLN (concatenation of first name and last name) Customer Age : 32Phone No : 12345667 No. of order placed : 6Notice that if the customer is not found, then print as follows : Enter CustomerID : 6 (User Input ‘6’ given)Customer with id 6 not found. 6. Pro_search_supplier: Given a supplierid, find the details about that supplier and the products provided by the supplier. Enter Supplier Id : 1 (User Input ‘1’ given)Supplier Name : SupplFN SuppLN (Concatenation of first name and last name separated by one blank space)Supplier Phone No : 12344Supplier Address : West Lafayette, 47906 No. of products supplied : 6Products :Product Id Product Name-------------------------------------------------4 Iphone X78 Black Shoe