insert_data_23511190.sql

-- ============================================================
-- Sample Data for Cricket Database
-- ============================================================

-- Tournament
INSERT INTO Tournament (name, year, format, organising_body, start_date, end_date) VALUES
('ICC T20 World Cup',        2024, 'T20',  'ICC', '2024-06-01', '2024-06-29'),
('ICC Cricket World Cup',    2023, 'ODI',  'ICC', '2023-10-05', '2023-11-19'),
('The Ashes',                2023, 'Test', 'ECB', '2023-06-16', '2023-07-31'),
('IPL',                      2024, 'T20',  'BCCI','2024-03-22', '2024-05-26'),
('ICC T20 World Cup',        2022, 'T20',  'ICC', '2022-10-16', '2022-11-13'),
('Border-Gavaskar Trophy',   2023, 'Test', 'ICC', '2023-02-09', '2023-03-13'),
('ICC Champions Trophy',     2025, 'ODI',  'ICC', '2025-02-19', '2025-03-09');

-- Stadium
INSERT INTO Stadium (name, city, country, capacity) VALUES
('Melbourne Cricket Ground',    'Melbourne',    'Australia',    100024),
('Eden Gardens',                'Kolkata',      'India',        68000),
('Lords Cricket Ground',        'London',       'England',      30000),
('Nassau County Stadium',       'New York',     'USA',          34000),
('Sydney Cricket Ground',       'Sydney',       'Australia',    48000),
('Wankhede Stadium',            'Mumbai',       'India',        33108),
('Newlands Cricket Ground',     'Cape Town',    'South Africa', 25000),
('Gaddafi Stadium',             'Lahore',       'Pakistan',     27000),
('Kensington Oval',             'Bridgetown',   'Barbados',     28000),
('Optus Stadium',               'Perth',        'Australia',    60000);

-- Player
INSERT INTO Player (name, dob, nationality, batting_style, bowling_style, player_role) VALUES
-- Australia
('Pat Cummins',         '1993-05-08', 'Australian',    'Right-hand bat', 'Right-arm fast',           'Bowler'),
('David Warner',        '1986-10-27', 'Australian',    'Left-hand bat',  NULL,                       'Batter'),
('Steve Smith',         '1989-06-02', 'Australian',    'Right-hand bat', 'Right-arm leg-break',      'Batter'),
('Mitchell Starc',      '1990-01-30', 'Australian',    'Left-hand bat',  'Left-arm fast',            'Bowler'),
('Travis Head',         '1993-12-29', 'Australian',    'Left-hand bat',  'Right-arm off-break',      'Batter'),
-- India
('Rohit Sharma',        '1987-04-30', 'Indian',        'Right-hand bat', 'Right-arm off-break',      'Batter'),
('Virat Kohli',         '1988-11-05', 'Indian',        'Right-hand bat', 'Right-arm medium',         'Batter'),
('Jasprit Bumrah',      '1993-12-06', 'Indian',        'Right-hand bat', 'Right-arm fast',           'Bowler'),
('Hardik Pandya',       '1993-10-11', 'Indian',        'Right-hand bat', 'Right-arm fast-medium',    'All-rounder'),
('Ravindra Jadeja',     '1988-12-06', 'Indian',        'Left-hand bat',  'Left-arm orthodox',        'All-rounder'),
-- England
('Ben Stokes',          '1991-06-04', 'English',       'Left-hand bat',  'Right-arm fast-medium',    'All-rounder'),
('Joe Root',            '1990-12-30', 'English',       'Right-hand bat', 'Right-arm off-break',      'Batter'),
('Jonny Bairstow',      '1989-09-26', 'English',       'Right-hand bat', NULL,                       'Batter'),
('Stuart Broad',        '1986-06-24', 'English',       'Right-hand bat', 'Right-arm fast-medium',    'Bowler'),
('James Anderson',      '1982-07-30', 'English',       'Right-hand bat', 'Right-arm fast-medium',    'Bowler'),
-- Pakistan
('Babar Azam',          '1994-10-15', 'Pakistani',     'Right-hand bat', 'Right-arm off-break',      'Batter'),
('Shaheen Afridi',      '1999-04-06', 'Pakistani',     'Left-hand bat',  'Left-arm fast',            'Bowler'),
('Mohammad Rizwan',     '1992-06-01', 'Pakistani',     'Right-hand bat', NULL,                       'Wicket-keeper'),
-- South Africa
('Kagiso Rabada',       '1995-05-25', 'South African', 'Right-hand bat', 'Right-arm fast',           'Bowler'),
('Quinton de Kock',     '1992-12-17', 'South African', 'Left-hand bat',  NULL,                       'Wicket-keeper'),
-- New Zealand
('Kane Williamson',     '1990-08-08', 'New Zealander', 'Right-hand bat', 'Right-arm off-break',      'Batter'),
('Trent Boult',         '1989-07-22', 'New Zealander', 'Right-hand bat', 'Left-arm fast-medium',     'Bowler'),
-- West Indies
('Nicholas Pooran',     '1995-10-02', 'West Indian',   'Left-hand bat',  NULL,                       'Wicket-keeper'),
('Sunil Narine',        '1988-05-26', 'West Indian',   'Left-hand bat',  'Right-arm off-break',      'All-rounder');

-- Team
INSERT INTO Team (team_name, country, coach_name, captain_id) VALUES
('Australia',       'Australia',     'Andrew McDonald',  1),   -- Pat Cummins
('India',           'India',         'Rahul Dravid',     6),   -- Rohit Sharma
('England',         'England',       'Brendon McCullum', 11),  -- Ben Stokes
('Pakistan',        'Pakistan',      'Grant Bradburn',   16),  -- Babar Azam
('South Africa',    'South Africa',  'Rob Walter',       19),  -- Kagiso Rabada
('New Zealand',     'New Zealand',   'Gary Stead',       21),  -- Kane Williamson
('West Indies',     'West Indies',   'Daren Sammy',      23);  -- Nicholas Pooran

-- Umpire
INSERT INTO Umpire (name, nationality, certification_level) VALUES
('Marais Erasmus',      'South African', 'International'),
('Kumar Dharmasena',    'Sri Lankan',    'International'),
('Richard Kettleborough', 'English',     'International'),
('Nitin Menon',         'Indian',        'International'),
('Paul Reiffel',        'Australian',    'International'),
('Joel Wilson',         'West Indian',   'International'),
('Michael Gough',       'English',       'Domestic'),
('Rod Tucker',          'Australian',    'International');

-- Match
INSERT INTO `Match` (match_date, match_type, result_summary, stadium_id, tournament_id) VALUES
-- ICC T20 World Cup 2024 (tournament_id = 1)
('2024-06-02', 'T20', 'India won by 6 runs',            4,  1),  -- match_id 1: India vs Pakistan (Nassau)
('2024-06-08', 'T20', 'Australia won by 5 wickets',     9,  1),  -- match_id 2: Australia vs England (Kensington)
('2024-06-15', 'T20', 'South Africa won by 4 wickets',  7,  1),  -- match_id 3: South Africa vs NZ (Newlands)
('2024-06-22', 'T20', 'India won by 68 runs',           9,  1),  -- match_id 4: India vs Australia SF
('2024-06-29', 'T20', 'India won by 7 runs',            9,  1),  -- match_id 5: India vs South Africa Final
-- ICC Cricket World Cup 2023 (tournament_id = 2)
('2023-10-08', 'ODI', 'India won by 8 wickets',         6,  2),  -- match_id 6: India vs Australia (Wankhede)
('2023-10-14', 'ODI', 'Australia won by 62 runs',       2,  2),  -- match_id 7: Australia vs Pakistan (Eden Gardens)
('2023-10-28', 'ODI', 'India won by 6 wickets',         6,  2),  -- match_id 8: India vs England (Wankhede)
('2023-11-11', 'ODI', 'New Zealand won by 4 wickets',   2,  2),  -- match_id 9: NZ vs Pakistan (Eden Gardens)
('2023-11-19', 'ODI', 'Australia won by 6 wickets',     6,  2),  -- match_id 10: Australia vs India Final
-- The Ashes 2023 (tournament_id = 3)
('2023-06-16', 'Test', 'Match drawn',                   3,  3),  -- match_id 11: England vs Australia (Lords)
('2023-06-28', 'Test', 'England won by 3 wickets',      3,  3),  -- match_id 12
('2023-07-06', 'Test', 'Australia won by 2 wickets',    3,  3),  -- match_id 13
-- Border-Gavaskar Trophy 2023 (tournament_id = 6)
('2023-02-09', 'Test', 'India won by an innings and 132 runs', 2, 6), -- match_id 14
('2023-03-01', 'Test', 'Australia won by 9 wickets',    1,  6),       -- match_id 15
-- ICC Champions Trophy 2025 (tournament_id = 7)
('2025-02-23', 'ODI', 'India won by 6 wickets',         8,  7),  -- match_id 16
('2025-03-09', 'ODI', 'India won by 8 runs',            8,  7);  -- match_id 17: Final

