SQL Joins exercises on soccer Database: 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 number of booked 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)

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: From the following tables, write a SQL query to find those referees who managed the number of matches in each venue. Return referee name, country name, venue name, number of matches.
Next: From the following tables, write a SQL query to find those referees who booked most number of players. Return referee name, number of matches.