w3resource
Soccer Database Exercises

SQL exercises on soccer Database: Find the captains for all the matches in the tournament

SQL soccer Database: Joins Exercise-32 with Solution

32. Write a query in SQL to find the captains with other information for all the matches in the tournament.

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)

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 captains for the top four teams with other information which participated in the semifinals (match 48 and 49) in the tournament.
Next: Write a query in SQL to find the captain and goal keeper with other information for all the matches for all the team.

What is the difficulty level of this exercise?



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