w3resource
Soccer Database Exercises

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

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 assistant referees with their countries for each matches.
Next: Write a query in SQL to find the countries from where the assistant referees assist most of the matches.

What is the difficulty level of this exercise?



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