w3resource
Soccer Database Exercises

SQL Joins exercises on soccer Database: Prepare a list for the player of the match against each match

SQL soccer Database: Joins Exercise-36 with Solution

36. Write a query in SQL to prepare a list for the player of the match against each match.

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)

Practice Online


Sample Database: soccer

soccer database relationship structure

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query in SQL to find the substitute players who came into the field in the first half of play within normal play schedule.
Next: Write a query in SQL to find the player along with his country who taken the penalty shot number 26.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming