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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
What is the best way to paginate results in SQL Server?
SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, * FROM Orders WHERE OrderDate >= '1980-01-01' ) AS RowConstrainedResult WHERE RowNum >= 1 AND RowNum < 20 ORDER BY RowNum
Database: SQL Server
Ref: https://bit.ly/3MGrNlk
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook