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.