Please answer the three questions: 156 Chapter 30esson A) sou Language PROBLEMSO
ID: 3814018 • Letter: P
Question
Please answer the three questions:
156 Chapter 30esson A) sou Language PROBLEMSOLVING EXERCISES to load the Outdoor Clubs & Product datab Run the script file outdoorDB v4.sql to exercises. block program unit that displays for each spori Ex3A-1. a OL of membership id, p date n membership details in the form For those sporting clubs that do not yet and values. should be ex3ai membership, "No unit as a script file with product anonymous block program displays Ex3A-2. create a block program unit that price attribute value. Save the product table, and a status text based on the with filename ex3a2. The rule anonymous block program unit as a script file categorizing the price status text display are: Price Status Price Range Low $5.00 Medium Between 5 and 20 High 20 The program unit output is as follows: Product Name Price Status Low Medium High Ex3A-3. create a PLUSQL anonymous block program unit that counts how many products lisedn the product table fall within the price range categories shown below. Save the PLSQL anonymous block program unit a scriptfile with flename ex3a3.The rule for categorizing price ranges are: Price Range Price StatusExplanation / Answer
Answer for 3A-1)
DECLARE
MemID club_membership.membership_id%type;
MemDate club_membership.membership_date%type;
First_Name customer.first_name%type;
Last_Name customer.last_name%type;
Duration_T club_membership.duration%type;
BEGIN
SELECT cm.membership_id, cm.membership_date, cm.duration,
c.first_name, c.last_name
INTO MemID, MemDate, Duration_T, First_Name, Last_Name
FROM club_membership cm, customer c
WHERE cm.membership_id = c.membership_id;
dbms_output.put_line('club_membership: '||MemID||' club_membership:'||MemDate||' club_membership:'||Duration_T||'
customer:'||First_Name||' customer:'||Last_Name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No Membership Yet');
END;
Answer 3A-2)
DECLARE
a number (2) := 5;
b number (2) := 20;
BEGIN
IF (price < a) then
//Formatting the output
dbms_output.put_line('Product Name Price Status');
dbms_output.put_line('------------- ------------');
dbms_output.put_line( product_name || Low);
IF (price > b) then
//Formatting the output
dbms_output.put_line('Product Name Price Status');
dbms_output.put_line('------------- ------------');
dbms_output.put_line( product_name || High);
ELSE
dbms_output.put_line('Product Name Price Status');
dbms_output.put_line('------------- ------------');
dbms_output.put_line( product_name || Medium);
END IF;
END IF;
END;
Answer 3A-3)
DECLARE
a number (2) := 5;
b number (2) := 20;
nLow number(5) :=0;
nMedium number(5) :=0;
nHigh number(5) :=0;
BEGIN
IF (price < a) then
//Formatting the output
dbms_output.put_line('Product Name Price Status');
dbms_output.put_line('------------- ------------');
dbms_output.put_line( product_name || Low);
nLow++;
dbms_output.put_line( nLow);
IF (price > b) then
//Formatting the output
dbms_output.put_line('Product Name Price Status');
dbms_output.put_line('------------- ------------');
dbms_output.put_line( product_name || High);
nHigh++;
dbms_output.put_line( nHigh);
ELSE
dbms_output.put_line('Product Name Price Status');
dbms_output.put_line('------------- ------------');
dbms_output.put_line( product_name || Medium);
nMedium++;
dbms_output.put_line( nMedium);
END IF;
END IF;
END