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

代写CS&Finance|建模|代码|系统|报告|考试

编程类：C++,JAVA ,数据库,WEB,Linux,Nodejs,JSP,Html,Prolog,Python,Haskell,hadoop算法,系统 机器学习

金融类：统计，计量，风险投资，金融工程，R语言，Python语言，Matlab，建立模型，数据分析，数据处理

服务类：Lab/Assignment/Project/Course/Qzui/Midterm/Final/Exam/Test帮助代写代考辅导

E-mail:850190831@qq.com   微信：BadGeniuscs  工作时间：无休息工作日-早上8点到凌晨3点

---

如果您用的手机请先保存二维码到手机里面，识别图中二维码。如果用电脑，直接掏出手机果断扫描。