Questions $[15$ marks$]$

$1.[3$ marks$]$ The Brewbean$$s application contains a page displaying order summary information, including IDBASKET, SUBTOTAL, SHIPPING, TAX and TOTAL columns from BB$\_$BASKET table. Create a PL$/$SQL block with scalar variables to retrieve this data and then display it$.$ An initialized variable should provide the IDBASKET value. Test the block using any existing basket ID

$2.[5$ marks$]$ An organization has committed to matching pledge amounts based on the donor type and pledge amount. Donor types include I $($for Individual$),$ B $($for Business organization$),$ and G $($for Grant funds$).$ The matching percentage is shown below:

Donor type Pledge Amount Matching $\%$

I $$500$ or more $30\%$

I $$300-$$$49940\%$

I $$100-$$$29950\%$

B $$10000$ or more $10\%$

B $$1000-$$$999920\%$

B $$500-$$$99940\%$

G $$1000$r more $10\%$

Create a PL$/$SQL anonymous block to accomplish the task. Input values for the block are the donor type code and the pledge amount.

$3.[3$ marks$]$ Create a PL$/$SQL anonymous block to insert a new project in DoGood Donor database. Create and use a sequence to handle generating and populating the project ID$.$ The first number issue by the sequence should be $800,$ and no caching should be used. Use a record variable to handle the data to be added. Data for the new row should be the following: project name is $$Covid$-19$ relief fund$,$ start date: Feb $1,2023,$ end date: Jun $30,2023,$ and fundraising goal is half million. Any columns not addressed in the data list are currently unknown.

$4.[4$ marks$]$ Create anonymous block to retrieve and display data for all pledges made in a specified month. One row of output should be displayed for each pledge. More specifically, each row include:

a$.$ Pledge ID$,$ donor ID$,$ and pledge amount

b$.$ If the pledge is being paid in a lump sum, display $$Lump Sum$$

c$.$ If the pledge is being paid in monthly, display $$Monthly $**$ followed by number of months for payment

d$.$ The list should be sorted to display all lump sum pledges first