SQL Exercise: Number of players played for france in the final

Last update on January 16 2026 12:32:37 (UTC/GMT +8 hours)

22. From the following tables, write a SQL query to count the number of players played for 'France' in the final. Return 'Number of players shared fields'.

Sample table: player_in_out

 match_no | team_id | player_id | in_out | time_in_out | play_schedule | play_half
----------+---------+-----------+--------+-------------+---------------+-----------
        1 |    1207 |    160151 | I      |          66 | NT            |         2
        1 |    1207 |    160160 | O      |          66 | NT            |         2
        1 |    1207 |    160161 | I      |          77 | NT            |         2
        1 |    1207 |    160161 | O      |          77 | NT            |         2
        1 |    1207 |    160157 | I      |           2 | ST            |         2
        1 |    1207 |    160154 | O      |           2 | ST            |         2
        1 |    1216 |    160365 | I      |          61 | NT            |         2
        1 |    1216 |    160366 | O      |          61 | NT            |         2
.........
       51 |    1214 |    160316 | O      |          79 | NT            |         2

View the table

Sample table: match_details

 match_no | play_stage | team_id | win_lose | decided_by | goal_score | penalty_score | ass_ref | player_gk
----------+------------+---------+----------+------------+------------+---------------+---------+-----------
        1 | G          |    1207 | W        | N          |          2 |               |   80016 |    160140
        1 | G          |    1216 | L        | N          |          1 |               |   80020 |    160348
        2 | G          |    1201 | L        | N          |          0 |               |   80003 |    160001
        2 | G          |    1221 | W        | N          |          1 |               |   80023 |    160463
        3 | G          |    1224 | W        | N          |          2 |               |   80031 |    160532
        3 | G          |    1218 | L        | N          |          1 |               |   80025 |    160392
        4 | G          |    1206 | D        | N          |          1 |               |   80008 |    160117
        4 | G          |    1217 | D        | N          |          1 |               |   80019 |    160369
        5 | G          |    1222 | L        | N          |          0 |               |   80011 |    160486
..........
       51 | F          |    1207 | L        | N          |          0 |               |   80007 |    160140

View the table

Sample table: soccer_country

 country_id | country_abbr |    country_name
------------+--------------+---------------------
       1201 | ALB          | Albania
       1202 | AUT          | Austria
       1203 | BEL          | Belgium
       1204 | CRO          | Croatia
       1205 | CZE          | Czech Republic
       1206 | ENG          | England
       1207 | FRA          | France
       1208 | GER          | Germany
       1209 | HUN          | Hungary
.....
       1229 | NOR          | Norway

View the table

Sample Solution:

SQL Code:

SELECT COUNT(*)+11 as "Number of players shared fields" 
FROM player_in_out 
WHERE match_no=(
SELECT match_no 
FROM match_mast 
WHERE play_stage='F')
AND in_out='I' 
AND team_id=(
SELECT country_id 
FROM soccer_country 
WHERE country_name='France');

Sample Output:

 Number of players shared fields
---------------------------------
                              14
(1 row)

Code Explanation:

The said query in SQL that returns the total number of players who played in the final match for the French team, including the starting eleven.
The subqueries have been used to find the match number of the final match, the team ID of the French team, and filters the 'player_in_out' table to only include players who were substituted in during the match.
The inner most subquery returns the ID of the French team,
and the subquery out of the inner most returns the match number for the final match, and the outer query counts the number of players who match these criteria and adds 11 for the starting eleven.

Alternative Solution:

Using JOINs and Subqueries:


SELECT COUNT(*) + 11 as "Number of players shared fields"
FROM player_in_out pio
JOIN match_mast mm ON pio.match_no = mm.match_no
JOIN soccer_country sc ON pio.team_id = sc.country_id
WHERE mm.play_stage = 'F'
  AND pio.in_out = 'I'
  AND sc.country_name = 'France';

Explanation:

This query uses JOIN operations to connect the player_in_out table with match_mast and soccer_country based on their respective IDs. It then applies the specified conditions to filter the results.

Using JOINs with Derived Tables:


SELECT COUNT(*) + 11 as "Number of players shared fields"
FROM (
    SELECT *
    FROM player_in_out
    WHERE match_no = (
        SELECT match_no 
        FROM match_mast 
        WHERE play_stage='F'
    )
      AND in_out='I' 
      AND team_id = (
        SELECT country_id 
        FROM soccer_country 
        WHERE country_name='France'
    )
) AS filtered_players;

Explanation:

This query uses derived tables to first filter the player_in_out table based on the specified conditions. Then, the outer query counts the number of rows in this filtered set and adds 11.

Using EXISTS:


SELECT COUNT(*) + 11 as "Number of players shared fields"
FROM player_in_out pio
WHERE EXISTS (
    SELECT 1
    FROM match_mast mm
    JOIN soccer_country sc ON pio.team_id = sc.country_id
    WHERE pio.match_no = mm.match_no
      AND mm.play_stage = 'F'
      AND pio.in_out = 'I'
      AND sc.country_name = 'France'
);

Explanation:

This query uses the EXISTS keyword with a subquery to check if there exists a record in match_mast and soccer_country that satisfies the specified conditions.

Go to:

PREV : The captain of Portugal EURO cup 2016 winning team.
NEXT : German player who did not concede a goal in group stage.

Practice Online