SQL Subqueries exercises on soccer Database: Find the maximum penalty shots taken by the teams
SQL soccer Database: Subqueries Exercise-25 with Solution
25. Write a query in SQL to find the maximum penalty shots taken by the teams.
Sample table: soccer_country
Sample table: penalty_shootout
SELECT a.country_name, COUNT(b.*) shots FROM soccer_country a, penalty_shootout b WHERE b.team_id=a.country_id GROUP BY a.country_name having COUNT(b.*)=( SELECT MAX(shots) FROM ( SELECT COUNT(*) shots FROM penalty_shootout GROUP BY team_id) inner_result);
country_name | shots --------------+------- Poland | 9 Italy | 9 Germany | 9 (3 rows)
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?