Question

Create a PLSQL block that retrieves and displays information for a specific project based on project ID. Display the following on a single row of output: project ID, project name, number of pledges made, total dollars pledged, and the average pledge amount

Answer 1

set serveroutput on
DECLARE           --declaration of variables in Plsql block
proj_name         d_p.projname%type;     -- projectname
proj_id           d_p.idproj%type;       -- projectid
pledge_count      d_pl.pledgeamt%type;   -- total pledge count
pledge_average    d_pl.pledgeamt%type;   -- average pledge
pledge_sum        d_pl.pledgeamt%type;   -- sum of all pledges
BEGIN
SELECT d_p.idproj, projname, sum(pledgeamt), avg(pledgeamt), count(pledgeamt)
/* The above line Selects the following identity under a object d_p */
into proj_id, proj_name, pledge_count, pledge_sum, pledge_average
from d_pl, d_p
 /* condition clause */
where d_pl.idproj = d_p.idproj
and d_p.idproj = &proj_id
group by d_p.idproj, projname;
 -- display
DBMS_OUTPUT.PUT_LINE(
'Project ID: ' || proj_id
||' Project Name: ' || proj_name
||' Total Dollars Pledged : ' || pledge_sum
||' Total Pledge: ' || pledge_count
||' Average Amount Pledged: ' || pledge_average
);
end

-- & /* */ denotes comments remove them before execution hope it clears your doubt