-- Member_Of
INSERT INTO Member_Of (player_id, team_id, jersey_number, date_joined) VALUES
-- Australia (team_id = 1)
(1,  1, 30, '2011-09-03'),
(2,  1, 31, '2009-01-03'),
(3,  1, 49, '2010-07-15'),
(4,  1, 56, '2010-10-17'),
(5,  1, 38, '2013-11-17'),
-- India (team_id = 2)
(6,  2, 45, '2007-06-23'),
(7,  2, 18, '2008-08-18'),
(8,  2, 93, '2016-01-23'),
(9,  2, 33, '2016-01-26'),
(10, 2, 8,  '2009-02-13'),
-- England (team_id = 3)
(11, 3, 55, '2011-07-27'),
(12, 3, 66, '2012-12-16'),
(13, 3, 21, '2011-08-02'),
(14, 3, 16, '2003-06-05'),
(15, 3, 9,  '2003-05-22'),
-- Pakistan (team_id = 4)
(16, 4, 56, '2015-05-23'),
(17, 4, 10, '2017-10-27'),
(18, 4, 15, '2015-05-31'),
-- South Africa (team_id = 5)
(19, 5, 25, '2014-08-18'),
(20, 5, 12, '2012-12-19'),
-- New Zealand (team_id = 6)
(21, 6, 22, '2010-12-09'),
(22, 6, 26, '2011-12-09'),
-- West Indies (team_id = 7)
(23, 7, 31, '2015-07-09'),
(24, 7, 11, '2011-07-22');

-- Participates_In
INSERT INTO Participates_In (team_id, match_id, team_role) VALUES
-- match 1: India vs Pakistan
(2, 1, 'Home'), (4, 1, 'Away'),
-- match 2: Australia vs England
(1, 2, 'Home'), (3, 2, 'Away'),
-- match 3: South Africa vs New Zealand
(5, 3, 'Home'), (6, 3, 'Away'),
-- match 4: India vs Australia (SF)
(2, 4, 'Home'), (1, 4, 'Away'),
-- match 5: India vs South Africa (Final)
(2, 5, 'Home'), (5, 5, 'Away'),
-- match 6: India vs Australia
(2, 6, 'Home'), (1, 6, 'Away'),
-- match 7: Australia vs Pakistan
(1, 7, 'Home'), (4, 7, 'Away'),
-- match 8: India vs England
(2, 8, 'Home'), (3, 8, 'Away'),
-- match 9: New Zealand vs Pakistan
(6, 9, 'Home'), (4, 9, 'Away'),
-- match 10: Australia vs India (Final)
(1, 10, 'Home'), (2, 10, 'Away'),
-- match 11: England vs Australia
(3, 11, 'Home'), (1, 11, 'Away'),
-- match 12: England vs Australia
(3, 12, 'Home'), (1, 12, 'Away'),
-- match 13: England vs Australia
(3, 13, 'Home'), (1, 13, 'Away'),
-- match 14: India vs Australia
(2, 14, 'Home'), (1, 14, 'Away'),
-- match 15: Australia vs India
(1, 15, 'Home'), (2, 15, 'Away'),
-- match 16: India vs Pakistan
(2, 16, 'Home'), (4, 16, 'Away'),
-- match 17: India vs NZ (Final)
(2, 17, 'Home'), (6, 17, 'Away');

