SQL Subqueries exercises on soccer Database: Find the match no. and teams who played the match where highest number of penalty shots had been taken
SQL soccer Database: Subqueries Exercise-28 with Solution
Write a query in SQL to find the match no. and teams who played the match where highest number of penalty shots had been taken.
Sample table: penalty_shootout
SELECT b.match_no, a.country_name FROM penalty_shootout b, soccer_country a WHERE b.team_id=a.country_id AND match_no= (SELECT match_no FROM penalty_shootout GROUP BY match_no HAVING COUNT(*)= (SELECT MAX(shots) FROM (SELECT COUNT(*) shots FROM penalty_shootout GROUP BY match_no) inner_result)) GROUP BY b.match_no, a.country_name;
match_no | country_name ----------+-------------- 47 | Germany 47 | Italy (2 rows)
SQL Code Editor:
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?