w3resource

SQL Exercise: Assistant referees of each countries assists matches

SQL soccer Database: Joins Exercise-46 with Solution

46. From the following table, write a SQL query to find the assistant referees of each country assist the number of matches. Sort the result-set in descending order on number of matches. Return country name, number of matches.

Sample table: match_details
 match_no | play_stage | team_id | win_lose | decided_by | goal_score | penalty_score | ass_ref | player_gk
----------+------------+---------+----------+------------+------------+---------------+---------+-----------
        1 | G          |    1207 | W        | N          |          2 |               |   80016 |    160140
        1 | G          |    1216 | L        | N          |          1 |               |   80020 |    160348
        2 | G          |    1201 | L        | N          |          0 |               |   80003 |    160001
        2 | G          |    1221 | W        | N          |          1 |               |   80023 |    160463
        3 | G          |    1224 | W        | N          |          2 |               |   80031 |    160532
        3 | G          |    1218 | L        | N          |          1 |               |   80025 |    160392
        4 | G          |    1206 | D        | N          |          1 |               |   80008 |    160117
        4 | G          |    1217 | D        | N          |          1 |               |   80019 |    160369
        5 | G          |    1222 | L        | N          |          0 |               |   80011 |    160486
        5 | G          |    1204 | W        | N          |          1 |               |   80022 |    160071
        6 | G          |    1213 | W        | N          |          1 |               |   80036 |    160279
        6 | G          |    1212 | L        | N          |          0 |               |   80029 |    160256
        7 | G          |    1208 | W        | N          |          2 |               |   80014 |    160163
        7 | G          |    1223 | L        | N          |          0 |               |   80006 |    160508
        8 | G          |    1219 | W        | N          |          1 |               |   80018 |    160416
        8 | G          |    1205 | L        | N          |          0 |               |   80012 |    160093
        9 | G          |    1215 | D        | N          |          1 |               |   80017 |    160324
        9 | G          |    1220 | D        | N          |          1 |               |   80010 |    160439
       10 | G          |    1203 | L        | N          |          0 |               |   80004 |    160047
       10 | G          |    1211 | W        | N          |          2 |               |   80007 |    160231
       11 | G          |    1202 | L        | N          |          0 |               |   80026 |    160024
       11 | G          |    1209 | W        | N          |          2 |               |   80028 |    160187
       12 | G          |    1214 | D        | N          |          1 |               |   80009 |    160302
       12 | G          |    1210 | D        | N          |          1 |               |   80015 |    160208
       13 | G          |    1217 | L        | N          |          1 |               |   80001 |    160369
       13 | G          |    1218 | W        | N          |          2 |               |   80002 |    160392
       14 | G          |    1216 | D        | N          |          1 |               |   80030 |    160348
       14 | G          |    1221 | D        | N          |          1 |               |   80032 |    160463
       15 | G          |    1207 | W        | N          |          2 |               |   80033 |    160140
       15 | G          |    1201 | L        | N          |          0 |               |   80027 |    160001
       16 | G          |    1206 | W        | N          |          2 |               |   80005 |    160117
       16 | G          |    1224 | L        | N          |          1 |               |   80013 |    160531
       17 | G          |    1223 | L        | N          |          0 |               |   80035 |    160508
       17 | G          |    1212 | W        | N          |          2 |               |   80034 |    160256
       18 | G          |    1208 | D        | N          |          0 |               |   80021 |    160163
       18 | G          |    1213 | D        | N          |          0 |               |   80024 |    160278
       19 | G          |    1211 | W        | N          |          1 |               |   80016 |    160231
       19 | G          |    1220 | L        | N          |          0 |               |   80020 |    160439
       20 | G          |    1205 | D        | N          |          2 |               |   80004 |    160093
       20 | G          |    1204 | D        | N          |          2 |               |   80007 |    160071
       21 | G          |    1219 | W        | N          |          3 |               |   80017 |    160416
       21 | G          |    1222 | L        | N          |          0 |               |   80010 |    160486
       22 | G          |    1203 | W        | N          |          3 |               |   80009 |    160047
       22 | G          |    1215 | L        | N          |          0 |               |   80015 |    160324
       23 | G          |    1210 | D        | N          |          1 |               |   80030 |    160208
       23 | G          |    1209 | D        | N          |          1 |               |   80032 |    160187
       24 | G          |    1214 | D        | N          |          0 |               |   80008 |    160302
       24 | G          |    1202 | D        | N          |          0 |               |   80019 |    160024
       25 | G          |    1216 | L        | N          |          0 |               |   80035 |    160348
       25 | G          |    1201 | W        | N          |          1 |               |   80034 |    160001
       26 | G          |    1221 | D        | N          |          0 |               |   80001 |    160463
       26 | G          |    1207 | D        | N          |          0 |               |   80002 |    160140
       27 | G          |    1217 | L        | N          |          0 |               |   80011 |    160369
       27 | G          |    1224 | W        | N          |          3 |               |   80022 |    160531
       28 | G          |    1218 | D        | N          |          0 |               |   80003 |    160392
       28 | G          |    1206 | D        | N          |          0 |               |   80023 |    160117
       29 | G          |    1223 | L        | N          |          0 |               |   80031 |    160508
       29 | G          |    1213 | W        | N          |          1 |               |   80025 |    160278
       30 | G          |    1212 | L        | N          |          0 |               |   80026 |    160256
       30 | G          |    1208 | W        | N          |          1 |               |   80028 |    160163
       31 | G          |    1205 | L        | N          |          0 |               |   80033 |    160093
       31 | G          |    1222 | W        | N          |          2 |               |   80027 |    160486
       32 | G          |    1204 | W        | N          |          2 |               |   80021 |    160071
       32 | G          |    1219 | L        | N          |          1 |               |   80024 |    160416
       33 | G          |    1210 | W        | N          |          2 |               |   80018 |    160208
       33 | G          |    1202 | L        | N          |          1 |               |   80012 |    160024
       34 | G          |    1209 | D        | N          |          3 |               |   80014 |    160187
       34 | G          |    1214 | D        | N          |          3 |               |   80006 |    160302
       35 | G          |    1211 | L        | N          |          0 |               |   80036 |    160233
       35 | G          |    1215 | W        | N          |          1 |               |   80029 |    160324
       36 | G          |    1220 | L        | N          |          0 |               |   80005 |    160439
       36 | G          |    1203 | W        | N          |          1 |               |   80013 |    160047
       37 | R          |    1221 | L        | P          |          1 |             4 |   80004 |    160463
       37 | R          |    1213 | W        | P          |          1 |             5 |   80007 |    160278
       38 | R          |    1224 | W        | N          |          1 |               |   80014 |    160531
       38 | R          |    1212 | L        | N          |          0 |               |   80006 |    160256
       39 | R          |    1204 | L        | N          |          0 |               |   80003 |    160071
       39 | R          |    1214 | W        | N          |          1 |               |   80023 |    160302
       40 | R          |    1207 | W        | N          |          2 |               |   80008 |    160140
       40 | R          |    1215 | L        | N          |          1 |               |   80019 |    160324
       41 | R          |    1208 | W        | N          |          3 |               |   80018 |    160163
       41 | R          |    1218 | L        | N          |          0 |               |   80012 |    160392
       42 | R          |    1209 | L        | N          |          0 |               |   80017 |    160187
       42 | R          |    1203 | W        | N          |          4 |               |   80010 |    160047
       43 | R          |    1211 | W        | N          |          2 |               |   80009 |    160231
       43 | R          |    1219 | L        | N          |          0 |               |   80015 |    160416
       44 | R          |    1206 | L        | N          |          1 |               |   80001 |    160117
       44 | R          |    1210 | W        | N          |          2 |               |   80002 |    160208
       45 | Q          |    1213 | L        | P          |          1 |             3 |   80005 |    160278
       45 | Q          |    1214 | W        | P          |          1 |             5 |   80013 |    160302
       46 | Q          |    1224 | W        | N          |          3 |               |   80001 |    160531
       46 | Q          |    1203 | L        | N          |          1 |               |   80002 |    160047
       47 | Q          |    1208 | W        | P          |          1 |             6 |   80016 |    160163
       47 | Q          |    1211 | L        | P          |          1 |             5 |   80020 |    160231
       48 | Q          |    1207 | W        | N          |          5 |               |   80021 |    160140
       48 | Q          |    1210 | L        | N          |          2 |               |   80024 |    160208
       49 | S          |    1214 | W        | N          |          2 |               |   80011 |    160302
       49 | S          |    1224 | L        | N          |          0 |               |   80022 |    160531
       50 | S          |    1207 | W        | N          |          2 |               |   80008 |    160140
       50 | S          |    1208 | L        | N          |          1 |               |   80019 |    160163
       51 | F          |    1214 | W        | N          |          1 |               |   80004 |    160302
       51 | F          |    1207 | L        | N          |          0 |               |   80007 |    160140
Sample table: asst_referee_mast
 ass_ref_id |       ass_ref_name       | country_id
------------+--------------------------+------------
      80034 | Tomas Mokrusch           |       1205
      80038 | Martin Wilczek           |       1205
      80004 | Simon Beck               |       1206
      80006 | Stephen Child            |       1206
      80007 | Jake Collin              |       1206
      80014 | Mike Mullarkey           |       1206
      80026 | Frederic Cano            |       1207
      80028 | Nicolas Danos            |       1207
      80005 | Mark Borsch              |       1208
      80013 | Stefan Lupp              |       1208
      80016 | Gyorgy Ring              |       1209
      80020 | Vencel Toth              |       1209
      80033 | Damien McGraith          |       1215
      80008 | Elenito Di Liberatore    |       1211
      80019 | Mauro Tonolini           |       1211
      80021 | Sander van Roekel        |       1226
      80024 | Erwin Zeinstra           |       1226
      80025 | Frank Andas              |       1229
      80031 | Kim Haglund              |       1229
      80012 | Tomasz Listkiewicz       |       1213
      80018 | Pawel Sokolnicki         |       1213
      80029 | Sebastian Gheorghe       |       1216
      80036 | Octavian Sovre           |       1216
      80030 | Nikolay Golubev          |       1217
      80032 | Tikhon Kalugin           |       1217
      80037 | Anton Averyanov          |       1217
      80027 | Frank Connor             |       1228
      80010 | Dalibor Durdevic         |       1227
      80017 | Milovan Ristic           |       1227
      80035 | Roman Slysko             |       1218
      80001 | Jure Praprotnik          |       1225
      80002 | Robert Vukan             |       1225
      80003 | Roberto Alonso Fernandez |       1219
      80023 | Juan Yuste Jimenez       |       1219
      80011 | Mathias Klasenius        |       1220
      80022 | Daniel Warnmark          |       1220
      80009 | Bahattin Duran           |       1222
      80015 | Tarik Ongun              |       1222
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
       1210 | ISL          | Iceland
       1211 | ITA          | Italy
       1212 | NIR          | Northern Ireland
       1213 | POL          | Poland
       1214 | POR          | Portugal
       1215 | IRL          | Republic of Ireland
       1216 | ROU          | Romania
       1217 | RUS          | Russia
       1218 | SVK          | Slovakia
       1219 | ESP          | Spain
       1220 | SWE          | Sweden
       1221 | SUI          | Switzerland
       1222 | TUR          | Turkey
       1223 | UKR          | Ukraine
       1224 | WAL          | Wales
       1225 | SLO          | Slovenia
       1226 | NED          | Netherlands
       1227 | SRB          | Serbia
       1228 | SCO          | Scotland
       1229 | NOR          | Norway

Sample Solution:

SQL Code:

-- Selecting the country name and the count of distinct match numbers for each country where an assistant referee is involved
SELECT 
    country_name, -- Selecting the country name from the soccer_country table
    COUNT(DISTINCT match_no) -- Counting the distinct match numbers for each country
FROM 
    match_details a -- Specifying the match_details table with alias 'a'
JOIN 
    asst_referee_mast c ON a.ass_ref = c.ass_ref_id -- Joining the asst_referee_mast table with match_details based on assistant referee ID
JOIN 
    soccer_country b ON c.country_id = b.country_id -- Joining the soccer_country table based on country ID
GROUP BY 
    country_name -- Grouping the results by country name
	-- Ordering the results by the count of match numbers in descending order
ORDER BY 
    COUNT(*) DESC; 

Sample Output:

    country_name     | count
---------------------+-------
 England             |     7
 Slovenia            |     4
 Italy               |     4
 Turkey              |     3
 Hungary             |     3
 Netherlands         |     3
 Poland              |     3
 Germany             |     3
 Spain               |     3
 Sweden              |     3
 Serbia              |     3
 Russia              |     2
 France              |     2
 Norway              |     2
 Romania             |     2
 Republic of Ireland |     2
 Czech Republic      |     2
 Scotland            |     2
 Slovakia            |     2
(19 rows)

Code Explanation:

The said query in SQL that returns the number of distinct match_no values for each country in the soccer_country table.
This query is identifies which countries have the most soccer matches with the specified assistant referees.
The JOIN clause joins the match_details, asst_referee_mast, and soccer_country tables using their respective keys (ass_ref in match_details, ass_ref_id in asst_referee_mast, and country_id in soccer_country) to match the data between them.
This orders the results by the count of match_no values in descending order.

Alternative Solutions:

Cross Join with Subquery and HAVING:

-- Selecting the country name and the count of distinct match numbers for each country where an assistant referee is involved
SELECT 
  b.country_name, -- Selecting the country name from the soccer_country table
  COUNT(DISTINCT a.match_no) AS Match_Count -- Counting the distinct match numbers for each country
FROM 
  soccer_country b -- Specifying the soccer_country table with alias 'b'
CROSS JOIN 
  asst_referee_mast c -- Performing a cross join with the asst_referee_mast table to get all combinations of country and assistant referee
LEFT JOIN 
  match_details a ON a.ass_ref = c.ass_ref_id AND c.country_id = b.country_id -- Left joining the match_details table based on assistant referee ID and country ID
GROUP BY 
  b.country_name -- Grouping the results by country name
HAVING 
  COUNT(DISTINCT a.match_no) > 0 -- Filtering out countries with no matches involving an assistant referee
  -- Ordering the results by the count of match numbers in descending order
ORDER BY 
  Match_Count DESC; 

Explanation:

This query uses a cross join to generate all possible combinations of country_name and ass_ref. It then left joins with match_details to count the distinct matches. The HAVING clause is used to eliminate countries with zero matches.

Correlated Subquery with HAVING:

-- Selecting the country name and the count of distinct match numbers for each country where an assistant referee is involved
SELECT 
  sub.country_name, -- Selecting the country name from the subquery results
  sub.Match_Count -- Selecting the count of distinct match numbers from the subquery results
FROM (
    -- Subquery to calculate the count of distinct match numbers for each country
    SELECT 
      b.country_name, -- Selecting the country name from the soccer_country table
      (
        -- Subquery to count the distinct match numbers where an assistant referee from the current country is involved
        SELECT COUNT(DISTINCT a.match_no)
        FROM match_details a
        JOIN asst_referee_mast c ON a.ass_ref = c.ass_ref_id
        WHERE c.country_id = b.country_id -- Filtering matches based on the current country ID
      ) AS Match_Count -- Assigning the count of distinct match numbers to the alias 'Match_Count'
    FROM 
      soccer_country b -- Specifying the soccer_country table with alias 'b'
) sub -- Assigning the subquery results to the alias 'sub'
WHERE sub.Match_Count > 0 -- Filtering out countries with no matches involving an assistant referee
-- Ordering the results by the count of match numbers in descending order
ORDER BY sub.Match_Count DESC; 

Explanation:

This query uses a correlated subquery to count the distinct match_no for each country. The subquery is correlated to the outer query by the country_id and counts the matches accordingly. The HAVING clause is then used to eliminate countries with zero matches.

Relational Algebra Expression:

Relational Algebra Expression: Find the assistant referees of each countries assists the number of matches.

Relational Algebra Tree:

Relational Algebra Tree: Find the assistant referees of each countries assists the number of matches.

Practice Online


Sample Database: soccer

soccer database relationship structure

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Each match assistant referee and their country.
Next SQL Exercise: Countries where assistant referees assist most matches.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.