w3resource
Soccer Database Exercises

SQL Joins exercises on soccer Database: Find those referees who managed most of the match

SQL soccer Database: Joins Exercise-52 with Solution

52. Write a query in SQL to find those referees who managed most of the match.

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
HAVING count(a.match_no) =
  (SELECT max(mm)
   FROM
     (SELECT count(a.match_no) mm
      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) hh);

Sample Output:

   referee_name   | country_name | count
------------------+--------------+-------
 Damir Skomina    | Slovenia     |     4
 Mark Clattenburg | England      |     4
 Nicola Rizzoli   | Italy        |     4
(3 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 referees managed the number of matches.
Next: Write a query in SQL to find the referees managed the number of matches in each venue.

What is the difficulty level of this exercise?



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