CS348 – Project 2 PL/SQL
Project 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.sql

Step 3 : Then, add 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 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_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 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 REPORT

AVAILABLE CRITICAL OUT OF STOCK



———————————————————————————-

450

32

7

Out of stock products Product Id


Product Name


Supplier Name


Supplier Phone No.

—————————————————————————————————————–

1

Iphone

ABC

8912899911

4

Pixel

XYZ

0987179987


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, <= 50}. The number of bins depends on the age groups of the customers available. If the lowest age available is 32 and largest is 67, then there will be 4 bins as {>30, <=40}, {>40, <=50}, {>50, <=60} and {>60, <=70}. The bin size is fixed but the number of bins depends on the range of age available.


HIT CATEGORIES

>30, <=40 >40, <=50 >50, <=60 >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 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 : 12345667



No. of order placed : 6

Notice 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 : 12344

Supplier Address : West Lafayette, 47906 No. of products supplied : 6

Products :

Product Id Product Name

————————————————-

4 Iphone X

78 Black Shoe