SQL Exercise: Number of substitutes for each stage of the tournament

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

21. From the following table, write a SQL query to count the number of substitutes during various stages of the tournament. Sort the result-set in ascending order by play-half, play-schedule and number of substitute happened. Return play-half, play-schedule, number of substitute happened.

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 retrieves the 'play_half', 'play_schedule', and the count of rows for each group from the 'player_in_out' table,
-- where the 'in_out' column has the value 'I'. It then groups the results by 'play_half' and 'play_schedule', 
-- and orders the result set in descending order based on the count.

SELECT play_half, play_schedule, COUNT(*) 
-- Selects the 'play_half', 'play_schedule', and the count of rows for each group.
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'.
GROUP BY play_half, play_schedule
-- Groups the results by 'play_half' and 'play_schedule'.
ORDER BY play_half, play_schedule, COUNT(*) DESC;
-- Orders the result set first by 'play_half' and 'play_schedule', and then by the count of rows in descending order.

Sample Output:

 play_half | play_schedule | count
-----------+---------------+-------
         1 | ET            |     4
         1 | NT            |     3
         2 | ET            |     5
         2 | NT            |   272
         2 | ST            |     9
(5 rows)

Code Explanation:

The said query in SQL that returns multiple rows with columns play_half, play_schedule, and the count of players who entered the game ("I") in each group. The results will be grouped first by play half and then by play schedule, and sorted in ascending order by play half, play schedule, and descending order by the count of players in each group.

Relational Algebra Expression: