SQL Exercise: Players who came onto the field during the first half
35. From the following tables, write a SQL query to find the substitute players who entered the field during the first half of play within the normal time frame for the game. Return match_no, country_name, player_name, jersey_no 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
.........
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
160023 | 1201 | 10 | Armando Sadiku | FD | 1991-05-27 | 25 | Vaduz
.......
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:
-- This query selects the match number, country name, player name, jersey number, and time in/out for players who are substituted in during the first half of matches with play schedule 'NT'.
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 data from the player_in_out table and aliasing it as 'a'
JOIN
player_mast b ON a.player_id=b.player_id -- Joining the player_in_out table with the player_mast table based on the player_id and aliasing it as 'b'
JOIN
soccer_country c ON b.team_id=c.country_id -- Joining the player_mast table with the soccer_country table based on the team_id and aliasing it as 'c'
WHERE
a.in_out='I' -- Filtering rows where players are substituted in
AND a.play_schedule='NT' -- Filtering matches with play schedule 'NT'
AND a.play_half=1 -- Filtering substitutions made during the first half of matches
-- Ordering the results by match number
ORDER BY
match_no;
Sample Output:
match_no | country_name | player_name | jersey_no | time_in_out
----------+--------------+-------------------------+-----------+-------------
9 | Sweden | Erik Johansson | 3 | 45
47 | Germany | Bastian Schweinsteiger | 7 | 16
51 | Portugal | Ricardo Quaresma | 20 | 25
(3 rows)
Code Explanation:
The given query in SQL that retrieves information about players who were substituted in during the first half of soccer matches that had no extra time, including their name, jersey number, time of substitution, and the country they belong to from the tables player_in_out, player_mast, and soccer_country.
The 'player_in_out' table alias as 'a' joined with the 'player_mast' table alias as 'b' based on the common column "player_id" in 'a' and 'b'. Then, the resulting table is joined with the 'soccer_country' table alias as 'c' based on the common column "team_id" in 'b' and "country_id" in 'c'.
The WHERE clause includes data in the result only rows where the "in_out" column in 'player_in_out' has the value of 'I', the "play_schedule" column has the value of 'NT', and the "play_half" column has the value of 1.
The result set then sorted in ascending order of the "match_no" column.
Alternative Solution:
Using INNER JOIN with Subquery:
-- This query selects the match number, country name, player name, jersey number, and time in/out for players who are substituted in during the first half of matches with play schedule 'NT'.
SELECT
a.match_no, -- Selecting the match number from the player_in_out table aliased as 'a'
c.country_name, -- Selecting the country name from the soccer_country table aliased as 'c'
b.player_name, -- Selecting the player name from the player_mast subquery aliased as 'b'
b.jersey_no, -- Selecting the jersey number from the player_mast subquery aliased as 'b'
a.time_in_out -- Selecting the time in/out from the player_in_out table aliased as 'a'
FROM
player_in_out a -- Specifying the player_in_out table with an alias 'a'
JOIN
(
-- Subquery to select player information from the player_mast table
SELECT
player_id, -- Selecting the player ID
player_name, -- Selecting the player name
jersey_no, -- Selecting the jersey number
team_id -- Selecting the team ID
FROM
player_mast -- Specifying the player_mast table for the subquery
) b ON a.player_id = b.player_id -- Joining the subquery with the player_in_out table based on the player_id
JOIN
soccer_country c ON b.team_id = c.country_id -- Joining the soccer_country table with the player_mast subquery based on the team_id
WHERE
a.in_out = 'I' -- Filtering for players who are substituted in ('I')
AND a.play_schedule = 'NT' -- Filtering for matches with play schedule 'NT'
AND a.play_half = 1 -- Filtering substitutions made during the first half of matches
-- Ordering the results by match number
ORDER BY
a.match_no;
Explanation:
This query uses a subquery to first select necessary columns from player_mast. It then performs INNER JOINs to retrieve the desired information and applies the specified conditions. The results are ordered by match_no.
Relational Algebra Expression:
Relational Algebra Tree:
Go to:
PREV : Which player won Man of the Match at EURO cup 2016?
NEXT : List of the players of each match against each match.
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.