SQL exercises on soccer Database: Find the highest individual scorer in EURO cup 2016

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

SQL soccer Database: Joins Exercise-4 with Solution

4. From the following tables, write a SQL query to find the highest individual scorer in EURO cup 2016. Return player name, country name and highest individual scorer.

Sample table: goal_details

Sample table: player_mast

Sample table: soccer_country

Sample Solution:

SQL Code:

SELECT player_name,country_name,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
GROUP BY country_name,player_name HAVING COUNT(player_name) >= ALL
  (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
   GROUP BY country_name,player_name);

Sample Output:

     player_name     | country_name | count
--------------------+--------------+-------
 Antoine Griezmann  | France       |     6
(1 row)

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 count the number of goals scored by each player within normal play schedule. Group the result set on player name and country name and sorts the result-set according to the highest to the lowest scorer. Return player name, number of goals and country name.
Next: From the following tables, write a SQL query to find the scorer in the final of EURO cup 2016. Return player name, jersey number and country name.

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