w3resource

SQL Exercise: Find the captain who was also the goalkeeper

SQL soccer Database: Joins Exercise-39 with Solution

39. From the following tables, write a SQL query to find the captain who was also the goalkeeper. Return match number, country name, player name and jersey number.

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)

Code Explanation:

The given query in SQL that retrieves the match number, country name, player name, and jersey number from the match_captain table aliased as "a", soccer_country table aliased as "b", and player_mast table aliased as "c" respectively. The query filters the results to only include players who play as goalkeeper (GK), and orders the results by match number.
The JOIN clause joins the match_captain table and the soccer_country table based on the team_id and country_id columns, and then join the result with the player_mast table based on the player_captain and player_id columns.
The WHERE clause filters the results to only include rows where the posi_to_play column is equal to 'GK', indicating that the player plays as a goalkeeper.
The ORDER BY statement sorts the results by match_no in ascending order.

Relational Algebra Expression:

Relational Algebra Expression: Find the captain who was also the goalkeeper.

Relational Algebra Tree:

Relational Algebra Tree: Find the captain who was also the goalkeeper.

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 the team that took penalty shot number 26.
Next SQL Exercise: Find the number of captains who were also goalkeepers.

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