SQL Exercise: Number of players replaced in the extra time of play
20. From the following table, write a SQL query to calculate the total number of players who were replaced during the extra time.
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
Sample Solution:
-- This SQL query calculates the count of rows in the 'player_in_out' table where the 'play_schedule' column has the value 'ET'
-- and the 'in_out' column has the value 'I'.
SELECT COUNT(*)
-- COUNT(*) is an aggregate function that counts the number of rows in a result set.
FROM player_in_out
-- 'player_in_out' is the name of the table being queried.
WHERE play_schedule='ET'
-- The WHERE clause filters rows where the 'play_schedule' column has the value 'ET'.
AND in_out='I';
-- Further filters rows where the 'in_out' column has the value 'I'.
Sample Output:
count
-------
9
(1 row)
Code Explanation:
The said query in SQL that returns a single row with a single column containing the count of players who entered the game ("I") during Extra Time ("ET") from the player_in_out table.
Relational Algebra Expression:
Relational Algebra Tree:
Go to:
PREV : Total number of goalless draws in the tournament.
NEXT : Number of substitutes for each stage of the tournament.
Practice Online
Sample Database: soccer
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.