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. From the following tables, write a SQL query to count the number of matches played in 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)

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 find the stadium hosted the final match of EURO cup 2016. Return venue_name, city, aud_capacity, audience.
Next: From the following tables, write a SQL query to find the player who was the first player to be sent off at the tournament EURO cup 2016. Return match Number, country name and player name.