SQL exercises on soccer Database: Find the captain and goal keeper for all the matches for all the team

SQL soccer Database: Joins Exercise-33 with Solution

33. From the following tables, write a SQL query to find the captain and goalkeeper of all the matches. Return match number, Captain, Goal Keeper and country name.

Sample table: soccer_country

Sample table: match_captain

Sample table: match_details

Sample table: player_mast

Sample Solution:

SQL Code:

SELECT a.match_no,c.player_name as "Captain", 
d.player_name as "Goal Keeper",e.country_name
FROM match_captain a
NATURAL JOIN match_details b
JOIN soccer_country e ON b.team_id=e.country_id
JOIN player_mast c ON a.player_captain=c.player_id
JOIN player_mast d ON b.player_gk=d.player_id;

Sample Output:

 match_no |         Captain         |     Goal Keeper     |    country_name
----------+-------------------------+---------------------+--------------------
        1 | Hugo Lloris             | Hugo Lloris         | France
        1 | Vlad Chiriches          | Ciprian Tatarusanu  | Romania
        2 | Lorik Cana              | Etrit Berisha       | Albania
        2 | Stephan Lichtsteiner    | Yann Sommer         | Switzerland
        3 | Ashley Williams         | Danny Ward          | Wales
        3 | Martin Skrtel           | MatusKozacik        | Slovakia
        4 | Wayne Rooney            | Joe Hart            | England
        4 | Vasili Berezutski       | Igor Akinfeev       | Russia
        5 | Arda Turan              | Volkan Babacan      | Turkey
        5 | Darijo Srna             | Danijel SubaSic     | Croatia
        6 | Robert Lewandowski      | Wojciech Szczesny   | Poland
        6 | Steven Davis            | Michael McGovern    | Northern Ireland
        7 | Manuel Neuer            | Manuel Neuer        | Germany
        7 | Vyacheslav Shevchuk     | Andriy Pyatov       | Ukraine
        8 | Sergio Ramos            | David de Gea        | Spain
        8 | TomasRosicky            | Petr Cech           | Czech Republic
        9 | John OShea              | Darren Randolph     | Republic of Ireland
        9 | Zlatan Ibrahimovic      | Andreas Isaksson    | Sweden
       10 | Eden Hazard             | Thibaut Courtois    | Belgium
       10 | Gianluigi Buffon        | Gianluigi Buffon    | Italy
       11 | Christian Fuchs         | Robert Almer        | Austria
       11 | Balazs Dzsudzsak        | Gabor Kiraly        | Hungary
       12 | Cristiano Ronaldo       | Rui Patricio        | Portugal
       12 | Aron Gunnarsson         | Hannes Halldorsson  | Iceland
       13 | Vasili Berezutski       | Igor Akinfeev       | Russia
       13 | Martin Skrtel           | MatusKozacik        | Slovakia
       14 | Vlad Chiriches          | Ciprian Tatarusanu  | Romania
       14 | Stephan Lichtsteiner    | Yann Sommer         | Switzerland
       15 | Hugo Lloris             | Hugo Lloris         | France
       15 | Ansi Agolli             | Etrit Berisha       | Albania
       16 | Wayne Rooney            | Joe Hart            | England
       16 | Ashley Williams         | Wayne Hennessey     | Wales
       17 | Vyacheslav Shevchuk     | Andriy Pyatov       | Ukraine
       17 | Steven Davis            | Michael McGovern    | Northern Ireland
       18 | Manuel Neuer            | Manuel Neuer        | Germany
       18 | Robert Lewandowski      | Lukasz Fabianski    | Poland
       19 | Gianluigi Buffon        | Gianluigi Buffon    | Italy
       19 | Zlatan Ibrahimovic      | Andreas Isaksson    | Sweden
       20 | TomasRosicky            | Petr Cech           | Czech Republic
       20 | Darijo Srna             | Danijel SubaSic     | Croatia
       21 | Sergio Ramos            | David de Gea        | Spain
       21 | Arda Turan              | Volkan Babacan      | Turkey
       22 | Eden Hazard             | Thibaut Courtois    | Belgium
       22 | John OShea              | Darren Randolph     | Republic of Ireland
       23 | Aron Gunnarsson         | Hannes Halldorsson  | Iceland
       23 | Balazs Dzsudzsak        | Gabor Kiraly        | Hungary
       24 | Cristiano Ronaldo       | Rui Patricio        | Portugal
       24 | Christian Fuchs         | Robert Almer        | Austria
       25 | Vlad Chiriches          | Ciprian Tatarusanu  | Romania
       25 | Ansi Agolli             | Etrit Berisha       | Albania
       26 | Stephan Lichtsteiner    | Yann Sommer         | Switzerland
       26 | Hugo Lloris             | Hugo Lloris         | France
       27 | Roman Shirokov          | Igor Akinfeev       | Russia
       27 | Ashley Williams         | Wayne Hennessey     | Wales
       28 | Martin Skrtel           | MatusKozacik        | Slovakia
       28 | Gary Cahill             | Joe Hart            | England
       29 | Ruslan Rotan            | Andriy Pyatov       | Ukraine
       29 | Robert Lewandowski      | Lukasz Fabianski    | Poland
       30 | Steven Davis            | Michael McGovern    | Northern Ireland
       30 | Manuel Neuer            | Manuel Neuer        | Germany
       31 | Petr Cech               | Petr Cech           | Czech Republic
       31 | Arda Turan              | Volkan Babacan      | Turkey
       32 | Darijo Srna             | Danijel SubaSic     | Croatia
       32 | Sergio Ramos            | David de Gea        | Spain
       33 | Aron Gunnarsson         | Hannes Halldorsson  | Iceland
       33 | Christian Fuchs         | Robert Almer        | Austria
       34 | Balazs Dzsudzsak        | Gabor Kiraly        | Hungary
       34 | Cristiano Ronaldo       | Rui Patricio        | Portugal
       35 | Leonardo Bonucci        | Salvatore Sirigu    | Italy
       35 | Seamus Coleman          | Darren Randolph     | Republic of Ireland
       36 | Zlatan Ibrahimovic      | Andreas Isaksson    | Sweden
       36 | Eden Hazard             | Thibaut Courtois    | Belgium
       37 | Stephan Lichtsteiner    | Yann Sommer         | Switzerland
       37 | Robert Lewandowski      | Lukasz Fabianski    | Poland
       38 | Ashley Williams         | Wayne Hennessey     | Wales
       38 | Steven Davis            | Michael McGovern    | Northern Ireland
       39 | Darijo Srna             | Danijel SubaSic     | Croatia
       39 | Cristiano Ronaldo       | Rui Patricio        | Portugal
       40 | Hugo Lloris             | Hugo Lloris         | France
       40 | Seamus Coleman          | Darren Randolph     | Republic of Ireland
       41 | Manuel Neuer            | Manuel Neuer        | Germany
       41 | Martin Skrtel           | MatusKozacik        | Slovakia
       42 | Balazs Dzsudzsak        | Gabor Kiraly        | Hungary
       42 | Eden Hazard             | Thibaut Courtois    | Belgium
       43 | Gianluigi Buffon        | Gianluigi Buffon    | Italy
       43 | Sergio Ramos            | David de Gea        | Spain
       44 | Wayne Rooney            | Joe Hart            | England
       44 | Aron Gunnarsson         | Hannes Halldorsson  | Iceland
       45 | Robert Lewandowski      | Lukasz Fabianski    | Poland
       45 | Cristiano Ronaldo       | Rui Patricio        | Portugal
       46 | Ashley Williams         | Wayne Hennessey     | Wales
       46 | Eden Hazard             | Thibaut Courtois    | Belgium
       47 | Manuel Neuer            | Manuel Neuer        | Germany
       47 | Gianluigi Buffon        | Gianluigi Buffon    | Italy
       48 | Hugo Lloris             | Hugo Lloris         | France
       48 | Aron Gunnarsson         | Hannes Halldorsson  | Iceland
       49 | Cristiano Ronaldo       | Rui Patricio        | Portugal
       49 | Ashley Williams         | Wayne Hennessey     | Wales
       50 | Hugo Lloris             | Hugo Lloris         | France
       50 | Bastian Schweinsteiger  | Manuel Neuer        | Germany
       51 | Cristiano Ronaldo       | Rui Patricio        | Portugal
       51 | Hugo Lloris             | Hugo Lloris         | France
(102 rows)

Practice Online

Sample Database: soccer

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

Previous: From the following tables, write a SQL query to find the captains of all the matches in the tournament. Return match number, country name, player name, jersey number and position to play.
Next: From the following table, write a SQL query to find the player who was selected for the ‘Man of the Match’ award in the finals of EURO cup 2016.Return player name, country name.