w3resource
Soccer Database Exercises

SQL Joins exercises on soccer Database: Find the referees and number of booked he made

SQL soccer Database: Joins Exercise-54 with Solution

54. Write a query in SQL to find the referees and number of booked he made.

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
ORDER BY count(b.match_no) DESC;

Sample Output:

      referee_name       | count
-------------------------+-------
 Mark Clattenburg        |    21
 Nicola Rizzoli          |    20
 Milorad Mazic           |    13
 Viktor Kassai           |    12
 Sergei Karasev          |    12
 Damir Skomina           |    12
 Bjorn Kuipers           |    12
 Cuneyt Cakir            |    11
 Pavel Kralovec          |    11
 Jonas Eriksson          |    11
 Carlos Velasco Carballo |    10
 Szymon Marciniak        |    10
 Ovidiu Hategan          |     9
 Felix Brych             |     9
 Martin Atkinson         |     9
 William Collum          |     8
 Svein Oddvar Moen       |     8
 Clement Turpin          |     3
(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 referees managed the number of matches in each venue.
Next: Write a query in SQL to find the referees who booked most number of players.

What is the difficulty level of this exercise?



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