The WorldCups.ai Database

SELECT g.*, p.family_name, p.given_name
FROM worldcupsai.goals g
JOIN worldcupsai.players p ON g.player_id = p.player_id

SELECT g.*, t.team_name
FROM worldcupsai.goals g
JOIN worldcupsai.teams t ON g.team_id = t.team_id

SELECT t.*, c.confederation_name, c.confederation_code
FROM worldcupsai.teams t
JOIN worldcupsai.confederations c ON t.confederation_id = c.confederation_id

SELECT g.*, m.match_date, m.stage_name, m.group_name
FROM worldcupsai.goals g
JOIN worldcupsai.matches m ON g.match_id = m.match_id

SELECT m.*, t.tournament_name, t.year, t.host_country
FROM worldcupsai.matches m
JOIN worldcupsai.tournaments t ON m.tournament_id = t.tournament_id

SELECT g.*, t.tournament_name, t.year
FROM worldcupsai.goals g
JOIN worldcupsai.matches m ON g.match_id = m.match_id
JOIN worldcupsai.tournaments t ON m.tournament_id = t.tournament_id

SELECT p.family_name, p.given_name, c.confederation_code
FROM worldcupsai.players p
JOIN worldcupsai.squads s ON p.player_id = s.player_id
JOIN worldcupsai.teams t ON s.team_id = t.team_id
JOIN worldcupsai.confederations c ON t.confederation_id = c.confederation_id

SELECT m.match_id, m.match_date, s.stadium_name, s.city_name, s.country_name
FROM worldcupsai.matches m
JOIN worldcupsai.stadiums s ON m.stadium_id = s.stadium_id

SELECT tr.year, gs.stage_name, gs.group_name, gs.position, t.team_name, gs.points, gs.goal_difference
FROM worldcupsai.group_standings gs
JOIN worldcupsai.teams t ON gs.team_id = t.team_id
JOIN worldcupsai.tournaments tr ON gs.tournament_id = tr.tournament_id
ORDER BY tr.year, gs.group_name, gs.position

SQL

SELECT
  p.family_name,
  p.given_name,
  t.team_name,
  COUNT(*) AS goals
FROM worldcupsai.goals g
JOIN worldcupsai.players p ON g.player_id = p.player_id
JOIN worldcupsai.teams t ON g.player_team_id = t.team_id
WHERE g.own_goal = FALSE
GROUP BY p.player_id, p.family_name, p.given_name, t.team_name
ORDER BY goals DESC
LIMIT 10;

R

library(tidyverse)
goals <- read_csv("goals.csv")

goals |>
  filter(!own_goal) |>
  count(player_id, family_name, given_name, player_team_name, sort = TRUE) |>
  rename(goals = n) |>
  slice_head(n = 10)

Python

import pandas as pd

goals = pd.read_csv("goals.csv")

(goals
  .query("own_goal == False")
  .groupby(["player_id", "family_name", "given_name", "player_team_name"], dropna=False)
  .size()
  .reset_index(name="goals")
  .sort_values("goals", ascending=False)
  .head(10))

SQL

SELECT
  t.team_name,
  COUNT(*) AS matches_played,
  SUM(CASE WHEN ta.result = 'win' THEN 1 ELSE 0 END) AS wins,
  ROUND(AVG(CASE WHEN ta.result = 'win' THEN 1.0 ELSE 0 END) * 100, 1) AS win_pct
FROM worldcupsai.team_appearances ta
JOIN worldcupsai.teams t ON ta.team_id = t.team_id
GROUP BY t.team_id, t.team_name
HAVING COUNT(*) >= 10
ORDER BY win_pct DESC;

R

library(tidyverse)
team_appearances <- read_csv("team_appearances.csv")

team_appearances |>
  group_by(team_name) |>
  summarise(
    matches_played = n(),
    wins = sum(result == "win"),
    win_pct = round(wins / matches_played * 100, 1)
  ) |>
  filter(matches_played >= 10) |>
  arrange(desc(win_pct))

Python

import pandas as pd

team_appearances = pd.read_csv("team_appearances.csv")

(team_appearances
  .groupby("team_name")
  .agg(matches_played=("result", "count"), wins=("win", "sum"))
  .query("matches_played >= 10")
  .assign(win_pct=lambda x: (x["wins"] / x["matches_played"] * 100).round(1))
  .sort_values("win_pct", ascending=False))

SQL

SELECT
  t.tournament_name,
  t.year,
  COUNT(g.goal_id) AS total_goals,
  COUNT(DISTINCT m.match_id) AS total_matches,
  ROUND(COUNT(g.goal_id)::NUMERIC / COUNT(DISTINCT m.match_id), 2) AS goals_per_match
FROM worldcupsai.tournaments t
JOIN worldcupsai.matches m ON t.tournament_id = m.tournament_id
LEFT JOIN worldcupsai.goals g ON m.match_id = g.match_id
GROUP BY t.tournament_id, t.tournament_name, t.year
ORDER BY t.year;

