My SQL Question

This assignment consisted of three parts.

Part 1: Using OES4 database, redo the following two queries (you did them in assignment 4) with PL/SQL:

  1. List number of orders for each state with their total purchase (SUBTOTAL), total shipping charge, total tax, and total purchases (there are 15 branches)
  2. List number of customers with their total purchases (SUBTOTAL), total shipping charge, total tax, and overall total purchases.
  3. List TOP 25% of employees (employee number, employee last name) based on total net sales ( their total subtotal – their total amount returned)
  4. List all product categories and total amount of products sold for each category for every year

 

Part2: Using OES4, answer the following queries with stored procedures

4 Create several procedures to accept product number and produce the following result  a. Procedure Get_Product_Num_Cust.

  1. Procedure Get_ Product_Cust_Purchases
  2. Procedure Get_product_Num_orders

5 Develop a driver program to call all the 5 above procedures and produce the following report  Note: Net sales=Total_sales_amount – total_return_amount

Part3: Using OES4, answer the following queries with user defined functions

  1. Create several user defined functions to accept product number and produce the following result a. Function Get_Product_Num_Cust
  2. Function Get_ Product_Cust_Purchases
  3. Function Get_product_Num_orders
  4. Develop SQL program to call these UDFs

Deliverables:
1. A notepad document to hold all queries, SQL code, and result