SQL exercises on soccer Database: Find the player who was the first player to be sent off at the tournament EURO cup 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)

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 count the number of matches played in each venue. Sort the result-set on venue name. Return Venue name, city, and number of matches.
Next: From the following tables, write a SQL query to find those teams that scored only one goal to the tournament. Return country_name as "Team", team in the group, goal_for.