w3resource

SQL exercises on soccer Database: Find the club which supplied the most number of players to the 2016 EURO cup

SQL soccer Database: Subqueries Exercise-13 with Solution

13. From the following table, write a SQL query to find the club, which supplied the most number of players to the 2016-EURO cup. Return club name, number of players.

Sample table: player_mast


Sample Solution:

SQL Code:

SELECT playing_club, COUNT(playing_club) 
FROM player_mast  GROUP BY playing_club 
HAVING COUNT (playing_club)=( 
SELECT MAX(mycount) 
FROM ( 
SELECT playing_club, COUNT(playing_club) mycount 
FROM player_mast 
GROUP BY playing_club) pm);

Sample Output:

 playing_club | count
--------------+-------
 Juventus     |    12
 Liverpool    |    12
(2 rows)

Practice Online


Sample Database: soccer

soccer database relationship structure

Query Visualization:

Duration:

Query visualization of Find the club which supplied the most number of players to the 2016 EURO cup - Duration

Rows:

Query visualization of Find the club which supplied the most number of players to the 2016 EURO cup - Rows

Cost:

Query visualization of Find the club which supplied the most number of players to the 2016 EURO cup - 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 team, which was defeated by Portugal in EURO cup 2016 final. Return the country name of the team.
Next: From the following tables, write a SQL query to find the player who scored the first penalty of the tournament. Return player name and Jersey number.

What is the difficulty level of this exercise?