SQL Exercise: Assistant referees of each countries assists matches
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
......
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
........
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
......
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:
Go to:
PREV : Each match assistant referee and their country.
NEXT : Countries where assistant referees assist most matches.
Practice Online
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.