w3resource

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

soccer database relationship structure

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.



Follow us on Facebook and Twitter for latest update.

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

Ref: https://bit.ly/3PIUmPL

 





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