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.
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.
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
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')
;
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')
;
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')
;
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>
id | name | 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 * 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.