SQL Exercise: Find the referees managed the number of matches

Last update on January 15 2026 12:41:20 (UTC/GMT +8 hours)

51. From the following tables, write a SQL query to find the number of matches managed by each referee. Return referee name, country name, number of matches.

Sample table: match_mast

 match_no | play_stage | play_date  | results | decided_by | goal_score | venue_id | referee_id | audence | plr_of_match | stop1_sec | stop2_sec
----------+------------+------------+---------+------------+------------+----------+------------+---------+--------------+-----------+-----------
        1 | G          | 2016-06-11 | WIN     | N          | 2-1        |    20008 |      70007 |   75113 |       160154 |       131 |       242
        2 | G          | 2016-06-11 | WIN     | N          | 0-1        |    20002 |      70012 |   33805 |       160476 |        61 |       182
        3 | G          | 2016-06-11 | WIN     | N          | 2-1        |    20001 |      70017 |   37831 |       160540 |        64 |       268
        4 | G          | 2016-06-12 | DRAW    | N          | 1-1        |    20005 |      70011 |   62343 |       160128 |         0 |       185
        5 | G          | 2016-06-12 | WIN     | N          | 0-1        |    20007 |      70006 |   43842 |       160084 |       125 |       325
        6 | G          | 2016-06-12 | WIN     | N          | 1-0        |    20006 |      70014 |   33742 |       160291 |         2 |       246
        7 | G          | 2016-06-13 | WIN     | N          | 2-0        |    20003 |      70002 |   43035 |       160176 |        89 |       188
        8 | G          | 2016-06-13 | WIN     | N          | 1-0        |    20010 |      70009 |   29400 |       160429 |       360 |       182
        9 | G          | 2016-06-13 | DRAW    | N          | 1-1        |    20008 |      70010 |   73419 |       160335 |        67 |       194
.........
       51 | F          | 2016-07-11 | WIN     | N          | 1-0        |    20008 |      70005 |   75868 |       160307 |       161 |       181

View the table

Sample table: referee_mast

 referee_id |      referee_name       | country_id
------------+-------------------------+------------
      70001 | Damir Skomina           |       1225
      70002 | Martin Atkinson         |       1206
      70003 | Felix Brych             |       1208
      70004 | Cuneyt Cakir            |       1222
      70005 | Mark Clattenburg        |       1206
      70006 | Jonas Eriksson          |       1220
      70007 | Viktor Kassai           |       1209
      70008 | Bjorn Kuipers           |       1226
      70009 | Szymon Marciniak        |       1213
......
      70018 | Clement Turpin          |       1207

View the table

Sample table: soccer_country

 country_id | country_abbr |    country_name
------------+--------------+---------------------
       1201 | ALB          | Albania
       1202 | AUT          | Austria
       1203 | BEL          | Belgium
       1204 | CRO          | Croatia
       1205 | CZE          | Czech Republic
       1206 | ENG          | England
       1207 | FRA          | France
       1208 | GER          | Germany
       1209 | HUN          | Hungary
.......
       1229 | NOR          | Norway

View the table

Code Explanation:

The given query in SQL that selects the name of referees, the name of countries, and the count of match numbers in the match_mast table for each combination of referee and country.
The JOIN clause in this query then joins the match_mast and referee_mast tables based on the referee_id column and joins the results with soccer_country tables based on the country_id column.
The query groups the results by referee name and country name using the GROUP BY clause and applies an aggregate function, count(), to count the number of match numbers for each combination of referee and country.

Alternative Solution:

Using Subqueries:

-- Selecting the referee name, country name, and the count of matches for each referee in each country
SELECT 
    c.referee_name, -- Selecting the referee name
    b.country_name, -- Selecting the country name
    match_count -- Selecting the count of matches for each referee in each country
FROM 
    referee_mast c -- Specifying the referee_mast table with alias 'c'
JOIN 
    soccer_country b ON c.country_id = b.country_id -- Joining the referee_mast table with the soccer_country table based on country ID
JOIN 
    ( -- Subquery to calculate the count of matches for each referee in each country
        SELECT 
            a.referee_id, -- Selecting the referee ID
            b.country_id, -- Selecting the country ID
            COUNT(a.match_no) as match_count -- Counting the number of matches for each referee in each country
        FROM 
            match_mast a -- Specifying the match_mast table with alias 'a'
        JOIN 
            referee_mast b ON a.referee_id = b.referee_id -- Joining the match_mast table with the referee_mast table based on referee ID
        GROUP BY 
            a.referee_id, -- Grouping the results by referee ID
            b.country_id -- Grouping the results by country ID
			-- Joining the main query with the subquery based on referee ID and country ID
    ) a ON a.referee_id = c.referee_id AND a.country_id = b.country_id;

Explanation:

This query we first perform the join between match_mast and referee_mast on referee_id. Then, in the subquery, we group the results by referee_id and country_id to get the match counts. Finally, we join this subquery with referee_mast and soccer_country to get the referee names and country names.

Relational Algebra Expression: