SQL Exercise: Number of players replaced in the first half of play

Last update on January 13 2026 12:20:24 (UTC/GMT +8 hours)

18. From the following table, write a SQL query to count the number of players who were replaced during the first half. Return number of players as "Player Replaced".

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
        1 |    1216 |    160357 | I      |          72 | NT            |         2
........
       51 |    1214 |    160316 | O      |          79 | NT            |         2

View the table

Sample Solution:

-- This SQL query calculates the count of rows in the 'player_in_out' table where the 'in_out' column has the value 'I',
-- the 'play_schedule' column has the value 'NT', and the 'play_half' column has the value 1.
-- It aliases the result as "Player Replaced".

SELECT COUNT(*) as "Player Replaced"
-- COUNT(*) is an aggregate function that counts the number of rows in a result set, and the alias is set to "Player Replaced".
FROM player_in_out
-- 'player_in_out' is the name of the table being queried.
WHERE in_out='I'
-- The WHERE clause filters rows where the 'in_out' column has the value 'I'.
AND play_schedule='NT'
-- Further filters rows where the 'play_schedule' column has the value 'NT'.
AND play_half=1;
-- Further filters rows where the 'play_half' column has the value 1.

Sample Output:

 Player Replaced
-----------------
               3
(1 row)

Code Explanation:

The said query in SQL which selects the count of players who were replaced during Normal Time ("NT"), entered the game ("I"), and played in the first half of the game from the player_in_out table. The result will be labeled as "Player Replaced".

Go to:

PREV : Number of players replaced in the stoppage time.
NEXT : Total number of goalless draws in the tournament.

Practice Online