SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE user (id INTEGER NOT NULL, name TEXT);  -- other fields
INSERT INTO user VALUES (1, 'Nelson Mandela'),(2, 'Donald Trump'),(3, 'Justin Trudeau'),(4, 'Boris Johnson'),(5, 'Emmanuel Macron'),(6, 'Angela Merkel'),(7, 'Leo Varadkar'),(8, 'Narendra Modi');
SELECT * FROM user;


id
name


1
Nelson Mandela

2
Donald Trump

3
Justin Trudeau

4
Boris Johnson

5
Emmanuel Macron

6
Angela Merkel

7
Leo Varadkar

8
Narendra Modi


CREATE TABLE teams (
  id INTEGER SERIAL,
  player1 INTEGER REFERENCES User(id) NOT NULL,
  player2 INTEGER REFERENCES User(id) NOT NULL,
  rating INTEGER NOT NULL,
  PRIMARY KEY (id),
  CHECK (player1 != player2)
);
WITH cte1 AS 
(
  SELECT u1.id AS u1id, u2.id AS u2id FROM user u1, user u2
  WHERE u1.id != u2.id
)
INSERT INTO teams (player1, player2, rating)
SELECT u1id, u2id, 5 FROM cte1;
CREATE TABLE matches 
(
  id INTEGER PRIMARY KEY NOT NULL,
  team_1 INTEGER REFERENCES Team(id) NOT NULL,
  team_2 INTEGER REFERENCES Team(id) NOT NULL,
  timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO matches (id, team_1, team_2) VALUES (1, 1, 2), (2, 1, 3), (3, 1, 4), (4, 1, 5), (5, 1, 6);
CREATE TABLE match_winner
(
  match_id INTEGER NOT NULL,
  team_id  INTEGER NOT NULL,
  winning_score INTEGER NOT NULL CHECK (winning_score > 0),

  CONSTRAINT 'match_winner_pk' PRIMARY KEY (match_id, team_id),
  CONSTRAINT 'mw_match_fk'     FOREIGN KEY (match_id) REFERENCES matches (match_id),
  CONSTRAINT 'mw_team_fk'      FOREIGN KEY (team_id)  REFERENCES teams   (team_id) 
);
INSERT INTO match_winner VALUES (1, 2, 7), (3, 4, 33), (5, 1, 56);
SELECT t.player1, t.player2, m.id, mw.match_id, mw.team_id, mw.winning_score
FROM teams t
JOIN matches m ON t.player1 = m.team_1 AND t.player2 = m.team_2
JOIN match_winner mw ON m.id = mw.match_id;


player1
player2
id
match_id
team_id
winning_score


1
2
1
1
2
7

1
4
3
3
4
33

1
6
5
5
1
56


CREATE TABLE team (
  -- id INTEGER SERIAL,
  player1 INTEGER REFERENCES User(id) NOT NULL,
  player2 INTEGER REFERENCES User(id) NOT NULL,
  rating INTEGER NOT NULL,
  PRIMARY KEY (player1, player2),
  CHECK (player1 != player2)
);
WITH cte1 AS 
(
  SELECT u1.id AS u1id, u2.id AS u2id FROM user u1, user u2
  WHERE u1.id != u2.id
)
INSERT INTO team (player1, player2, rating)
SELECT u1id, u2id, 5 FROM cte1;
SELECT u1.name, u2.name, t.player1, t.player2, m.id, mw.match_id, mw.team_id, mw.winning_score
FROM team t
JOIN user u1 ON t.player1 = u1.id
JOIN user u2 ON t.player2 = u2.id
JOIN matches m ON t.player1 = m.team_1 AND t.player2 = m.team_2
JOIN match_winner mw ON m.id = mw.match_id;


name
name
player1
player2
id
match_id
team_id
winning_score


Nelson Mandela
Donald Trump
1
2
1
1
2
7

Nelson Mandela
Boris Johnson
1
4
3
3
4
33

Nelson Mandela
Angela Merkel
1
6
5
5
1
56
id	name
1	Nelson Mandela
2	Donald Trump
3	Justin Trudeau
4	Boris Johnson
5	Emmanuel Macron
6	Angela Merkel
7	Leo Varadkar
8	Narendra Modi