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
2 | 1201 | 160015 | 89 | | NT | 2
2 | 1201 | 160009 | 93 | | NT | 2
3 | 1218 | 160401 | 2 | | ST | 2
3 | 1218 | 160406 | 31 | | NT | 1
3 | 1218 | 160408 | 78 | | NT | 2
3 | 1218 | 160411 | 80 | | NT | 2
3 | 1218 | 160407 | 83 | | NT | 2
4 | 1206 | 160120 | 62 | | NT | 2
4 | 1217 | 160377 | 72 | | NT | 2
5 | 1222 | 160505 | 31 | | NT | 1
5 | 1222 | 160490 | 48 | | NT | 2
5 | 1204 | 160077 | 80 | | NT | 2
5 | 1222 | 160502 | 91 | | NT | 2
6 | 1213 | 160290 | 65 | | NT | 2
6 | 1212 | 160258 | 69 | | NT | 2
6 | 1213 | 160284 | 89 | | NT | 2
7 | 1223 | 160518 | 68 | | NT | 2
8 | 1205 | 160100 | 61 | | NT | 2
9 | 1215 | 160336 | 43 | | NT | 1
9 | 1220 | 160445 | 61 | | NT | 2
9 | 1215 | 160341 | 77 | | NT | 2
10 | 1211 | 160236 | 65 | | NT | 2
10 | 1211 | 160248 | 75 | | NT | 2
10 | 1211 | 160235 | 78 | | NT | 2
10 | 1211 | 160245 | 84 | | NT | 2
10 | 1203 | 160057 | 93 | | NT | 2
11 | 1202 | 160027 | 33 | | NT | 1
11 | 1202 | 160027 | 66 | Y | NT | 2
11 | 1209 | 160204 | 80 | | NT | 2
12 | 1210 | 160227 | 2 | | ST | 2
12 | 1210 | 160221 | 55 | | NT | 2
13 | 1218 | 160395 | 46 | | NT | 2
14 | 1221 | 160480 | 2 | | ST | 2
14 | 1216 | 160361 | 22 | | NT | 1
14 | 1216 | 160357 | 24 | | NT | 1
14 | 1216 | 160367 | 37 | | NT | 1
14 | 1221 | 160477 | 50 | | NT | 2
14 | 1216 | 160352 | 76 | | NT | 2
15 | 1201 | 160015 | 55 | | NT | 2
15 | 1201 | 160011 | 81 | | NT | 2
15 | 1207 | 160152 | 88 | | NT | 2
16 | 1224 | 160535 | 61 | | NT | 2
17 | 1223 | 160528 | 40 | | NT | 1
17 | 1212 | 160272 | 63 | | NT | 2
17 | 1223 | 160523 | 67 | | NT | 2
17 | 1212 | 160266 | 87 | | NT | 2
17 | 1212 | 160259 | 90 | | NT | 2
18 | 1208 | 160175 | 3 | | NT | 1
18 | 1213 | 160294 | 3 | | ST | 2
18 | 1208 | 160177 | 34 | | NT | 1
18 | 1213 | 160293 | 45 | | NT | 1
18 | 1213 | 160288 | 55 | | NT | 2
18 | 1208 | 160165 | 67 | | NT | 2
19 | 1211 | 160242 | 69 | | NT | 2
19 | 1220 | 160447 | 89 | | NT | 2
19 | 1211 | 160231 | 94 | | NT | 2
20 | 1204 | 160080 | 14 | | NT | 1
20 | 1205 | 160101 | 72 | | NT | 2
20 | 1204 | 160081 | 74 | | NT | 2
20 | 1204 | 160078 | 88 | | NT | 2
21 | 1219 | 160424 | 2 | | NT | 1
21 | 1222 | 160504 | 9 | | NT | 1
21 | 1222 | 160500 | 41 | | NT | 1
22 | 1215 | 160334 | 42 | | NT | 1
22 | 1203 | 160056 | 49 | | NT | 2
23 | 1209 | 160199 | 2 | | ST | 2
23 | 1210 | 160229 | 42 | | NT | 1
23 | 1210 | 160227 | 75 | | NT | 2
23 | 1210 | 160216 | 77 | | NT | 2
23 | 1209 | 160192 | 81 | | NT | 2
23 | 1209 | 160197 | 83 | | NT | 2
24 | 1202 | 160028 | 6 | | NT | 1
24 | 1214 | 160321 | 31 | | NT | 1
24 | 1214 | 160307 | 40 | | NT | 1
24 | 1202 | 160037 | 47 | | NT | 2
24 | 1202 | 160029 | 78 | | NT | 2
24 | 1202 | 160042 | 86 | | NT | 2
25 | 1201 | 160012 | 6 | | NT | 1
25 | 1216 | 160353 | 54 | | NT | 2
25 | 1201 | 160017 | 85 | | NT | 2
25 | 1216 | 160356 | 85 | | NT | 2
25 | 1216 | 160364 | 91 | | NT | 2
25 | 1201 | 160007 | 95 | | NT | 2
26 | 1207 | 160147 | 25 | | NT | 1
26 | 1207 | 160145 | 83 | | NT | 2
27 | 1224 | 160551 | 16 | | NT | 1
27 | 1217 | 160383 | 64 | | NT | 2
28 | 1218 | 160409 | 24 | | NT | 1
28 | 1206 | 160119 | 52 | | NT | 2
29 | 1223 | 160520 | 25 | | NT | 1
29 | 1223 | 160513 | 38 | | NT | 1
29 | 1213 | 160290 | 60 | | NT | 2
31 | 1222 | 160491 | 35 | | NT | 1
31 | 1205 | 160108 | 36 | | NT | 1
31 | 1205 | 160107 | 39 | Y | NT | 1
31 | 1222 | 160490 | 50 | | NT | 2
31 | 1205 | 160112 | 87 | | NT | 2
32 | 1204 | 160087 | 29 | | NT | 1
32 | 1204 | 160079 | 70 | | NT | 2
32 | 1204 | 160076 | 70 | | NT | 2
32 | 1204 | 160085 | 88 | | NT | 2
33 | 1210 | 160218 | 36 | | NT | 1
33 | 1210 | 160230 | 51 | | NT | 2
33 | 1202 | 160045 | 70 | | NT | 2
33 | 1210 | 160220 | 78 | | NT | 2
33 | 1210 | 160208 | 82 | | NT | 2
34 | 1209 | 160190 | 13 | | NT | 1
34 | 1209 | 160191 | 28 | | NT | 1
34 | 1209 | 160203 | 34 | | NT | 1
34 | 1209 | 160202 | 56 | | NT | 2
35 | 1211 | 160233 | 39 | | NT | 1
35 | 1215 | 160343 | 39 | | NT | 1
35 | 1215 | 160332 | 73 | | NT | 2
35 | 1211 | 160234 | 78 | | NT | 2
35 | 1211 | 160253 | 87 | | NT | 2
35 | 1211 | 160251 | 93 | | NT | 2
36 | 1203 | 160064 | 1 | | ST | 1
36 | 1203 | 160055 | 30 | | NT | 1
36 | 1220 | 160451 | 72 | | NT | 2
37 | 1221 | 160470 | 55 | | NT | 2
37 | 1213 | 160282 | 58 | | NT | 2
38 | 1212 | 160266 | 44 | | NT | 1
38 | 1224 | 160538 | 58 | | NT | 2
38 | 1212 | 160267 | 67 | | NT | 2
38 | 1224 | 160544 | 92 | | NT | 2
39 | 1214 | 160318 | 78 | | NT | 2
40 | 1215 | 160328 | 25 | | NT | 1
40 | 1207 | 160152 | 27 | | NT | 1
40 | 1215 | 160334 | 41 | | NT | 1
40 | 1207 | 160147 | 44 | | NT | 1
40 | 1215 | 160329 | 66 | Y | NT | 2
40 | 1215 | 160343 | 72 | | NT | 2
41 | 1218 | 160407 | 2 | | ST | 2
41 | 1218 | 160401 | 13 | | NT | 1
41 | 1208 | 160169 | 46 | | NT | 2
41 | 1208 | 160168 | 67 | | NT | 2
42 | 1209 | 160192 | 34 | | NT | 1
42 | 1209 | 160194 | 47 | | NT | 2
42 | 1209 | 160196 | 61 | | NT | 2
42 | 1203 | 160056 | 67 | | NT | 2
42 | 1203 | 160065 | 89 | | NT | 2
42 | 1203 | 160061 | 91 | | NT | 2
42 | 1209 | 160207 | 92 | | NT | 2
43 | 1219 | 160431 | 2 | | ST | 2
43 | 1211 | 160238 | 24 | | NT | 1
43 | 1219 | 160436 | 41 | | NT | 1
43 | 1211 | 160252 | 54 | | NT | 2
43 | 1211 | 160245 | 89 | | NT | 2
43 | 1219 | 160427 | 89 | | NT | 2
43 | 1219 | 160421 | 89 | | NT | 2
44 | 1210 | 160208 | 38 | | NT | 1
44 | 1206 | 160137 | 47 | | NT | 2
44 | 1210 | 160222 | 65 | | NT | 2
45 | 1214 | 160318 | 2 | | ST | 2
45 | 1213 | 160282 | 42 | | NT | 1
45 | 1213 | 160281 | 66 | | NT | 2
45 | 1214 | 160310 | 70 | | NT | 2
45 | 1213 | 160290 | 89 | | NT | 2
46 | 1224 | 160535 | 5 | | NT | 1
46 | 1224 | 160533 | 16 | | NT | 1
46 | 1224 | 160536 | 24 | | NT | 1
46 | 1203 | 160061 | 59 | | NT | 2
46 | 1224 | 160544 | 75 | | NT | 2
46 | 1203 | 160050 | 85 | | NT | 2
47 | 1211 | 160247 | 56 | | NT | 2
47 | 1211 | 160238 | 57 | | NT | 2
47 | 1211 | 160246 | 59 | | NT | 2
47 | 1208 | 160168 | 90 | | NT | 2
47 | 1208 | 160180 | 112 | | NT | 2
48 | 1210 | 160221 | 58 | | NT | 2
48 | 1207 | 160149 | 75 | | NT | 2
49 | 1224 | 160540 | 8 | | NT | 1
49 | 1224 | 160533 | 62 | | NT | 2
49 | 1214 | 160303 | 71 | | NT | 2
49 | 1214 | 160322 | 72 | | NT | 2
49 | 1224 | 160547 | 88 | | NT | 2
50 | 1208 | 160177 | 1 | | ST | 1
50 | 1208 | 160172 | 36 | | NT | 1
50 | 1207 | 160143 | 43 | | NT | 1
50 | 1208 | 160180 | 45 | | NT | 1
50 | 1208 | 160173 | 50 | | NT | 2
50 | 1207 | 160152 | 75 | | NT | 2
51 | 1214 | 160304 | 34 | | NT | 1
51 | 1214 | 160313 | 62 | | NT | 2
51 | 1207 | 160149 | 80 | | NT | 2
51 | 1214 | 160308 | 95 | | ET | 1
51 | 1207 | 160153 | 97 | | ET | 1
51 | 1214 | 160318 | 98 | | ET | 1
51 | 1207 | 160145 | 107 | | ET | 2
51 | 1207 | 160155 | 115 | | ET | 2
51 | 1214 | 160306 | 119 | | ET | 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
10 | G | 2016-06-14 | WIN | N | 0-2 | 20004 | 70005 | 55408 | 160244 | 63 | 189
11 | G | 2016-06-14 | WIN | N | 0-2 | 20001 | 70018 | 34424 | 160197 | 61 | 305
12 | G | 2016-06-15 | DRAW | N | 1-1 | 20009 | 70004 | 38742 | 160320 | 15 | 284
13 | G | 2016-06-15 | WIN | N | 1-2 | 20003 | 70001 | 38989 | 160405 | 62 | 189
14 | G | 2016-06-15 | DRAW | N | 1-1 | 20007 | 70015 | 43576 | 160477 | 74 | 206
15 | G | 2016-06-16 | WIN | N | 2-0 | 20005 | 70013 | 63670 | 160154 | 71 | 374
16 | G | 2016-06-16 | WIN | N | 2-1 | 20002 | 70003 | 34033 | 160540 | 62 | 212
17 | G | 2016-06-16 | WIN | N | 0-2 | 20004 | 70016 | 51043 | 160262 | 7 | 411
18 | G | 2016-06-17 | DRAW | N | 0-0 | 20008 | 70008 | 73648 | 160165 | 6 | 208
19 | G | 2016-06-17 | WIN | N | 1-0 | 20010 | 70007 | 29600 | 160248 | 2 | 264
20 | G | 2016-06-17 | DRAW | N | 2-2 | 20009 | 70005 | 38376 | 160086 | 71 | 280
21 | G | 2016-06-18 | WIN | N | 3-0 | 20006 | 70010 | 33409 | 160429 | 84 | 120
22 | G | 2016-06-18 | WIN | N | 3-0 | 20001 | 70004 | 39493 | 160064 | 11 | 180
23 | G | 2016-06-18 | DRAW | N | 1-1 | 20005 | 70015 | 60842 | 160230 | 61 | 280
24 | G | 2016-06-19 | DRAW | N | 0-0 | 20007 | 70011 | 44291 | 160314 | 3 | 200
25 | G | 2016-06-20 | WIN | N | 0-1 | 20004 | 70016 | 49752 | 160005 | 125 | 328
26 | G | 2016-06-20 | DRAW | N | 0-0 | 20003 | 70001 | 45616 | 160463 | 60 | 122
27 | G | 2016-06-21 | WIN | N | 0-3 | 20010 | 70006 | 28840 | 160544 | 62 | 119
28 | G | 2016-06-21 | DRAW | N | 0-0 | 20009 | 70012 | 39051 | 160392 | 62 | 301
29 | G | 2016-06-21 | WIN | N | 0-1 | 20005 | 70017 | 58874 | 160520 | 29 | 244
30 | G | 2016-06-21 | WIN | N | 0-1 | 20007 | 70018 | 44125 | 160177 | 21 | 195
31 | G | 2016-06-22 | WIN | N | 0-2 | 20002 | 70013 | 32836 | 160504 | 60 | 300
32 | G | 2016-06-22 | WIN | N | 2-1 | 20001 | 70008 | 37245 | 160085 | 70 | 282
33 | G | 2016-06-22 | WIN | N | 2-1 | 20008 | 70009 | 68714 | 160220 | 7 | 244
34 | G | 2016-06-22 | DRAW | N | 3-3 | 20004 | 70002 | 55514 | 160322 | 70 | 185
35 | G | 2016-06-23 | WIN | N | 0-1 | 20003 | 70014 | 44268 | 160333 | 79 | 221
36 | G | 2016-06-23 | WIN | N | 0-1 | 20006 | 70003 | 34011 | 160062 | 63 | 195
37 | R | 2016-06-25 | WIN | P | 1-1 | 20009 | 70005 | 38842 | 160476 | 126 | 243
38 | R | 2016-06-25 | WIN | N | 1-0 | 20007 | 70002 | 44342 | 160547 | 5 | 245
39 | R | 2016-06-26 | WIN | N | 0-1 | 20002 | 70012 | 33523 | 160316 | 61 | 198
40 | R | 2016-06-26 | WIN | N | 2-1 | 20004 | 70011 | 56279 | 160160 | 238 | 203
41 | R | 2016-06-26 | WIN | N | 3-0 | 20003 | 70009 | 44312 | 160173 | 62 | 124
42 | R | 2016-06-27 | WIN | N | 0-4 | 20010 | 70010 | 28921 | 160062 | 3 | 133
43 | R | 2016-06-27 | WIN | N | 2-0 | 20008 | 70004 | 76165 | 160235 | 63 | 243
44 | R | 2016-06-28 | WIN | N | 1-2 | 20006 | 70001 | 33901 | 160217 | 5 | 199
45 | Q | 2016-07-01 | WIN | P | 1-1 | 20005 | 70003 | 62940 | 160316 | 58 | 181
46 | Q | 2016-07-02 | WIN | N | 3-1 | 20003 | 70001 | 45936 | 160550 | 14 | 182
47 | Q | 2016-07-03 | WIN | P | 1-1 | 20001 | 70007 | 38764 | 160163 | 63 | 181
48 | Q | 2016-07-04 | WIN | N | 5-2 | 20008 | 70008 | 76833 | 160159 | 16 | 125
49 | S | 2016-07-07 | WIN | N | 2-0 | 20004 | 70006 | 55679 | 160322 | 2 | 181
50 | S | 2016-07-08 | WIN | N | 2-0 | 20005 | 70011 | 64078 | 160160 | 126 | 275
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
70010 | Milorad Mazic | 1227
70011 | Nicola Rizzoli | 1211
70012 | Carlos Velasco Carballo | 1219
70013 | William Collum | 1228
70014 | Ovidiu Hategan | 1216
70015 | Sergei Karasev | 1217
70016 | Pavel Kralovec | 1205
70017 | Svein Oddvar Moen | 1229
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:
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.