SQL exercises on soccer Database: Find the substitute players who came into the field in the first half of play within normal play schedule

SQL soccer Database: Joins Exercise-35 with Solution

35. From the following tables, write a SQL query to find the substitute players who came into the field in the first half of play within normal play schedule. 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)

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: From the following tables, write a SQL query to find the player who was selected for the ‘Man of the Match’ award in the finals of EURO cup 2016.Return player name, country name.
Next: From the following table, write a SQL query to prepare a list for the player of the match against each match. Return match number, play date, country name, player of the Match, jersey number.