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

# 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)
```

Duration:

Rows:

Cost:

## Practice Online

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?

﻿

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