w3resource
Soccer Database Exercises

SQL exercises on soccer Database: Find the player and his team and how many matches he kept goal for his team

SQL soccer Database: Joins Exercise-24 with Solution

24. Write a query in SQL to find the player and his team and how many matches he kept goal for his team.

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

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 match no. and the teams played in that match where the 2nd highest stoppage time had been added in the 2nd half of play.
Next: Write a query in SQL to find the venue that has seen the most goals.

What is the difficulty level of this exercise?



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