Banner

 

5 - Many-to-Many Relationship

A continuation of the case from chapters 3 and 4.

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

After creating the data model, write the following queries:

  1. List all events, their location, and the total value of the donations received for each event, in descending order of the total value received.
  2. How many donors participated in each event?
  3. Which events received no donations?
  4. Which club members participated in more than three events?
  5. List each event that has not taken place yet and the names and phone numbers of each member who is working on it.

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;

 

This page is part of the promotional and support material for Data Management (open edition) by Richard T. Watson
For questions and comments please contact the author
Date revised: 10-Dec-2021