SQL$/$PL

$1.$ In the following code, identify examples of these procedural constructs: variable, conditional control statement, reusable$/$callable program unit $($functions or methods$),$ assignment statement, and modular block structure.

DECLARE

v$\_$first$\_$name varchar$2(40)$;

v$\_$last$\_$name varchar$2(40)$;

v$\_$first$\_$letter varchar$2(1)$;

BEGIN

SELECT first$\_$name, last$\_$name INTO v$\_$first$\_$name, v$\_$last$\_$name

FROM students

WHERE student$\_$id $=105$;

v$\_$first$\_$letter :$=$ get$\_$first$\_$letter$($v$\_$last$\_$name$)$;

IF $\text{'}$N$\text{'}>$ v$\_$first$\_$letter THEN

DBMS$\_$OUTPUT.PUT$\_$LINE$($ v$\_$last$\_$name $||\text{'}$ is between A and M$\text{'})$;

ELSE

DBMS$\_$OUTPUT.PUT$\_$LINE$($ v$\_$last$\_$name $||\text{'}$ is between N and Z$\text{'})$;

END IF;

END;

a$.$ Variables?

b$.$ Conditional Control Statement?

c$.$ Reusable$/$callable program units?

d$.$ Assignment statement?

e$.$ Modular block structure?

$4.$ Which of the following PL$/$SQL blocks executes successfully? For the blocks that fail, explain why they fail.

a$.$ BEGIN

END;

b$.$ DECLARE

amount INTEGER$(10)$;

END;

c$.$ DECLARE

BEGIN

END;

d$.$ DECLARE

amount INTEGER$(10)$;

BEGIN

DBMS$\_$OUTPUT.PUT$\_$LINE$($amount$)$;

END;

$5.$ Create and execute a simple anonymous PL$/$SQL block to accomplish the following:

$$ Declares a variable $($v$\_$timestamp$)$ of datatype $$DATE$$ and populates it with the date that is six months from today using the $$DUAL$$ table. Then, display the result on the screen.

Hint: use $$ADD$\_$MONTHS$($SYSDATE$,6)$ to get six months from today.

$$ Example Output: if today is Jan. $4.2021,$ the result should be $04-$Jul$-2021$

$6.$Write and run SQL statements to perform the following tasks by using the tables below:

$$ dd$\_$donor

$$ dd$\_$project

$$ dd$\_$status

$$ dd$\_$pledge

$$ dd$\_$payment

a$)$ List each donor who has made a pledge and paid in a single lump sum payment $($i$.$e$.,$ where $$paymonths$$ is equal to $0).$ Include the donor$$s first name, last name, pledge date, and pledge amount.

Write your SQL statement and include a screenshot of the result here.

b$)$ Display an unduplicated list of projects $($ID and name$)$ that have received pledges. Include only those projects with assigned pledges.

Write your SQL statement and include a screenshot of the result here.

c$)$ Show the number of pledges made by each donor. Include the donor ID$,$ first name, last name, and number of pledges.

Write your SQL statement and include a screenshot of the result here.