SELECT l.id, l.title, l.rating, l.year
FROM library l
JOIN (
    SELECT year, MAX(rating) AS max_rating
    FROM library
    GROUP BY year
) AS year_max
ON l.year = year_max.year AND l.rating = year_max.max_rating
ORDER BY l.year ASC;

SELECT p.first_name AS "First Name", 
       p.last_name AS "Last Name", 
       COUNT(r.sport_id) AS "Number of Sports Registered"
FROM player p
JOIN registration r ON p.player_id = r.player_id
WHERE r.payment_status = 'Paid' 
  AND YEAR(r.reg_date) = 2024
GROUP BY p.first_name, p.last_name;

SELECT c.First_Name AS "First Name", 
       c.Last_Name AS "Last Name", 
       a.Account_ID AS "Account ID"
FROM customer c
JOIN account a ON c.Customer_ID = a.Customer_ID
JOIN transaction t ON a.Account_ID = t.Account_ID
WHERE t.Amount > 30000;

SELECT Transaction_ID, Account_ID
FROM transaction
WHERE Transaction_Type = 'debit'
AND Amount > 45000;