SQL Exercise: Which player was the first to be sent off at Euro 2016

SQL soccer Database: Joins Exercise-15 with Solution

15. From the following tables, write a SQL query to find the player who was the first player to be sent off at the tournament EURO cup 2016. Return match Number, country name and player name.

Sample table: player_booked

Sample table: player_mast

Sample table: soccer_country

Sample Solution:

SQL Code:

SELECT match_no, country_name, player_name, 
booking_time as "sent_off_time", play_schedule, jersey_no
FROM player_booked a
JOIN player_mast b
ON a.player_id=b.player_id
JOIN soccer_country c
ON a.team_id=c.country_id
AND  a.sent_off='Y'
AND match_no=(
	SELECT MIN(match_no) 
	from player_booked)
ORDER BY match_no,play_schedule,play_half,booking_time;

Sample Output:

 match_no | country_name |   player_name   | sent_off_time | play_schedule | jersey_no
----------+--------------+-----------------+---------------+---------------+-----------
        1 | France       | Olivier Giroud  |            69 | NT            |         9
(1 row)

Code Explanation:

The said query in SQL that selects information about players who have been sent off during a specific match. The specific match is determined by the subquery that returns the minimum match number.
The JOIN statements are used to link the tables player_booked, player_mast, and soccer_country together based on matching columns. The ON clauses specify the conditions for the join.
The WHERE clause filters the results to only include rows where the sent_off column in the player_booked table is equal to 'Y', and the match_no column in the player_booked table is equal to the minimum match number returned by the subquery.
The results are sorted in ascending order by match_no, play_schedule, play_half, and booking_time.

Practice Online

Sample Database: soccer

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Number of matches played at each venue and their city
Next SQL Exercise: Teams that scored only one goal to the torunament.