/* Yan Hwee
-- Find Team with most Poke Points 2 years ago
SELECT Team.TeamID, Team.TeamName AS "Team Name", SUM(Owns.NumCaught * Pokemon.PokePoints) AS "Total PokePoints"
FROM Team
INNER JOIN Owns ON Owns.TeamID = Team.TeamID
INNER JOIN Pokemon ON Pokemon.PokemonID = Owns.PokemonID
WHERE Team.EventID IN (SELECT EventID FROM Event WHERE YEAR(EventYear) = DATEPART(YEAR, GETDATE()) - 2)
GROUP BY Team.TeamID, Team.TeamName
ORDER BY SUM(Owns.NumCaught * Pokemon.PokePoints) DESC

-- Find Pokemon that is being caught the most
SELECT Pokemon.PokemonID, Pokemon.PokemonName AS "PokemonName",	SUM(Owns.NumCaught) AS "Total Caught"
FROM Pokemon
INNER JOIN Owns ON Owns.PokemonID = Pokemon.PokemonID
GROUP BY Pokemon.PokemonID, Pokemon.PokemonName
ORDER BY SUM(Owns.NumCaught) DESC

-- Find Member with most Award
SELECT m.MemberID, m.Name AS "Member Name", COUNT(*) AS "Total Awards"
FROM Member m
INNER JOIN Award ON Award.AwardID IN (SELECT AwardID FROM Participation WHERE MemberID = m.MemberID)
GROUP BY m.MemberID, m.Name
ORDER BY COUNT(*) DESC
*/

/* Jeffrey
-- List the Team Names and the Year of past year team champions
SELECT T.TeamName, YEAR(E.EventYear) AS "Year"
FROM Team T INNER JOIN Event E
ON T.EventID = E.EventID
WHERE AwardID IN(
	SELECT AwardID
	FROM Award
	WHERE AwardName = 'Top Team')

-- Find members whose expenses are more than $500 in a single billing period
SELECT Name, MemberID, Email, Address
FROM Member
WHERE MemberID IN(
	SELECT M.PrincipalID
	FROM MemberTrans M INNER JOIN Bill B
	ON M.BillRef = B.BillRef
	WHERE B.TotalAmtDue > 500);

-- List the Member ID of the Principal Members who sponsors 2 or more members
SELECT SponsorID, COUNT(MemberID) AS "Number of Members being sponsored"
FROM Member
WHERE SponsorID IS NOT NULL
GROUP BY SponsorID
HAVING COUNT(MemberID) >= 2;
*/
/* Joel
-- List down the names of all members in ascending order and their membership tier
SELECT Name, t.TierDesc
FROM Member m
INNER JOIN MemberTier t
ON t.MemberTierID = m.MemberTierID
ORDER BY Name ASC

-- List down members who caught the most pokemon from the highest to lowest
SELECT m.MemberID, Name, NumCaught
FROM Member m
INNER JOIN Owns o
ON m.MemberID = o.MemberID
ORDER BY NumCaught DESC


-- List down all teams that participated in the 2017 MokeChase
SELECT TeamID, TeamName, e.EventID, EventYear
FROM Team t
INNER JOIN Event e
ON e.EventID = t.EventID
WHERE EventYear = '2017'
*/

---- List Down the Team ID and Team Name who took part in the 2018 MokeChase event in ascending order
--SELECT t.TeamID, t.TeamName
--FROM Team t
--INNER JOIN Event e ON e.EventID = t.EventID
--WHERE EventDesc = 'The 2018 MokeChase'
--ORDER BY t.TeamID ASC;

---- LIST all the Member ID and Name who has won the ‘Top Individual’ award
--SELECT m.MemberID , m.Name
--FROM Member m
--INNER JOIN Participation p ON p.MemberID = m.MemberID
--INNER JOIN Award a on a.AwardID = p.AwardID
--WHERE AwardDesc = 'Individual with most points'

---- Find Member with the most Female Pokemon caught
--SELECT m.MemberID , m.Name , COUNT(*) AS "Total Female Pokemons Caught "
--FROM Member m
--INNER JOIN Owns o ON o.MemberID = m.MemberID
--INNER JOIN Pokemon p ON p.PokemonID = o.PokemonID
--WHERE PokemonGender = 'F'
--GROUP BY m.MemberID , m.Name
--ORDER BY COUNT(*)


-- List the members who pay their bills with a mastercard, in ascending order of their names
SELECT m.MemberID, m.Name, m.SponsorID
FROM Member m WHERE m.SponsorID IN
    (SELECT mt.PrincipalID from MemberTrans mt
     INNER JOIN Payment pa
     ON mt.BillRef = pa.BillRef
     WHERE pa.CardType = 'M')
ORDER BY m.Name ASC;

-- List down the total amount spent by the members in descending order
SELECT m.MemberId, m.Name, SUM(b.TotalAmtDue) AS "Total Amount Spent"
FROM Member m
INNER JOIN MemberTrans mt
ON m.SponsorID = mt.PrincipalID
INNER JOIN Bill b
ON mt.BillRef = b.BillRef
WHERE b.BillEndDate IS NOT NULL
GROUP BY m.MemberID, m.Name
ORDER BY "Total Amount Spent" DESC;

-- List down the members who registered for enrichment classes in 2018 in ascending order of their names
SELECT m.MemberID, m.Name, c.ClassStartDate, c.ClassDesc
FROM Member m
INNER JOIN MemberTrans mt
ON m.MemberID = mt.PrincipalID
INNER JOIN Registration r
ON mt.TransID = r.TransID
INNER JOIN Class c
ON r.ClassID = c.ClassID
WHERE DATEPART(year, c.ClassStartDate) = 2018
ORDER BY c.ClassID ASC;