1. List each donor who has made a pledge and indicated a single lump sum payment
ID: 3878031 • Letter: 1
Question
1. List each donor who has made a pledge and indicated a single lump sum payment. Include first name, last name, pledge date, and pledge amount.
2. List each donor who has made a pledge and indicated monthly payments over one year. Include first name, last name, pledge date, and pledge amount. Also, display the monthly payment amount. (Equal monthly payments are made for all pledges paid in monthly payments.)
3. Display an unduplicated list of projects (ID and name) that have pledges committed. Don’t display all projects defined; list only those that have pledges assigned.
4. Display the number of pledges made by each donor. Include the donor ID, first name, last name, and number of pledges.
5. Display all pledges made before March 8, 2012. Include all column data from the DD_PLEDGE table.
--Structure --
dd donor dd project Name Null Type Name Null Type IDPROJ PROJNAME PROJSTARTDATE PROJENDDATE PROJFUNDGOAL PROJCOORD NOT NULL NUMBER (6) IDDONOR NOT NULL NUMBER (4) FIRSTNAME LASTNAME TYPECODE STREET CITY STATE ZIP PHONE FAX EMAIL NEWS DTENTERED VARCHAR2 (60) DATE DATE NUMBER (12, 2) VARCHAR2 (20) VARCHAR2 (15) VARCHAR2 (30) CHAR (1) VARCHAR2 (40) VARCHAR2 (20) CHAR (2) VARCHAR2 (9) VARCHAR2 (10) VARCHAR2 (10) VARCHAR2 (25) CHAR (1) DATE dd status Name Null Type IDSTATUS NOT NULL NUMBER (2) STATUSDESC VARCHAR2 (15) dd _pledge dd_payment Name Null Type Name Null Type NOT NULL NUMBER (5) IDPLEDGE IDDONOR PLEDGEDATE PLEDGEAMT IDPROJ IDSTATUS WRITEOFF PAYMONTHS CAMPAIGN FIRSTPLEDGE IDPAY IDPLEDGE PAYAMT PAYDATE PAYMETHOD NOT NULL NUMBER (6) NUMBER (4) DATE NUMBER (8,2) NUMBER (5) NUMBER (2) NUMBER (8,2) NUMBER (3) NUMBER (4) CHAR (1) NUMBER (5) NUMBER (8,2) DATE CHAR (2)Explanation / Answer
Solution:
After creating the tables given in the image, you can apply the following queries to retrieve the data from the tables:
(1).
select FIRSTNAME, LASTNAME, PLEDGEDATE, PLEDGEAMT
from DD_DONOR E, DD_PLEDGE G
where PAYMONTHS = 0
and E.IDDONOR = G.IDDONOR;
(2).
select FIRSTNAME, LASTNAME, PLEDGEDATE, PLEDGEAMT,
(PLEDGEAMT/PAYMONTHS) as MONTHLYPAYMENTS
from DD_PLEDGE, DD_DONOR
where PAYMONTHS = 12
and DD_DONOR.IDDONOR = DD_PLEDGE.IDDONOR;
(3).
select unique J.IDPROJ, J.PROJNAME
from DD_PROJECT J, DD_PLEDGE L
where J.IDPROJ = L.IDPROJ;
Order by J.IDPROJ;
(4)
select E.IDDONOR, FIRSTNAME, LASTNAME, count(G.IDDONOR)
as NUMBER_OF_PLEDGES
from DD_DONOR E, DD_PLEDGE G
where E.IDDONOR = G.IDDONOR
group by E.IDDONOR, FIRSTNAME, LASTNAME
order by NUMBER_OF_PLEDGES, E.IDDONOR;
(5).
select * from DD_PLEDGE
where PLEDGEDATE < '08-MAR-12'
order by IDPLEDGE;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.