-- Suggested solution to HW #4, Joint DB, Spring 2025

-- (1) List all swimmer names and their primary caretaker names in the following format.

SELECT DISTINCT CONCAT(s.FName, ' ', s.LName) AS swimmer,
    CONCAT(c.FName, ' ', c.LName) AS `Primary Caretaker`
FROM swimmer AS s INNER JOIN caretaker AS c ON (s.Main_CT_Id = c.CT_Id);

-- (2) List the names of the swimmers with current level (i.e. currentLevelId) is Yellow or  above. Note that the level of Yellow has a LevelId of 3. However, you should not use 3 in your query. You should use "Yellow".

SELECT DISTINCT s.LName, s.FName, s.currentLevelId
FROM Swimmer AS s, 
    (SELECT DISTINCT levelId FROM Level WHERE level = 'Yellow') AS l
WHERE s.currentLevelId >= l.levelId;

-- or
SELECT DISTINCT s.LName, s.FName, s.currentLevelId
FROM Swimmer AS s
WHERE s.currentLevelId >= 
	(SELECT DISTINCT levelId FROM Level WHERE level = 'Yellow');

-- (3) List the swimmer names (in a single column) that have participated in an event of a meet in a venue with a phone with an area code of 713 in the following format.
SELECT DISTINCT CONCAT(s.FName, ' ', s.LName) as Swimmer,
	m.Title AS Meet,
    v.name AS Venue,
	v.phone
FROM Swimmer AS s INNER JOIN Participation 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)
	INNER JOIN Venue AS v ON (m.venueId = v.venueId)
WHERE v.phone LIKE '%713%';

-- A better solution using regular expression.
SELECT DISTINCT CONCAT(s.FName, ' ', s.LName) as Swimmer,
	m.Title AS Meet,
    v.name AS Venue,
	v.phone
FROM Swimmer AS s INNER JOIN Participation 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)
	INNER JOIN Venue AS v ON (m.venueId = v.venueId)
WHERE v.phone REGEXP '\s*713-*[0-9]{3}-*[0-9]{4}\s*';

-- (4) List all swimmers (their names, levels AND event titles) who have participated in events in the meet 'UHCL Open' in the following manners.
SELECT DISTINCT CONCAT(s.FName, ' ', s.LName) as `Swimmer in UHCL Open`,
	l.level, e.title AS event
FROM Level 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 m.Title = 'UHCL Open';

-- (5) List all swimmers with their primary caretakers and their other caretakers in the following manner. Order the result by last names and then first names of the swimmers.
SELECT DISTINCT CONCAT(s.FName, ' ', s.LName) AS Swimmer,
	CONCAT(c1.FName, ' ', c1.LName) AS `Primary Caretaker`,
	IFNULL(GROUP_CONCAT(CONCAT(c2.FName, ' ', c2.LName) SEPARATOR "; "), "") AS `Other Caretakers`
FROM Swimmer AS s INNER JOIN Caretaker c1 ON (s.Main_CT_Id = c1.CT_Id)
	LEFT JOIN OtherCareTaker AS o ON (s.SwimmerId = o.SwimmerId)
	LEFT JOIN Caretaker c2 ON (o.CT_Id = c2.CT_Id)
GROUP BY s.LName, s.FName, Swimmer, `Primary Caretaker`
ORDER BY s.LName ASC, s.FName ASC;

-- (6) List the swimmers (names and their number of participated events) who have participated in two or more events in 'UHCL Open' in the following format.
SELECT DISTINCT CONCAT(s.FName, ' ', s.LName) as Swimmer,
	COUNT(e.EventId) AS `Number of Events in UHCL Open`
FROM Swimmer AS s 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 m.Title = 'UHCL Open'
GROUP BY Swimmer
HAVING `Number of Events in UHCL Open` >= 2;

-- (7) List the name of the swimmers who have  participated in in event 3 but not event 4.
SELECT CONCAT(s.fname, ' ', s.lname) AS swimmer
FROM Swimmer AS s INNER JOIN Participation AS p ON (s.SwimmerId = p.SwimmerId) 
WHERE p.EventId = 3
AND s.SwimmerId NOT IN
	(SELECT DISTINCT SwimmerId FROM participation WHERE EventId = 4);

-- (8) List the titles of the events with the least number of participants in the following format. 
WITH t1 AS (
	SELECT p.eventId, COUNT(DISTINCT p.SwimmerId) AS numSwimmers
	FROM participation AS p
	GROUP BY p.eventId
)
SELECT CONCAT(m.Title, ': ', e.Title) AS `Least popular event`, 
	t1.numSwimmers AS `number of swimmers`
FROM t1 INNER JOIN event As e ON (t1.eventId = e.eventId)
	INNER JOIN Meet AS m ON (e.MeetId = m.MeetId)
WHERE t1.numSwimmers =
	(SELECT MIN(t1.numSwimmers) FROM t1);