R

library(tidyverse)
tournaments <- read_csv("tournaments.csv")
matches <- read_csv("matches.csv")
goals <- read_csv("goals.csv")

matches |>
  left_join(goals |> count(match_id, name = "goals"), by = "match_id") |>
  mutate(goals = replace_na(goals, 0)) |>
  group_by(tournament_id) |>
  summarise(
    total_goals = sum(goals),
    total_matches = n(),
    goals_per_match = round(total_goals / total_matches, 2)
  ) |>
  left_join(tournaments |> select(tournament_id, tournament_name, year), by = "tournament_id") |>
  arrange(year)

Python

import pandas as pd

tournaments = pd.read_csv("tournaments.csv")
matches = pd.read_csv("matches.csv")
goals = pd.read_csv("goals.csv")

goals_per_match = goals.groupby("match_id").size().reset_index(name="goals")
matches = matches.merge(goals_per_match, on="match_id", how="left").fillna({"goals": 0})

(matches
  .groupby("tournament_id")
  .agg(total_goals=("goals", "sum"), total_matches=("match_id", "count"))
  .assign(goals_per_match=lambda x: (x["total_goals"] / x["total_matches"]).round(2))
  .reset_index()
  .merge(tournaments[["tournament_id", "tournament_name", "year"]], on="tournament_id")
  .sort_values("year"))

SQL

SELECT
  t.team_name,
  COUNT(*) AS kicks_taken,
  SUM(CASE WHEN pk.converted THEN 1 ELSE 0 END) AS converted,
  ROUND(AVG(CASE WHEN pk.converted THEN 1.0 ELSE 0 END) * 100, 1) AS conversion_pct
FROM worldcupsai.penalty_kicks pk
JOIN worldcupsai.teams t ON pk.team_id = t.team_id
GROUP BY t.team_id, t.team_name
HAVING COUNT(*) >= 5
ORDER BY conversion_pct DESC;

R

library(tidyverse)
penalty_kicks <- read_csv("penalty_kicks.csv")

penalty_kicks |>
  group_by(team_name) |>
  summarise(
    kicks_taken = n(),
    converted = sum(converted),
    conversion_pct = round(converted / kicks_taken * 100, 1)
  ) |>
  filter(kicks_taken >= 5) |>
  arrange(desc(conversion_pct))

Python

import pandas as pd

penalty_kicks = pd.read_csv("penalty_kicks.csv")

(penalty_kicks
  .groupby("team_name")
  .agg(kicks_taken=("converted", "count"), converted=("converted", "sum"))
  .query("kicks_taken >= 5")
  .assign(conversion_pct=lambda x: (x["converted"] / x["kicks_taken"] * 100).round(1))
  .sort_values("conversion_pct", ascending=False))

SQL

WITH bookings_per_team AS (
  SELECT
    b.tournament_id,
    t.tournament_name,
    tr.year,
    tm.team_name,
    COUNT(*) AS total_bookings,
    SUM(CASE WHEN b.yellow_card THEN 1 ELSE 0 END) AS yellow_cards,
    SUM(CASE WHEN b.red_card OR b.second_yellow_card THEN 1 ELSE 0 END) AS send_offs,
    RANK() OVER (
      PARTITION BY b.tournament_id
      ORDER BY COUNT(*) DESC
    ) AS rank
  FROM worldcupsai.bookings b
  JOIN worldcupsai.teams tm ON b.team_id = tm.team_id
  JOIN worldcupsai.matches m ON b.match_id = m.match_id
  JOIN worldcupsai.tournaments t ON b.tournament_id = t.tournament_id
  JOIN worldcupsai.tournaments tr ON b.tournament_id = tr.tournament_id
  GROUP BY b.tournament_id, t.tournament_name, tr.year, tm.team_id, tm.team_name
)
SELECT tournament_name, year, team_name, total_bookings, yellow_cards, send_offs
FROM bookings_per_team
WHERE rank <= 5
ORDER BY year, rank;

R

library(tidyverse)
bookings <- read_csv("bookings.csv")
tournaments <- read_csv("tournaments.csv")

bookings |>
  group_by(tournament_id, tournament_name, team_name) |>
  summarise(
    total_bookings = n(),
    yellow_cards = sum(yellow_card),
    send_offs = sum(red_card | second_yellow_card),
    .groups = "drop"
  ) |>
  left_join(tournaments |> select(tournament_id, year), by = "tournament_id") |>
  group_by(tournament_id) |>
  slice_max(total_bookings, n = 5, with_ties = FALSE) |>
  arrange(year, desc(total_bookings))

Python

import pandas as pd

bookings = pd.read_csv("bookings.csv")
tournaments = pd.read_csv("tournaments.csv")

