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
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;
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)
Sample Database: soccer
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?