SQL Exercise: Find the referees and number of booked he made

SQL soccer Database: Joins Exercise-54 with Solution

54. From the following tables, write a SQL query to find the referees and the number of bookings they made. Return referee name, number of matches.

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)

Code Explanation:

The given query in SQL that retrieves data from player_booked table aliased as a, match_mast table aliased as b, and referee_mast table aliased as c and returns the number of bookings received by each referee.
The JOIN keyword joins the player_booked and match_mast tables based on the match_no column, the match_mast and the referee_mast tables based on the referee_id column.
The GROUP BY statement groups the results by referee_name.
The ORDER BY statement sorts the results in descending order by the number of bookings received by each referee.

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Sample Database: soccer

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Find the number of matches each referee managed.
Next SQL Exercise: Find the referees who booked most number of players.