w3resource

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
 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 Expression: Find the referees and number of booked he made.

Relational Algebra Tree:

Relational Algebra Tree: Find the referees and number of booked he made.

Practice Online


Sample Database: soccer

soccer database relationship structure

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.



Follow us on Facebook and Twitter for latest update.