A continuation of the case from chapters 3, 4 and 5:

A club member first joins as a provisional member. All provisionals are given one or more mentors (a current club member) but a current club member can mentor at most one provisional member. All committees and events have chairpersons, and can have a treasurer and/or co-chairperson. A club member can be an officer of many committees.

Revise the data model and answer the following queries:

1. List all mentors and whom they are mentoring.
2. List all provisional members and their mentors.
3. List each event, the committee responsible for it, and the names of the chairpersons, co-chairs, and treasurers.
4. Which club members are mentoring more than two provisional members?
5. List all club members who have been an officer of at least three committees or events.

Answers

List all mentors and whom they are mentoring.

SELECT mentor.first_name, mentor.last_name, mentee.first_name, mentee.last_name 
FROM person mentor, person mentee
WHERE mentor.mentor_of = mentee.person_number;

List all provisional members and their mentors.

SELECT mentee.first_name, mentee.last_name, mentor.first_name, mentor.last_name
			FROM person mentor, person mentee
					WHERE mentee.member_type = ‘provisional’
					AND mentee.person_number = mentor.mentor_of   
					   ORDER BY mentee.first_name, mentee.last_name;

List each event, the committee responsible for it, and the names of the chairpersons, co-chairs and treasurers.

SELECT event_name AS event_committee, evt_officertype, last_name, first_name 
			FROM eventofficer, event, person
				  WHERE eventofficer.event_code = event.event_code
				  AND eventofficer.person_number = person.person_number
UNION
SELECT committee_name AS event_committee, officer_type, last_name, first_name
				FROM committeeofficer, committee, person
				   WHERE committeeofficer.committee_code = committee.committee_code
				   AND committeeofficer.person_number = person.person_number
ORDER BY event_committee;

List all club members who have been an officer of at least three committees or events.

CREATE VIEW officer
				AS (SELECT event_code AS event_committee, person_number, officer_type                 
   FROM eventofficer
				UNION
				   SELECT committee_code AS event_committee, person_number,    officer_type 
   FROM committeeofficer);
				
SELECT last_name, first_name
		 FROM officer, person
				  WHERE officer.person_number = person.person_number
				     GROUP BY last_name, first_name
				        HAVING COUNT(*) >= 3;