SQL Exercise: Find the highest individual scorer in EURO cup 2016
4. From the following table, write a SQL query to find out who scored the most goals in the 2016 Euro Cup. Return player name, country name and highest individual scorer.
Sample table: goal_details
goal_id | match_no | player_id | team_id | goal_time | goal_type | play_stage | goal_schedule | goal_half
---------+----------+-----------+---------+-----------+-----------+------------+---------------+-----------
1 | 1 | 160159 | 1207 | 57 | N | G | NT | 2
2 | 1 | 160368 | 1216 | 65 | P | G | NT | 2
3 | 1 | 160154 | 1207 | 89 | N | G | NT | 2
4 | 2 | 160470 | 1221 | 5 | N | G | NT | 1
5 | 3 | 160547 | 1224 | 10 | N | G | NT | 1
6 | 3 | 160403 | 1218 | 61 | N | G | NT | 2
7 | 3 | 160550 | 1224 | 81 | N | G | NT | 2
8 | 4 | 160128 | 1206 | 73 | N | G | NT | 2
9 | 4 | 160373 | 1217 | 93 | N | G | ST | 2
.............
108 | 51 | 160319 | 1214 | 109 | N | F | ET | 2
Sample table: player_mast
player_id | team_id | jersey_no | player_name | posi_to_play | dt_of_bir | age | playing_club
-----------+---------+-----------+-------------------------+--------------+------------+-----+---------------------
160001 | 1201 | 1 | Etrit Berisha | GK | 1989-03-10 | 27 | Lazio
160008 | 1201 | 2 | Andi Lila | DF | 1986-02-12 | 30 | Giannina
160016 | 1201 | 3 | Ermir Lenjani | MF | 1989-08-05 | 26 | Nantes
160007 | 1201 | 4 | Elseid Hysaj | DF | 1994-02-20 | 22 | Napoli
160013 | 1201 | 5 | Lorik Cana | MF | 1983-07-27 | 32 | Nantes
160010 | 1201 | 6 | Frederic Veseli | DF | 1992-11-20 | 23 | Lugano
160004 | 1201 | 7 | Ansi Agolli | DF | 1982-10-11 | 33 | Qarabag
160012 | 1201 | 8 | Migjen Basha | MF | 1987-01-05 | 29 | Como
160017 | 1201 | 9 | Ledian Memushaj | MF | 1986-12-17 | 29 | Pescara
......
160548 | 1224 | 23 | Simon Church | FD | 1988-12-10 | 27 | MK Dons
Sample table: soccer_country
country_id | country_abbr | country_name
------------+--------------+---------------------
1201 | ALB | Albania
1202 | AUT | Austria
1203 | BEL | Belgium
1204 | CRO | Croatia
1205 | CZE | Czech Republic
1206 | ENG | England
1207 | FRA | France
1208 | GER | Germany
1209 | HUN | Hungary
........
1229 | NOR | Norway
Sample Solution:
SQL Code:
-- Selecting player_name, country_name, and the count of occurrences
SELECT player_name, country_name, count(player_name)
-- Joining goal_details with player_mast using player_id
FROM goal_details gd
JOIN player_mast pm ON gd.player_id = pm.player_id
-- Joining the result with soccer_country using team_id and country_id
JOIN soccer_country sc ON pm.team_id = sc.country_id
-- Grouping the results by country_name and player_name
GROUP BY country_name, player_name
-- Filtering the results to include only those with count(player_name) greater than or equal to the maximum count
HAVING COUNT(player_name) >= ALL
(
-- Subquery to find the maximum count of occurrences for each player and country
SELECT COUNT(player_name)
FROM goal_details gd
JOIN player_mast pm ON gd.player_id = pm.player_id
JOIN soccer_country sc ON pm.team_id = sc.country_id
-- Grouping the results by country_name and player_name
GROUP BY country_name, player_name
);
Sample Output:
player_name | country_name | count
--------------------+--------------+-------
Antoine Griezmann | France | 6
(1 row)
Code Explanation:
The said query in SQL that selects the player name, country name, and the count of goals scored by each player, but only for players who have scored the most goals in their respective countries.
The subquery uses to determine the maximum number of goals scored by any player in each country. The outer query then filters the results to include only the players who have scored at least as many goals as the maximum for their country.
The JOIN clause joins the goal_details, player_mast, and soccer_country tables using their respective IDs.
The results set then grouped by the country name and player name.
Alternative Solutions:
Using a Window Function:
-- Selecting player_name, country_name, and player_count
SELECT player_name, country_name, player_count
-- Subquery to calculate the count of occurrences for each player and country, and find the maximum player_count
FROM (
-- Selecting player_name, country_name, the count of occurrences, and the maximum count of occurrences over all rows
SELECT pm.player_name, sc.country_name, COUNT(pm.player_name) as player_count,
MAX(COUNT(pm.player_name)) OVER () as max_player_count
-- Joining goal_details with player_mast using player_id
FROM goal_details gd
JOIN player_mast pm ON gd.player_id = pm.player_id
-- Joining the result with soccer_country using team_id and country_id
JOIN soccer_country sc ON pm.team_id = sc.country_id
-- Grouping the results by country_name and player_name
GROUP BY sc.country_name, pm.player_name
) as inner_result
-- Filtering the results to include only those with player_count equal to the maximum player_count
WHERE player_count = max_player_count;
Explanation:
This query uses a window function to calculate the maximum player count across all countries. It then filters the results to include only those rows where the player count matches the maximum player count.
Using a Derived Table:
-- Selecting player_name, country_name, and the count of occurrences
SELECT pm.player_name, sc.country_name, COUNT(pm.player_name)
-- Joining goal_details with player_mast using player_id
FROM goal_details gd
JOIN player_mast pm ON gd.player_id = pm.player_id
-- Joining the result with soccer_country using team_id and country_id
JOIN soccer_country sc ON pm.team_id = sc.country_id
-- Grouping the results by country_name and player_name
GROUP BY sc.country_name, pm.player_name
-- Filtering the results to include only those with player_count equal to the maximum player_count
HAVING COUNT(pm.player_name) = (
-- Subquery to find the maximum player_count
SELECT MAX(player_count)
FROM (
-- Subquery to calculate the count of occurrences for each player and country
SELECT COUNT(pm.player_name) as player_count
FROM goal_details gd
JOIN player_mast pm ON gd.player_id = pm.player_id
JOIN soccer_country sc ON pm.team_id = sc.country_id
-- Grouping the results by country_name and player_name
GROUP BY sc.country_name, pm.player_name
) as player_counts
);
Explanation:
This query uses a derived table to calculate the player counts for each country and player combination. It then selects the maximum player count from the derived table and filters the results to include only rows where the player count matches the maximum.
Using a Self-Join:
-- Selecting player_name, country_name, and the count of occurrences
SELECT pm.player_name, sc.country_name, COUNT(pm.player_name)
-- Joining goal_details with player_mast using player_id
FROM goal_details gd
JOIN player_mast pm ON gd.player_id = pm.player_id
-- Joining the result with soccer_country using team_id and country_id
JOIN soccer_country sc ON pm.team_id = sc.country_id
-- Grouping the results by country_name and player_name
GROUP BY sc.country_name, pm.player_name
-- Filtering the results to include only those with player_count greater than or equal to the maximum player_count
HAVING COUNT(pm.player_name) >= (
-- Subquery to find the maximum player_count
SELECT COUNT(pm.player_name)
FROM goal_details gd
JOIN player_mast pm ON gd.player_id = pm.player_id
JOIN soccer_country sc ON pm.team_id = sc.country_id
-- Grouping the results by country_name and player_name
GROUP BY sc.country_name, pm.player_name
-- Ordering the results by player_count in descending order
ORDER BY COUNT(pm.player_name) DESC
-- Limiting the result to 1 row to get the maximum player_count
LIMIT 1
);
Explanation:
This query uses a self-join in the HAVING clause to compare the count of player names for each country and player combination with the maximum count obtained through a subquery. It groups the results by country name and player name.
Go to:
PREV : Goals scored by each player during normal play.
NEXT : Find the scorer of only goal along with his country.
Practice Online
Sample Database: soccer
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.