carded = (bookings
  .groupby(["tournament_id", "tournament_name", "team_name"])
  .agg(
    total_bookings=("booking_id", "count"),
    yellow_cards=("yellow_card", "sum"),
    send_offs=("sending_off", "sum")
  )
  .reset_index()
  .merge(tournaments[["tournament_id", "year"]], on="tournament_id"))

(carded
  .sort_values("total_bookings", ascending=False)
  .groupby("tournament_id")
  .head(5)
  .sort_values(["year", "total_bookings"], ascending=[True, False]))

• uuid — a universally unique identifier (the primary key format used throughout the database)
• text — a free-form text string
• integer — a whole number
• boolean — a boolean (coded TRUE/FALSE)
• date — a calendar date in YYYY-MM-DD format
• enum — a text value constrained to a fixed set of allowed values (listed in the description)

SQL schema

CREATE TABLE worldcupsai.tournaments(
  tournament_id UUID NOT NULL,
  tournament_name TEXT NOT NULL,
  men BOOLEAN NOT NULL,
  women BOOLEAN NOT NULL,
  year INTEGER NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  host_country TEXT NOT NULL,
  host_won BOOLEAN NOT NULL,
  count_teams INTEGER NOT NULL,
  group_stage BOOLEAN NOT NULL,
  second_group_stage BOOLEAN NOT NULL,
  final_round BOOLEAN NOT NULL,
  round_of_16 BOOLEAN NOT NULL,
  quarter_finals BOOLEAN NOT NULL,
  semi_finals BOOLEAN NOT NULL,
  third_place_match BOOLEAN NOT NULL,
  final BOOLEAN NOT NULL,
  PRIMARY KEY (tournament_id),
  CONSTRAINT dates_ordered CHECK (end_date >= start_date)
);

SQL schema

CREATE TABLE worldcupsai.confederations(
  confederation_id UUID NOT NULL,
  confederation_name TEXT NOT NULL,
  confederation_code TEXT NOT NULL,
  wikipedia_link_confederation TEXT,
  PRIMARY KEY (confederation_id)
);

SQL schema

CREATE TABLE worldcupsai.teams(
  team_id UUID NOT NULL,
  team_name TEXT NOT NULL,
  team_code TEXT NOT NULL,
  men BOOLEAN NOT NULL,
  women BOOLEAN NOT NULL,
  federation_name TEXT NOT NULL,
  region_name worldcupsai.region_name NOT NULL,
  confederation_id UUID NOT NULL,
  wikipedia_link_men TEXT,
  wikipedia_link_women TEXT,
  wikipedia_link_federation TEXT,
  PRIMARY KEY (team_id),
  FOREIGN KEY (confederation_id) REFERENCES worldcupsai.confederations (confederation_id)
);

SQL schema

CREATE TABLE worldcupsai.matches(
  tournament_id UUID NOT NULL,
  match_id UUID NOT NULL,
  match_name TEXT NOT NULL,
  stage_name worldcupsai.stage_name NOT NULL,
  group_name worldcupsai.group_name,
  group_stage BOOLEAN NOT NULL,
  knockout_stage BOOLEAN NOT NULL,
  replayed BOOLEAN NOT NULL,
  replay BOOLEAN NOT NULL,
  match_date DATE NOT NULL,
  match_time TEXT NOT NULL,
  stadium_id UUID NOT NULL,
  home_team_id UUID NOT NULL,
  away_team_id UUID NOT NULL,
  score TEXT NOT NULL,
  home_team_score INTEGER NOT NULL,
  away_team_score INTEGER NOT NULL,
  home_team_score_margin INTEGER NOT NULL,
  away_team_score_margin INTEGER NOT NULL,
  extra_time BOOLEAN NOT NULL,
  penalty_shootout BOOLEAN NOT NULL,
  score_penalties TEXT,
  home_team_score_penalties INTEGER,
  away_team_score_penalties INTEGER,
  result worldcupsai.match_result NOT NULL,
  home_team_win BOOLEAN NOT NULL,
  away_team_win BOOLEAN NOT NULL,
  draw BOOLEAN NOT NULL,
  PRIMARY KEY (match_id),
  FOREIGN KEY (tournament_id) REFERENCES worldcupsai.tournaments (tournament_id),
  FOREIGN KEY (stadium_id) REFERENCES worldcupsai.stadiums (stadium_id),
  FOREIGN KEY (home_team_id) REFERENCES worldcupsai.teams (team_id),
  FOREIGN KEY (away_team_id) REFERENCES worldcupsai.teams (team_id),
  CONSTRAINT different_teams CHECK (home_team_id != away_team_id),
  CONSTRAINT penalties_iff_shootout CHECK (
    (penalty_shootout = TRUE AND home_team_score_penalties IS NOT NULL
      AND away_team_score_penalties IS NOT NULL)
    OR
    (penalty_shootout = AND home_team_score_penalties IS NULL
      AND away_team_score_penalties IS NULL)
  ),
  CONSTRAINT margins_consistent CHECK (
    home_team_score_margin = home_team_score - away_team_score AND
    away_team_score_margin = away_team_score - home_team_score
  ),
  CONSTRAINT result_flags_consistent CHECK (
    (home_team_win = TRUE AND away_team_win = AND draw = FALSE
      AND result = 'home team win') OR
    (home_team_win = AND away_team_win = TRUE AND draw = FALSE
      AND result = 'away team win') OR
    (home_team_win = AND away_team_win = AND draw = TRUE
      AND result = 'draw')
  ),
  CONSTRAINT shootout_requires_extra_time CHECK (
    penalty_shootout = OR extra_time = TRUE
  ),
  CONSTRAINT scores_non_negative CHECK (
    home_team_score >= 0 AND away_team_score >= 0
  ),
  CONSTRAINT group_name_iff_group_stage CHECK (
    (group_stage = TRUE AND group_name IS NOT NULL) OR
    (knockout_stage = TRUE AND group_name IS NULL)
  )
);

SQL schema

CREATE TABLE worldcupsai.players(
  player_id UUID NOT NULL,
  family_name TEXT NOT NULL,
  given_name TEXT,
  birth_date DATE,
  male BOOLEAN NOT NULL,
  female BOOLEAN NOT NULL,
  goal_keeper BOOLEAN NOT NULL,
  defender BOOLEAN NOT NULL,
  midfielder BOOLEAN NOT NULL,
  forward BOOLEAN NOT NULL,
  count_tournaments INTEGER NOT NULL,
  list_tournaments TEXT NOT NULL,
  wikipedia_link_player TEXT,
  PRIMARY KEY (player_id)
);

SQL schema

CREATE TABLE worldcupsai.managers(
  manager_id UUID NOT NULL,
  family_name TEXT NOT NULL,
  given_name TEXT,
  male BOOLEAN NOT NULL,
  female BOOLEAN NOT NULL,
  country_name TEXT NOT NULL,
  wikipedia_link_manager TEXT,
  PRIMARY KEY (manager_id)
);

SQL schema

CREATE TABLE worldcupsai.referees(
  referee_id UUID NOT NULL,
  family_name TEXT NOT NULL,
  given_name TEXT,
  male BOOLEAN NOT NULL,
  female BOOLEAN NOT NULL,
  country_name TEXT NOT NULL,
  confederation_id UUID NOT NULL,
  wikipedia_link_referee TEXT,
  PRIMARY KEY (referee_id),
  FOREIGN KEY (confederation_id) REFERENCES worldcupsai.confederations (confederation_id)
);

SQL schema

CREATE TABLE worldcupsai.stadiums(
  stadium_id UUID NOT NULL,
  stadium_name TEXT NOT NULL,
  city_name TEXT NOT NULL,
  country_name TEXT NOT NULL,
  stadium_capacity INTEGER NOT NULL,
  wikipedia_link_stadium TEXT,
  wikipedia_link_city TEXT,
  PRIMARY KEY (stadium_id)
);

SQL schema

CREATE TABLE worldcupsai.awards(
  award_id UUID NOT NULL,
  award_name TEXT NOT NULL,
  award_description TEXT NOT NULL,
  year_introduced INTEGER NOT NULL,
  PRIMARY KEY (award_id)
);

SQL schema

CREATE TABLE worldcupsai.host_countries(
  tournament_id UUID NOT NULL,
  team_id UUID NOT NULL,
  progress worldcupsai.stage_name NOT NULL,
  PRIMARY KEY (tournament_id, team_id),
  FOREIGN KEY (tournament_id) REFERENCES worldcupsai.tournaments (tournament_id),
  FOREIGN KEY (team_id) REFERENCES worldcupsai.teams (team_id)
);

SQL schema

CREATE TABLE worldcupsai.tournament_stages(
  tournament_id UUID NOT NULL,
  stage_number INTEGER NOT NULL,
  stage_name worldcupsai.stage_name NOT NULL,
  group_stage BOOLEAN NOT NULL,
  knockout_stage BOOLEAN NOT NULL,
  unbalanced_groups BOOLEAN,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  count_matches INTEGER NOT NULL,
  count_teams INTEGER NOT NULL,
  count_scheduled INTEGER NOT NULL,
  count_replays INTEGER NOT NULL,
  count_playoffs INTEGER NOT NULL,
  count_walkovers INTEGER NOT NULL,
  PRIMARY KEY (tournament_id, stage_number),
  FOREIGN KEY (tournament_id) REFERENCES worldcupsai.tournaments (tournament_id),
  CONSTRAINT dates_ordered CHECK (end_date >= start_date),
  CONSTRAINT unbalanced_groups_only_in_group_stage CHECK (
    group_stage = TRUE OR unbalanced_groups IS NOT DISTINCT FROM FALSE
  )
);

SQL schema

