SQL exercises on soccer Database: Find the venue with number of goals that has seen
SQL soccer Database: Joins Exercise-18 with Solution
18. Write a query in SQL to find the venue with number of goals that has seen.
Sample table: soccer_country
Sample table: goal_details
Sample table: match_mast
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;
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)
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?