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
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;
match_no | country_name | player_name | sent_off_time | play_schedule | jersey_no ----------+--------------+-----------------+---------------+---------------+----------- 1 | France | Olivier Giroud | 69 | NT | 9 (1 row)
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.
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
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
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
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