w3resource
Soccer Database Exercises

SQL exercises on soccer Database: Compute a report that contain the number of matches played in each venue along with their city

SQL soccer Database: Joins Exercise-14 with Solution

14. Write a query in SQL to compute a report that contain the number of matches played in each venue along with their city.

Sample table: soccer_venue


Sample table: soccer_city


Sample table: match_mast


Sample Solution:

SQL Code:

SELECT a.venue_name, b.city, count(c.match_no)
FROM soccer_venue a
JOIN soccer_city b ON a.city_id=b.city_id
JOIN match_mast c ON a.venue_id=c.venue_id
GROUP BY venue_name,city
ORDER BY venue_name;

Sample Output:

       venue_name        |     city      | count
-------------------------+---------------+-------
 Parc des Princes        | Paris         |     5
 Stade Bollaert-Delelis  | Lens          |     4
 Stade de Bordeaux       | Bordeaux      |     5
 Stade de France         | Saint-Denis   |     7
 Stade de Lyon           | Lyon          |     6
 Stade de Nice           | Nice          |     4
 Stade Geoffroy Guichard | Saint-Etienne |     4
 Stade Pierre Mauroy     | Lille         |     6
 Stade VElodrome         | Marseille     |     6
 Stadium de Toulouse     | Toulouse      |     4
(10 rows)

Practice Online


Sample Database: soccer

soccer database relationship structure

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query in SQL to find the stadium hosted the final match of EURO cup 2016 along with the capacity, and audance for that match.
Next: Write a query in SQL to find the player who was the first player to be sent off at the tournament EURO cup 2016.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming