Task-1: Create a procedure/function to calculate the total order price for a giv
ID: 3756341 • Letter: T
Question
Task-1: Create a procedure/function to calculate the total order price for a given customer number for the Premiere product database (script for Premiere product is provided: Premiere.txt). Display customer number, customer name, and order total. Write a PL/SQL script to complete this task.
Task-2: Create a procedure to find how many parts have been sold by each sales representative for a given part number. using Premiere product database.
CREATE TABLE REP
(REP_NUM CHAR(2) PRIMARY KEY,
LAST_NAME CHAR(15),
FIRST_NAME CHAR(15),
STREET CHAR(15),
CITY CHAR(15),
STATE CHAR(2),
ZIP CHAR(5),
COMMISSION DECIMAL(7,2),
RATE DECIMAL(3,2) );
CREATE TABLE CUSTOMER
(CUSTOMER_NUM CHAR(3) PRIMARY KEY,
CUSTOMER_NAME CHAR(35) NOT NULL,
STREET CHAR(15),
CITY CHAR(15),
STATE CHAR(2),
ZIP CHAR(5),
BALANCE DECIMAL(8,2),
CREDIT_LIMIT DECIMAL(8,2),
REP_NUM CHAR(2) );
CREATE TABLE ORDERS
(ORDER_NUM CHAR(5) PRIMARY KEY,
ORDER_DATE DATE,
CUSTOMER_NUM CHAR(3) );
CREATE TABLE PART
(PART_NUM CHAR(4) PRIMARY KEY,
DESCRIPTION CHAR(15),
ON_HAND DECIMAL(4,0),
CLASS CHAR(2),
WAREHOUSE CHAR(1),
PRICE DECIMAL(6,2) );
CREATE TABLE ORDER_LINE
(ORDER_NUM CHAR(5),
PART_NUM CHAR(4),
NUM_ORDERED DECIMAL(3,0),
QUOTED_PRICE DECIMAL(6,2),
PRIMARY KEY (ORDER_NUM, PART_NUM) );
INSERT INTO REP
VALUES
('20','Kaiser','Valerie','624
Randall','Grove','FL','33321',20542.50,0.05);
INSERT INTO REP
VALUES
('35','Hull','Richard','532
Jackson','Sheldon','FL','33553',39216.00,0.07);
INSERT INTO REP
VALUES
('65','Perez','Juan','1626 Taylor','Fillmore','FL','33336',23487.00,0.05);
INSERT INTO CUSTOMER
VALUES
('148','Al''s Appliance and Sport','2837
Greenway','Fillmore','FL','33336',6550.00,7500.00,'20');
INSERT INTO CUSTOMER
VALUES
('282','Brookings Direct','3827
Devon','Grove','FL','33321',431.50,10000.00,'35');
INSERT INTO CUSTOMER
VALUES
('356','Ferguson''s','382
Wildwood','Northfield','FL','33146',5785.00,7500.00,'65');
INSERT INTO CUSTOMER
VALUES
('408','The Everything Shop','1828
Raven','Crystal','FL','33503',5285.25,5000.00,'35');
INSERT INTO CUSTOMER
VALUES
('462','Bargains Galore','3829
Central','Grove','FL','33321',3412.00,10000.00,'65');
INSERT INTO CUSTOMER
VALUES
('524','Kline''s','838
Ridgeland','Fillmore','FL','33336',12762.00,15000.00,'20');
INSERT INTO CUSTOMER
VALUES
('608','Johnson''s Department Store','372
Oxford','Sheldon','FL','33553',2106.00,10000.00,'65');
INSERT INTO CUSTOMER
VALUES
('687','Lee''s Sport and Appliance','282
Evergreen','Altonville','FL','32543',2851.00,5000.00,'35');
INSERT INTO CUSTOMER
VALUES
('725','Deerfield''s Four Seasons','282
Columbia','Sheldon','FL','33553',248.00,7500.00,'35');
INSERT INTO CUSTOMER
VALUES
('842','All Season','28
Lakeview','Grove','FL','33321',8221.00,7500.00,'20');
INSERT INTO ORDERS
VALUES
('21608','20-Oct-2007','148');
INSERT INTO ORDERS
VALUES
('21610','20-Oct-2007','356');
INSERT INTO ORDERS
VALUES
('21613','21-Oct-2007','408');
INSERT INTO ORDERS
VALUES
('21614','21-Oct-2007','282');
INSERT INTO ORDERS
VALUES
('21617','23-Oct-2007','608');
INSERT INTO ORDERS
VALUES
('21619','23-Oct-2007','148');
INSERT INTO ORDERS
VALUES
('21623','23-Oct-2007','608');
INSERT INTO PART
VALUES
('AT94','Iron',50,'HW','3',24.95);
INSERT INTO PART
VALUES
('BV06','Home Gym',45,'SG','2',794.95);
INSERT INTO PART
VALUES
('CD52','Microwave Oven',32,'AP','1',165.00);
INSERT INTO PART
VALUES
('DL71','Cordless Drill',21,'HW','3',129.95);
INSERT INTO PART
VALUES
('DR93','Gas Range',8,'AP','2',495.00);
INSERT INTO PART
VALUES
('DW11','Washer',12,'AP','3',399.99);
INSERT INTO PART
VALUES
('FD21','Stand Mixer',22,'HW','3',159.95);
INSERT INTO PART
VALUES
('KL62','Dryer',12,'AP','1',349.95);
INSERT INTO PART
VALUES
('KT03','Dishwasher',8,'AP','3',595.00);
INSERT INTO PART
VALUES
('KV29','Treadmill',9,'SG','2',1390.00);
INSERT INTO ORDER_LINE
VALUES
('21608','AT94',11,21.95);
INSERT INTO ORDER_LINE
VALUES
('21610','DR93',1,495.00);
INSERT INTO ORDER_LINE
VALUES
('21610','DW11',1,399.99);
INSERT INTO ORDER_LINE
VALUES
('21613','KL62',4,329.95);
INSERT INTO ORDER_LINE
VALUES
('21614','KT03',2,595.00);
INSERT INTO ORDER_LINE
VALUES
('21617','BV06',2,794.95);
INSERT INTO ORDER_LINE
VALUES
('21617','CD52',4,150.00);
INSERT INTO ORDER_LINE
VALUES
('21619','DR93',1,495.00);
INSERT INTO ORDER_LINE
VALUES
('21623','KV29',2,1290.00);
Explanation / Answer
Hi,
Below are the solutions-
Task 1-
CREATE OR REPLACE PROCEDURE proc_calc_price (p_cust_num IN number(10))
IS
DECLARE
v_cust_num Number(10);
v_cust_name varchar2(10);
v_price number(10,2);
BEGIN
select c.customer_num,c.customer_name,quoted_price into v_cust_num,v_cust_name,v_price
from customer c inner join orders o on c.customer_num=o.customer_num
inner join order_line ol on o.order_num=ol.order_num where c.customer_num=p_cust_num;
dbms_output.put_line ('customer number '|| v_cust_num);
dbms_output.put_line ('customer name '|| v_cust_name);
dbms_output.put_line ('Price '|| v_price);
END;
Task 2-
CREATE OR REPLACE PROCEDURE proc_calc_number (p_part_num IN number(10))
AS
rep_ename varchar2(10);
num_parts number(10);
CURSOR c1 IS
select r.rep_num, ol.num_ordered
from rep r inner join customer c on r.rep_num=c.rep_num
inner join orders o on c.customer_num=o.customer_num
inner join order_line ol on o.order_num=ol.order_num where part_num=p_part_num;
BEGIN
open c1;
LOOP
FETCH c1 INTO rep_ename, num_parts;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(rep_ename||' '||num_parts );
end loop;
close c1;
END ;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.