w3resource
Soccer Database Exercises

SQL Subqueries exercises on soccer Database: Find the match no. where highest number of penalty shots taken

SQL soccer Database: Subqueries Exercise-27 with Solution

27. Write a query in SQL to find the match no. where highest number of penalty shots taken.

Sample table: penalty_shootout


Sample Solution:

SQL Code:

SELECT match_no,
       COUNT(*) shots
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);

Sample Output:

 match_no | shots
----------+-------
       47 |    18
(1 row)

Query Visualization:

Duration:

Query visualization of Find the match no. where highest number of penalty shots taken - Duration

Rows:

Query visualization of Find the match no. where highest number of penalty shots taken - Rows

Cost:

Query visualization of Find the match no. where highest number of penalty shots taken - Cost

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 maximum number of penalty shots taken by the players.
Next: 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.

What is the difficulty level of this exercise?



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