-- DB Fall 2025 HW #4

-- (1)	Provide the meet name, venue name, and the coach name of every meet in the year 2016 in the following manner. Tips: the function YEAR(d) returns the year of the date d.

SELECT DISTINCT m.title AS meet, 
	v.Name AS venue,
	CONCAT(c.FName, ' ', c.LName) AS `main coach`
FROM meet AS m LEFT JOIN venue AS v ON (m.venueId = v.venueId)
	LEFT JOIN coach AS c ON (m.coachId = c.coachId)
WHERE YEAR(m.date) = 2016;

-- (2) Provide the ids and names of all swimmers and their records of level history in the following manner. The result should be shown in the order of swimmer names and levelId.
SELECT DISTINCT s.swimmerId, 
	CONCAT(s.FName, ' ', s.LName) AS swimmer,
	l.levelId,
	l.level,
	lh.StartDate AS since
FROM Swimmer AS s LEFT JOIN 
	(LevelHistory AS lh INNER JOIN Level AS l ON (lh.levelId = l.levelId)) ON (s.swimmerId = lh.swimmerId)
ORDER BY Swimmer, l.levelId ASC;

-- (3) Provide the names of all swimmers with two or more 'other caretakers' in the following format.

SELECT CONCAT(s.FName, ' ', s.LName) AS swimmer,
	COUNT(o.ct_id) AS `Number of other caretakers`
FROM Swimmer AS s INNER JOIN OtherCaretaker AS o ON (s.SwimmerId = o.SwimmerId)
GROUP BY s.SwimmerId, s.fname, s.lname
HAVING `Number of other caretakers` >= 2;

-- (4) List the names of all caretakers (primary and other secondary) of every swimmer in the following manner. The result should be in the order of swimmer and type (primary and secondary).

-- using CTE.
WITH t1 AS
	(SELECT DISTINCT Swimmerid, Main_CT_Id AS CT_Id, 'primary' AS `type` FROM Swimmer),
t2 AS (SELECT DISTINCT Swimmerid, CT_Id, 'secondary' AS `type` FROM OtherCareTaker),
t3 AS (SELECT * FROM t1 
	UNION
	SELECT * FROM t2)
SELECT DISTINCT CONCAT(s.FName, ' ', s.LName) AS swimmer,
	CONCAT(c.FName, ' ', c.LName) AS caretaker,
	t3.`type`
FROM Swimmer AS s INNER JOIN t3 ON (s.swimmerId = t3.SwimmerId)
	INNER JOIN caretaker AS c ON (c.CT_Id = t3.CT_Id)
ORDER BY swimmer, t3.`type`;

-- without using CTE	
SELECT DISTINCT CONCAT(s.FName, ' ', s.LName) AS swimmer,
	CONCAT(c.FName, ' ', c.LName) AS caretaker,
	temp.`type`
FROM ((SELECT DISTINCT Swimmerid, Main_CT_Id AS CT_Id, 'primary' AS `type` FROM Swimmer)
		UNION
	  (SELECT DISTINCT Swimmerid, CT_Id, 'secondary' AS `type` FROM OtherCareTaker)) AS temp
	 INNER JOIN Swimmer AS s ON (s.swimmerId = temp.SwimmerId) 
	 INNER JOIN caretaker AS c ON (c.CT_Id = temp.CT_Id)
ORDER BY swimmer, temp.`type`;

-- (5) List the name of the swimmers who have participated in both a '50M Butterfly' event and a '100M Butterfly' event in the following format.

SELECT DISTINCT CONCAT(s.FName, ' ', s.LName) AS swimmer
FROM Swimmer AS s INNER JOIN Participation AS p1 ON (s.SwimmerId = p1.SwimmerId)
	INNER JOIN Event AS e1 ON (p1.eventId = e1.eventId)
	INNER JOIN participation AS p2 ON (s.SwimmerId = p2.SwimmerId)
	INNER JOIN Event AS e2 ON (p2.eventId = e2.eventId)
WHERE e1.title = '50M Butterfly'
AND e2.title = '100M Butterfly';

-- (6) List the names of all swimmers and the total numbers of events they have participated in using the following format. List only those swimmers whose have the last name 'Khan' and have participated in five or more events.

SELECT CONCAT(s.fname, ' ', s.lname) AS Swimmer, 
	COUNT(p.EventId) AS `Number of Events`
FROM Swimmer s LEFT JOIN Participation p
	ON (s.SwimmerId = p.SwimmerId)
WHERE s.LName = 'Khan'
GROUP BY s.SwimmerId, s.fname, s.lname
HAVING `Number of Events` >= 5;

-- (7) (Bonus: 15%) List all swimmers who have participated in three or more events, including a '50M Butterfly' event but not a '200M Freestyle' event. For targeted practice, you must use CTE and EXCEPT in this question.

WITH t1 AS
(SELECT DISTINCT s.SwimmerId
FROM Swimmer AS s INNER JOIN Participation AS p1 ON (s.SwimmerId = p1.SwimmerId)
	INNER JOIN Event AS e1 ON (p1.eventId = e1.eventId)
WHERE e1.title = '50M Butterfly'),
t2 AS
(SELECT DISTINCT s.SwimmerId
FROM Swimmer AS s INNER JOIN Participation AS p1 ON (s.SwimmerId = p1.SwimmerId)
	INNER JOIN Event AS e1 ON (p1.eventId = e1.eventId)
WHERE e1.title = '200M Freestyle'),
t3 AS (SELECT * FROM t1 EXCEPT SELECT * FROM t2)
SELECT CONCAT(s.fname, ' ', s.lname) AS Swimmer, 
	COUNT(p.EventId) AS `Number of Events`
FROM Swimmer s LEFT JOIN Participation p
	ON (s.SwimmerId = p.SwimmerId)
	INNER JOIN t3 ON (s.SwimmerId = t3.SwimmerId)
GROUP BY s.SwimmerId, s.fname, s.lname
HAVING `Number of Events` >= 3;