SQL Exercise: Which player won Man of the Match at EURO cup 2016?
34. From the following table, write a SQL query to find out the player who was selected for the ‘Man of the Match’ award in the finals of EURO cup 2016. Return player name, country name.
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 table: match_mast
match_no | play_stage | play_date | results | decided_by | goal_score | venue_id | referee_id | audence | plr_of_match | stop1_sec | stop2_sec
----------+------------+------------+---------+------------+------------+----------+------------+---------+--------------+-----------+-----------
1 | G | 2016-06-11 | WIN | N | 2-1 | 20008 | 70007 | 75113 | 160154 | 131 | 242
2 | G | 2016-06-11 | WIN | N | 0-1 | 20002 | 70012 | 33805 | 160476 | 61 | 182
3 | G | 2016-06-11 | WIN | N | 2-1 | 20001 | 70017 | 37831 | 160540 | 64 | 268
4 | G | 2016-06-12 | DRAW | N | 1-1 | 20005 | 70011 | 62343 | 160128 | 0 | 185
5 | G | 2016-06-12 | WIN | N | 0-1 | 20007 | 70006 | 43842 | 160084 | 125 | 325
6 | G | 2016-06-12 | WIN | N | 1-0 | 20006 | 70014 | 33742 | 160291 | 2 | 246
7 | G | 2016-06-13 | WIN | N | 2-0 | 20003 | 70002 | 43035 | 160176 | 89 | 188
8 | G | 2016-06-13 | WIN | N | 1-0 | 20010 | 70009 | 29400 | 160429 | 360 | 182
9 | G | 2016-06-13 | DRAW | N | 1-1 | 20008 | 70010 | 73419 | 160335 | 67 | 194
........
51 | F | 2016-07-11 | WIN | N | 1-0 | 20008 | 70005 | 75868 | 160307 | 161 | 181
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 Solution:
SQL Code:
-- This query selects the player name and country name for the player of the match in the final stage matches.
SELECT
a.player_name, -- Selecting the player name from the player_mast table aliased as 'a'
b.country_name -- Selecting the country name from the soccer_country table aliased as 'b'
FROM
player_mast a -- Specifying the player_mast table with an alias 'a'
JOIN
match_mast c ON c.plr_of_match = a.player_id -- Joining the match_mast table with the player_mast table based on the player of the match, filtered for final stage matches
AND c.play_stage = 'F' -- Filtering for final stage matches
-- Joining the soccer_country table with the player_mast table based on the team_id
JOIN
soccer_country b ON a.team_id = b.country_id;
Sample Output:
player_name | country_name -------------+-------------- Pepe | Portugal (1 row)
Code Explanation:
The said query in SQL that retrieves information about the player of the match in the final stage of soccer matches, including their name and country from the tables player_mast, match_mast, and soccer_country.
The JOIN clause joins the 'player_mast' table alias as 'a' with the 'match_mast' table alias as 'c' based on the common column "plr_of_match" in 'c' and "player_id" in 'a'. Additionally, only rows where the "play_stage" column in 'c' has the value of 'F' will be joined. Then, the resulting table is joined with the 'soccer_country' table alias as 'b' based on the common column "team_id" in 'a' and "country_id" in 'b'.
Alternative Solution:
Using INNER JOIN with Subquery:
-- This query selects the player name and country name for the player of the match in the final stage matches.
SELECT
a.player_name, -- Selecting the player name from the player_mast table aliased as 'a'
b.country_name -- Selecting the country name from the soccer_country table aliased as 'b'
FROM
player_mast a -- Specifying the player_mast table with an alias 'a'
JOIN
(
-- Subquery to select player of the match and play stage from match_mast for final stage matches
SELECT
plr_of_match, -- Selecting the player of the match
play_stage -- Selecting the play stage
FROM
match_mast -- Specifying the match_mast table for the subquery
WHERE
play_stage = 'F' -- Filtering for final stage matches
) c ON c.plr_of_match = a.player_id -- Joining the subquery with the player_mast table based on the player of the match
-- Joining the soccer_country table with the player_mast table based on the team_id
JOIN
soccer_country b ON a.team_id = b.country_id;
Explanation:
This query uses a subquery to first select plr_of_match and play_stage with the condition play_stage = 'F'. It then joins player_mast and soccer_country based on the player_id and team_id respectively.
Relational Algebra Expression:
Relational Algebra Tree:
Go to:
PREV : All the captains and goalkeepers for all the teams.
NEXT : Players who came onto the field during the first half.
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.