SQL Exercise: Players who entered the field in the most recent play
61. From the following tables, write a SQL query to find those players who came into the field at the end of play. Return match number, country name, player name, jersey number and time in out.
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
1 | 1216 | 160363 | O | 72 | NT | 2
..........
51 | 1214 | 160316 | O | 79 | NT | 2
Sample table: player_mast
player_id | team_id | jersey_no | player_name | posi_to_play | dt_of_bir | age | playing_club
-----------+---------+-----------+-------------------------+--------------+------------+-----+---------------------
160001 | 1201 | 1 | Etrit Berisha | GK | 1989-03-10 | 27 | Lazio
160008 | 1201 | 2 | Andi Lila | DF | 1986-02-12 | 30 | Giannina
160016 | 1201 | 3 | Ermir Lenjani | MF | 1989-08-05 | 26 | Nantes
160007 | 1201 | 4 | Elseid Hysaj | DF | 1994-02-20 | 22 | Napoli
160013 | 1201 | 5 | Lorik Cana | MF | 1983-07-27 | 32 | Nantes
160010 | 1201 | 6 | Frederic Veseli | DF | 1992-11-20 | 23 | Lugano
160004 | 1201 | 7 | Ansi Agolli | DF | 1982-10-11 | 33 | Qarabag
160012 | 1201 | 8 | Migjen Basha | MF | 1987-01-05 | 29 | Como
160017 | 1201 | 9 | Ledian Memushaj | MF | 1986-12-17 | 29 | Pescara
........
160548 | 1224 | 23 | Simon Church | FD | 1988-12-10 | 27 | MK Dons
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
Sample Solution:
SQL Code:
-- Selecting the match number, country name, player name, jersey number, and time in/out of players
SELECT
match_no, -- Selecting the match number
country_name, -- Selecting the country name
player_name, -- Selecting the player name
jersey_no, -- Selecting the jersey number
time_in_out -- Selecting the time in/out
FROM
player_in_out a -- Selecting from the player_in_out table with alias 'a'
JOIN
player_mast b ON a.player_id = b.player_id -- Joining with the player_mast table with alias 'b' based on player ID
JOIN
soccer_country c ON a.team_id = c.country_id -- Joining with the soccer_country table with alias 'c' based on team ID
WHERE
time_in_out = ( -- Filtering records where time in/out matches the maximum time in/out
SELECT
max(time_in_out) -- Subquery to find the maximum time in/out
FROM
player_in_out
-- Filtering records where the player is in
)
AND in_out = 'I';
Sample Output:
match_no | country_name | player_name | jersey_no | time_in_out
----------+--------------+------------------+-----------+-------------
39 | Croatia | Andrej Kramaric | 9 | 120
47 | Italy | Simone Zaza | 7 | 120
(2 rows)
Code Explanation:
The said query in SQL that returns information about the last player substitutions in soccer matches, where a player was substituted into the game. The results include the match number, the name of the country, the name of the player, the player's jersey number, and the time the player was substituted in.
The JOIN clause joins the player_in_out and player_mast tables based on player_id column, and the player_in_out and soccer_country tables based on the team_id and country_id columns.
The WHERE clause filters the results to only include the last player substitutions, where a player was substituted into the game (in_out='I').
The time_in_out column is used to find the latest substitution time which comes from a subquery, and the subquery returns this value.,/
Alternative Solution:
Using a Subquery:
-- Selecting the match number, country name, player name, jersey number, and time in/out of players
SELECT
a.match_no, -- Selecting the match number
c.country_name, -- Selecting the country name
b.player_name, -- Selecting the player name
b.jersey_no, -- Selecting the jersey number
a.time_in_out -- Selecting the time in/out
FROM
player_in_out a -- Selecting from the player_in_out table with alias 'a'
JOIN
player_mast b ON a.player_id = b.player_id -- Joining with the player_mast table with alias 'b' based on player ID
JOIN
soccer_country c ON a.team_id = c.country_id -- Joining with the soccer_country table with alias 'c' based on team ID
WHERE
a.time_in_out = ( -- Filtering records where time in/out matches the maximum time in/out for 'in' status
SELECT
MAX(time_in_out) -- Subquery to find the maximum time in/out for 'in' status
FROM
player_in_out
WHERE
in_out = 'I'
)
-- Filtering records where the player is in
AND a.in_out = 'I';
Explanation:
This query uses a subquery to find the maximum time_in_out specifically for players coming in (in_out = 'I'). It then joins the relevant tables and applies the conditions.
Go to:
PREV : Goals scored by players based on their position.
NEXT : SQL Exercises on Hospital Database
Practice Online
Sample Database: soccer
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.