SQL exercises on soccer Database: Find the player and his team and how many matches he played as a goalkeeper for his team

SQL soccer Database: Joins Exercise-24 with Solution

24. From the following tables, write a SQL query to find the number of matches played a player as a goalkeeper for his team. Return country name, player name, number of matches played as a goalkeeper.

Sample table: player_mast

Sample table: match_details

Sample table: soccer_country

Sample Solution:

SQL Code:

SELECT b.country_name,c.player_name,COUNT(a.player_gk) count_gk
FROM match_details a
JOIN soccer_country b ON a.team_id=b.country_id
JOIN player_mast c ON a.player_gk=c.player_id
GROUP BY b.country_name,c.player_name
ORDER BY country_name,player_name,count_gk DESC;

Sample Output:

    country_name     |     player_name     | count_gk
---------------------+---------------------+----------
 Albania             | Etrit Berisha       |        3
 Austria             | Robert Almer        |        3
 Belgium             | Thibaut Courtois    |        5
 Croatia             | Danijel SubaSic     |        4
 Czech Republic      | Petr Cech           |        3
 England             | Joe Hart            |        4
 France              | Hugo Lloris         |        7
 Germany             | Manuel Neuer        |        6
 Hungary             | Gabor Kiraly        |        4
 Iceland             | Hannes Halldorsson  |        5
 Italy               | Gianluigi Buffon    |        4
 Italy               | Salvatore Sirigu    |        1
 Northern Ireland    | Michael McGovern    |        4
 Poland              | Lukasz Fabianski    |        4
 Poland              | Wojciech Szczesny   |        1
 Portugal            | Rui Patricio        |        7
 Republic of Ireland | Darren Randolph     |        4
 Romania             | Ciprian Tatarusanu  |        3
 Russia              | Igor Akinfeev       |        3
 Slovakia            | MatusKozacik        |        4
 Spain               | David de Gea        |        4
 Sweden              | Andreas Isaksson    |        3
 Switzerland         | Yann Sommer         |        4
 Turkey              | Volkan Babacan      |        3
 Ukraine             | Andriy Pyatov       |        3
 Wales               | Danny Ward          |        1
 Wales               | Wayne Hennessey     |        5
(27 rows)

Practice Online

Sample Database: soccer

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following tables, write a SQL query to find those match(s) where the 2nd highest stoppage time had been added in the second half of play. Return match number, country name and stoppage time.
Next: From the following tables, write a SQL query to find the venue that has seen the most number of goals. Return venue name, number of goals.