-- Officiated_By
INSERT INTO Officiated_By (match_id, umpire_id, umpire_role) VALUES
(1,  1, 'On-field'), (1,  2, 'On-field'), (1,  3, 'Third'),
(2,  4, 'On-field'), (2,  5, 'On-field'), (2,  6, 'Third'),
(3,  1, 'On-field'), (3,  3, 'On-field'), (3,  7, 'Third'),
(4,  2, 'On-field'), (4,  4, 'On-field'), (4,  8, 'Third'),
(5,  1, 'On-field'), (5,  5, 'On-field'), (5,  3, 'Third'),
(6,  6, 'On-field'), (6,  2, 'On-field'), (6,  4, 'Third'),
(7,  3, 'On-field'), (7,  7, 'On-field'), (7,  1, 'Third'),
(8,  5, 'On-field'), (8,  8, 'On-field'), (8,  2, 'Third'),
(9,  4, 'On-field'), (9,  6, 'On-field'), (9,  3, 'Third'),
(10, 1, 'On-field'), (10, 2, 'On-field'), (10, 5, 'Third'),
(11, 3, 'On-field'), (11, 4, 'On-field'), (11, 6, 'Third'),
(12, 7, 'On-field'), (12, 8, 'On-field'), (12, 1, 'Third'),
(13, 2, 'On-field'), (13, 5, 'On-field'), (13, 4, 'Third'),
(14, 6, 'On-field'), (14, 3, 'On-field'), (14, 8, 'Third'),
(15, 1, 'On-field'), (15, 7, 'On-field'), (15, 2, 'Third'),
(16, 4, 'On-field'), (16, 5, 'On-field'), (16, 3, 'Third'),
(17, 2, 'On-field'), (17, 6, 'On-field'), (17, 8, 'Third');

