SQL Exercise: Which player was the first to be sent off at Euro 2016
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
match_no | team_id | player_id | booking_time | sent_off | play_schedule | play_half
----------+---------+-----------+--------------+----------+---------------+-----------
1 | 1216 | 160349 | 32 | | NT | 1
1 | 1216 | 160355 | 45 | | NT | 1
1 | 1207 | 160159 | 69 | Y | NT | 2
1 | 1216 | 160360 | 78 | | NT | 2
2 | 1221 | 160470 | 14 | | NT | 1
2 | 1201 | 160013 | 23 | | NT | 1
2 | 1201 | 160013 | 36 | | NT | 1
2 | 1201 | 160014 | 63 | | NT | 2
2 | 1221 | 160472 | 66 | | NT | 2
.......
51 | 1214 | 160302 | 122 | | ET | 2
Sample table: player_mast
player_id | team_id | jersey_no | player_name | posi_to_play | dt_of_bir | age | playing_club
-----------+---------+-----------+-------------------------+--------------+------------+-----+---------------------
160001 | 1201 | 1 | Etrit Berisha | GK | 1989-03-10 | 27 | Lazio
160008 | 1201 | 2 | Andi Lila | DF | 1986-02-12 | 30 | Giannina
160016 | 1201 | 3 | Ermir Lenjani | MF | 1989-08-05 | 26 | Nantes
160007 | 1201 | 4 | Elseid Hysaj | DF | 1994-02-20 | 22 | Napoli
160013 | 1201 | 5 | Lorik Cana | MF | 1983-07-27 | 32 | Nantes
160010 | 1201 | 6 | Frederic Veseli | DF | 1992-11-20 | 23 | Lugano
160004 | 1201 | 7 | Ansi Agolli | DF | 1982-10-11 | 33 | Qarabag
160012 | 1201 | 8 | Migjen Basha | MF | 1987-01-05 | 29 | Como
160017 | 1201 | 9 | Ledian Memushaj | MF | 1986-12-17 | 29 | Pescara
.........
160548 | 1224 | 23 | Simon Church | FD | 1988-12-10 | 27 | MK Dons
Sample table: soccer_country
country_id | country_abbr | country_name
------------+--------------+---------------------
1201 | ALB | Albania
1202 | AUT | Austria
1203 | BEL | Belgium
1204 | CRO | Croatia
1205 | CZE | Czech Republic
1206 | ENG | England
1207 | FRA | France
1208 | GER | Germany
1209 | HUN | Hungary
.......
1229 | NOR | Norway
Sample Solution:
SQL Code:
-- Selecting match_no, country_name, player_name, sent_off_time, play_schedule, and jersey_no for booked players who were sent off
SELECT match_no, country_name, player_name,
booking_time as "sent_off_time", play_schedule, jersey_no
-- From clause with JOINs between player_booked, player_mast, and soccer_country
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
-- Conditions for filtering booked players who were sent off
AND a.sent_off = 'Y'
-- Condition for filtering matches with the minimum match_no among booked players
AND match_no = (
SELECT MIN(match_no)
-- Subquery to find the minimum match_no among booked players
FROM player_booked
)
-- Ordering the results by match_no, play_schedule, play_half, and booking_time
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.
Alternative Solutions:
Using an Inner Join and Subquery:
-- Selecting match_no, country_name, player_name, sent_off_time, play_schedule, and jersey_no for booked players who were sent off
SELECT pb.match_no, sc.country_name, pm.player_name,
pb.booking_time AS "sent_off_time", pb.play_schedule, pm.jersey_no
-- From clause with JOINs between player_booked, player_mast, and soccer_country
FROM player_booked pb
JOIN player_mast pm ON pb.player_id = pm.player_id
JOIN soccer_country sc ON pb.team_id = sc.country_id
-- Joining with a subquery to filter matches with the minimum match_no among booked players
JOIN (
SELECT MIN(match_no) AS min_match_no
FROM player_booked
) min_match ON pb.match_no = min_match.min_match_no
-- Condition for filtering sent-off players
WHERE pb.sent_off = 'Y'
-- Ordering the results by match_no, play_schedule, play_half, and booking_time
ORDER BY pb.match_no, pb.play_schedule, pb.play_half, pb.booking_time;
Explanation:
This query uses an inner join with a subquery that finds the minimum match number. The join condition ensures that only the rows with the minimum match number are selected.
Using ROW_NUMBER() Window Function:
-- Selecting match_no, country_name, player_name, sent_off_time, play_schedule, and jersey_no for the earliest booked player who was sent off
SELECT match_no, country_name, player_name,
sent_off_time, play_schedule, jersey_no
-- From clause with a subquery (aliased as subquery) using ROW_NUMBER() window function
FROM (
-- Subquery to select match_no, country_name, player_name, sent_off_time, play_schedule, jersey_no, and assign row numbers
SELECT pb.match_no, sc.country_name, pm.player_name,
pb.booking_time AS sent_off_time, pb.play_schedule, pm.jersey_no,
ROW_NUMBER() OVER (ORDER BY pb.match_no, pb.play_schedule, pb.play_half, pb.booking_time) as row_num
-- From clause with JOINs between player_booked, player_mast, and soccer_country
FROM player_booked pb
JOIN player_mast pm ON pb.player_id = pm.player_id
JOIN soccer_country sc ON pb.team_id = sc.country_id
-- Condition for filtering sent-off players
WHERE pb.sent_off = 'Y'
) subquery
-- Filtering the results to include only rows with row number equal to 1 (earliest booked player who was sent off)
WHERE row_num = 1;
Explanation:
This query uses a subquery with a ROW_NUMBER() window function to assign a unique number to each row based on the specified order. The outer query then selects only the rows with row number 1, effectively choosing the earliest occurrence for each match.
Go to:
PREV : Number of matches played at each venue and their city
NEXT : Teams that scored only one goal to the torunament.
Practice Online
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.