-- Suggested solution to HW #5 Joint DB Spring 2024

-- (1) List the names, phones and join times of all swimmers joining in the years of 1/2014 to 6/2016 ordered by join time in the following manner.
SELECT DISTINCT s.LName, s.FName, s.Phone, s.joinTime
FROM swimmer AS s
WHERE s.joinTime BETWEEN '2014-01-01' AND '2016-06-30'
ORDER BY s.joinTime ASC;
-- or
SELECT DISTINCT s.LName, s.FName, s.Phone, s.joinTime
FROM swimmer AS s
WHERE s.joinTime >= '2014-01-01'
AND s.joinTime <= '2016-06-30'
ORDER BY s.joinTime ASC;

-- (2) List the names of the swimmers who have the same last name as their primary care takers in the following manner.
SELECT DISTINCT CONCAT(s.FName, ' ', s.LName) AS swimmer,
	CONCAT(c.FName, ' ', c.LName) AS `primary caretaker`
FROM Swimmer AS s INNER JOIN Caretaker c ON (s.Main_CT_Id = c.CT_Id)
WHERE c.LName = s.LName;

-- (3) List all swimmers (their names, levels) event titles, and meet titles in which the swimmers have participated in a butterfly event of the meet.
SELECT DISTINCT DISTINCT CONCAT(s.FName, ' ', s.LName) AS swimmer,
	l.level, m.title AS meet, e.title AS event
FROM level AS l INNER JOIN Swimmer AS s ON (l.LevelId = s.CurrentLevelId)
	INNER JOIN participation AS p ON (s.SwimmerId = p.SwimmerId)
	INNER JOIN Event AS e ON (p.EventId = e.EventId)
	INNER JOIN Meet AS m ON (e.MeetId = m.MeetId)
WHERE e.Title LIKE '%Butterfly%';

-- (4) List the names of the swimmers who have participated in a 100M Freestyle event and a 50M Butterfly event in the following manner.
SELECT DISTINCT s.swimmerId,
	CONCAT(s.FName, ' ', s.LName) AS swimmer,
	e1.eventId AS `100M Freestyle eventId`,
	e2.eventId AS `50M Butterfly eventId`
FROM Swimmer AS s INNER JOIN participation AS p1 ON (s.SwimmerId = p1.SwimmerId)
	INNER JOIN event AS e1 ON (p1.eventId = e1.eventId AND e1.title = '100M Freestyle')
	INNER JOIN participation AS p2 ON (s.SwimmerId = p2.SwimmerId)
	INNER JOIN event AS e2 ON (p2.eventId = e2.eventId AND e2.title = '50M Butterfly');

-- (5) Redo (4), but show the result in the following manner.
WITH t1 AS (
	SELECT DISTINCT s.swimmerId,
		CONCAT(s.FName, ' ', s.LName) AS swimmer,
		GROUP_CONCAT(e.eventId SEPARATOR ', ') AS `100M Freestyle eventIds`
	FROM Swimmer AS s INNER JOIN participation AS p ON (s.SwimmerId = p.SwimmerId)
		INNER JOIN event AS e ON (p.eventId = e.eventId AND e.title = '100M Freestyle')
	GROUP BY 1, 2
),
t2 AS (
	SELECT DISTINCT s.swimmerId,
		CONCAT(s.FName, ' ', s.LName) AS swimmer,
		GROUP_CONCAT(e.eventId SEPARATOR ', ') AS `50M Butterfly eventIds`
	FROM Swimmer AS s INNER JOIN participation AS p ON (s.SwimmerId = p.SwimmerId)
		INNER JOIN event AS e ON (p.eventId = e.eventId AND e.title = '50M Butterfly')
	GROUP BY 1, 2
)
SELECT t1.swimmerId, t1.Swimmer, t1.`100M Freestyle eventIds`, t2.`50M Butterfly eventIds`
FROM t1 NATURAL JOIN t2;

-- (6) List the numbers of events on distances. Assume that the event title uses the format of "<distance>M <style>", e.g. "50M Butterfly". Note the order of the result is in ascending distance.

SELECT SUBSTR(e.title, 1, LOCATE("M", e.title)-1) AS `event distance`,
	COUNT(*) AS `number of events`
FROM event AS e
GROUP BY `event distance`
ORDER BY CAST(`event distance` AS INTEGER);

-- (7) List the swimmers (names and their number of participated events) who have participated in two or more non-'100M' events (events without the substring '100M' in their titles) in the following format. Note that the order of the result is in descending of the counts of non-100M event participations.
SELECT CONCAT(s.FName, ' ', s.LName) AS Swimmer, 
	COUNT(e.eventId) AS `# of non-100M Events`
FROM Swimmer AS s INNER JOIN participation AS p ON (s.swimmerId = p.swimmerId)
	INNER JOIN event AS e ON (p.eventId = e.eventId)
WHERE e.Title NOT LIKE '%100M%'
GROUP BY Swimmer
HAVING `# of non-100M Events` >= 2
ORDER BY `# of non-100M Events` DESC;