Soccer - SQL Query

LAMP Stack

Local Group

Preface

Goal: Playing SQL with Soccer Case. Fixtures.

For typical community data, I’d rather use excel. But for the sake of fun, SQL is better. We can playing with data, we can also add our own interface, such as desktop or web based.

Working example is considerably important. In many occassion, I look back for my own SQL statement, as an example to build difficult case.

Here we start as playing with more SQL statement example.

Source

You can obtain the SQL source code here:

You can also download the ready to use sqlite here:

github.com/…/soccer/soccer.db

Warning

No image picture here, nor any screenshot.

SQL Statement

With this simple soccer team, we can get a lot of type of data representation. Now you can imagine, how you can mine huge databases.

Common Feature

We can add WHERE to filter, and ORDER to sort the query.

sqlite> SELECT name, age, gender
FROM People
WHERE age > 20
ORDER by age;

name	age	gender
dr. Komal Sharma	26	Female
Ekaterina Petrova	26	Female
Kim Kwan	35	Male
Wilson Weasley	37	Male
Smith Sonian	40	Male
dr. Johnson Bun	50	Male

COUNT

We can count people by age.

sqlite> SELECT
    age, COUNT(*)
  FROM People
  GROUP BY age;

age	COUNT(*)
15	8
16	6
17	6
18	4
26	2
35	1
37	1
40	1
50	1

We can also get age count for players only.

SELECT
    People.age, COUNT(*)
  FROM People
  INNER JOIN PeopleRoles
    ON People.id = PeopleRoles.person_id
  WHERE PeopleRoles.role_id = 1
  GROUP BY People.age;

age	COUNT(*)
15	8
16	2
17	4
18	1

More Join Example

We can retrieve roles for all soccer players, without ever joining PeopleRoles table.

sqlite> SELECT
    People.name,
    Roles.name as roles,
    Seats.name as seat
  FROM People
  INNER JOIN PlayersSeats
    ON People.id = PlayersSeats.person_id
  INNER JOIN Roles
    ON PlayersSeats.role_id = Roles.id
  INNER JOIN Seats
    ON PlayersSeats.seat_id = Seats.id;

name	roles	seat
Takumi Sato	Players	Captain
Jian Chen	Players	Striker
Vladimir Ivanov	Players	Winger
Yusuf Abdullah	Players	Winger
Andi Suharto	Players	Midfielder
Marco Rossi	Players	Midfielder
Gustav Andersen	Players	Defender
Ahmad Rahman	Players	Defender
Nur Hidayah	Players	Striker
Joko Susilo	Players	Goalkeeper
Huda Al-Farsi	Players	Goalkeeper
Tetsuya Suzuki	Players	Back
Akira Kato	Players	Back
Ravi Singh	Players	Striker
Nikolai Ivanov	Players	Winger

The same example with staff.

sqlite> SELECT
    People.name,
    Roles.name as roles,
    Responsibilities.name as responsibility
  FROM People
  INNER JOIN StaffResps
    ON People.id = StaffResps.person_id
  INNER JOIN Roles
    ON StaffResps.role_id = Roles.id
  INNER JOIN Responsibilities
    ON StaffResps.resp_id = Responsibilities.id
  ORDER BY Roles.id;

name	roles	responsibility
Sakura Yamamoto	Coach	Coach Assistant
Lia Wijaya	Coach	Coach Trainee
Smith Sonian	Coach	Head Coach
dr. Komal Sharma	Support	Doctor
Yan Liu	Support	Equipment
Ekaterina Petrova	Support	Bus Driver
Fatima Al-Khalifa	Support	Cooking Chef
Alessia Bianchi	Support	Equipment
Maria Svensson	Support	Cooking Chef
Ratih Wijayanti	Support	Physiotherapist
Abdullah Al-Bakr	Support	Cooking Chef
dr. Johnson Bun	Support	Doctor
Wilson Weasley	Support	Bus Driver
Kim Kwan	Support	Physiotherapist

Let’s say you need to e-mail all doctors at night. You can also use query.

SELECT
    People.email,
    Responsibilities.name as responsibility
  FROM People
  INNER JOIN StaffResps
    ON People.id = StaffResps.person_id
  INNER JOIN Responsibilities
    ON StaffResps.resp_id = Responsibilities.id
  WHERE Responsibilities.name = 'Doctor';

email	responsibility
komal.sharma@example.com	Doctor
dr.johnson@example.com	Doctor

VIEW

We can simplify complex query by using view.

Player JOIN

Do not forget the we have other data as well in People data, such as gender, age, and e-mail

sqlite> SELECT
    People.name,
    People.age,
    Seats.name as seat
  FROM People
  INNER JOIN PlayersSeats
    ON People.id = PlayersSeats.person_id
  INNER JOIN Seats
    ON PlayersSeats.seat_id = Seats.id
  ORDER BY People.name;

name	age	seat
Ahmad Rahman	17	Defender
Akira Kato	16	Back
Andi Suharto	15	Midfielder
Gustav Andersen	15	Defender
Huda Al-Farsi	15	Goalkeeper
Jian Chen	15	Striker
Joko Susilo	15	Goalkeeper
Marco Rossi	17	Midfielder
Nikolai Ivanov	15	Winger

We can simplify above table with VIEW.

Player VIEW

Consider make a VIEW for Players.

