--
-- Suggested Solutions of 
-- Homework #7, CSCI 4333 Spring 2017
--

-- (1) Provide the names and phones of all 
--     swimmers currently in level (of id) 3. 

select distinct FName, LName, Phone
from Swimmer
where CurrentLevelId = 3;

-- (2) Provide the names of swimmers who have signed up to participate
--     in the event '100M Butterfly' of Meet id 1. 
select distinct s.FName, s.LName
from Swimmer s join Event e join Participation p
	on (s.SwimmerId = p.SwimmerId and
		e.EventId = p.EventId)
where e.MeetId = 1
and e.Title = '100M Butterfly';

-- (3) Provide the names and phones of all secondary caretakers 
--     of a swimmer in level 2.

select distinct c.FName, c.LName, c.Phone
from Caretaker c join OtherCaretaker oc join Swimmer s
	on (c.CT_Id = oc.CT_Id and oc.SwimmerId = s.SwimmerId)
where s.CurrentLevelId = 2;

--(4) Provide the name of caretakers who are the primary (main)
--    caretakers of at least two swimmers.

select distinct c.FName, c.LName
from Caretaker c, Swimmer s1, Swimmer s2
where c.CT_Id = s1.Main_CT_Id
and c.CT_Id = s2.Main_CT_Id 
and s1.SwimmerId <> s2.SwimmerId;

-- Alternatively,

select distinct c.FName, c.LName
from Caretaker c, Swimmer s
where c.CT_Id = s.Main_CT_Id
group by c.CT_ID, c.FName, c.LName
having count(*) >= 2; 

-- (5) List the names of all caretakers who have volunteered for the
-- task 'Recording' but not the task 'Officiating'.

select distinct c.FName, c.LName
from Caretaker c 
where c.CT_Id in
	(select distinct c.CT_Id
	 from V_Task v join Commitment c
		on (v.VT_Id = c.VT_Id)
	 where v.Name ='Recording')
and c.CT_Id not in
	(select distinct c.CT_Id
	 from V_Task v join Commitment c
		on (v.VT_Id = c.VT_Id)
	 where v.Name ='Officiating');
	 
-- (6) Show the number of tasks volunteered by caretakers
-- in descendant order with the names of the caretakers in
-- the following format. This counts all commitments, rescinded,
-- carried out, or not.

select concat(ct.FName, ' ', ct.LName) as `caretaker`,
	count(c.CommitmentId) as `number of tasks volunteered`
from Caretaker ct join Commitment c
	on (ct.CT_id = c.CT_Id)
group by `caretaker`
order by `number of tasks volunteered` desc;

-- (7) For every swimmer, provide her name, her primary 
--     caretaker name, current level, signed up meet names, dates
--     and events in the following format.

select concat(s.FName, ' ', s.LName) as `swimmer`,
	concat(ct.FName, ' ', ct.LName) as `caretaker`,
	l.level as level,
	m.Title as meet,
	m.Date as `meet date`,
	e.Title as event
from Swimmer s join Caretaker ct join Level l join Meet m
	join Event e join Participation p
	on (s.Main_CT_id = ct.CT_id and s.CurrentLevelId = l.LevelId
		and m.MeetId = e.MeetId and e.EventId = p.EventId
		and p.SwimmerId = s.SwimmerId)
order by `swimmer` asc;