w3resource

SQL Exercise: How many games the goalkeeper played 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 by 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)

Code Explanation:

The said query in SQL that retrieves the country name, player name, and count_gk columns from the match_details, soccer_country, and player_mast tables.
The JOIN clause joins the table match_details on team_id with soccer_country, and the table player_mast on player_gk with player_id column.
The query groups the data by country name and player name, and orders the data by country name, player name, and count_gk in descending order.

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: 2nd highest stoppage time in the 2nd half of matches.
Next SQL Exercise: Find the venue that has seen the most goals.

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