INSERT INTO Country (country_id, name) VALUES (1, 'USA'), (2, 'Canada'), (3, 'Sweden'), (4, 'Finland');
INSERT INTO Country VALUES (5, 'Germany');
INSERT INTO Player (player_id, name, country_id) VALUES
    (1, 'Auston Matthews', 1),
    (2, 'Connor McDavid', 2),
    (3, 'Rasmus Dahlin', 3),
    (4, 'Alexander Barkov', 4),
    (5, 'Patrik Laine', 4);
INSERT INTO Ranking (ranking_id, player_id, rank) VALUES
    (1, 1, 3),
    (2, 2, 1),
    (3, 3, 2),
    (4, 4, 5),
    (5, 5, 4);
INSERT INTO Tournament (tournament_id, name, country_id) VALUES
    (1, 'Four Nations Cup 2025', 2);
INSERT INTO Umpire (umpire_id, name) VALUES
    (1, 'Gord Dwyer'),
    (2, 'Jean Hebert'),
    (3, 'Wes McCauley'),
    (4, 'Chris Rooney'),
    (5, 'Scott Cherrey');
INSERT INTO Sponsor (sponsor_id, name) VALUES
    (1, 'Rogers'),
    (2, 'Discover'),
    (3, 'SAP'),
    (4, 'EA Sports'),
    (5, 'Veikkaus');

INSERT INTO Sponsored_by (sponsor_id, player_id) VALUES
    (1, 1),
    (2, 2),
    (3, 3),
    (4, 4),
    (5, 5);
INSERT INTO Match (match_id, tournament_id, umpire_id) VALUES
    (1, 1, 1),
    (2, 1, 2),
    (3, 1, 3),
    (4, 1, 4),
    (5, 1, 5);
INSERT INTO Player_in_tournament (tournament_id, player_id) VALUES
    (1, 1),
    (1, 2),
    (1, 3),
    (1, 4),
    (1, 5);
INSERT INTO Player_in_match (match_id, player_id, possiblepartner_id) VALUES
    (1, 1, NULL),
    (2, 2, NULL),
    (3, 3, NULL),
    (4, 4, NULL),
    (5, 5, NULL);

INSERT INTO Results (result_id, match_id, winner_id, loser_id, winner_score, loser_score) VALUES
    (1, 1, 2, 3, 4, 3),
    (2, 2, 1, 4, 6, 1),
    (3, 3, 4, 3, 4, 3),
    (4, 4, 1, 2, 3, 1),
    (5, 5, 2, 4, 5, 3);




SELECT Player.player_id, Player.name, Ranking.rank, Tournament.name AS tournament_name
FROM Player
JOIN Ranking ON Player.player_id = Ranking.player_id
JOIN Player_in_tournament ON Player.player_id = Player_in_tournament.player_id
JOIN Tournament ON Player_in_tournament.tournament_id = Tournament.tournament_id;

SELECT Player.player_id, Player.name
FROM Player
JOIN Player_in_tournament ON Player.player_id = Player_in_tournament.player_id;



SELECT Player.player_id, Player.name, Sponsor.name AS sponsor_name FROM Player
JOIN Sponsored_by ON Player.player_id = Sponsored_by.player_id
JOIN Sponsor ON Sponsored_by.sponsor_id = Sponsor.sponsor_id
WHERE Sponsor.name = 'Veikkaus';


SELECT Match.match_id, Tournament.name AS tournament_name, Umpire.name AS umpire_name FROM Match
JOIN Tournament ON Match.tournament_id = Tournament.tournament_id
JOIN Umpire ON Match.umpire_id = Umpire.umpire_id
WHERE Umpire.name = 'Gord Dwyer';

SELECT Results.match_id, WinnerCountry.name AS winner_team, LoserCountry.name AS loser_team, Results.winner_score, Results.loser_score FROM Results
JOIN Player AS Winner ON Results.winner_id = Winner.player_id
JOIN Country AS WinnerCountry ON Winner.country_id = WinnerCountry.country_id
JOIN Player AS Loser ON Results.loser_id = Loser.player_id
JOIN Country AS LoserCountry ON Loser.country_id = LoserCountry.country_id;