w3resource
Soccer Database Exercises

SQL Joins exercises on soccer Database: Find the captain who was also the goalkeeper

SQL soccer Database: Joins Exercise-39 with Solution

39. Write a query in SQL to find the captain who was also the goalkeeper.

Sample table: match_captain


Sample table: soccer_country


Sample table: player_mast


Sample Solution:

SQL Code:

SELECT match_no,
       country_name,
       player_name,
       jersey_no
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
AND posi_to_play='GK'
ORDER BY match_no;

Sample Output:

 match_no |  country_name  |    player_name    | jersey_no
----------+----------------+-------------------+-----------
        1 | France         | Hugo Lloris       |         1
        7 | Germany        | Manuel Neuer      |         1
       10 | Italy          | Gianluigi Buffon  |         1
       15 | France         | Hugo Lloris       |         1
       18 | Germany        | Manuel Neuer      |         1
       19 | Italy          | Gianluigi Buffon  |         1
       26 | France         | Hugo Lloris       |         1
       30 | Germany        | Manuel Neuer      |         1
       31 | Czech Republic | Petr Cech         |         1
       40 | France         | Hugo Lloris       |         1
       41 | Germany        | Manuel Neuer      |         1
       43 | Italy          | Gianluigi Buffon  |         1
       47 | Germany        | Manuel Neuer      |         1
       47 | Italy          | Gianluigi Buffon  |         1
       48 | France         | Hugo Lloris       |         1
       50 | France         | Hugo Lloris       |         1
       51 | France         | Hugo Lloris       |         1
(17 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 team against which the penalty shot number 26 had been taken.
Next: Write a query in SQL to find the number of captains who was also the goalkeeper.

What is the difficulty level of this exercise?



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