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:
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;
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.
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');
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);
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 |