-- CSCI 4333 Spring 2026 Homework #5
--
-- Q1. List the id, fname, lname of every caretaker who has not committed
--     to the task 'Officiating' (as shown in the commitment table).
--
SELECT DISTINCT c.CT_Id, c.LName, c.FName
FROM caretaker AS c 
WHERE c.CT_Id NOT IN
	(SELECT DISTINCT CT_Id 
	 FROM commitment AS co INNER JOIN v_task AS v USING (VT_Id)
	 WHERE v.Name = 'Officiating');

--
-- Q2. List the id, fname, lname of every caretaker who has committed to at least one task.
--
SELECT DISTINCT c.CT_Id, c.LName, c.FName
FROM caretaker AS c INNER JOIN commitment AS co USING (CT_Id);
  
--
-- Q3. List the names of all caretakers who committed
-- to two or more tasks in the following manners. List
-- also the ids of all the committed tasks.
--	
SELECT DISTINCT c.CT_Id, 
	CONCAT(c.FName, ' ', c.LName) AS caretaker,
	GROUP_CONCAT(co.VT_ID SEPARATOR  '; ') AS `task ids`
FROM caretaker AS c INNER JOIN commitment AS co ON (c.CT_id = co.CT_id)
GROUP BY c.CT_Id, caretaker
HAVING COUNT(*) >= 2;
  
--
-- Q4. List the id, fname, lname of every 'enthusiatic' caretaker in the following manner: a person who
--   [1] is a primary caretaker.
--   [2] is a secondary caretaker.
--   [3] has committed to two or more tasks.
--
SELECT DISTINCT c.CT_Id, 
	CONCAT(c.FName, ' ', c.LName) AS `enthusiatic caretaker`,
	GROUP_CONCAT(co.VT_ID SEPARATOR  '; ') AS `task ids`
FROM caretaker AS c INNER JOIN commitment AS co ON (c.CT_id = co.CT_id)
WHERE c.CT_Id IN (SELECT DISTINCT Main_CT_Id FROM Swimmer)
AND c.CT_ID IN (SELECT DISTINCT CT_Id FROM othercaretaker)
GROUP BY 1, 2;

--
-- Q5. List all caretaker names, with their committed task names and the 
--     event names of the committments in the following manner.
--     Note that caretakers without amy committed task should also be shown. E.g. Benjamin Smith.
--
SELECT DISTINCT c.LName, c.FName, 
    IFNULL(v.Name, '') AS `Task Name`, 
	IFNULL(m.title, ' ') AS `Meet Title`
FROM caretaker c LEFT JOIN Commitment com ON (c.CT_id = com.CT_id)
    LEFT JOIN V_Task AS v ON (com.VT_Id = v.VT_Id)
    LEFT JOIN V_TaskList AS vt ON (v.VTL_Id = vt.VTL_Id)
    LEFT JOIN Meet AS m ON (vt.meetId = m.meetId);
    
--
--  Q6. List the ids, names of caretakers, and their numbers of commitments. 
--  in the meet 'UHCL Open' in the following manner and order (by
--  the descending order of the number of committed tasks in UHCL Open.)
--
SELECT DISTINCT c.CT_Id, 
    CONCAT(c.fname, ' ', c.lname) AS `caretaker`,
    COUNT(DISTINCT v.VT_id) AS `Number of committed tasks in UHCL Open`
FROM commitment AS com INNER JOIN V_Task AS v ON (com.VT_Id = v.VT_Id)
    INNER JOIN V_TaskList AS vt ON (v.VTL_Id = vt.VTL_Id)
    INNER JOIN meet AS m ON (vt.meetId = m.meetId AND m.title = 'UHCL Open')
	RIGHT JOIN caretaker AS c  ON (c.CT_id = com.CT_id)
GROUP BY c.CT_Id, `caretaker`
ORDER BY `Number of committed tasks in UHCL Open` DESC;

-- OR
WITH ut AS
(SELECT com.CT_Id, com.VT_Id
 FROM commitment AS com INNER JOIN V_Task AS v ON (com.VT_Id = v.VT_Id)
    INNER JOIN V_TaskList AS vt ON (v.VTL_Id = vt.VTL_Id)
    INNER JOIN meet AS m ON (vt.meetId = m.meetId AND m.title = 'UHCL Open'))
SELECT DISTINCT c.CT_Id, 
    CONCAT(c.fname, ' ', c.lname) AS `caretaker`,
    COUNT(DISTINCT ut.VT_id) AS `Number of committed tasks in UHCL Open`
FROM caretaker AS c LEFT JOIN ut USING (CT_Id)
GROUP BY c.CT_Id, `caretaker`
ORDER BY `Number of committed tasks in UHCL Open` DESC;

-- Note that the following SQL queries will miss Benjamin Smith.

SELECT DISTINCT c.CT_Id,
    CONCAT(c.fname, ' ', c.lname) AS `caretaker`,
    COUNT(v.VT_id) AS `Number of committed tasks in UHCL Open`
FROM caretaker AS c LEFT JOIN Commitment com ON (c.CT_id = com.CT_id)
    LEFT JOIN V_Task AS v ON (com.VT_Id = v.VT_Id)
    LEFT JOIN V_TaskList AS vt ON (v.VTL_Id = vt.VTL_Id)
    LEFT JOIN meet AS m ON (vt.meetId = m.meetId)
WHERE m.title = 'UHCL Open'
GROUP BY c.CT_Id, `caretaker`
ORDER BY `Number of committed tasks in UHCL Open` DESC;

SELECT DISTINCT c.CT_Id,
    CONCAT(c.fname, ' ', c.lname) AS `caretaker`,
    COUNT(DISTINCT v.VT_id) AS `Number of committed tasks in UHCL Open`
FROM caretaker AS c LEFT JOIN commitment com ON (c.CT_id = com.CT_id)
    INNER JOIN V_Task AS v ON (com.VT_Id = v.VT_Id)
    INNER JOIN V_TaskList AS vt ON (v.VTL_Id = vt.VTL_Id)
    INNER JOIN meet AS m ON (vt.meetId = m.meetId AND m.title = 'UHCL Open')
GROUP BY c.CT_Id, `caretaker`
ORDER BY `Number of committed tasks in UHCL Open` DESC;

--
-- Q7. For every swimmer, list the id, name, number of events participated in,
--     the number of levels achieved (in the level history), and the number of 
--     secondary caretakers (as in the othercaretaker table) in the following manner.
--

WITH L AS
(SELECT SwimmerId, COUNT(LevelId) AS n_levels
 FROM levelHistory
 GROUP BY SwimmerId),
 P AS
(SELECT SwimmerId, COUNT(EventId) AS n_events
 FROM participation
 GROUP BY SwimmerId),
 OC AS
 (SELECT SwimmerId, COUNT(CT_Id) AS n_oc
 FROM othercaretaker
 GROUP BY SwimmerId)
SELECT s.SwimmerId, 
	CONCAT(s.FName, ' ', s.LName) AS swimmer, 
    IFNULL(L.n_levels, 0) AS `# historical levels`, 
	IFNULL(P.n_events, 0) AS `# events participated`,
	IFNULL(OC.n_oc, 0) AS `# secondary caretakers`
FROM Swimmer AS s LEFT JOIN L ON (s.SwimmerId = L.SwimmerId)
    LEFT JOIN P ON (s.SwimmerId = p.SwimmerId)
	LEFT JOIN OC ON (s.SwimmerId = OC.swimmerId);