part3_queries.sql

-- 1 filter players by batting style and role
-- stye -> 'Right-hand bat'
-- role -> batter or all rounder
SELECT 
    name,
    nationality,
    batting_style,
    player_role
FROM Player
WHERE batting_style = 'Right-hand bat'
  AND player_role IN ('Batter', 'All-rounder')
ORDER BY nationality, name;


-- 2 calculate player ages using TIMESTAMPDIFF and CURDATE
SELECT
    name,
    nationality,
    dob,
    TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age_years
FROM Player
ORDER BY age_years DESC;


-- 3 filter matches by format and year, join with stadium name
-- format shud be t20 and in the year 2024
SELECT
    m.match_id,
    m.match_date,
    s.name          AS stadium,
    s.country       AS country,
    m.result_summary
FROM `Match` m
JOIN Stadium s ON m.stadium_id = s.stadium_id
WHERE m.match_type = 'T20'
  AND YEAR(m.match_date) = 2024
ORDER BY m.match_date;

-- 4 filter stadiums by capacity with numeric condition and FORMAT()
SELECT
    name,
    city,
    country,
    FORMAT(capacity, 0) AS capacity
FROM Stadium
WHERE capacity > 50000
ORDER BY capacity DESC;

-- 5 count wins per team using LEFT JOIN, COUNT and GROUP BY
-- left join makes it so that 
-- even if team has no wins in the Wins table
-- it is stil counted as '0'
SELECT
    t.team_name,
    COUNT(w.match_id) AS total_wins
FROM Team t
LEFT JOIN Wins w ON t.team_id = w.team_id
GROUP BY t.team_id, t.team_name
ORDER BY total_wins DESC;

-- 6 aggregate innings scores grouped by cricket format
SELECT
    t.format,
    COUNT(i.innings_number)      AS total_innings,
    ROUND(AVG(i.total_runs), 2)  AS avg_runs,
    MAX(i.total_runs)            AS highest_score,
    MIN(i.total_runs)            AS lowest_score
FROM Tournament t
JOIN `Match` m  ON t.tournament_id = m.tournament_id
JOIN Innings i  ON m.match_id      = i.match_id
GROUP BY t.format
ORDER BY avg_runs DESC; 

-- 7 find non-captains using a NOT IN subquery
SELECT
    p.name,
    p.nationality,
    p.player_role
FROM Player p
WHERE p.player_id NOT IN (
    SELECT captain_id
    FROM Team
    WHERE captain_id IS NOT NULL
)
ORDER BY p.nationality, p.name;

-- 8 show teams and winner per match using joins and COALESCE
SELECT
    m.match_date,
    m.match_type,
    home_team.team_name                                     AS home_team,
    away_team.team_name                                     AS away_team,
    COALESCE(wt.team_name, 'No result / Draw')              AS winner,
    COALESCE(CONCAT(w.win_margin, ' ', w.win_type), 'N/A') AS margin
FROM `Match` m
JOIN Tournament tr ON m.tournament_id = tr.tournament_id
JOIN Participates_In pi_h  ON m.match_id = pi_h.match_id AND pi_h.team_role = 'Home'
JOIN Team home_team        ON pi_h.team_id = home_team.team_id
JOIN Participates_In pi_a  ON m.match_id = pi_a.match_id AND pi_a.team_role = 'Away'
JOIN Team away_team        ON pi_a.team_id = away_team.team_id
LEFT JOIN Wins w           ON m.match_id = w.match_id
LEFT JOIN Team wt          ON w.team_id  = wt.team_id
WHERE tr.name = 'ICC T20 World Cup'
  AND tr.year = 2024
ORDER BY m.match_date;