CREATE TABLE worldcupsai.groups(
  tournament_id UUID NOT NULL,
  stage_number INTEGER NOT NULL,
  stage_name worldcupsai.stage_name NOT NULL,
  group_name worldcupsai.group_name NOT NULL,
  count_teams INTEGER NOT NULL,
  PRIMARY KEY (tournament_id, stage_number, group_name),
  FOREIGN KEY (tournament_id, stage_number)
    REFERENCES worldcupsai.tournament_stages (tournament_id, stage_number),
  FOREIGN KEY (tournament_id) REFERENCES worldcupsai.tournaments (tournament_id)
);

SQL schema

CREATE TABLE worldcupsai.group_standings(
  tournament_id UUID NOT NULL,
  stage_number INTEGER NOT NULL,
  stage_name worldcupsai.stage_name NOT NULL,
  group_name worldcupsai.group_name NOT NULL,
  position INTEGER NOT NULL,
  team_id UUID NOT NULL,
  played INTEGER NOT NULL,
  wins INTEGER NOT NULL,
  draws INTEGER NOT NULL,
  losses INTEGER NOT NULL,
  goals_for INTEGER NOT NULL,
  goals_against INTEGER NOT NULL,
  goal_difference INTEGER NOT NULL,
  points INTEGER NOT NULL,
  advanced BOOLEAN NOT NULL,
  PRIMARY KEY (tournament_id, stage_number, group_name, position),
  FOREIGN KEY (tournament_id, stage_number)
    REFERENCES worldcupsai.tournament_stages (tournament_id, stage_number),
  FOREIGN KEY (tournament_id) REFERENCES worldcupsai.tournaments (tournament_id),
  FOREIGN KEY (team_id) REFERENCES worldcupsai.teams (team_id),
  CONSTRAINT unique_team_in_group UNIQUE (tournament_id, stage_number, group_name, team_id),
  CONSTRAINT standings_non_negative CHECK (
    played >= 0 AND wins >= 0 AND draws >= 0 AND losses >= 0 AND
    goals_for >= 0 AND goals_against >= 0 AND points >= 0
  ),
  CONSTRAINT played_consistent CHECK (played = wins + draws + losses),
  CONSTRAINT goal_diff_consistent CHECK (goal_difference = goals_for - goals_against),
  CONSTRAINT points_consistent CHECK (
    points = (wins * 3) + draws OR
    points = (wins * 2) + draws
  )
);

SQL schema

CREATE TABLE worldcupsai.tournament_standings(
  tournament_id UUID NOT NULL,
  position INTEGER NOT NULL,
  team_id UUID NOT NULL,
  PRIMARY KEY (tournament_id, position),
  FOREIGN KEY (tournament_id) REFERENCES worldcupsai.tournaments (tournament_id),
  FOREIGN KEY (team_id) REFERENCES worldcupsai.teams (team_id),
  CONSTRAINT unique_team_in_standings UNIQUE (tournament_id, team_id)
);

SQL schema

CREATE TABLE worldcupsai.award_winners(
  tournament_id UUID NOT NULL,
  award_id UUID NOT NULL,
  shared BOOLEAN NOT NULL,
  player_id UUID NOT NULL,
  team_id UUID NOT NULL,
  PRIMARY KEY (tournament_id, award_id, player_id),
  FOREIGN KEY (tournament_id) REFERENCES worldcupsai.tournaments (tournament_id),
  FOREIGN KEY (award_id) REFERENCES worldcupsai.awards (award_id),
  FOREIGN KEY (player_id) REFERENCES worldcupsai.players (player_id),
  FOREIGN KEY (team_id) REFERENCES worldcupsai.teams (team_id)
);

SQL schema

CREATE TABLE worldcupsai.qualified_teams(
  tournament_id UUID NOT NULL,
  team_id UUID NOT NULL,
  count_matches INTEGER NOT NULL,
  progress worldcupsai.stage_name NOT NULL,
  PRIMARY KEY (tournament_id, team_id),
  FOREIGN KEY (tournament_id) REFERENCES worldcupsai.tournaments (tournament_id),
  FOREIGN KEY (team_id) REFERENCES worldcupsai.teams (team_id)
);

SQL schema

CREATE TABLE worldcupsai.squads(
  tournament_id UUID NOT NULL,
  team_id UUID NOT NULL,
  player_id UUID NOT NULL,
  shirt_number INTEGER NOT NULL,
  squad_position_name worldcupsai.squad_position_name NOT NULL,
  squad_position_code worldcupsai.squad_position_code NOT NULL,
  PRIMARY KEY (tournament_id, team_id, player_id),
  FOREIGN KEY (tournament_id) REFERENCES worldcupsai.tournaments (tournament_id),
  FOREIGN KEY (team_id) REFERENCES worldcupsai.teams (team_id),
  FOREIGN KEY (player_id) REFERENCES worldcupsai.players (player_id),
  CONSTRAINT shirt_number_valid CHECK (shirt_number BETWEEN 0 AND 99)
);

