SQL Joins exercises on soccer Database: Find the referees of each country managed number of matches

SQL soccer Database: Joins Exercise-49 with Solution

49. From the following tables, write a SQL query to count the number of matches managed by referees of each country. Return country name, number of matches.

Sample table: match_mast

Sample table: referee_mast

Sample table: soccer_country

Sample Solution:

SQL Code:

SELECT country_name,
       count(match_no)
FROM match_mast a
JOIN referee_mast c ON a.referee_id=c.referee_id
JOIN soccer_country b ON c.country_id=b.country_id
GROUP BY country_name
ORDER BY count(match_no) DESC;

Sample Output:

  country_name  | count
----------------+-------
 England        |     7
 Italy          |     4
 Slovenia       |     4
 Spain          |     3
 Serbia         |     3
 Netherlands    |     3
 Turkey         |     3
 Germany        |     3
 Poland         |     3
 Sweden         |     3
 Hungary        |     3
 France         |     2
 Czech Republic |     2
 Norway         |     2
 Russia         |     2
 Romania        |     2
 Scotland       |     2
(17 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 name of referees for each match. Sort the result-set on match number. Return match number, country name, referee name.
Next: From the following tables, write a SQL query to find the countries from where the referees managed most of the matches. Return country name, number of matches.