SQL Subqueries exercises on soccer Database: Find the maximum penalty shots taken by the teams

Last update on May 30 2022 07:30:23 (UTC/GMT +8 hours)

SQL soccer Database: Subqueries Exercise-25 with Solution

25. From the following tables, write a SQL query to find the maximum penalty shots taken by the teams. Return country name, maximum penalty shots.

Sample table: soccer_country

Sample table: penalty_shootout

Sample Solution:

SQL Code:

SELECT a.country_name, COUNT(b.*) shots 
FROM soccer_country a, penalty_shootout b
WHERE b.team_id=a.country_id
GROUP BY a.country_name
having COUNT(b.*)=(
SELECT MAX(shots) FROM (
SELECT COUNT(*) shots 
FROM penalty_shootout
GROUP BY team_id) inner_result);

Sample Output:

 country_name | shots
--------------+-------
 Poland       |     9
 Italy        |     9
 Germany      |     9
(3 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 tables, write a SQL query to find the runners-up in Football EURO cup 2016. Return country name.
Next: 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.

SQL: Tips of the Day

SQL Server SELECT into existing table.

INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key

This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:

INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key

Database: SQL Server

Ref: https://bit.ly/3y6tpA3