SQL Exercise: Number of matches played at each venue and their city

SQL soccer Database: Joins Exercise-14 with Solution

14. From the following tables, write a SQL query to count the number of matches played at each venue. Sort the result-set on venue name. Return Venue name, city, and number of matches.

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)

Code Explanation:

The said query in SQL that selects the venue name, city, and count of matches played in each venue and city combination, by joining tables soccer_venue, soccer_city, and match_mast on the venue_id and city_id fields.
The result is grouped by venue_name and city, and ordered by venue_name in ascending order.

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 SQL Exercise: Stadium hosted the final match of EURO cup 2016.
Next SQL Exercise: Which player was the first to be sent off at Euro 2016.