A continuation of the case from chapters 3 and 4.

You need to modify the earlier data model to include the following detail.

• An event may be organized by one or more committees.
• A club member can work on any event regardless of committee affiliation.
• Events receive donations that have a monetary value, a description, and the name of the club member who is the contact person for each donation. It is necessary to show if the donation has been received.
• Donations come from one and only one donor, but a donor can make many donations.
• Donor information includes the name of the donating organization, address, phone number, contact person, and the contact person’s home phone number, cell number, fax number, work phone number, and e-mail address.
• Each event benefits one or more charities and a charity may also benefit from one or more events.
• For each charity, we want to track its name, contact information, personal contact, and a description of its work.
• For each event, the benefiting charities receive a percentage of the proceeds (for instance, from the Fall Ball proceeds, MADD may get 50%, Habitat for Humanity 25%, and Sierra Club 25%).

After creating the data model, write the following queries:

Answers

List all events, their location, and the total value of the donations received for each event, in descending order of the total value received.

SELECT event_name, location, SUM (amount)
			FROM  donation, event
			   WHERE donation.event_code = event.event_code
								 GROUP BY event_name, location
								    ORDER BY SUM(amount) DESCENDING;

How many donors participated in each event?

SELECT event_name, COUNT(DISTINCT org_code)
			FROM donation, event, organization
					 WHERE donation.event_code = event.event_code
      AND organization.org_code = donation.org_code
      AND org_type = 'donor'
									GROUP BY event_name;

*Note: In MS Access COUNT(DISTINCT) is not supported. See the instructors' manual for chapter 4, end of the chapter questions for problem #9. This will give you a model of how to perform this query in Access.

Which events received no donations?

SELECT event_name
			FROM event
						WHERE NOT EXISTS
								(SELECT * FROM donation
									  WHERE donation.event_code = event.event_code
           AND organization.org_code = donation.org_code 
           AND org_type = 'donor');

Which club members participated in more than three events?

SELECT last_name, first_name
			FROM personevent
						WHERE person_type = 'member'
      AND person_number IN
								(SELECT person_number FROM personevent
									  GROUP BY person_number
									     HAVING COUNT(event_code) > 3);

List each event that has not taken place yet and the names and phone numbers of each member who is working on it.

SELECT event_name, last_name, first_name, phone_number
			FROM personevent, event, person
						WHERE personevent.event_code = event.event_code
								AND personevent.person_number = person.person_number
        AND person_type = 'member'
								AND begin_date_time > SYSDATE
								   ORDER BY begin_date_time, last_name, first_name;