-- Innings
INSERT INTO Innings (match_id, innings_number, total_runs, total_wickets, total_overs, total_fours, total_sixes, team_id) VALUES
-- Match 1: India vs Pakistan (T20)
(1, 1, 119, 10, 19.0, 11, 2, 2),  -- India batting
(1, 2, 113, 10, 19.4, 9,  2, 4),  -- Pakistan batting
-- Match 2: Australia vs England (T20)
(2, 1, 201, 7,  20.0, 18, 8, 1),  -- Australia batting
(2, 2, 177, 10, 18.4, 15, 5, 3),  -- England batting
-- Match 3: South Africa vs New Zealand (T20)
(3, 1, 163, 6,  20.0, 14, 6, 5),  -- SA batting
(3, 2, 159, 10, 19.3, 12, 4, 6),  -- NZ batting
-- Match 4: India vs Australia SF (T20)
(4, 1, 176, 7,  20.0, 16, 7, 2),  -- India batting
(4, 2, 108, 10, 16.2, 8,  2, 1),  -- Australia batting
-- Match 5: India vs South Africa Final (T20)
(5, 1, 176, 4,  20.0, 14, 9, 2),  -- India batting
(5, 2, 169, 8,  20.0, 13, 6, 5),  -- SA batting
-- Match 6: India vs Australia ODI WC
(6, 1, 199, 10, 40.2, 20, 3, 1),  -- Australia batting
(6, 2, 200, 2,  34.3, 22, 4, 2),  -- India batting
-- Match 7: Australia vs Pakistan ODI WC
(7, 1, 367, 9,  50.0, 32, 8, 1),  -- Australia batting
(7, 2, 305, 10, 45.2, 28, 5, 4),  -- Pakistan batting
-- Match 8: India vs England ODI WC
(8, 1, 229, 10, 45.5, 21, 3, 3),  -- England batting
(8, 2, 232, 4,  42.1, 25, 4, 2),  -- India batting
-- Match 9: NZ vs Pakistan ODI WC
(9, 1, 237, 10, 49.1, 22, 3, 4),  -- Pakistan batting
(9, 2, 238, 6,  47.3, 20, 5, 6),  -- NZ batting
-- Match 10: Australia vs India Final ODI WC
(10, 1, 240, 10, 43.0, 24, 4, 2), -- India batting
(10, 2, 241, 4,  43.0, 26, 6, 1), -- Australia batting
-- Match 11: England vs Australia Test (Ashes) - drawn
(11, 1, 393, 8,  110.0, 42, 2, 3), -- England batting
(11, 2, 386, 7,  105.3, 38, 1, 1), -- Australia batting
(11, 3, 327, 10, 95.2,  30, 2, 3), -- England 2nd innings
(11, 4, 214, 3,  60.0,  18, 0, 1), -- Australia 2nd (drawn)
-- Match 12: England vs Australia Test
(12, 1, 325, 10, 98.0,  30, 1, 1), -- Australia batting
(12, 2, 416, 9,  115.0, 40, 3, 3), -- England batting
(12, 3, 224, 10, 70.2,  20, 1, 1), -- Australia 2nd
(12, 4, 135, 7,  42.3,  12, 0, 3), -- England chase
-- Match 13: England vs Australia Test
(13, 1, 263, 10, 80.1,  24, 1, 3), -- England batting
(13, 2, 303, 7,  90.0,  28, 2, 1), -- Australia batting
(13, 3, 185, 10, 55.4,  16, 0, 3), -- England 2nd
(13, 4, 147, 8,  48.2,  12, 0, 1), -- Australia chase
-- Match 14: India vs Australia Test BGT
(14, 1, 177, 10, 58.2,  18, 1, 1), -- Australia batting
(14, 2, 487, 6,  120.0, 48, 3, 2), -- India batting
-- Match 15: Australia vs India Test BGT
(15, 1, 480, 8,  142.0, 46, 4, 1), -- Australia batting
(15, 2, 258, 10, 75.3,  22, 2, 2), -- India batting
(15, 3, 53,  1,  18.0,  6,  0, 1), -- Australia 2nd (chase)
-- Match 16: India vs Pakistan ODI CT
(16, 1, 356, 7,  50.0,  34, 8, 2), -- India batting
(16, 2, 167, 10, 32.1,  14, 2, 4), -- Pakistan batting
-- Match 17: India vs NZ Final ODI CT
(17, 1, 251, 7,  50.0,  24, 5, 6), -- NZ batting
(17, 2, 252, 2,  47.4,  28, 6, 2); -- India batting

-- Wins
INSERT INTO Wins (match_id, team_id, win_margin, win_type) VALUES
(1,  2, 6,   'Runs'),       -- India beat Pakistan
(2,  1, 5,   'Wickets'),    -- Australia beat England
(3,  5, 4,   'Wickets'),    -- SA beat NZ
(4,  2, 68,  'Runs'),       -- India beat Australia
(5,  2, 7,   'Runs'),       -- India beat SA (Final)
(6,  2, 8,   'Wickets'),    -- India beat Australia ODI
(7,  1, 62,  'Runs'),       -- Australia beat Pakistan ODI
(8,  2, 6,   'Wickets'),    -- India beat England ODI
(9,  6, 4,   'Wickets'),    -- NZ beat Pakistan ODI
(10, 1, 6,   'Wickets'),    -- Australia beat India ODI Final
(12, 3, 3,   'Wickets'),    -- England beat Australia Ashes
(13, 1, 2,   'Wickets'),    -- Australia beat England Ashes
(14, 2, 132, 'Runs'),       -- India beat Australia BGT
(15, 1, 9,   'Wickets'),    -- Australia beat India BGT
(16, 2, 189, 'Runs'),       -- India beat Pakistan CT
(17, 2, 8,   'Runs');       -- India beat NZ CT Final
-- Note: match_id 11 is a draw so no entry in Wins