w3resource
Soccer Database Exercises

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. Write a query in SQL to find the maximum number of penalty shots taken by the players.

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)

Query Visualization:

Duration:

Query visualization of Find the maximum number of penalty shots taken by the players - Duration

Rows:

Query visualization of Find the maximum number of penalty shots taken by the players - Rows

Cost:

Query visualization of Find the maximum number of penalty shots taken by the players - 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 penalty shots taken by the teams.
Next: Write a query in SQL to find the match no. where highest number of penalty shots taken.

What is the difficulty level of this exercise?



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