Question

Use the Donor database attached to the assignment answer the following:

Question 1:

Create a block to retrieve and display pledge and payment information for a specific donor. For each pledge payment from the donor, display the pledge ID, pledge amount, number of monthly payments, payment date, and payment amount. The list should be sorted by pledge ID and then by payment date. For the first payment made for each pledge, display “first payment” on that output row.

2 marks

Question 2:

Redo question 1, but use a different cursor form to perform the same task.

1 mark

Question 3:

An administration page in the DoGood Donor application allows employees to enter multiple combinations of donor type and pledge amount to determine data to retrieve. Create a block with a single cursor that allows retrieving data and handling multiple combinations of donor type and pledge amount as input. The donor name and pledge amount should be retrieved and displayed for each pledge that matches the donor type and is greater than the pledge amount indicated. Use a collection to provide the input data. Test the block using the following input data. Keep in mind that these inputs should be processed with one execution of the block. The donor type code I represents Individual, and B represents Business.

DonorType	PledgeAmount
I	250
B	500

2 marks

Question 4

Create a block with a single cursor that can perform a different query of pledge payment data based on user input. Input provided to the block includes a donor ID and an indicator value of D or S. The D represents details and indicates that each payment on all pledges the donor has made should be displayed. The S indicates displaying summary data of the pledge payment total for each pledge the donor has made.

2 marks

Question 5

The DoGood Donor application contains a page that allows administrators to change the ID assigned to a donor in the DD_DONOR table. Create a PL/SQL block to handle this task. Include exception-handling code to address an error raised by attempting to enter a duplicate donor ID. If this error occurs, display the message “This ID is already assigned.” Test the code by changing donor ID 305.
(Don’t include a COMMIT statement; roll back any DML actions used.)

3 marks

Answer 1

Hey, I have solved your question and will give the instructions below,

I hope you will like the answer and if you still have any problem then do let me know in the comments,

Question 1:

1> Declare

CURSOR PLEDGE_PAYMENT IS

SELECT DDP.IDPLEDGE, DDP.PLEDGEAMT, DDP.PAYMONTHS, DDPY.PAYDATE , DDPY.PAYAMT FROM ((DD_DONOR DDD INNER JOIN DD_PLEDGE DDP ON DD_DONOR.IDDONOR=DD_PLEDGE.IDDONOR) INNER JOIN DD_PAYMENT DDPYon DDP.IDPLEDGE = DDPY.IDPLEDGE) ORDER BY IDPLEDGE, PAYDATE.

Rec_Pledge_Payment Pledge_Payment%rowtype;

BEGIN

OPEN PLEDGE_PAYMENT;

LOOP

FETCH PLEDGE_PAYMENT INTO Rec_Pledge_Payment;

EXIT When PLEDGE_PAYMENT%NOTFOUND:

dbms_output.putline(PLEDGE_PAYMENT.IDPLEDGE||' '|| PLEDGE_PAYMENT.PLEDGEAMT||' '||PLEDGE_PAYMENT.PAYMONTHS||' '||PLEDGE_PAYMENT.PAYDATE||' '||PLEDGE_PAYMENT.PAYAMT);

ENDLOOP;

END;

I hope you liked it,

Thankyou.