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

soccer database relationship structure

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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

Follow us on Facebook and Twitter for latest update.

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


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