SQL schema

CREATE TABLE worldcupsai.manager_appointments(
  tournament_id UUID NOT NULL,
  team_id UUID NOT NULL,
  manager_id UUID NOT NULL,
  PRIMARY KEY (tournament_id, team_id, manager_id),
  FOREIGN KEY (tournament_id) REFERENCES worldcupsai.tournaments (tournament_id),
  FOREIGN KEY (team_id) REFERENCES worldcupsai.teams (team_id),
  FOREIGN KEY (manager_id) REFERENCES worldcupsai.managers (manager_id)
);

SQL schema

CREATE TABLE worldcupsai.referee_appointments(
  tournament_id UUID NOT NULL,
  referee_id UUID NOT NULL,
  PRIMARY KEY (tournament_id, referee_id),
  FOREIGN KEY (tournament_id) REFERENCES worldcupsai.tournaments (tournament_id),
  FOREIGN KEY (referee_id) REFERENCES worldcupsai.referees (referee_id)
);

SQL schema

CREATE TABLE worldcupsai.team_appearances(
  tournament_id UUID NOT NULL,
  match_id UUID NOT NULL,
  team_id UUID NOT NULL,
  opponent_id UUID NOT NULL,
  home_team BOOLEAN NOT NULL,
  away_team BOOLEAN NOT NULL,
  goals_for INTEGER NOT NULL,
  goals_against INTEGER NOT NULL,
  goal_differential INTEGER NOT NULL,
  extra_time BOOLEAN NOT NULL,
  penalty_shootout BOOLEAN NOT NULL,
  penalties_for INTEGER,
  penalties_against INTEGER,
  result worldcupsai.team_result NOT NULL,
  win BOOLEAN NOT NULL,
  lose BOOLEAN NOT NULL,
  draw BOOLEAN NOT NULL,
  PRIMARY KEY (tournament_id, match_id, team_id),
  FOREIGN KEY (tournament_id) REFERENCES worldcupsai.tournaments (tournament_id),
  FOREIGN KEY (match_id) REFERENCES worldcupsai.matches (match_id),
  FOREIGN KEY (team_id) REFERENCES worldcupsai.teams (team_id),
  FOREIGN KEY (opponent_id) REFERENCES worldcupsai.teams (team_id),
  CONSTRAINT result_flags_consistent CHECK (
    (win = TRUE AND lose = AND draw = AND result = 'win') OR
    (win = AND lose = TRUE AND draw = AND result = 'lose') OR
    (win = AND lose = AND draw = TRUE AND result = 'draw')
  ),
  CONSTRAINT goal_diff_consistent CHECK (
    goal_differential = goals_for - goals_against
  ),
  CONSTRAINT shootout_requires_extra_time CHECK (
    penalty_shootout = OR extra_time = TRUE
  ),
  CONSTRAINT scores_non_negative CHECK (goals_for >= 0 AND goals_against >= 0),
  CONSTRAINT penalties_non_negative CHECK (
    penalties_for >= 0 AND penalties_against >= 0
  ),
  CONSTRAINT penalties_iff_shootout CHECK (
    (penalty_shootout = TRUE AND penalties_for IS NOT NULL
      AND penalties_against IS NOT NULL) OR
    (penalty_shootout = AND penalties_for IS NULL
      AND penalties_against IS NULL)
  ),
  CONSTRAINT home_or_away CHECK (home_team != away_team)
);

SQL schema

CREATE TABLE worldcupsai.player_appearances(
  tournament_id UUID NOT NULL,
  match_id UUID NOT NULL,
  team_id UUID NOT NULL,
  home_team BOOLEAN NOT NULL,
  away_team BOOLEAN NOT NULL,
  player_id UUID NOT NULL,
  shirt_number INTEGER NOT NULL,
  position_name worldcupsai.player_position_name NOT NULL,
  position_code worldcupsai.player_position_code NOT NULL,
  starter BOOLEAN NOT NULL,
  substitute BOOLEAN NOT NULL,
  PRIMARY KEY (tournament_id, match_id, team_id, player_id),
  FOREIGN KEY (tournament_id) REFERENCES worldcupsai.tournaments (tournament_id),
  FOREIGN KEY (match_id) REFERENCES worldcupsai.matches (match_id),
  FOREIGN KEY (team_id) REFERENCES worldcupsai.teams (team_id),
  FOREIGN KEY (player_id) REFERENCES worldcupsai.players (player_id),
  CONSTRAINT starter_or_sub CHECK (
    (starter AND NOT substitute) OR (substitute AND NOT starter)
  ),
  CONSTRAINT shirt_number_valid CHECK (shirt_number BETWEEN 1 AND 99),
  CONSTRAINT home_or_away CHECK (home_team != away_team)
);

SQL schema

