w3resource
Soccer Database Exercises

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. Write a query in SQL to find the referees of each country managed 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)

Practice Online


Sample Database: soccer

soccer database relationship structure

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query in SQL to list the name of referees with their countries for each match.
Next: Write a query in SQL to find the countries from where the referees managed most of the matches.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming