SQL Exercise: Players who came onto the field during the first half
SQL soccer Database: Joins Exercise-35 with Solution
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
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 b.team_id=c.country_id
WHERE a.in_out='I'
AND a.play_schedule='NT'
AND a.play_half=1
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.
Relational Algebra Expression:

Relational Algebra Tree:

Practice Online
Sample Database: soccer

Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Which player won Man of the Match at EURO cup 2016?
Next SQL Exercise: List of the players of each match against each match.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
How to drop all tables from a database with one SQL query?
USE Databasename SELECT 'DROP TABLE [' + name + '];' FROM sys.tables
Ref: https://bit.ly/3PIUmPL
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- 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
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