Q:

From the following tables, write a SQL query to find those referees who booked most number of players

0

From the following tables, write a SQL query to find those referees who booked most number of players. 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
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
 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

All Answers

need an explanation for this answer? contact us directly to get an explanation for this answer

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
HAVING count(b.match_no)=
  (SELECT max(mm)
   FROM
     (SELECT count(b.match_no) mm
      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) hh);

Sample Output:

   referee_name   | count
------------------+-------
 Mark Clattenburg |    21
(1 row)

need an explanation for this answer? contact us directly to get an explanation for this answer

total answers (1)

Similar questions


need a help?


find thousands of online teachers now