sqlite> CREATE VIEW Players
AS
  SELECT
    People.*,
    Roles.name as roles,
    Seats.name as seat
  FROM People
  INNER JOIN PlayersSeats
    ON People.id = PlayersSeats.person_id
  INNER JOIN Roles
    ON PlayersSeats.role_id = Roles.id
  INNER JOIN Seats
    ON PlayersSeats.seat_id = Seats.id;

Then we can SELECT for players only.

sqlite> SELECT
    name, age, gender, seat
  FROM Players;

name	age	gender	seat
Takumi Sato	17	Male	Captain
Jian Chen	15	Male	Striker
Vladimir Ivanov	15	Male	Winger
Yusuf Abdullah	17	Male	Winger
Andi Suharto	15	Male	Midfielder
Marco Rossi	17	Male	Midfielder
Gustav Andersen	15	Male	Defender
Ahmad Rahman	17	Male	Defender
Nur Hidayah	16	Male	Striker
Joko Susilo	15	Male	Goalkeeper
Huda Al-Farsi	15	Male	Goalkeeper
Tetsuya Suzuki	18	Male	Back
Akira Kato	16	Male	Back
Ravi Singh	15	Male	Striker
Nikolai Ivanov	15	Male	Winger

CASE WHEN

Instead of using additional table, we can compute a column result. But first let’s add is_bench column to the view.

sqlite> DROP VIEW Players;
sqlite> CREATE VIEW Players
AS
  SELECT
    People.*,
    Roles.name as roles,
    Seats.name as seat,
    PlayersSeats.is_bench
  FROM People
  INNER JOIN PlayersSeats
    ON People.id = PlayersSeats.person_id
  INNER JOIN Roles
    ON PlayersSeats.role_id = Roles.id
  INNER JOIN Seats
    ON PlayersSeats.seat_id = Seats.id;

And practice the CASE statement.

SELECT
    name, age, seat, 
    CASE
      WHEN is_bench = false then 'Playing'
      ELSE 'Bench Warmer'
    END AS status
  FROM Players;

name	age	seat	status
Takumi Sato	17	Captain	Playing
Jian Chen	15	Striker	Playing
Vladimir Ivanov	15	Winger	Playing
Yusuf Abdullah	17	Winger	Playing
Andi Suharto	15	Midfielder	Playing
Marco Rossi	17	Midfielder	Playing
Gustav Andersen	15	Defender	Playing
Ahmad Rahman	17	Defender	Playing
Nur Hidayah	16	Striker	Playing
Joko Susilo	15	Goalkeeper	Playing
Huda Al-Farsi	15	Goalkeeper	Bench Warmer
Tetsuya Suzuki	18	Back	Bench Warmer
Akira Kato	16	Back	Playing
Ravi Singh	15	Striker	Playing
Nikolai Ivanov	15	Winger	Bench Warmer

UNION

One reason, why I need to split, the staff table and players table on the first place is, that I need an example on how to unified two queries.

sqlite> SELECT
    People.name, People.age,
    Roles.name as roles,
    Seats.name as 'job desc'
  FROM People
  INNER JOIN PlayersSeats
    ON People.id = PlayersSeats.person_id
  INNER JOIN Roles
    ON PlayersSeats.role_id = Roles.id
  INNER JOIN Seats
    ON PlayersSeats.seat_id = Seats.id
UNION
SELECT
    People.name, People.age,
    Roles.name as roles,
    Responsibilities.name as 'job desc'
  FROM People
  INNER JOIN StaffResps
    ON People.id = StaffResps.person_id
  INNER JOIN Roles
    ON StaffResps.role_id = Roles.id
  INNER JOIN Responsibilities
    ON StaffResps.resp_id = Responsibilities.id
ORDER BY People.name;

name	age	roles	job desc
Abdullah Al-Bakr	16	Support	Cooking Chef
Ahmad Rahman	17	Players	Defender
Akira Kato	16	Players	Back
Alessia Bianchi	16	Support	Equipment
Andi Suharto	15	Players	Midfielder
Ekaterina Petrova	26	Support	Bus Driver
Fatima Al-Khalifa	18	Support	Cooking Chef
Gustav Andersen	15	Players	Defender
Huda Al-Farsi	15	Players	Goalkeeper
Jian Chen	15	Players	Striker
Joko Susilo	15	Players	Goalkeeper
Kim Kwan	35	Support	Physiotherapist
Lia Wijaya	16	Coach	Coach Trainee
Marco Rossi	17	Players	Midfielder
Maria Svensson	18	Support	Cooking Chef
Nikolai Ivanov	15	Players	Winger
Nur Hidayah	16	Players	Striker
Ratih Wijayanti	17	Support	Physiotherapist
Ravi Singh	15	Players	Striker
Sakura Yamamoto	18	Coach	Coach Assistant
Smith Sonian	40	Coach	Head Coach
Takumi Sato	17	Players	Captain
Tetsuya Suzuki	18	Players	Back
Vladimir Ivanov	15	Players	Winger
Wilson Weasley	37	Support	Bus Driver
Yan Liu	17	Support	Equipment
Yusuf Abdullah	17	Players	Winger
dr. Johnson Bun	50	Support	Doctor
dr. Komal Sharma	26	Support	Doctor

After examining some example of SQL capability, you can build what is suitable for specific situation.

I guess this is clear now.

What is Next 🤔?

From SQL, we can continue to python’s ORM using SQLAlchemy

Consider continue reading [ Soccer - SQL Alchemy ].

Thank you for reading.