w3resource

SQL Exercise: All the captains and goalkeepers for all the teams

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)

Code Explanation:

The said query in SQL that retrieves information about the captain and goal keeper of soccer teams in various matches, including their country name, and the matches' numbers from the tables match_captain, match_details, soccer_country, and player_mast.
The 'match_captain' table alias as 'a' is naturally joined with the 'match_details' table alias as 'b' based on all common columns. Then, the resulting table is joined with the 'soccer_country' table an alias of 'e' based on the common column "team_id" in 'b' and "country_id" in 'e'.
Finally, the resulting table is joined with the 'player_mast' table twice, aliases of 'c' and 'd' based on the common column "player_captain" in 'a' and "player_id" in 'c', and "player_gk" in 'b' and "player_id" in 'd' respectively.

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: Find captains for all the matches in the tournament.
Next SQL Exercise: Which player won Man of the Match at EURO cup 2016?

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

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

 





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