SQL Exercise: Find the number of matches each referee managed
SQL soccer Database: Joins Exercise-53 with Solution
53. From the following tables, write a SQL query to find those referees who managed the number of matches at each venue. Return referee name, country name, venue name, number of matches.
Sample table: match_mast
Sample table: referee_mast
Sample table: soccer_country
Sample table: soccer_venue
Sample Solution:
SQL Code:
SELECT c.referee_name,
b.country_name,
d.venue_name,
count(a.match_no)
FROM match_mast a
JOIN referee_mast c ON a.referee_id=c.referee_id
JOIN soccer_country b ON c.country_id=b.country_id
JOIN soccer_venue d ON a.venue_id=d.venue_id
GROUP BY c.referee_name,
country_name,
venue_name
ORDER BY referee_name;
Sample Output:
referee_name | country_name | venue_name | count -------------------------+----------------+-------------------------+------- Bjorn Kuipers | Netherlands | Stade de Bordeaux | 1 Bjorn Kuipers | Netherlands | Stade de France | 2 Carlos Velasco Carballo | Spain | Stade Bollaert-Delelis | 2 Carlos Velasco Carballo | Spain | Stade Geoffroy Guichard | 1 Clement Turpin | France | Parc des Princes | 1 Clement Turpin | France | Stade de Bordeaux | 1 Cuneyt Cakir | Turkey | Stade de Bordeaux | 1 Cuneyt Cakir | Turkey | Stade de France | 1 Cuneyt Cakir | Turkey | Stade Geoffroy Guichard | 1 Damir Skomina | Slovenia | Stade de Nice | 1 Damir Skomina | Slovenia | Stade Pierre Mauroy | 3 Felix Brych | Germany | Stade Bollaert-Delelis | 1 Felix Brych | Germany | Stade de Nice | 1 Felix Brych | Germany | Stade VElodrome | 1 Jonas Eriksson | Sweden | Parc des Princes | 1 Jonas Eriksson | Sweden | Stade de Lyon | 1 Jonas Eriksson | Sweden | Stadium de Toulouse | 1 Mark Clattenburg | England | Stade de France | 1 Mark Clattenburg | England | Stade de Lyon | 1 Mark Clattenburg | England | Stade Geoffroy Guichard | 2 Martin Atkinson | England | Parc des Princes | 1 Martin Atkinson | England | Stade de Lyon | 1 Martin Atkinson | England | Stade Pierre Mauroy | 1 Milorad Mazic | Serbia | Stade de France | 1 Milorad Mazic | Serbia | Stade de Nice | 1 Milorad Mazic | Serbia | Stadium de Toulouse | 1 Nicola Rizzoli | Italy | Parc des Princes | 1 Nicola Rizzoli | Italy | Stade de Lyon | 1 Nicola Rizzoli | Italy | Stade VElodrome | 2 Ovidiu Hategan | Romania | Stade de Nice | 1 Ovidiu Hategan | Romania | Stade Pierre Mauroy | 1 Pavel Kralovec | Czech Republic | Stade de Lyon | 2 Sergei Karasev | Russia | Parc des Princes | 1 Sergei Karasev | Russia | Stade VElodrome | 1 Svein Oddvar Moen | Norway | Stade de Bordeaux | 1 Svein Oddvar Moen | Norway | Stade VElodrome | 1 Szymon Marciniak | Poland | Stade de France | 1 Szymon Marciniak | Poland | Stade Pierre Mauroy | 1 Szymon Marciniak | Poland | Stadium de Toulouse | 1 Viktor Kassai | Hungary | Stade de Bordeaux | 1 Viktor Kassai | Hungary | Stade de France | 1 Viktor Kassai | Hungary | Stadium de Toulouse | 1 William Collum | Scotland | Stade Bollaert-Delelis | 1 William Collum | Scotland | Stade VElodrome | 1 (44 rows)
Code Explanation:
The said query in SQL that retrieves data from multiple tables and returns the number of matches each referee has officiated in each venue and country.
The JOIN clause joins the match_mast and referee_mast tables based on the referee_id column, the referee_mast and soccer_country tables based on the country_id column and the match_mast and soccer_venue tables based on the venue_id column.
The GROUP BY statement groups the results by referee_name, country_name, and venue_name.
The ORDER BY statement sorts the results in ascending order by referee_name.
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: Find those referees who managed most of the match.
Next SQL Exercise: Find the referees and number of booked he made.
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