SQL Exercise: Find the number of captains who were also goalkeepers
SQL soccer Database: Joins Exercise-40 with Solution
40. From the following tables, write a SQL query to find the number of captains who was also the goalkeeper. Return number of captains.
Sample table: match_captain
Sample table: player_mast
Sample table: soccer_country
Sample Solution:
SQL Code:
SELECT count(DISTINCT player_name)
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';
Sample Output:
count ------- 4 (1 row)
Code Explanation:
The said query in SQL that retrieves the number of distinct player names who have played as goalkeepers and acted as captains in matches for their respective countries.
The JOIN clause joins the tables match_captain and the soccer_country based on the columns team_id and country_id and join the player_mast with the result set based on the player_captain column of match_captain table and player_id column of player_mast table.
The WHERE clause filters the results to only include players who have played in the goalkeeper position.
Relational Algebra Expression:

Relational Algebra Tree:

Practice Online
Sample Database: soccer

Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Find the captain who was also the goalkeeper.
Next SQL Exercise: Players with their teams booked in the tournament.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
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
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
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