w3resource
Soccer Database Exercises

SQL Joins exercises on soccer Database: Find the referees who booked most number of players

SQL soccer Database: Joins Exercise-55 with Solution

55. Write a query in SQL to find the referees who booked most number of players.

Sample table: player_booked


Sample table: match_mast


Sample table: referee_mast


Sample Solution:

SQL Code:

SELECT c.referee_name,
       count(b.match_no)
FROM player_booked a
JOIN match_mast b ON a.match_no=b.match_no
JOIN referee_mast c ON b.referee_id=c.referee_id
GROUP BY referee_name
HAVING count(b.match_no)=
  (SELECT max(mm)
   FROM
     (SELECT count(b.match_no) mm
      FROM player_booked a
      JOIN match_mast b ON a.match_no=b.match_no
      JOIN referee_mast c ON b.referee_id=c.referee_id
      GROUP BY referee_name) hh);

Sample Output:

   referee_name   | count
------------------+-------
 Mark Clattenburg |    21
(1 row)

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 and number of booked he made.
Next: Write a query in SQL to find the player of each team who wear jersey number 10.

What is the difficulty level of this exercise?



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