In: Computer Science
Utilize the Outdoor Clubs & Product database to create a PL/SQL anonymous block program unit that lists the product_name attribute value and a demand status text display. The demand status text display is determined by counting the number of times a product has been ordered so far. The demand status text display will be either “Low Demand” or “High Demand”. Low Demand is displayed if the product has been ordered less than 2 times. High Demand is displayed if the product has been ordered more than 2 times. Save the PL/SQL anonymous block program unit as a script file. Due to the business rule of not carrying products that have low demand, reduce the price of products (by $1) that have low demand.
--- Database Tables --- _
id varchar2(4) constraint supplier_pk primary key, name varchar2(30), street varchar2(30), city varchar2(15), state char(2), zip number(5), phone varchar2(10)); create table product (product_id number(5) constraint product_pk primary key, product_name varchar2(30), quantity_in_stock number(3), reorder_point number(2), price number(5,2), supplier_id varchar2(4) constraint product_fk references supplier, reorder_qty number(2)); create table purchase_order (po_no varchar2(4) constraint purchase_order_pk primary key, po_date date, product_id number(5) constraint purchase_order_fk1 references product, quantity number(3), supplier_id varchar2(4) constraint purchase_order_fk2 references supplier); create table customer (customer_id number(3) constraint customer_pk primary key, first_name varchar2(10), last_name varchar2(10), street varchar2(30), city varchar2(15), state char(2)default 'MO', zip number(5), phone varchar2(10)); create table club_membership (membership_id number(5) constraint club_membership_pk primary key, membership_date date, duration number(2), amount number(4), payment_type varchar2(5)constraint membership_payment_type_ck check ((payment_type = 'CC') or (payment_type = 'Check')), club_id number(3) constraint club_membership_fk1 references sporting_clubs, customer_id number(3) constraint club_membership_fk2 references customer); create table product_order (order_id number(4) constraint product_order_pk primary key, order_date date, ship_date date, payment_type varchar2(5)constraint prod_order_payment_type_ck check ((payment_type = 'CC') or (payment_type = 'Check')), total number (6,2), customer_id number(3) constraint product_order_fk1 references customer); create table order_details (order_id number(4), product_id number(5), quantity number(2), constraint order_details_pk primary key (order_id,product_id), constraint order_details_fk1 foreign key (order_id) references product_order, constraint order_details_fk2 foreign key (product_id) references product); create sequence club_sequence start with 100 increment by 10 nocache; insert into sporting_clubs values(club_sequence.nextval, 'Hillside Mountain Club', '1 Winona St','Wichita','KS',34342,'3163997676'); insert into sporting_clubs values(club_sequence.nextval, 'Branson Climbing Club', '2 Sherwood Dr.','Branson','MO',65670,'4174485676'); insert into sporting_clubs values(club_sequence.nextval, 'Cherokee Rafting Club', '44 Kent Ave.','St. Charles','MO',66572,'3147780870'); insert into sporting_clubs values(club_sequence.nextval, 'White Plains Club', '225 Tracy St.','New York','NY',13567,'2126678090'); insert into club_activity values(100,'Hiking'); insert into club_activity values(100,'Climbing'); insert into club_activity values(100,'Walking'); insert into club_activity values(110,'Hiking'); insert into club_activity values(110,'Climbing'); insert into club_activity values(110,'Conservation'); insert into club_activity values(110,'Walking'); insert into club_activity values(120,'Conservation'); insert into club_activity values(120,'Canoeing'); insert into club_activity values(130,'Conservation'); insert into club_activity values(130,'Canoeing'); insert into club_activity values(130,'Walking'); create sequence supplier_sequence start with 500 increment by 10 nocache; insert into supplier values('S'||supplier_sequence.nextval,'Hillside Ski','2717 S. Western Ave.','Los Angeles','CA',90006,'7146654959'); insert into supplier values('S'||supplier_sequence.nextval,'Tiger Mountain','2600 S. Vermont Ave.','Los Angeles','CA',90006,'7143327878'); insert into supplier values('S'||supplier_sequence.nextval,'Asha Outdoor','44 S. LaSalle St.','Chicago','IL',60603,'3125554678'); insert into supplier values('S'||supplier_sequence.nextval,'Sheraton Recreation','225 Tracy St.','New York','NY',13567,'2128889569'); create sequence product_id_sequence start with 10010 increment by 1 nocache; insert into product values(product_id_sequence.nextval,'Beginner''s Ski Boot',20,5,9.75,'S500',25); insert into product values(product_id_sequence.nextval,'Intermediate Ski Boot',18,5,12.99,'S500',20); insert into product values(product_id_sequence.nextval,'Pro Ski Boot',21,7,15.49,'S510',25); insert into product values(product_id_sequence.nextval,'Beginner''s Ski Pole',15,3,25.49,'S500',20); insert into product values(product_id_sequence.nextval,'Intermediate Ski Pole',20,3,29.99,'S520',22); insert into product values(product_id_sequence.nextval,'Pro Ski Pole',21,5,34.99,'S530',25); insert into product values(product_id_sequence.nextval,'Road Bicycle',15,4,34.95,'S520',18); insert into product values(product_id_sequence.nextval,'Mountain Bicycle',19,4,49.99,'S520',20); insert into product values(product_id_sequence.nextval,'Tire Pump',8,2,7.99,'S530',10); insert into product values(product_id_sequence.nextval,'Water Bottle',25,4,2.49,'S510',25); insert into product values(product_id_sequence.nextval,'Bicycle Tires',30,5,4.99,'S500',33); insert into product values(product_id_sequence.nextval,'Bicycle Helmet',23,6,10.95,'S510',25); create sequence po_sequence start with 11 nocache; insert into purchase_order values('PO'||po_sequence.nextval,to_date('5/25/12','mm/dd/yy'),10011,20,'S500'); insert into purchase_order values('PO'||po_sequence.nextval,to_date('5/12/12','mm/dd/yy'),10015,25,'S530'); insert into purchase_order values('PO'||po_sequence.nextval,to_date('6/25/12','mm/dd/yy'),10011,20,'S500'); insert into purchase_order values('PO'||po_sequence.nextval,to_date('6/15/12','mm/dd/yy'),10018,10,'S530'); insert into purchase_order values('PO'||po_sequence.nextval,to_date('7/10/12','mm/dd/yy'),10015,25,'S530'); insert into purchase_order values('PO'||po_sequence.nextval,to_date('7/25/12','mm/dd/yy'),10019,25,'S510'); create sequence customer_sequence start with 101 nocache; insert into customer values(customer_sequence.nextval,'Jack','Russell','25 North Madison Ave.','Springfield','MO',65807,'4178823434'); insert into customer values(customer_sequence.nextval,'Betty','Trumbell','550 South Court Dr.','St. Louis','MO',63140,'3125556670'); insert into customer values(customer_sequence.nextval,'Anil','Kaul','400 South Circle St.','Kansas City','MO',64530,'4316667070'); insert into customer values(customer_sequence.nextval,'Tom','Wiley','1500 North Grand St.','Springfield','MO',65810,'4178825560'); insert into customer values(customer_sequence.nextval,'Sharon','Stone','200 West Wagner St.','Springfield','MO',65807,'4176668890'); create sequence membership_sequence start with 10010 increment by 10 nocache; insert into club_membership values(membership_sequence.nextval,to_date('6/12/12','mm/dd/yy'),4,200,'CC',100,101); insert into club_membership values(membership_sequence.nextval,to_date('6/15/12','mm/dd/yy'),2,100,'Check',110,102); insert into club_membership values(membership_sequence.nextval,to_date('6/21/12','mm/dd/yy'),5,250,'Check',120,103); create sequence product_order_sequence start with 1001 nocache; insert into product_order values(product_order_sequence.nextval,to_date('5/27/12','mm/dd/yy'),to_date('6/1/12','mm/dd/yy'),'CC',134.95,102); insert into product_order values(product_order_sequence.nextval,to_date('5/28/12','mm/dd/yy'),to_date('6/2/12','mm/dd/yy'),'CC',134.85,103); insert into product_order values(product_order_sequence.nextval,to_date('5/28/12','mm/dd/yy'),to_date('6/3/12','mm/dd/yy'),'Check',12.45,104); insert into product_order values(product_order_sequence.nextval,to_date('6/5/12','mm/dd/yy'),to_date('6/10/12','mm/dd/yy'),'CC',44.43,105); insert into product_order values(product_order_sequence.nextval,to_date('6/6/12','mm/dd/yy'),to_date('6/8/12','mm/dd/yy'),'Check',52.48,103); insert into product_order values(product_order_sequence.nextval,to_date('6/8/12','mm/dd/yy'),to_date('6/12/12','mm/dd/yy'),'CC',131.94,104); insert into order_details values(1001,10011,2); insert into order_details values(1001,10015,3); insert into order_details values(1002,10011,5); insert into order_details values(1002,10016,2); insert into order_details values(1003,10019,5); insert into order_details values(1004,10018,3); insert into order_details values(1004,10011,1); insert into order_details values(1004,10019,3); insert into order_details values(1005,10017,1); insert into order_details values(1005,10019,1); insert into order_details values(1005,10021,1); insert into order_details values(1006,10012,4); insert into order_details values(1006,10015,2); commit;
The Problem can be solved by taking following steps 1.) First we have to find out how many times a product is ordered.We can Get this from table PRODUCT AND ORDER_DETAILS BY TAKING CROSS PRODUCT. 2.)NOW WE HAVE CALCULATED NO OF ORDERS,SO NOW WE CAN SET THE VALUE OF DEMAND_STATUS BASED ON VALUE. 3.)FOLLOWING PROGRAM IS THE EXECUTION CODE: ------------------------------------------------------------------------------------------------ BEGIN dbms_output.put_line(RPAD('Product name',40,' ') || 'Demand status'); dbms_output.put_line(RPAD('************',40,' ') || '**************'); for record in (select a.product_name ,case when a.cnt_orders < 2 then 'Low Demand' else 'High Demand' end as Demand_Status from (select product_name ,count(1) as cnt_orders from product p left join order_details o on p.product_id = o.product_id group by p.product_id ,p.product_name order by p.product_id) a) LOOP dbms_output.put_line(RPAD(rec.product_name,40,' ') || record.Demand_Status); END LOOP; END;
----------------------------------------------------------------------------------------------------------------------------------------------------------------
THIS WILL PRODUCE OUTPUT AS:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
THIS IS THE FINAL OUTPUT OF THE PROGRAM.
2.)
NOW AS WE GOT ALL THE PRODUCTS THAT HAVE LOW DEMAND
WE CAN REDUCE THE PRICE VALUE FOR THEM IN PRODUCT TABLE
THIS CAN BE DONE USING FOLLOWING QUERY.
UPDATE product product.price=price-1 from product p left join order_details o on p.product_id = o.product_id where count(1)<2 group by p.product_id ,p.product_name order by p.product_id;
-------------------------------------------------------------------------------------------------------------------------------------------------
IT TOOK LOT OF EFFORT .
PLEASE APPRECIATE WITH AN UPVOTE
-------------------------------------------------------------THANK YOU-------------------------------------------------------------------