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

Sample table: asst_referee_mast

Sample table: soccer_country

Sample Solution:

SQL Code:

SELECT country_name,
       count(DISTINCT match_no)
FROM match_details a
JOIN asst_referee_mast c ON a.ass_ref=c.ass_ref_id
JOIN soccer_country b ON c.country_id=b.country_id
GROUP BY country_name
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.

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.