Question

Create a PL/SQL block to retrieve and display data for all pledges made in a specified month. One row of output
should be displayed for each pledge. Include the following in each row of output:
  
- Pledge ID, donor ID, and Pledge Amount
- If the pledge is being paid in a lump sum, display "LUMP SUM".
- If the pledge is being paid in monthly payments, display "Monthly - #" (With the # representing the number
of months for payment).

Answer 1

Solution:

PL/SQL Code:

SET SERVEROUTPUT ON

DECLARE

PLEDGES DD_PLEDGE%ROWTYPE;

START_MONTH_DATE DD_PLEDGE.PLEDGEDATE%TYPE := '01-OCT-12';

END_MONTH_DATE DD_PLEDGE.PLEDGEDATE%TYPE := '31-OCT-12';

BEGIN

FOR PLEDGES IN

(SELECT IDPLEDGE, IDDONOR, PLEDGEAMT, CASE

WHEN PAYMONTHS = 0 THEN 'Lump Sum.'

ELSE 'Monthly - ' || PAYMONTHS

END AS MONTHLY_PAYMENT

FROM DD_PLEDGE

WHERE PLEDGEDATE >= START_MONTH_DATE AND PLEDGEDATE <= END_MONTH_DATE

ORDER BY PAYMONTHS)

LOOP

DBMS_OUTPUT.PUT_LINE('Pledge ID: ' || PLEDGES.IDPLEDGE || ', Donor ID: '

|| PLEDGES.IDDONOR || ', Pledge Amount: ' ||to_char(PLEDGES.PLEDGEAMT,

'$9999.99') || ', Monthly Payments: ' || PLEDGES. MONTHLY_PAYMENT);

END LOOP;

END;

------------------------------------------------------------------------------------------------------------------------------------------------------

anonymous block completed

Pledge ID: 102, Donor ID: 310, Pledge Amount: $500.00, Monthly Payments: Lump Sum.

Pledge ID: 103, Donor ID: 307, Pledge Amount: $2000.00, Monthly Payments: Lump Sum.

Pledge ID: 106, Donor ID: 301, Pledge Amount: $75.00, Monthly Payments: Lump Sum.

Pledge ID: 105, Donor ID: 309, Pledge Amount: $120.00, Monthly Payments: Monthly - 12

Pledge ID: 104, Donor ID: 308, Pledge Amount: $240.00, Monthly Payments: Monthly - 12

Pledge ID: 107, Donor ID: 302, Pledge Amount: $1200.00, Monthly Payments: Monthly – 24

------------------------

desc DD_PLEDGE;

SELECT IDPLEDGE, IDDONOR, PLEDGEAMT, PAYMONTHS,

CASE WHEN PAYMONTHS = 0 THEN 'Lump Sum.'

ELSE 'Monthly - ' || PAYMONTHS

END AS MONTHLY_PAYMENT

FROM DD_PLEDGE

--WHERE PLEDGEDATE >= START_MONTH_DATE AND PLEDGEDATE <= END_MONTH_DATE

ORDER BY PAYMONTHS

IDPLEDGE IDDONOR PLEDGEAMT PAYMONTHS MONTHLY_PAYMENT   

---------- ---------- ---------- ---------- --------------------------------------------------

100 303 80 0 Lump Sum.

101 304 35 0 Lump Sum.

102 310 500 0 Lump Sum.

103 307 2000 0 Lump Sum.

106 301 75 0 Lump Sum.

111 306 1500 0 Lump Sum.

112 309 240 12 Monthly - 12   

109 301 360 12 Monthly - 12

110 303 300 12 Monthly - 12   

105 309 120 12 Monthly - 12   

104 308 240 12 Monthly - 12   

107 302 1200 24 Monthly - 24   

108 308 480 24 Monthly - 24

13 rows selected

--------------------------------------------------

#please consider my effort and give me a like...thank u...