SQL Exercise: Players who entered the field in the most recent play
SQL soccer Database: Joins Exercise-61 with Solution
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
Sample table: player_mast
Sample table: soccer_country
Sample Solution:
SQL Code:
SELECT match_no,
country_name,
player_name,
jersey_no,
time_in_out
FROM player_in_out a
JOIN player_mast b ON a.player_id=b.player_id
JOIN soccer_country c ON a.team_id=c.country_id
WHERE time_in_out=
(SELECT max(time_in_out)
FROM player_in_out)
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.,/
Practice Online
Sample Database: soccer

Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Goals scored by players based on their position.
Next SQL Exercise: SQL Exercises on Hospital Database
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
What is the best way to paginate results in SQL Server?
SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, * FROM Orders WHERE OrderDate >= '1980-01-01' ) AS RowConstrainedResult WHERE RowNum >= 1 AND RowNum < 20 ORDER BY RowNum
Database: SQL Server
Ref: https://bit.ly/3MGrNlk
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook