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
How to drop all tables from a database with one SQL query?
USE Databasename SELECT 'DROP TABLE [' + name + '];' FROM sys.tables
Ref: https://bit.ly/3PIUmPL
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- 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
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