Soccer - Fixtures

   
   
 sql  
 backend  
Article Series

LAMP Stack

Where to Discuss?

Local Group

Table of Content

Preface

Goal: Playing SQL with Soccer Case. Fixtures.

We need an example data. For this we need to CREATE the tables first. And INSERT the data after. We are going to check the result using SELECT statement.

I had already used Firebird for a while in about 2007. And I use also mySQL a long time ago. It has been a long time since the last time I use SQL. The easiest way for me to refresh my knowldege, is to start using SQLite.

So here we are, puring my previous ERD into, an SQL schema, and test the schema in SQLite.

You can obtain the SQL source code here:

You can also download the ready to use sqlite here:

Players and Staff Name

I have already make my own database with real names in 2007. I have intended to use this as tutorial, but I cancel, because the fixtures contain real people in real life.

So I decide to make fake club with fake names. After I made this tutorial, I suddenly realize that one of the name in the database, exist in real world, such as: Ekaterina Kuznetsova. And there might be other name too in real life.

I never meant to use poeple name from real life name. I just want to make a diverse name, from different country.

CREATE TABLE

We have different tables. We need to setup the statement for use with SQLite.

People Table

Consider to start with People table.

CREATE TABLE People (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT,
    gender VARCHAR(50)
);

And pour it into the sqlite3 CLI.

SQLite: Create: People

Lookup Table

Now continue with Roles, Seat, and Responsibility table. And append each table by each SQL statement in the sqlite3 CLI.

-- Lookup table
CREATE TABLE Roles (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

-- Lookup table
CREATE TABLE Responsibilities (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

-- Lookup table
CREATE TABLE Seats (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

If you need a more fancy CLI, you can use litecli instead.

SQLite: Litecli

Relationships Table

And finally, tables who hold the relationships between tables: PeopleRoles, PlayersSeats, and StaffResps.

-- People_Role table
CREATE TABLE PeopleRoles (
    person_id INT NOT NULL,
    role_id INT NOT NULL,
    PRIMARY KEY (person_id, role_id),
    FOREIGN KEY (person_id) REFERENCES People(id),
    FOREIGN KEY (role_id) REFERENCES Roles(id)
);

-- People_Role_Seat table
CREATE TABLE PlayersSeats (
    person_id INT NOT NULL,
    role_id INT NOT NULL,
    seat_id INT NOT NULL,
    is_bench BOOLEAN DEFAULT FALSE,
    PRIMARY KEY (person_id, role_id, seat_id),
    FOREIGN KEY (person_id) REFERENCES People(id),
    FOREIGN KEY (role_id) REFERENCES Roles(id),
    FOREIGN KEY (seat_id) REFERENCES Seats(id)
);

-- People_Role_Responsibility table
CREATE TABLE StaffResps (
    person_id INT NOT NULL,
    role_id INT NOT NULL,
    resp_id INT NOT NULL,
    PRIMARY KEY (person_id, role_id, resp_id),
    FOREIGN KEY (person_id) REFERENCES People(id),
    FOREIGN KEY (role_id) REFERENCES Roles(id),
    FOREIGN KEY (resp_id) REFERENCES Responsibilities(id)
);

Again, pour the SQL statement into the sqlite3 CLI.

Check

You can check all tables by this statement.

sqlite> .tables
People            PlayersSeats      Roles             StaffResps      
PeopleRoles       Responsibilities  Seats

SQLite: Create: All Tables

Save

You can save your work by using this statement.

sqlite> .save soccer.db

Now you can close SQLite. If you ever need it again, you can open it again by using:

sqlite> .open soccer.db

INSERT

Now we can fill the data, table by table. Since we have a bunch of example data. we have to start with one table, and continue later on.

People Table

Consider start with People.

About thirty people are gathering from our neighbourhood.

INSERT INTO People (id, name, email, age, gender)
VALUES 
    (1, 'Takumi Sato', 'takumi.sato@example.com', 17, 'Male'),
    (2, 'Sakura Yamamoto', 'sakura.yamamoto@example.com', 18, 'Female'),
    (3, 'Rajesh Patel', 'rajesh.patel@example.com', 16, 'Male'),
    (4, 'dr. Komal Sharma', 'komal.sharma@example.com', 26, 'Female'),
    (5, 'Jian Chen', 'jian.chen@example.com', 15, 'Male'),
    (6, 'Yan Liu', 'yan.liu@example.com', 17, 'Female'),
    (7, 'Vladimir Ivanov', 'vladimir.ivanov@example.com', 15, 'Male'),
    (8, 'Ekaterina Petrova', 'ekaterina.petrova@example.com', 26, 'Female'),
    (9, 'Yusuf Abdullah', 'yusuf.abdullah@example.com', 17, 'Male'),
    (10, 'Fatima Al-Khalifa', 'fatima.alkhalifa@example.com', 18, 'Female'),
    (11, 'Andi Suharto', 'andi.suharto@example.com', 15, 'Male'),
    (12, 'Lia Wijaya', 'lia.wijaya@example.com', 16, 'Female'),
    (13, 'Marco Rossi', 'marco.rossi@example.com', 17, 'Male'),
    (14, 'Alessia Bianchi', 'alessia.bianchi@example.com', 16, 'Female'),
    (15, 'Gustav Andersen', 'gustav.andersen@example.com', 15, 'Male'),
    (16, 'Maria Svensson', 'maria.svensson@example.com', 18, 'Female'),
    (17, 'Ahmad Rahman', 'ahmad.rahman@example.com', 17, 'Male'),
    (18, 'Nur Hidayah', 'nur.hidayah@example.com', 16, 'Male'),
    (19, 'Joko Susilo', 'joko.susilo@example.com', 15, 'Male'),
    (20, 'Ratih Wijayanti', 'ratih.wijayanti@example.com', 17, 'Female'),
    (21, 'Abdullah Al-Bakr', 'abdullah.albakr@example.com', 16, 'Male'),
    (22, 'Huda Al-Farsi', 'huda.alfarsi@example.com', 15, 'Male'),
    (23, 'Tetsuya Suzuki', 'tetsuya.suzuki@example.com', 18, 'Male'),
    (24, 'Akira Kato', 'akira.kato@example.com', 16, 'Male'),
    (25, 'Ravi Singh', 'ravi.singh@example.com', 15, 'Male'),
    (26, 'Nikolai Ivanov', 'nikolai.ivanov@example.com', 15, 'Male'),
    (27, 'dr. Johnson Bun', 'dr.johnson@example.com', 50, 'Male'),
    (28, 'Wilson Weasley', 'busdriver.wilson@example.com', 37, 'Male'),
    (29, 'Smith Sonian', 'coach.smith@example.com', 40, 'Male'),
    (30, 'Kim Kwan', 'physio.kim@example.com', 35, 'Male')
;

SQLite: Insert: People

Lookup Table

We can do the same with lookup table

INSERT INTO Roles (id, name)
VALUES
    (1, 'Players'),
    (2, 'Coach'),
    (3, 'Support')
;
INSERT INTO Seats (id, name)
VALUES 
    (1, 'Captain'),
    (2, 'Goalkeeper'),
    (3, 'Back'),
    (4, 'Striker'),
    (5, 'Winger'),
    (6, 'Midfielder'),
    (7, 'Defender')
;

SQLite: Insert: Lookup

INSERT INTO Responsibilities (id, name)
VALUES 
    (1, 'Head Coach'),
    (2, 'Coach Assistant'),
    (3, 'Coach Trainee'),
    (11, 'Doctor'),
    (12, 'Physiotherapist'),
    (21, 'Bus Driver'),
    (22, 'Cooking Chef'),
    (23, 'Equipment')
;

SQLite: Insert: Lookup

Relationships Table

And finally, tables who hold the relationships between tables: PeopleRoles, PlayersSeats, and StaffResps.

-- People_Role table
INSERT INTO PeopleRoles (person_id, role_id)
VALUES
    (1, 1),
    (2, 2),
    (4, 3),
    (5, 1),
    (6, 3),
    (7, 1),
    (8, 3),
    (9, 1),
    (10, 3),
    (11, 1),
    (12, 2),
    (13, 1),
    (14, 3),
    (15, 1),
    (16, 3),
    (17, 1),
    (18, 1),
    (19, 1),
    (20, 3),
    (21, 3),
    (22, 1),
    (23, 1),
    (24, 1),
    (25, 1),
    (26, 1),
    (27, 3),
    (28, 3),
    (29, 2),
    (30, 3)
;

-- People_Role_Seat table
INSERT INTO PlayersSeats (person_id, role_id, seat_id, is_bench)
VALUES
    (1, 1, 1, false),
    (5, 1, 4, false),
    (7, 1, 5, false),
    (9, 1, 5, false),
    (11, 1, 6, false),
    (13, 1, 6, false),
    (15, 1, 7, false),
    (17, 1, 7, false),
    (18, 1, 4, false),
    (19, 1, 2, false),
    (22, 1, 2, true),
    (23, 1, 3, true),
    (24, 1, 3, false),
    (25, 1, 4, false),
    (26, 1, 5, true)
;

-- People_Role_Responsibility table
INSERT INTO StaffResps (person_id, role_id, resp_id)
VALUES
    (2, 2, 2),
    (4, 3, 11),
    (6, 3, 23),
    (8, 3, 21),
    (10, 3, 22),
    (12, 2, 3),
    (14, 3, 23),
    (16, 3, 22),
    (20, 3, 12),
    (21, 3, 22),
    (27, 3, 11),
    (28, 3, 21),
    (29, 2, 1),
    (30, 3, 12)
;

SELECT

I have already ceate the SQL file for both insert and create. This is what I do for empty database

.read create.sql
.read insert.sql
.mode table
.shell reset

You can use also use Ctrl+L to reset the terminal.

People Table

Now we can retrieve the data, table by table, starting from people.

❯ sqlite3
SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open soccer.db
sqlite> .mode table
sqlite> SELECT * FROM PEOPLE;
+----+----------------------+----------------------------------+-----+--------+
| id |         name         |              email               | age | gender |
+----+----------------------+----------------------------------+-----+--------+
| 1  | Takumi Sato          | takumi.sato@example.com          | 17  | Male   |
| 2  | Sakura Yamamoto      | sakura.yamamoto@example.com      | 18  | Female |
| 3  | Rajesh Patel         | rajesh.patel@example.com         | 16  | Male   |
| 4  | dr. Komal Sharma     | komal.sharma@example.com         | 26  | Female |
| 5  | Jian Chen            | jian.chen@example.com            | 15  | Male   |
| 6  | Yan Liu              | yan.liu@example.com              | 17  | Female |
| 7  | Vladimir Ivanov      | vladimir.ivanov@example.com      | 15  | Male   |
| 8  | Ekaterina Petrova    | ekaterina.petrova@example.com    | 26  | Female |
| 9  | Yusuf Abdullah       | yusuf.abdullah@example.com       | 17  | Male   |
| 10 | Fatima Al-Khalifa    | fatima.alkhalifa@example.com     | 18  | Female |
| 11 | Andi Suharto         | andi.suharto@example.com         | 15  | Male   |
| 12 | Lia Wijaya           | lia.wijaya@example.com           | 16  | Female |
| 13 | Marco Rossi          | marco.rossi@example.com          | 17  | Male   |
| 14 | Alessia Bianchi      | alessia.bianchi@example.com      | 16  | Female |
| 15 | Gustav Andersen      | gustav.andersen@example.com      | 15  | Male   |
| 16 | Maria Svensson       | maria.svensson@example.com       | 18  | Female |
| 17 | Ahmad Rahman         | ahmad.rahman@example.com         | 17  | Male   |
| 18 | Nur Hidayah          | nur.hidayah@example.com          | 16  | Male   |
| 19 | Joko Susilo          | joko.susilo@example.com          | 15  | Male   |
| 20 | Ratih Wijayanti      | ratih.wijayanti@example.com      | 17  | Female |
| 21 | Abdullah Al-Bakr     | abdullah.albakr@example.com      | 16  | Male   |
| 22 | Huda Al-Farsi        | huda.alfarsi@example.com         | 15  | Male   |
| 23 | Tetsuya Suzuki       | tetsuya.suzuki@example.com       | 18  | Male   |
| 24 | Akira Kato           | akira.kato@example.com           | 16  | Male   |
| 25 | Ravi Singh           | ravi.singh@example.com           | 15  | Male   |
| 26 | Nikolai Ivanov       | nikolai.ivanov@example.com       | 15  | Male   |
| 27 | dr. Johnson Bun      | dr.johnson@example.com           | 50  | Male   |
| 28 | Wilson Weasley       | busdriver.wilson@example.com     | 37  | Male   |
| 29 | Smith Sonian         | coach.smith@example.com          | 40  | Male   |
| 30 | Kim Kwan             | physio.kim@example.com           | 35  | Male   |
+----+----------------------+----------------------------------+-----+--------+
sqlite>

SQLite: Select: People

id name email age gender
1 Takumi Sato takumi.sato@example.com 17 Male
2 Sakura Yamamoto sakura.yamamoto@example.com 18 Female
3 Rajesh Patel rajesh.patel@example.com 16 Male
4 dr. Komal Sharma komal.sharma@example.com 26 Female
5 Jian Chen jian.chen@example.com 15 Male
6 Yan Liu yan.liu@example.com 17 Female
7 Vladimir Ivanov vladimir.ivanov@example.com 15 Male
8 Ekaterina Petrova ekaterina.petrova@example.com 26 Female
9 Yusuf Abdullah yusuf.abdullah@example.com 17 Male
10 Fatima Al-Khalifa fatima.alkhalifa@example.com 18 Female
11 Andi Suharto andi.suharto@example.com 15 Male
12 Lia Wijaya lia.wijaya@example.com 16 Female
13 Marco Rossi marco.rossi@example.com 17 Male
14 Alessia Bianchi alessia.bianchi@example.com 16 Female
15 Gustav Andersen gustav.andersen@example.com 15 Male
16 Maria Svensson maria.svensson@example.com 18 Female
17 Ahmad Rahman ahmad.rahman@example.com 17 Male
18 Nur Hidayah nur.hidayah@example.com 16 Male
19 Joko Susilo joko.susilo@example.com 15 Male
20 Ratih Wijayanti ratih.wijayanti@example.com 17 Female
21 Abdullah Al-Bakr abdullah.albakr@example.com 16 Male
22 Huda Al-Farsi huda.alfarsi@example.com 15 Male
23 Tetsuya Suzuki tetsuya.suzuki@example.com 18 Male
24 Akira Kato akira.kato@example.com 16 Male
25 Ravi Singh ravi.singh@example.com 15 Male
26 Nikolai Ivanov nikolai.ivanov@example.com 15 Male
27 dr. Johnson Bun dr.johnson@example.com 50 Male
28 Wilson Weasley busdriver.wilson@example.com 37 Male
29 Smith Sonian coach.smith@example.com 40 Male
30 Kim Kwan physio.kim@example.com 35 Male

Lookup Table

sqlite> SELECT * FROM Roles;
id name
1 Players
2 Coach
3 Support

SQLite: Select: Roles

sqlite> SELECT * FROM Seats;
id name
1 Captain
2 Goalkeeper
3 Back
4 Striker
5 Winger
6 Midfielder
7 Defender

And the responsibility table.

sqlite> SELECT * FROM Responsibilities;
id name
1 Head Coach
2 Coach Assistant
3 Coach Trainee
11 Doctor
12 Physiotherapist
21 Bus Driver
22 Cooking Chef
23 Equipment

Relationships Table

For relationship table, there is nothing to say. This is all just resulting a bunch of indices.

SELECT * FROM PeopleRoles
SELECT * FROM PlayersSeats
SELECT * FROM StaffResps

JOIN

This way we can retrieve the data, with meaningful result.

People Roles

The SQL statement is a little longer.

SELECT
  People.name as name,
  Roles.name as role
FROM PeopleRoles
INNER JOIN People
  ON People.id = PeopleRoles.person_id
INNER JOIN Roles
  ON Roles.id  = PeopleRoles.role_id;

With the result as:

name role
Takumi Sato Players
Sakura Yamamoto Coach
dr. Komal Sharma Support
Jian Chen Players
Yan Liu Support
Vladimir Ivanov Players
Ekaterina Petrova Support
Yusuf Abdullah Players
Fatima Al-Khalifa Support
Andi Suharto Players
Lia Wijaya Coach
Marco Rossi Players
Alessia Bianchi Support
Gustav Andersen Players
Maria Svensson Support
Ahmad Rahman Players
Nur Hidayah Players
Joko Susilo Players
Ratih Wijayanti Support
Abdullah Al-Bakr Support
Huda Al-Farsi Players
Tetsuya Suzuki Players
Akira Kato Players
Ravi Singh Players
Nikolai Ivanov Players
dr. Johnson Bun Support
Wilson Weasley Support
Smith Sonian Coach
Kim Kwan Support

People Seats

The same way, we can check the player location.

SELECT
  People.name as name,
  Seats.name as position
FROM PlayersSeats
INNER JOIN People
  ON People.id = PlayersSeats.person_id
INNER JOIN Seats
  ON Seats.id  = PlayersSeats.seat_id;
name position
Takumi Sato Captain
Jian Chen Striker
Vladimir Ivanov Winger
Yusuf Abdullah Winger
Andi Suharto Midfielder
Marco Rossi Midfielder
Gustav Andersen Defender
Ahmad Rahman Defender
Nur Hidayah Striker
Joko Susilo Goalkeeper
Huda Al-Farsi Goalkeeper
Tetsuya Suzuki Back
Akira Kato Back
Ravi Singh Striker
Nikolai Ivanov Winger

People Responsibilities

And finally very similar with this staff table.

SELECT
  People.name as name,
  Responsibilities.name as `job desc`
FROM StaffResps
INNER JOIN People
  ON People.id = StaffResps.person_id
INNER JOIN Responsibilities
  ON Responsibilities.id  = StaffResps.resp_id;
name job desc
Sakura Yamamoto Coach Assistant
dr. Komal Sharma Doctor
Yan Liu Equipment
Ekaterina Petrova Bus Driver
Fatima Al-Khalifa Cooking Chef
Lia Wijaya Coach Trainee
Alessia Bianchi Equipment
Maria Svensson Cooking Chef
Ratih Wijayanti Physiotherapist
Abdullah Al-Bakr Cooking Chef
dr. Johnson Bun Doctor
Wilson Weasley Bus Driver
Smith Sonian Head Coach
Kim Kwan Physiotherapist

Here with simple SQL statement.

What is Next 🤔?

We need to explore more about SQL statement.

Consider continue reading [ Soccer - SQL Query ].

Thank you for reading.