SQL Exercise: List of the players of each match against each match
SQL soccer Database: Joins Exercise-36 with Solution
36. From the following table, write a SQL query to prepare a list for the “player of the match” against each match. Return match number, play date, country name, player of the Match, jersey number.
Sample table: match_mast
Sample table: player_mast
Sample table: soccer_country
Sample Solution:
SQL Code:
SELECT match_no,play_date,country_name,
player_name AS "Player of the Match",jersey_no
FROM match_mast a
JOIN player_mast b ON
a.plr_of_match=b.player_id
JOIN soccer_country c ON
b.team_id=c.country_id;
Sample Output:
match_no | play_date | country_name | Player of the Match | jersey_no ----------+------------+---------------------+-----------------------+----------- 25 | 2016-06-20 | Albania | Arlind Ajeti | 18 22 | 2016-06-18 | Belgium | Axel Witsel | 6 42 | 2016-06-27 | Belgium | Eden Hazard | 10 36 | 2016-06-23 | Belgium | Eden Hazard | 10 32 | 2016-06-22 | Croatia | Ivan PeriSic | 4 20 | 2016-06-17 | Croatia | Ivan Rakitic | 7 5 | 2016-06-12 | Croatia | Luka Modric | 10 4 | 2016-06-12 | England | Eric Dier | 17 50 | 2016-07-08 | France | Antoine Griezmann | 7 40 | 2016-06-26 | France | Antoine Griezmann | 7 15 | 2016-06-16 | France | Dimitri Payet | 8 1 | 2016-06-11 | France | Dimitri Payet | 8 48 | 2016-07-04 | France | Olivier Giroud | 9 47 | 2016-07-03 | Germany | Manuel Neuer | 1 30 | 2016-06-21 | Germany | Mesut ozil | 8 41 | 2016-06-26 | Germany | Julian Draxler | 11 18 | 2016-06-17 | Germany | Jerome Boateng | 17 7 | 2016-06-13 | Germany | Toni Kroos | 18 11 | 2016-06-14 | Hungary | Laszlo Kleinheisler | 15 44 | 2016-06-28 | Iceland | Ragnar Sigurdsson | 6 23 | 2016-06-18 | Iceland | Kolbeinn Sigthorsson | 9 33 | 2016-06-22 | Iceland | Kari Arnason | 14 19 | 2016-06-17 | Italy | Eder | 17 43 | 2016-06-27 | Italy | Leonardo Bonucci | 19 10 | 2016-06-14 | Italy | Emanuele Giaccherini | 23 17 | 2016-06-16 | Northern Ireland | Gareth McAuley | 4 6 | 2016-06-12 | Poland | Grzegorz Krychowiak | 10 51 | 2016-07-11 | Portugal | Pepe | 3 49 | 2016-07-07 | Portugal | Cristiano Ronaldo | 7 34 | 2016-06-22 | Portugal | Cristiano Ronaldo | 7 24 | 2016-06-19 | Portugal | Joao Moutinho | 8 45 | 2016-07-01 | Portugal | Renato Sanches | 16 39 | 2016-06-26 | Portugal | Renato Sanches | 16 12 | 2016-06-15 | Portugal | Nani | 17 35 | 2016-06-23 | Republic of Ireland | Robbie Brady | 19 9 | 2016-06-13 | Republic of Ireland | Wes Hoolahan | 20 13 | 2016-06-15 | Slovakia | Marek Hamsik | 17 28 | 2016-06-21 | Slovakia | MatusKozacik | 23 21 | 2016-06-18 | Spain | Andres Iniesta | 6 8 | 2016-06-13 | Spain | Andres Iniesta | 6 26 | 2016-06-20 | Switzerland | Yann Sommer | 1 14 | 2016-06-15 | Switzerland | Granit Xhaka | 10 37 | 2016-06-25 | Switzerland | Xherdan Shaqiri | 23 2 | 2016-06-11 | Switzerland | Xherdan Shaqiri | 23 31 | 2016-06-22 | Turkey | Burak Yilmaz | 17 29 | 2016-06-21 | Ukraine | Ruslan Rotan | 14 16 | 2016-06-16 | Wales | Joe Allen | 7 3 | 2016-06-11 | Wales | Joe Allen | 7 46 | 2016-07-02 | Wales | Hal Robson-Kanu | 9 27 | 2016-06-21 | Wales | Aaron Ramsey | 10 38 | 2016-06-25 | Wales | Gareth Bale | 11 (51 rows)
Code Explanation:
The said query in SQL that retrieves information about the player of the match in each soccer match, including their name, jersey number, the country they belong to, the match number, and the date the match was played from the tables match_mast, player_mast, and soccer_country.
The JOIN clause combines the tables'match_mast' and 'player_mast' alias as 'a' and 'b' based on the common columns "plr_of_match" and "player_id" respectively. Then, the resulting table is joined with the 'soccer_country' table alias as 'c' based on the common column "team_id" and "country_id" respectively in 'b' and 'c'.
Practice Online
Sample Database: soccer

Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Players who came onto the field during the first half.
Next SQL Exercise: Find the player who took the 26th penalty shot.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
What is the best way to paginate results in SQL Server?
SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, * FROM Orders WHERE OrderDate >= '1980-01-01' ) AS RowConstrainedResult WHERE RowNum >= 1 AND RowNum < 20 ORDER BY RowNum
Database: SQL Server
Ref: https://bit.ly/3MGrNlk
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
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