SQL exercises on soccer Database: Find the venue with number of goals that has seen

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

SQL soccer Database: Joins Exercise-18 with Solution

18. From the following tables, write a SQL query to count number of goals that has seen. Return venue name and number of goals.

Sample table: soccer_country

Sample table: goal_details

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
ORDER BY COUNT(venue_name) DESC;

Sample Output:

       venue_name        | count
-------------------------+-------
 Stade de France         |    18
 Stade de Lyon           |    16
 Stade Pierre Mauroy     |    13
 Stade de Bordeaux       |    13
 Stade VElodrome         |    11
 Stadium de Toulouse     |     9
 Stade de Nice           |     8
 Stade Geoffroy Guichard |     8
 Stade Bollaert-Delelis  |     7
 Parc des Princes        |     5
(10 rows)

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Sample Database: soccer

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 yellow cards received by each country. Return country name and number of yellow cards.
Next: From the following tables, write a SQL query to find the match where no stoppage time added in first half of play. Return match number, 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