SQL Exercise: Find the venue that has seen the most goals
SQL soccer Database: Joins Exercise-25 with Solution
25. From the following tables, write a SQL query to find the venue where the most goals have been scored. Return venue name, number of goals.
Sample table: goal_details
Sample table: soccer_country
Sample table: match_mast
Sample table: soccer_venue
Sample Solution:
SQL Code:
SELECT venue_name, count(venue_name)
FROM goal_details
JOIN soccer_country
ON goal_details.team_id=soccer_country.country_id
JOIN match_mast ON goal_details.match_no=match_mast.match_no
JOIN soccer_venue ON match_mast.venue_id=soccer_venue.venue_id
GROUP BY venue_name
HAVING COUNT (venue_name)=(
SELECT MAX(mycount)
FROM (
SELECT venue_name, COUNT(venue_name) mycount
FROM goal_details
JOIN soccer_country
ON goal_details.team_id=soccer_country.country_id
JOIN match_mast ON goal_details.match_no=match_mast.match_no
JOIN soccer_venue ON match_mast.venue_id=soccer_venue.venue_id
GROUP BY venue_name) gd);
Sample Output:
venue_name | count -----------------+------- Stade de France | 18 (1 row)
Code Explanation:
The said query in SQL that retrieves the name of the venue(s) where the maximum number of goals have been scored in a football tournament.
The JOIN statements are used to join the goal_details and soccer_country tables based on the team_id and country_id column, the goal_details and match_mast tables based on the match_no column, and the match_mast and soccer_venue tables based on the venue_id column.
The GROUP BY clause groups the results by venue_name, which means that the count of goals scored in each venue will be aggregated.
The HAVING clause is used to filter the results based on the maximum number of goals scored in a venue. Only the venues that have the maximum count of goals scored will be selected.
Practice Online
Sample Database: soccer

Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: How many games the goalkeeper played for his team?
Next SQL Exercise: Oldest player to have played in a EURO cup 2016 match.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
What is the best way to paginate results in SQL Server?
SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, * FROM Orders WHERE OrderDate >= '1980-01-01' ) AS RowConstrainedResult WHERE RowNum >= 1 AND RowNum < 20 ORDER BY RowNum
Database: SQL Server
Ref: https://bit.ly/3MGrNlk
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook