SQL Subqueries exercises on soccer Database: Find the maximum number of penalty shots taken by the players

SQL soccer Database: Subqueries Exercise-26 with Solution

26. From the following tables, write a SQL query to find the maximum number of penalty shots taken by the players. Return country name, player name, jersey number and number of penalty shots.

Sample table: player_mast

Sample table: penalty_shootout

Sample table: soccer_country

Sample Solution:

SQL Code:

SELECT c.country_name,a.player_name, a.jersey_no,COUNT(b.*) shots 
FROM player_mast a, penalty_shootout b, soccer_country c
WHERE b.player_id=a.player_id
AND b.team_id=c.country_id
GROUP BY c.country_name,a.player_name,a.jersey_no
HAVING COUNT(b.*)=(
SELECT MAX(shots) FROM (
SELECT COUNT(*) shots 
FROM penalty_shootout
GROUP BY player_id) inner_result);

Sample Output:

 country_name |      player_name      | jersey_no | shots
--------------+-----------------------+-----------+-------
 Poland       | Jakub Blaszczykowski  |        16 |     2
 Poland       | Arkadiusz Milik       |         7 |     2
 Poland       | Robert Lewandowski    |         9 |     2
 Poland       | Kamil Glik            |        15 |     2
(4 rows)

Practice Online

Sample Database: soccer

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following table, write a SQL query to find the maximum penalty shots taken by the teams. Return country name, maximum penalty shots.
Next: From the following table, write a SQL query to find those match where the highest number of penalty shots taken.