CREATE TABLE worldcupsai.manager_appearances(
  tournament_id UUID NOT NULL,
  match_id UUID NOT NULL,
  team_id UUID NOT NULL,
  home_team BOOLEAN NOT NULL,
  away_team BOOLEAN NOT NULL,
  manager_id UUID NOT NULL,
  PRIMARY KEY (tournament_id, match_id, team_id, manager_id),
  FOREIGN KEY (tournament_id) REFERENCES worldcupsai.tournaments (tournament_id),
  FOREIGN KEY (match_id) REFERENCES worldcupsai.matches (match_id),
  FOREIGN KEY (team_id) REFERENCES worldcupsai.teams (team_id),
  FOREIGN KEY (manager_id) REFERENCES worldcupsai.managers (manager_id),
  CONSTRAINT home_or_away CHECK (home_team != away_team)
);

SQL schema

CREATE TABLE worldcupsai.referee_appearances(
  tournament_id UUID NOT NULL,
  match_id UUID NOT NULL,
  referee_id UUID NOT NULL,
  PRIMARY KEY (tournament_id, match_id, referee_id),
  FOREIGN KEY (tournament_id) REFERENCES worldcupsai.tournaments (tournament_id),
  FOREIGN KEY (match_id) REFERENCES worldcupsai.matches (match_id),
  FOREIGN KEY (referee_id) REFERENCES worldcupsai.referees (referee_id)
);

SQL schema

CREATE TABLE worldcupsai.goals(
  goal_id UUID NOT NULL,
  tournament_id UUID NOT NULL,
  match_id UUID NOT NULL,
  team_id UUID NOT NULL,
  home_team BOOLEAN NOT NULL,
  away_team BOOLEAN NOT NULL,
  player_id UUID NOT NULL,
  shirt_number INTEGER NOT NULL,
  player_team_id UUID NOT NULL,
  minute_label TEXT NOT NULL,
  minute_regulation INTEGER NOT NULL,
  minute_stoppage INTEGER,
  match_period worldcupsai.match_period NOT NULL,
  own_goal BOOLEAN NOT NULL,
  penalty BOOLEAN NOT NULL,
  PRIMARY KEY (goal_id),
  FOREIGN KEY (tournament_id) REFERENCES worldcupsai.tournaments (tournament_id),
  FOREIGN KEY (match_id) REFERENCES worldcupsai.matches (match_id),
  FOREIGN KEY (team_id) REFERENCES worldcupsai.teams (team_id),
  FOREIGN KEY (player_id) REFERENCES worldcupsai.players (player_id),
  FOREIGN KEY (player_team_id) REFERENCES worldcupsai.teams (team_id),
  CONSTRAINT minutes_non_negative CHECK (minute_regulation >= 0),
  CONSTRAINT home_or_away CHECK (home_team != away_team),
  CONSTRAINT stoppage_only_in_stoppage_time CHECK (
    (minute_stoppage IS NULL) OR
    (match_period IN (
      'first half, stoppage time', 'second half, stoppage time',
      'extra time, first half, stoppage time',
      'extra time, second half, stoppage time'
    ))
  ),
  CONSTRAINT stoppage_required_in_stoppage_periods CHECK (
    (match_period NOT IN (
      'first half, stoppage time', 'second half, stoppage time',
      'extra time, first half, stoppage time',
      'extra time, second half, stoppage time'
    )) OR (minute_stoppage IS NOT NULL AND minute_stoppage > 0)
  )
);

SQL schema

CREATE TABLE worldcupsai.bookings(
  booking_id UUID NOT NULL,
  tournament_id UUID NOT NULL,
  match_id UUID NOT NULL,
  team_id UUID NOT NULL,
  home_team BOOLEAN NOT NULL,
  away_team BOOLEAN NOT NULL,
  player_id UUID NOT NULL,
  shirt_number INTEGER NOT NULL,
  minute_label TEXT NOT NULL,
  minute_regulation INTEGER NOT NULL,
  minute_stoppage INTEGER,
  match_period worldcupsai.match_period NOT NULL,
  yellow_card BOOLEAN NOT NULL,
  red_card BOOLEAN NOT NULL,
  second_yellow_card BOOLEAN NOT NULL,
  sending_off BOOLEAN NOT NULL,
  PRIMARY KEY (booking_id),
  FOREIGN KEY (tournament_id) REFERENCES worldcupsai.tournaments (tournament_id),
  FOREIGN KEY (match_id) REFERENCES worldcupsai.matches (match_id),
  FOREIGN KEY (team_id) REFERENCES worldcupsai.teams (team_id),
  FOREIGN KEY (player_id) REFERENCES worldcupsai.players (player_id),
  CONSTRAINT one_card_type CHECK (
    (yellow_card::int + red_card::int + second_yellow_card::int) = 1
  ),
  CONSTRAINT home_or_away CHECK (home_team != away_team),
  CONSTRAINT sending_off_consistent CHECK (
    sending_off = (red_card OR second_yellow_card)
  ),
  CONSTRAINT minutes_non_negative CHECK (minute_regulation >= 0),
  CONSTRAINT stoppage_only_in_stoppage_time CHECK (
    (minute_stoppage IS NULL) OR
    (match_period IN (
      'first half, stoppage time', 'second half, stoppage time',
      'extra time, first half, stoppage time',
      'extra time, second half, stoppage time'
    ))
  ),
  CONSTRAINT stoppage_required_in_stoppage_periods CHECK (
    (match_period NOT IN (
      'first half, stoppage time', 'second half, stoppage time',
      'extra time, first half, stoppage time',
      'extra time, second half, stoppage time'
    )) OR (minute_stoppage IS NOT NULL AND minute_stoppage > 0)
  )
);

