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.