SQL Joins exercises on soccer Database: Find the assistant referees of each countries assists the number of matches

SQL soccer Database: Joins Exercise-46 with Solution

46. From the following tables, write a SQL query to find the assistant referees of each country assists 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)

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: From the following table, write a SQL query to find the assistant referees. Return match number, country name, assistant referee name.
Next: From the following table, write a SQL query to find the countries from where the assistant referees assist most of the matches. Return country name and number of matches.