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 Tree:
Practice Online
Sample Database: soccer
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.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/sql-exercises/soccer-database-exercise/sql-joins-exercise-soccer-database-46.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics