w3resource
Soccer Database Exercises

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

SQL soccer Database: Joins Exercise-51 with Solution

51. Write a query in SQL to find the referees managed the number of matches.

Sample table: match_mast


Sample table: referee_mast


Sample table: soccer_country


Sample Solution:

SQL Code:

SELECT c.referee_name,
       b.country_name,
       count(a.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 c.referee_name,
         b.country_name;

Sample Output:

      referee_name       |  country_name  | count
-------------------------+----------------+-------
 Damir Skomina           | Slovenia       |     4
 Martin Atkinson         | England        |     3
 Clement Turpin          | France         |     2
 Jonas Eriksson          | Sweden         |     3
 Ovidiu Hategan          | Romania        |     2
 Sergei Karasev          | Russia         |     2
 Cuneyt Cakir            | Turkey         |     3
 Felix Brych             | Germany        |     3
 Viktor Kassai           | Hungary        |     3
 Carlos Velasco Carballo | Spain          |     3
 Svein Oddvar Moen       | Norway         |     2
 Milorad Mazic           | Serbia         |     3
 Szymon Marciniak        | Poland         |     3
 Bjorn Kuipers           | Netherlands    |     3
 William Collum          | Scotland       |     2
 Mark Clattenburg        | England        |     4
 Nicola Rizzoli          | Italy          |     4
 Pavel Kralovec          | Czech Republic |     2
(18 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 find the countries from where the referees managed most of the matches.
Next: Write a query in SQL to find those referees who managed most of the match.

What is the difficulty level of this exercise?



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