SQL schema

CREATE TABLE worldcupsai.substitutions(
  substitution_id UUID NOT NULL,
  tournament_id UUID NOT NULL,
  match_id UUID NOT NULL,
  team_id UUID NOT NULL,
  home_team BOOLEAN NOT NULL,
  away_team BOOLEAN NOT NULL,
  player_id UUID NOT NULL,
  shirt_number INTEGER NOT NULL,
  minute_label TEXT NOT NULL,
  minute_regulation INTEGER NOT NULL,
  minute_stoppage INTEGER,
  match_period worldcupsai.match_period NOT NULL,
  going_off BOOLEAN NOT NULL,
  coming_on BOOLEAN NOT NULL,
  PRIMARY KEY (substitution_id),
  FOREIGN KEY (tournament_id) REFERENCES worldcupsai.tournaments (tournament_id),
  FOREIGN KEY (match_id) REFERENCES worldcupsai.matches (match_id),
  FOREIGN KEY (team_id) REFERENCES worldcupsai.teams (team_id),
  FOREIGN KEY (player_id) REFERENCES worldcupsai.players (player_id),
  CONSTRAINT one_direction CHECK (going_off != coming_on),
  CONSTRAINT minutes_non_negative CHECK (minute_regulation >= 0),
  CONSTRAINT home_or_away CHECK (home_team != away_team),
  CONSTRAINT stoppage_only_in_stoppage_time CHECK (
    (minute_stoppage IS NULL) OR
    (match_period IN (
      'first half, stoppage time', 'second half, stoppage time',
      'extra time, first half, stoppage time',
      'extra time, second half, stoppage time'
    ))
  ),
  CONSTRAINT stoppage_required_in_stoppage_periods CHECK (
    (match_period NOT IN (
      'first half, stoppage time', 'second half, stoppage time',
      'extra time, first half, stoppage time',
      'extra time, second half, stoppage time'
    )) OR (minute_stoppage IS NOT NULL AND minute_stoppage > 0)
  )
);

SQL schema

CREATE TABLE worldcupsai.penalty_kicks(
  penalty_kick_id UUID NOT NULL,
  tournament_id UUID NOT NULL,
  match_id UUID NOT NULL,
  team_id UUID NOT NULL,
  home_team BOOLEAN NOT NULL,
  away_team BOOLEAN NOT NULL,
  player_id UUID NOT NULL,
  shirt_number INTEGER NOT NULL,
  converted BOOLEAN NOT NULL,
  PRIMARY KEY (penalty_kick_id),
  FOREIGN KEY (tournament_id) REFERENCES worldcupsai.tournaments (tournament_id),
  FOREIGN KEY (match_id) REFERENCES worldcupsai.matches (match_id),
  FOREIGN KEY (team_id) REFERENCES worldcupsai.teams (team_id),
  FOREIGN KEY (player_id) REFERENCES worldcupsai.players (player_id),
  CONSTRAINT home_or_away CHECK (home_team != away_team)
);

• The 1934 and 1938 tournaments were purely knockout from the first round with no group stage. The groups table has no records for these tournaments.
• The 1950 tournament used a round-robin final group instead of a final match; the winner was determined by points, not a single final.
• Through 1970, group names used numbers (Group 1, Group 2, etc.).
• The 1974, 1978, and 1982 tournaments had two group stages. The groups and group_standings tables include records for both stages. In 1974 and 1978, the first stage used numbered groups and the second stage used lettered groups. In 1982, both stages used numbered groups.
• From 1986 onward, group names have used letters (Group A, Group B, etc.).

• The home nations use their own codes: ENG (England), WAL (Wales), SCO (Scotland), NIR (Northern Ireland).
• Former countries: SCG (Serbia and Montenegro), YUG (Yugoslavia), DDR (East Germany), CSK (Czechoslovakia), SUN (Soviet Union).
• The Dutch East Indies uses IDN (Indonesia) as there is no ISO Alpha-3 code for the Dutch East Indies.
• West Germany uses the code for Germany (DEU) but is labeled as "West Germany" in the team_name field.