PL/SQL
Any Late Day wil be penalized by 10% of the grade until a maximum of 5 days, after
which the project wil not be acepted.
Overview: In this project, you wil perform. some comon 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 acurate 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)
Suplier(SuplierId, FirstName, LastName, Addres, PhoneNo)
Product(ProductId, ProductName, Category, SuplierId, 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.sql
Step 3 : Then, ad the data to the database using Script. data.sql.
SQL > @data.sql
Step 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 lok 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; /
Procedures
1. 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_MONTH 560 100
LAST_YEAR 1187 658
2. Pro_prod_report: Generate a report based on inventory of the products into 3
categories
a. Available (more than 10 units available)
b. Critical (10 or les units available)
c. Out of stock.
Then, print the product with suplier details of out of stock products (sorted by
product name ascending order). The output should be as follows :
INVENTORY REPORT
AVAILABLE CRITICAL OUT OF STOCK
----------------------------------------------------------------------------------
450 32 7
Out of stock products
Product Id Product Name Suplier Name Suplier Phone No.
-----------------------------------------------------------------------------------------------------------------
1 Iphone ABC 8912899911
4 Pixel XYZ 0987179987
3. Pro_age_categ: Find the top category ordered by customers of diferent 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 diferent ages. For example,
a customer of age 45 wil fall in bin {>40, 30, 40, 50, 60, 30, 40, 50, 60, <=70
------------------------------------------------------------------------------------------------------
Electronics Clothing Home Apliances 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 REPORT
CATEGORY TOTAL_UNITS AVG_PRICE AVG_DISCOUNT
---------------------------------------------------------------------------------------------------------------
Clothing 130 98.00 6.45
Electronics 32 459.00 45.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 : 32
Phone No : 1234567
No. of order placed : 6
Notice that if the customer is not found, then print as folows :
Enter CustomerID : 6 (User Input ‘6’ given)
Customer with id 6 not found.
6. Pro_search_suplier: Given a suplierid, find the details about that suplier and the
products provided by the supplier.
Enter Suplier Id : 1 (User Input ‘1’ given)
Suplier Name : SuplFN SuppLN (Concatenation of first name and last name
separated by one blank space)
Suplier Phone No : 1234
Suplier Addres : West Lafayete, 47906
No. of products suplied : 6
Products :
Product Id Product Name
-------------------------------------------------
4 Iphone X
78 Black Shoe