SQL Exercise: Find the referees and number of booked he made
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
match_no | team_id | player_id | booking_time | sent_off | play_schedule | play_half
----------+---------+-----------+--------------+----------+---------------+-----------
1 | 1216 | 160349 | 32 | | NT | 1
1 | 1216 | 160355 | 45 | | NT | 1
1 | 1207 | 160159 | 69 | Y | NT | 2
1 | 1216 | 160360 | 78 | | NT | 2
2 | 1221 | 160470 | 14 | | NT | 1
2 | 1201 | 160013 | 23 | | NT | 1
2 | 1201 | 160013 | 36 | | NT | 1
2 | 1201 | 160014 | 63 | | NT | 2
2 | 1221 | 160472 | 66 | | NT | 2
.......
51 | 1214 | 160302 | 122 | | ET | 2
Sample table: match_mast
match_no | play_stage | play_date | results | decided_by | goal_score | venue_id | referee_id | audence | plr_of_match | stop1_sec | stop2_sec
----------+------------+------------+---------+------------+------------+----------+------------+---------+--------------+-----------+-----------
1 | G | 2016-06-11 | WIN | N | 2-1 | 20008 | 70007 | 75113 | 160154 | 131 | 242
2 | G | 2016-06-11 | WIN | N | 0-1 | 20002 | 70012 | 33805 | 160476 | 61 | 182
3 | G | 2016-06-11 | WIN | N | 2-1 | 20001 | 70017 | 37831 | 160540 | 64 | 268
4 | G | 2016-06-12 | DRAW | N | 1-1 | 20005 | 70011 | 62343 | 160128 | 0 | 185
5 | G | 2016-06-12 | WIN | N | 0-1 | 20007 | 70006 | 43842 | 160084 | 125 | 325
6 | G | 2016-06-12 | WIN | N | 1-0 | 20006 | 70014 | 33742 | 160291 | 2 | 246
7 | G | 2016-06-13 | WIN | N | 2-0 | 20003 | 70002 | 43035 | 160176 | 89 | 188
8 | G | 2016-06-13 | WIN | N | 1-0 | 20010 | 70009 | 29400 | 160429 | 360 | 182
9 | G | 2016-06-13 | DRAW | N | 1-1 | 20008 | 70010 | 73419 | 160335 | 67 | 194
........
51 | F | 2016-07-11 | WIN | N | 1-0 | 20008 | 70005 | 75868 | 160307 | 161 | 181
Sample table: referee_mast
referee_id | referee_name | country_id
------------+-------------------------+------------
70001 | Damir Skomina | 1225
70002 | Martin Atkinson | 1206
70003 | Felix Brych | 1208
70004 | Cuneyt Cakir | 1222
70005 | Mark Clattenburg | 1206
70006 | Jonas Eriksson | 1220
70007 | Viktor Kassai | 1209
70008 | Bjorn Kuipers | 1226
70009 | Szymon Marciniak | 1213
......
70018 | Clement Turpin | 1207
Sample Solution:
SQL Code:
-- Selecting the referee name and the count of booked matches for each referee
SELECT
c.referee_name, -- Selecting the referee name
COUNT(b.match_no) -- Counting the number of booked matches
FROM
player_booked a -- Specifying the player_booked table with alias 'a'
JOIN
match_mast b ON a.match_no = b.match_no -- Joining with match_mast table on match number
JOIN
referee_mast c ON b.referee_id = c.referee_id -- Joining with referee_mast table on referee ID
GROUP BY
referee_name -- Grouping the results by referee name
-- Sorting the results by the count of booked matches in descending order
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.
Alternative Solutions:
Using Subquery in FROM Clause:
-- Selecting the referee name and the count of matches booked by each referee
SELECT
referee_name, -- Selecting the referee name
COUNT(match_no) -- Counting the number of booked matches
FROM
(
-- Subquery to retrieve the match number and referee name for each booked match
SELECT
a.match_no, -- Selecting the match number
c.referee_name -- Selecting the referee name
FROM
player_booked a -- Specifying the player_booked table with alias 'a'
JOIN
match_mast b ON a.match_no = b.match_no -- Joining with match_mast table on match number
JOIN
referee_mast c ON b.referee_id = c.referee_id -- Joining with referee_mast table on referee ID
) AS subquery -- Aliasing the subquery as 'subquery'
GROUP BY
referee_name -- Grouping the results by referee name
-- Sorting the results by the count of booked matches in descending order
ORDER BY
COUNT(match_no) DESC;
Explanation:
This query uses a subquery in the FROM clause to first join the necessary tables and select the relevant columns. Then, in the outer query, it performs the grouping and aggregation.
Using a Correlated Subquery:
-- Selecting the referee name and the count of matches officiated by each referee
SELECT
c.referee_name, -- Selecting the referee name
( -- Subquery to calculate the count of matches officiated by each referee
SELECT
COUNT(b.match_no) -- Counting the number of matches for each referee
FROM
match_mast b -- Selecting from the match_mast table with alias 'b'
WHERE
b.referee_id = c.referee_id -- Filtering matches based on the referee ID
) as match_count -- Alias for the count of matches as 'match_count'
FROM
referee_mast c -- Selecting from the referee_mast table with alias 'c'
-- Sorting the results by the count of matches in descending order
ORDER BY
match_count DESC;
Explanation:
This query uses a correlated subquery in the SELECT clause to count the matches for each referee. It correlates the subquery with the main query using the referee_id.
Using a CROSS JOIN and Subquery:
-- Selecting the referee name and the count of matches officiated by each referee
SELECT
c.referee_name, -- Selecting the referee name
COUNT(b.match_no) -- Counting the number of matches for each referee
FROM
referee_mast c -- Selecting from the referee_mast table with alias 'c'
CROSS JOIN
player_booked a -- Performing a cross join with the player_booked table with alias 'a'
JOIN
match_mast b ON a.match_no = b.match_no AND b.referee_id = c.referee_id -- Joining match_mast table with alias 'b' based on match number and referee ID
GROUP BY
c.referee_name -- Grouping the results by referee name
-- Sorting the results by the count of matches in descending order
ORDER BY
COUNT(b.match_no) DESC;
Explanation:
This query uses a CROSS JOIN to generate all possible combinations of referees and booked players. It then filters the results to match the respective IDs and groups by referee name.
Relational Algebra Expression:
Relational Algebra Tree:
Go to:
PREV : Find the number of matches each referee managed.
NEXT : Find the referees who booked most number of players.
Practice Online
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.