DoGood Donor team requested data for the following queries. Write SQL statements
ID: 3916002 • Letter: D
Question
DoGood Donor team requested data for the following queries. Write SQL statements to perform the following tasks:
List each donor who is from Norfolk, VA. Include first name, last name, pledge date, and pledge amount.
List each month in year 2012 and the total amount of pledge payments made in that month. Display the month name, year and the total amount.
Find out the project, which had the highest number of credit card payments. Display the project id, name, project fund goal and the number of credit card pledge payments. (Hint: Use cc for the credit card pledge payment type.)
Display the donors who have made more than one pledge. Include the donor ID, first name, last name and number of pledges.
Display all pledges made before September 01, 2012. Include all column data from the DD_PLEDGE table.
DD_DONOR Table:
DD_PLEDGE Table
DD_STATUS Table
DD_PROJECT Table
DD_PAYMENT
IDDONOR FIRSTNAME | LASTNAME TYPECODESTREET CITY Norfolk Norfolk Chesapeake Virginia Beach VA Chesapeake Virginia Beach VA Norfolk Virginia Beach VA NEWS DTENTERED 01-SEP-12 09-SEP-12 18-SEP-12 21-SEP-12 01-MAR-13 30-SEP-12 03-0CT-12 04-0CT-12 08-0CT-12 11-SEP-12 STATE ZIPPHONE 23510 (null) FAX 301 MaryY 302 Patrick 303 Terry 304 SheIIY 305 Thonas 306 (null) 307 (nul1) 308 Betty 309 Jin 310 (null) Treanot Lee Venor Pane Sheer Coastal Developers B VA Community Org G Konklirn Tapp Unique Dezigns 243 nain St ll Hooper St. 556 Loop Lane Center Blvd. 66 Train St 3667 Shore Dr 689 Bush Dr 1l Shark Ln 200 Pine Tree Blvd. Chesapeake Connect Circle Unit 12 Chesapeake (null) 23510 7572115445 (null) (null) (null) 23322 7579390022 null) 23450 8889220004 (null) EMAIL mtrea4920md.com Y pleeNorfogmail.com I tervenr@drw. edu Y toppane@yahoo.com Y tl33488@sheer.com Y coastvAßcdev.com Y VA 23320 (null) 23455 (null) VA VA 23513 7578337467 7578337468 vacorg@biz. co Y 23455 7574550087 null) (null) 233207574442121 (null? shark11@cox.net I (null) UDezigns@cox.net VA 23320 (null) 10 VAExplanation / Answer
List each donor who is from Norfolk, VA. Include first name, last name, pledge date, and pledge amount.
Select FIRSTNAME, LASTNAME, PLEDGEDATE, PLEDGEAMT
From DD_DONOR Inner Join DD_PLEDGE On DD_DONOR.IDDONOR = DD_PLEDGE.IDDONOR
Where CITY = "Norfolk" And STATE = "VA";
List each month in the year 2012 and the total amount of pledge payments made in that month. Display the month name, year and the total amount.
Select Month(PLEDGEDATE), Year(PLEDGEDATE), Sum(PLEDGEAMT)
From DD_PLEDGE
Where Year(PLEDGEDATE) = "2012"
Group By Month(PLEDGEDATE);
Find out the project, which had the highest number of credit card payments. Display the project id, name, project fund goal and the number of credit card pledge payments. (Hint: Use cc for the credit card pledge payment type.)
Select IDPROJ, PROJNAME, PROJFUNDGOAL, Count(PAYMETHOD)
From DD_PAYMENT Inner Join DD_PLEDGE On DD_PAYMENT.IDPLEDGE = DD_PLEDGE.IDPLEDGE, DD_PLEDGE Inner Join DD_PROJECT On DD_PLEDGE.IDPROJ = DD_PROJECT.IDPROJ
Where
Count(PAYMETHOD) >= All (Select Count(PAYMETHOD) From DD_PAYMENT
From DD_PAYMENT Inner Join DD_PLEDGE On DD_PAYMENT.IDPLEDGE = DD_PLEDGE.IDPLEDGE, DD_PLEDGE Inner Join DD_PROJECT On DD_PLEDGE.IDPROJ = DD_PROJECT.IDPROJ
Group By DD_PLEDGE.IDPROJ);
Display the donors who have made more than one pledge. Include the donor ID, first name, last name and number of pledges
Select IDDONOR, FIRSTNAME, LASTNAME, Count(DD_DONOR.IDDONOR)
From DD_DONOR Inner Join DD_PLEDGE On DD_DONOR.IDDONOR = DD_PLEDGE.IDDONOR
Where Count(DD_DONOR.IDDONOR) > 1 Group By DD_DONOR.IDDONOR;
Display all pledges made before September 01, 2012. Include all column data from the DD_PLEDGE table
Select *
From DD_PLEDGE
Where Date(PLEDGEDATE) > '2012-9-1';
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.