Question

In: Computer Science

Utilize the Outdoor Clubs & Product database to create a PL/SQL anonymous block program unit that...

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;

Solutions

Expert Solution

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-------------------------------------------------------------------


Related Solutions

Create a PL/SQL anonymous block that uses a nested loop (inner loop 1 to 15; outer...
Create a PL/SQL anonymous block that uses a nested loop (inner loop 1 to 15; outer loop 1 to 5) to perform computations using the SQL functions, ABS, EXP, SQRT, ROUND, MIN, MAX, LOG, MOD, REMAINDER and POWER The outer loop will use the functions, ABS, EXP, SQRT, ROUND to display the following messages (must be “ The absolute value of <outer loop index> is <value>” “ The value of e to the <outer loop index> power is <value>” “...
PL/SQL Write a PL/SQL block, using a While Loop, to calculate and print the sum of...
PL/SQL Write a PL/SQL block, using a While Loop, to calculate and print the sum of the odd integers from 10 to 120 inclusive. (Hint: 11 + 13 + 15 + . . . + 97 + 99)
Please write an anonymous PL/SQL program which uses an implicit cursor to print out the score...
Please write an anonymous PL/SQL program which uses an implicit cursor to print out the score and rank of team 'Mad Scientists' in the competition 'Science Olympiad Regional Baltimore'. Please handle exceptions. Problem 4: [15 points] Please write an anonymous PL/SQL program to print out the names of students and their school names for the team that won the first place (rank=1) in Science Olympiad Maryland State (name of a competition). drop table school cascade constraints; drop table student cascade...
PL/SQL Write a PL/SQL block, using a Case Statement that prints a student’s letter     grade...
PL/SQL Write a PL/SQL block, using a Case Statement that prints a student’s letter     grade based on the value stored in a variable called grade. Use the ACC      grading system described in the course syllabus to create the block and set     the initial value of grade as 95. Use only one print statement and no      logical operators in your code. Assume a grade can exceed 100, but it      can’t be negative. Grade Scale: Grade Scale:...
Assignment 2- 9   USING A FOR LOOP Create a PL/SQL block using a FOR loop to...
Assignment 2- 9   USING A FOR LOOP Create a PL/SQL block using a FOR loop to generate a payment schedule for a donor’s pledge, which is to be paid monthly in equal increments. The values variable for the block is starting payment due date, monthly payment amount, and a number of total monthly payments for the pledge. The list that’s generated should display a line for each monthly payment showing payment number, date due, payment amount, and donation balance (remaining...
ORACLE TASK 2-2 USING A FOR LOOP Create a PL/SQL block using a FOR loop to...
ORACLE TASK 2-2 USING A FOR LOOP Create a PL/SQL block using a FOR loop to generate a payment schedule for a donor’s pledge, which is to be paid monthly in equal increments. Values variable for the block are starting payment due date, monthly payment amount and number of total monthly payments for the pledge. The list that’s generated should display a line for each monthly payment showing payment number, date due, payment amount, and donation balance (remaining amount of...
Using your downloaded DBMS (MS SQL Server), create a new database. Create the database tables based...
Using your downloaded DBMS (MS SQL Server), create a new database. Create the database tables based on your entities defining The attributes within each table The primary and foreign keys within each table *****Show your database tables, tables attributes, primary and foreign keys***** Do not forget to check the lesson slides and videos that show you how to convert an ER/EER into a database schema, and how to create a database and tables using MS SQL Server.
Create the actual database using SQL syntax. This is completed using a Database Application (i.e Microsoft...
Create the actual database using SQL syntax. This is completed using a Database Application (i.e Microsoft Access, Oracle, or MySQL) as indicated by your professor. After creating the database – populate it with some data (could be made up). SQL syntax and the DB application will be discussed and taught in class. This is the final deliverable of the group project. Assignment is due by the due date as indicated by your professor. *Make sure to submit the completed database...
Create Procedure in (PL/SQL) called Make_Account (v_first, v_last, v_email, v_errormsg) Add a new user to the...
Create Procedure in (PL/SQL) called Make_Account (v_first, v_last, v_email, v_errormsg) Add a new user to the Account table. If the new user cannot be added, return a error message. Table Account (this is the table I need to add data using PROCEDURE) Account_no (PK) Email (VARCHAR2) first (VARCHAR2) last (VARCHAR2)
Database - SQL - Operations CS 203 Discrete Structure 2 Create a Microsoft Access Database consisting...
Database - SQL - Operations CS 203 Discrete Structure 2 Create a Microsoft Access Database consisting of the following two tables: Part_needs and Parts_Inventory Part_needs Supplier Part_number Project 23 1092 1 23 1101 3 23 9048 4 31 4975 3 31 3477 2 32 6984 4 32 9191 2 33 1001 1 Parts_Inventory Part_number Project Quantity Color_code 1001 1 14 8 1092 1 2 2 1101 3 1 1 3477 2 25 2 4975 3 6 2 6984 4 10...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT