w3resource

SQL Exercise: Find captains for all the matches in the tournament

SQL soccer Database: Joins Exercise-32 with Solution

32. 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.

Sample table: soccer_country


Sample table: match_captain


Sample table: player_mast


Sample Solution:

SQL Code:

SELECT match_no,country_name, player_name, jersey_no, posi_to_play 
FROM match_captain a
JOIN soccer_country b ON a.team_id=b.country_id
JOIN player_mast c ON a.player_captain=c.player_id
ORDER BY match_no;

Sample Output:

 match_no |    country_name     |       player_name       | jersey_no | posi_to_play
----------+---------------------+-------------------------+-----------+--------------
        1 | France              | Hugo Lloris             |         1 | GK
        1 | Romania             | Vlad Chiriches          |         6 | DF
        2 | Albania             | Lorik Cana              |         5 | MF
        2 | Switzerland         | Stephan Lichtsteiner    |         2 | DF
        3 | Slovakia            | Martin Skrtel           |         3 | DF
        3 | Wales               | Ashley Williams         |         6 | DF
        4 | England             | Wayne Rooney            |        10 | FD
        4 | Russia              | Vasili Berezutski       |        14 | DF
        5 | Turkey              | Arda Turan              |        10 | MF
        5 | Croatia             | Darijo Srna             |        11 | DF
        6 | Poland              | Robert Lewandowski      |         9 | FD
        6 | Northern Ireland    | Steven Davis            |         8 | MF
        7 | Germany             | Manuel Neuer            |         1 | GK
        7 | Ukraine             | Vyacheslav Shevchuk     |        13 | DF
        8 | Spain               | Sergio Ramos            |        15 | DF
        8 | Czech Republic      | TomasRosicky            |        10 | MF
        9 | Republic of Ireland | John OShea              |         4 | DF
        9 | Sweden              | Zlatan Ibrahimovic      |        10 | FD
       10 | Italy               | Gianluigi Buffon        |         1 | GK
       10 | Belgium             | Eden Hazard             |        10 | MF
       11 | Austria             | Christian Fuchs         |         5 | DF
       11 | Hungary             | Balazs Dzsudzsak        |         7 | FD
       12 | Portugal            | Cristiano Ronaldo       |         7 | FD
       12 | Iceland             | Aron Gunnarsson         |        17 | MF
       13 | Russia              | Vasili Berezutski       |        14 | DF
       13 | Slovakia            | Martin Skrtel           |         3 | DF
       14 | Romania             | Vlad Chiriches          |         6 | DF
       14 | Switzerland         | Stephan Lichtsteiner    |         2 | DF
       15 | France              | Hugo Lloris             |         1 | GK
       15 | Albania             | Ansi Agolli             |         7 | DF
       16 | Wales               | Ashley Williams         |         6 | DF
       16 | England             | Wayne Rooney            |        10 | FD
       17 | Ukraine             | Vyacheslav Shevchuk     |        13 | DF
       17 | Northern Ireland    | Steven Davis            |         8 | MF
       18 | Poland              | Robert Lewandowski      |         9 | FD
       18 | Germany             | Manuel Neuer            |         1 | GK
       19 | Sweden              | Zlatan Ibrahimovic      |        10 | FD
       19 | Italy               | Gianluigi Buffon        |         1 | GK
       20 | Croatia             | Darijo Srna             |        11 | DF
       20 | Czech Republic      | TomasRosicky            |        10 | MF
       21 | Turkey              | Arda Turan              |        10 | MF
       21 | Spain               | Sergio Ramos            |        15 | DF
       22 | Republic of Ireland | John OShea              |         4 | DF
       22 | Belgium             | Eden Hazard             |        10 | MF
       23 | Iceland             | Aron Gunnarsson         |        17 | MF
       23 | Hungary             | Balazs Dzsudzsak        |         7 | FD
       24 | Portugal            | Cristiano Ronaldo       |         7 | FD
       24 | Austria             | Christian Fuchs         |         5 | DF
       25 | Albania             | Ansi Agolli             |         7 | DF
       25 | Romania             | Vlad Chiriches          |         6 | DF
       26 | Switzerland         | Stephan Lichtsteiner    |         2 | DF
       26 | France              | Hugo Lloris             |         1 | GK
       27 | Russia              | Roman Shirokov          |        15 | MF
       27 | Wales               | Ashley Williams         |         6 | DF
       28 | Slovakia            | Martin Skrtel           |         3 | DF
       28 | England             | Gary Cahill             |         5 | DF
       29 | Ukraine             | Ruslan Rotan            |        14 | MF
       29 | Poland              | Robert Lewandowski      |         9 | FD
       30 | Northern Ireland    | Steven Davis            |         8 | MF
       30 | Germany             | Manuel Neuer            |         1 | GK
       31 | Czech Republic      | Petr Cech               |         1 | GK
       31 | Turkey              | Arda Turan              |        10 | MF
       32 | Croatia             | Darijo Srna             |        11 | DF
       32 | Spain               | Sergio Ramos            |        15 | DF
       33 | Iceland             | Aron Gunnarsson         |        17 | MF
       33 | Austria             | Christian Fuchs         |         5 | DF
       34 | Hungary             | Balazs Dzsudzsak        |         7 | FD
       34 | Portugal            | Cristiano Ronaldo       |         7 | FD
       35 | Republic of Ireland | Seamus Coleman          |         2 | DF
       35 | Italy               | Leonardo Bonucci        |        19 | DF
       36 | Sweden              | Zlatan Ibrahimovic      |        10 | FD
       36 | Belgium             | Eden Hazard             |        10 | MF
       37 | Poland              | Robert Lewandowski      |         9 | FD
       37 | Switzerland         | Stephan Lichtsteiner    |         2 | DF
       38 | Wales               | Ashley Williams         |         6 | DF
       38 | Northern Ireland    | Steven Davis            |         8 | MF
       39 | Croatia             | Darijo Srna             |        11 | DF
       39 | Portugal            | Cristiano Ronaldo       |         7 | FD
       40 | Republic of Ireland | Seamus Coleman          |         2 | DF
       40 | France              | Hugo Lloris             |         1 | GK
       41 | Germany             | Manuel Neuer            |         1 | GK
       41 | Slovakia            | Martin Skrtel           |         3 | DF
       42 | Belgium             | Eden Hazard             |        10 | MF
       42 | Hungary             | Balazs Dzsudzsak        |         7 | FD
       43 | Spain               | Sergio Ramos            |        15 | DF
       43 | Italy               | Gianluigi Buffon        |         1 | GK
       44 | Iceland             | Aron Gunnarsson         |        17 | MF
       44 | England             | Wayne Rooney            |        10 | FD
       45 | Portugal            | Cristiano Ronaldo       |         7 | FD
       45 | Poland              | Robert Lewandowski      |         9 | FD
       46 | Wales               | Ashley Williams         |         6 | DF
       46 | Belgium             | Eden Hazard             |        10 | MF
       47 | Italy               | Gianluigi Buffon        |         1 | GK
       47 | Germany             | Manuel Neuer            |         1 | GK
       48 | Iceland             | Aron Gunnarsson         |        17 | MF
       48 | France              | Hugo Lloris             |         1 | GK
       49 | Portugal            | Cristiano Ronaldo       |         7 | FD
       49 | Wales               | Ashley Williams         |         6 | DF
       50 | France              | Hugo Lloris             |         1 | GK
       50 | Germany             | Bastian Schweinsteiger  |         7 | MF
       51 | France              | Hugo Lloris             |         1 | GK
       51 | Portugal            | Cristiano Ronaldo       |         7 | FD
(102 rows)

Code Explanation:

The given query in SQL that retrieves data from the tables match_captain, soccer_country, and player_mast - using JOIN operations, and returns the columns match_no, country_name, player_name, jersey_no, and posi_to_play for each row of data.
The 'match_captain' table an alias of 'a' is joined with the 'soccer_country' table alias as 'b' based on the common column "team_id" in 'a' and "country_id" in 'b'. Then, the resulting table is joined with the 'player_mast' table alias as 'c' based on the common column "player_captain" in 'a' and "player_id" in 'c'.
The result set then sorted by the "match_no" column in ascending order.

Relational Algebra Expression: Relational Algebra Expression: From the following tables, write a SQL query to find the captains of the top four teams that participated in the semi-finals (match 48 and 49) in the tournament. Return country name, player name, jersey number and position to play.

Relational Algebra Tree:

Relational Algebra Tree: Find the captains for all the matches in the tournament.

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: Captains of the top four teams in the semifinals.
Next SQL Exercise: All the captains and goalkeepers for all the teams.

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