w3resource

SQL Exercise: Number of players replaced in the extra time of play

SQL soccer Database: Basic Exercise-20 with Solution

20. From the following table, write a SQL query to calculate the total number of players who were replaced during the extra time.

Sample table: player_in_out
 match_no | team_id | player_id | in_out | time_in_out | play_schedule | play_half
----------+---------+-----------+--------+-------------+---------------+-----------
        1 |    1207 |    160151 | I      |          66 | NT            |         2
        1 |    1207 |    160160 | O      |          66 | NT            |         2
        1 |    1207 |    160161 | I      |          77 | NT            |         2
        1 |    1207 |    160161 | O      |          77 | NT            |         2
        1 |    1207 |    160157 | I      |           2 | ST            |         2
        1 |    1207 |    160154 | O      |           2 | ST            |         2
        1 |    1216 |    160365 | I      |          61 | NT            |         2
        1 |    1216 |    160366 | O      |          61 | NT            |         2
        1 |    1216 |    160357 | I      |          72 | NT            |         2
        1 |    1216 |    160363 | O      |          72 | NT            |         2
        1 |    1216 |    160364 | I      |          82 | NT            |         2
        1 |    1216 |    160360 | O      |          82 | NT            |         2
        2 |    1201 |    160014 | I      |          62 | NT            |         2
        2 |    1201 |    160019 | O      |          62 | NT            |         2
        2 |    1201 |    160021 | I      |          74 | NT            |         2
        2 |    1201 |    160018 | O      |          74 | NT            |         2
        2 |    1201 |    160022 | I      |          82 | NT            |         2
        2 |    1201 |    160023 | O      |          82 | NT            |         2
        2 |    1221 |    160480 | I      |          62 | NT            |         2
        2 |    1221 |    160481 | O      |          62 | NT            |         2
        2 |    1221 |    160475 | I      |          76 | NT            |         2
        2 |    1221 |    160473 | O      |          76 | NT            |         2
        2 |    1221 |    160474 | I      |          88 | NT            |         2
        2 |    1221 |    160476 | O      |          88 | NT            |         2
        3 |    1218 |    160413 | I      |          59 | NT            |         2
        3 |    1218 |    160412 | O      |          59 | NT            |         2
        3 |    1218 |    160403 | I      |          60 | NT            |         2
        3 |    1218 |    160406 | O      |          60 | NT            |         2
        3 |    1218 |    160410 | I      |          83 | NT            |         2
        3 |    1218 |    160411 | O      |          83 | NT            |         2
        3 |    1224 |    160543 | I      |          69 | NT            |         2
        3 |    1224 |    160541 | O      |          69 | NT            |         2
        3 |    1224 |    160550 | I      |          71 | NT            |         2
        3 |    1224 |    160546 | O      |          71 | NT            |         2
        3 |    1224 |    160537 | I      |          88 | NT            |         2
        3 |    1224 |    160544 | O      |          88 | NT            |         2
        4 |    1206 |    160133 | I      |          78 | NT            |         2
        4 |    1206 |    160136 | O      |          78 | NT            |         2
        4 |    1206 |    160131 | I      |          87 | NT            |         2
        4 |    1206 |    160132 | O      |          87 | NT            |         2
        4 |    1217 |    160386 | I      |          77 | NT            |         2
        4 |    1217 |    160381 | O      |          77 | NT            |         2
        4 |    1217 |    160380 | I      |          80 | NT            |         2
        4 |    1217 |    160376 | O      |          80 | NT            |         2
        4 |    1217 |    160383 | I      |          85 | NT            |         2
        4 |    1217 |    160391 | O      |          85 | NT            |         2
        5 |    1204 |    160090 | I      |          87 | NT            |         2
        5 |    1204 |    160085 | O      |          87 | NT            |         2
        5 |    1204 |    160075 | I      |          90 | NT            |         2
        5 |    1204 |    160086 | O      |          90 | NT            |         2
        5 |    1204 |    160092 | I      |           3 | ST            |         2
        5 |    1204 |    160091 | O      |           3 | ST            |         2
        5 |    1222 |    160502 | I      |          46 | NT            |         2
        5 |    1222 |    160498 | O      |          46 | NT            |         2
        5 |    1222 |    160504 | I      |          65 | NT            |         2
        5 |    1222 |    160494 | O      |          65 | NT            |         2
        5 |    1222 |    160506 | I      |          69 | NT            |         2
        5 |    1222 |    160505 | O      |          69 | NT            |         2
        6 |    1212 |    160266 | I      |          46 | NT            |         2
        6 |    1212 |    160265 | O      |          46 | NT            |         2
        6 |    1212 |    160276 | I      |          66 | NT            |         2
        6 |    1212 |    160269 | O      |          66 | NT            |         2
        6 |    1212 |    160272 | I      |          76 | NT            |         2
        6 |    1212 |    160257 | O      |          76 | NT            |         2
        6 |    1213 |    160289 | I      |          78 | NT            |         2
        6 |    1213 |    160293 | O      |          78 | NT            |         2
        6 |    1213 |    160288 | I      |          80 | NT            |         2
        6 |    1213 |    160287 | O      |          80 | NT            |         2
        6 |    1213 |    160294 | I      |          88 | NT            |         2
        6 |    1213 |    160290 | O      |          88 | NT            |         2
        7 |    1208 |    160179 | I      |          78 | NT            |         2
        7 |    1208 |    160173 | O      |          78 | NT            |         2
        7 |    1208 |    160180 | I      |          90 | NT            |         2
        7 |    1208 |    160174 | O      |          90 | NT            |         2
        7 |    1223 |    160528 | I      |          66 | NT            |         2
        7 |    1223 |    160529 | O      |          66 | NT            |         2
        7 |    1223 |    160526 | I      |          73 | NT            |         2
        7 |    1223 |    160519 | O      |          73 | NT            |         2
        8 |    1205 |    160113 | I      |          75 | NT            |         2
        8 |    1205 |    160114 | O      |          75 | NT            |         2
        8 |    1205 |    160112 | I      |          86 | NT            |         2
        8 |    1205 |    160096 | O      |          86 | NT            |         2
        8 |    1205 |    160107 | I      |          88 | NT            |         2
        8 |    1205 |    160110 | O      |          88 | NT            |         2
        8 |    1219 |    160433 | I      |          62 | NT            |         2
        8 |    1219 |    160435 | O      |          62 | NT            |         2
        8 |    1219 |    160432 | I      |          70 | NT            |         2
        8 |    1219 |    160428 | O      |          70 | NT            |         2
        8 |    1219 |    160437 | I      |          82 | NT            |         2
        8 |    1219 |    160436 | O      |          82 | NT            |         2
        9 |    1215 |    160337 | I      |          64 | NT            |         2
        9 |    1215 |    160345 | O      |          64 | NT            |         2
        9 |    1215 |    160342 | I      |          78 | NT            |         2
        9 |    1215 |    160335 | O      |          78 | NT            |         2
        9 |    1215 |    160338 | I      |          85 | NT            |         2
        9 |    1215 |    160336 | O      |          85 | NT            |         2
        9 |    1220 |    160444 | I      |          45 | NT            |         1
        9 |    1220 |    160446 | O      |          45 | NT            |         1
        9 |    1220 |    160458 | I      |          59 | NT            |         2
        9 |    1220 |    160457 | O      |          59 | NT            |         2
        9 |    1220 |    160449 | I      |          86 | NT            |         2
        9 |    1220 |    160455 | O      |          86 | NT            |         2
       10 |    1203 |    160068 | I      |          62 | NT            |         2
       10 |    1203 |    160063 | O      |          62 | NT            |         2
       10 |    1203 |    160069 | I      |          73 | NT            |         2
       10 |    1203 |    160067 | O      |          73 | NT            |         2
       10 |    1203 |    160058 | I      |          76 | NT            |         2
       10 |    1203 |    160051 | O      |          76 | NT            |         2
       10 |    1211 |    160238 | I      |          58 | NT            |         2
       10 |    1211 |    160237 | O      |          58 | NT            |         2
       10 |    1211 |    160250 | I      |          75 | NT            |         2
       10 |    1211 |    160248 | O      |          75 | NT            |         2
       10 |    1211 |    160245 | I      |          78 | NT            |         2
       10 |    1211 |    160242 | O      |          78 | NT            |         2
       11 |    1202 |    160041 | I      |          59 | NT            |         2
       11 |    1202 |    160040 | O      |          59 | NT            |         2
       11 |    1202 |    160046 | I      |          65 | NT            |         2
       11 |    1202 |    160045 | O      |          65 | NT            |         2
       11 |    1202 |    160042 | I      |          77 | NT            |         2
       11 |    1202 |    160037 | O      |          77 | NT            |         2
       11 |    1209 |    160206 | I      |          69 | NT            |         2
       11 |    1209 |    160207 | O      |          69 | NT            |         2
       11 |    1209 |    160200 | I      |          79 | NT            |         2
       11 |    1209 |    160197 | O      |          79 | NT            |         2
       11 |    1209 |    160195 | I      |          89 | NT            |         2
       11 |    1209 |    160204 | O      |          89 | NT            |         2
       12 |    1210 |    160227 | I      |          81 | NT            |         2
       12 |    1210 |    160230 | O      |          81 | NT            |         2
       12 |    1210 |    160211 | I      |          90 | NT            |         2
       12 |    1210 |    160229 | O      |          90 | NT            |         2
       12 |    1214 |    160316 | I      |          71 | NT            |         2
       12 |    1214 |    160314 | O      |          71 | NT            |         2
       12 |    1214 |    160321 | I      |          76 | NT            |         2
       12 |    1214 |    160313 | O      |          76 | NT            |         2
       12 |    1214 |    160319 | I      |          84 | NT            |         2
       12 |    1214 |    160311 | O      |          84 | NT            |         2
       13 |    1217 |    160380 | I      |          46 | NT            |         2
       13 |    1217 |    160383 | I      |          46 | NT            |         2
       13 |    1217 |    160376 | O      |          46 | NT            |         2
       13 |    1217 |    160381 | O      |          46 | NT            |         2
       13 |    1217 |    160386 | I      |          75 | NT            |         2
       13 |    1217 |    160390 | O      |          75 | NT            |         2
       13 |    1218 |    160413 | I      |          67 | NT            |         2
       13 |    1218 |    160403 | O      |          67 | NT            |         2
       13 |    1218 |    160402 | I      |          72 | NT            |         2
       13 |    1218 |    160411 | O      |          72 | NT            |         2
       13 |    1218 |    160412 | I      |          80 | NT            |         2
       13 |    1218 |    160408 | O      |          80 | NT            |         2
       14 |    1216 |    160358 | I      |          46 | NT            |         2
       14 |    1216 |    160359 | O      |          46 | NT            |         2
       14 |    1216 |    160350 | I      |          62 | NT            |         2
       14 |    1216 |    160355 | O      |          62 | NT            |         2
       14 |    1216 |    160366 | I      |          84 | NT            |         2
       14 |    1216 |    160368 | O      |          84 | NT            |         2
       14 |    1221 |    160480 | I      |          63 | NT            |         2
       14 |    1221 |    160482 | O      |          63 | NT            |         2
       14 |    1221 |    160466 | I      |          83 | NT            |         2
       14 |    1221 |    160473 | O      |          83 | NT            |         2
       14 |    1221 |    160483 | I      |          90 | NT            |         2
       14 |    1221 |    160476 | O      |          90 | NT            |         2
       15 |    1201 |    160018 | I      |          71 | NT            |         2
       15 |    1201 |    160008 | O      |          71 | NT            |         2
       15 |    1201 |    160019 | I      |          74 | NT            |         2
       15 |    1201 |    160015 | O      |          74 | NT            |         2
       15 |    1201 |    160010 | I      |          85 | NT            |         2
       15 |    1201 |    160005 | O      |          85 | NT            |         2
       15 |    1207 |    160155 | I      |          46 | NT            |         2
       15 |    1207 |    160161 | O      |          46 | NT            |         2
       15 |    1207 |    160160 | I      |          68 | NT            |         2
       15 |    1207 |    160151 | O      |          68 | NT            |         2
       15 |    1207 |    160158 | I      |          77 | NT            |         2
       15 |    1207 |    160159 | O      |          77 | NT            |         2
       16 |    1206 |    160138 | I      |          46 | NT            |         2
       16 |    1206 |    160137 | I      |          46 | NT            |         2
       16 |    1206 |    160134 | O      |          46 | NT            |         2
       16 |    1206 |    160132 | O      |          46 | NT            |         2
       16 |    1206 |    160135 | I      |          73 | NT            |         2
       16 |    1206 |    160130 | O      |          73 | NT            |         2
       16 |    1224 |    160541 | I      |          67 | NT            |         2
       16 |    1224 |    160543 | O      |          67 | NT            |         2
       16 |    1224 |    160546 | I      |          72 | NT            |         2
       16 |    1224 |    160550 | O      |          72 | NT            |         2
       17 |    1212 |    160275 | I      |          69 | NT            |         2
       17 |    1212 |    160272 | O      |          69 | NT            |         2
       17 |    1212 |    160270 | I      |          84 | NT            |         2
       17 |    1212 |    160276 | O      |          84 | NT            |         2
       17 |    1212 |    160265 | I      |          87 | NT            |         2
       17 |    1212 |    160268 | O      |          87 | NT            |         2
       17 |    1223 |    160529 | I      |          71 | NT            |         2
       17 |    1223 |    160528 | O      |          71 | NT            |         2
       17 |    1223 |    160516 | I      |          76 | NT            |         2
       17 |    1223 |    160523 | O      |          76 | NT            |         2
       17 |    1223 |    160526 | I      |          83 | NT            |         2
       17 |    1223 |    160519 | O      |          83 | NT            |         2
       18 |    1208 |    160179 | I      |          66 | NT            |         2
       18 |    1208 |    160174 | O      |          66 | NT            |         2
       18 |    1208 |    160182 | I      |          71 | NT            |         2
       18 |    1208 |    160173 | O      |          71 | NT            |         2
       18 |    1213 |    160289 | I      |          76 | NT            |         2
       18 |    1213 |    160293 | O      |          76 | NT            |         2
       18 |    1213 |    160290 | I      |          80 | NT            |         2
       18 |    1213 |    160287 | O      |          80 | NT            |         2
       18 |    1213 |    160294 | I      |          87 | NT            |         2
       18 |    1213 |    160288 | O      |          87 | NT            |         2
       19 |    1211 |    160253 | I      |          60 | NT            |         2
       19 |    1211 |    160252 | O      |          60 | NT            |         2
       19 |    1211 |    160245 | I      |          74 | NT            |         2
       19 |    1211 |    160242 | O      |          74 | NT            |         2
       19 |    1211 |    160247 | I      |          85 | NT            |         2
       19 |    1211 |    160243 | O      |          85 | NT            |         2
       19 |    1220 |    160455 | I      |          79 | NT            |         2
       19 |    1220 |    160448 | I      |          79 | NT            |         2
       19 |    1220 |    160449 | O      |          79 | NT            |         2
       19 |    1220 |    160451 | O      |          79 | NT            |         2
       19 |    1220 |    160457 | I      |          85 | NT            |         2
       19 |    1220 |    160458 | O      |          85 | NT            |         2
       20 |    1204 |    160083 | I      |          62 | NT            |         2
       20 |    1204 |    160084 | O      |          62 | NT            |         2
       20 |    1204 |    160075 | I      |          90 | NT            |         2
       20 |    1204 |    160086 | O      |          90 | NT            |         2
       20 |    1204 |    160079 | I      |           2 | ST            |         2
       20 |    1204 |    160077 | O      |           2 | ST            |         2
       20 |    1205 |    160115 | I      |          67 | NT            |         2
       20 |    1205 |    160112 | I      |          67 | NT            |         2
       20 |    1205 |    160113 | O      |          67 | NT            |         2
       20 |    1205 |    160111 | O      |          67 | NT            |         2
       20 |    1205 |    160114 | I      |          86 | NT            |         2
       20 |    1205 |    160108 | O      |          86 | NT            |         2
       21 |    1219 |    160426 | I      |          64 | NT            |         2
       21 |    1219 |    160431 | O      |          64 | NT            |         2
       21 |    1219 |    160430 | I      |          71 | NT            |         2
       21 |    1219 |    160428 | O      |          71 | NT            |         2
       21 |    1219 |    160418 | I      |          81 | NT            |         2
       21 |    1219 |    160421 | O      |          81 | NT            |         2
       21 |    1222 |    160497 | I      |          46 | NT            |         2
       21 |    1222 |    160495 | O      |          46 | NT            |         2
       21 |    1222 |    160499 | I      |          62 | NT            |         2
       21 |    1222 |    160498 | O      |          62 | NT            |         2
       21 |    1222 |    160503 | I      |          70 | NT            |         2
       21 |    1222 |    160501 | O      |          70 | NT            |         2
       22 |    1203 |    160063 | I      |          57 | NT            |         2
       22 |    1203 |    160060 | O      |          57 | NT            |         2
       22 |    1203 |    160068 | I      |          64 | NT            |         2
       22 |    1203 |    160058 | O      |          64 | NT            |         2
       22 |    1203 |    160066 | I      |          83 | NT            |         2
       22 |    1203 |    160067 | O      |          83 | NT            |         2
       22 |    1215 |    160337 | I      |          62 | NT            |         2
       22 |    1215 |    160336 | O      |          62 | NT            |         2
       22 |    1215 |    160338 | I      |          71 | NT            |         2
       22 |    1215 |    160335 | O      |          71 | NT            |         2
       22 |    1215 |    160342 | I      |          79 | NT            |         2
       22 |    1215 |    160343 | O      |          79 | NT            |         2
       23 |    1209 |    160201 | I      |          66 | NT            |         2
       23 |    1209 |    160205 | I      |          66 | NT            |         2
       23 |    1209 |    160206 | O      |          66 | NT            |         2
       23 |    1209 |    160200 | O      |          66 | NT            |         2
       23 |    1209 |    160207 | I      |          84 | NT            |         2
       23 |    1209 |    160191 | O      |          84 | NT            |         2
       23 |    1210 |    160223 | I      |          65 | NT            |         2
       23 |    1210 |    160222 | O      |          65 | NT            |         2
       23 |    1210 |    160227 | I      |          69 | NT            |         2
       23 |    1210 |    160226 | O      |          69 | NT            |         2
       23 |    1210 |    160228 | I      |          84 | NT            |         2
       23 |    1210 |    160230 | O      |          84 | NT            |         2
       24 |    1202 |    160042 | I      |          65 | NT            |         2
       24 |    1202 |    160034 | O      |          65 | NT            |         2
       24 |    1202 |    160044 | I      |          85 | NT            |         2
       24 |    1202 |    160041 | O      |          85 | NT            |         2
       24 |    1202 |    160033 | I      |          87 | NT            |         2
       24 |    1202 |    160038 | O      |          87 | NT            |         2
       24 |    1214 |    160313 | I      |          71 | NT            |         2
       24 |    1214 |    160321 | O      |          71 | NT            |         2
       24 |    1214 |    160319 | I      |          83 | NT            |         2
       24 |    1214 |    160311 | O      |          83 | NT            |         2
       24 |    1214 |    160315 | I      |          89 | NT            |         2
       24 |    1214 |    160320 | O      |          89 | NT            |         2
       25 |    1201 |    160020 | I      |          59 | NT            |         2
       25 |    1201 |    160023 | O      |          59 | NT            |         2
       25 |    1201 |    160018 | I      |          77 | NT            |         2
       25 |    1201 |    160016 | O      |          77 | NT            |         2
       25 |    1201 |    160013 | I      |          83 | NT            |         2
       25 |    1201 |    160012 | O      |          83 | NT            |         2
       25 |    1216 |    160362 | I      |          46 | NT            |         2
       25 |    1216 |    160361 | O      |          46 | NT            |         2
       25 |    1216 |    160364 | I      |          57 | NT            |         2
       25 |    1216 |    160365 | O      |          57 | NT            |         2
       25 |    1216 |    160366 | I      |          68 | NT            |         2
       25 |    1216 |    160360 | O      |          68 | NT            |         2
       26 |    1207 |    160154 | I      |          63 | NT            |         2
       26 |    1207 |    160151 | O      |          63 | NT            |         2
       26 |    1207 |    160153 | I      |          77 | NT            |         2
       26 |    1207 |    160160 | O      |          77 | NT            |         2
       26 |    1221 |    160482 | I      |          74 | NT            |         2
       26 |    1221 |    160480 | O      |          74 | NT            |         2
       26 |    1221 |    160474 | I      |          79 | NT            |         2
       26 |    1221 |    160476 | O      |          79 | NT            |         2
       26 |    1221 |    160466 | I      |          86 | NT            |         2
       26 |    1221 |    160481 | O      |          86 | NT            |         2
       27 |    1217 |    160372 | I      |          46 | NT            |         2
       27 |    1217 |    160373 | O      |          46 | NT            |         2
       27 |    1217 |    160381 | I      |          52 | NT            |         2
       27 |    1217 |    160386 | O      |          52 | NT            |         2
       27 |    1217 |    160384 | I      |          70 | NT            |         2
       27 |    1217 |    160391 | O      |          70 | NT            |         2
       27 |    1224 |    160541 | I      |          74 | NT            |         2
       27 |    1224 |    160540 | O      |          74 | NT            |         2
       27 |    1224 |    160542 | I      |          76 | NT            |         2
       27 |    1224 |    160543 | O      |          76 | NT            |         2
       27 |    1224 |    160548 | I      |          83 | NT            |         2
       27 |    1224 |    160547 | O      |          83 | NT            |         2
       28 |    1206 |    160136 | I      |          56 | NT            |         2
       28 |    1206 |    160133 | O      |          56 | NT            |         2
       28 |    1206 |    160126 | I      |          61 | NT            |         2
       28 |    1206 |    160130 | O      |          61 | NT            |         2
       28 |    1206 |    160134 | I      |          76 | NT            |         2
       28 |    1206 |    160137 | O      |          76 | NT            |         2
       28 |    1218 |    160402 | I      |          57 | NT            |         2
       28 |    1218 |    160403 | O      |          57 | NT            |         2
       28 |    1218 |    160396 | I      |          67 | NT            |         2
       28 |    1218 |    160409 | O      |          67 | NT            |         2
       28 |    1218 |    160400 | I      |          78 | NT            |         2
       28 |    1218 |    160411 | O      |          78 | NT            |         2
       29 |    1213 |    160287 | I      |          46 | NT            |         2
       29 |    1213 |    160296 | O      |          46 | NT            |         2
       29 |    1213 |    160288 | I      |          71 | NT            |         2
       29 |    1213 |    160290 | O      |          71 | NT            |         2
       29 |    1213 |    160295 | I      |           3 | ST            |         2
       29 |    1213 |    160298 | O      |           3 | ST            |         2
       29 |    1223 |    160519 | I      |          73 | NT            |         2
       29 |    1223 |    160526 | O      |          73 | NT            |         2
       29 |    1223 |    160524 | I      |           1 | ST            |         2
       29 |    1223 |    160529 | O      |           1 | ST            |         2
       30 |    1208 |    160179 | I      |          55 | NT            |         2
       30 |    1208 |    160174 | O      |          55 | NT            |         2
       30 |    1208 |    160180 | I      |          69 | NT            |         2
       30 |    1208 |    160175 | O      |          69 | NT            |         2
       30 |    1208 |    160167 | I      |          76 | NT            |         2
       30 |    1208 |    160165 | O      |          76 | NT            |         2
       30 |    1212 |    160274 | I      |          59 | NT            |         2
       30 |    1212 |    160276 | O      |          59 | NT            |         2
       30 |    1212 |    160270 | I      |          70 | NT            |         2
       30 |    1212 |    160272 | O      |          70 | NT            |         2
       30 |    1212 |    160275 | I      |          84 | NT            |         2
       30 |    1212 |    160268 | O      |          84 | NT            |         2
       31 |    1205 |    160115 | I      |          57 | NT            |         2
       31 |    1205 |    160107 | O      |          57 | NT            |         2
       31 |    1205 |    160112 | I      |          71 | NT            |         2
       31 |    1205 |    160104 | O      |          71 | NT            |         2
       31 |    1205 |    160105 | I      |          90 | NT            |         2
       31 |    1205 |    160108 | O      |          90 | NT            |         2
       31 |    1222 |    160498 | I      |          61 | NT            |         2
       31 |    1222 |    160502 | O      |          61 | NT            |         2
       31 |    1222 |    160499 | I      |          69 | NT            |         2
       31 |    1222 |    160506 | O      |          69 | NT            |         2
       31 |    1222 |    160505 | I      |          90 | NT            |         2
       31 |    1222 |    160504 | O      |          90 | NT            |         2
       32 |    1204 |    160083 | I      |          82 | NT            |         2
       32 |    1204 |    160087 | O      |          82 | NT            |         2
       32 |    1204 |    160088 | I      |          90 | NT            |         2
       32 |    1204 |    160092 | O      |          90 | NT            |         2
       32 |    1204 |    160090 | I      |           2 | ST            |         2
       32 |    1204 |    160085 | O      |           2 | ST            |         2
       32 |    1219 |    160426 | I      |          60 | NT            |         2
       32 |    1219 |    160436 | O      |          60 | NT            |         2
       32 |    1219 |    160433 | I      |          67 | NT            |         2
       32 |    1219 |    160435 | O      |          67 | NT            |         2
       32 |    1219 |    160432 | I      |          84 | NT            |         2
       32 |    1219 |    160428 | O      |          84 | NT            |         2
       33 |    1202 |    160042 | I      |          46 | NT            |         2
       33 |    1202 |    160045 | I      |          46 | NT            |         2
       33 |    1202 |    160031 | O      |          46 | NT            |         2
       33 |    1202 |    160038 | O      |          46 | NT            |         2
       33 |    1202 |    160039 | I      |          78 | NT            |         2
       33 |    1202 |    160041 | O      |          78 | NT            |         2
       33 |    1210 |    160211 | I      |          71 | NT            |         2
       33 |    1210 |    160226 | O      |          71 | NT            |         2
       33 |    1210 |    160219 | I      |          80 | NT            |         2
       33 |    1210 |    160230 | O      |          80 | NT            |         2
       33 |    1210 |    160214 | I      |          86 | NT            |         2
       33 |    1210 |    160229 | O      |          86 | NT            |         2
       34 |    1209 |    160188 | I      |          46 | NT            |         2
       34 |    1209 |    160203 | O      |          46 | NT            |         2
       34 |    1209 |    160204 | I      |          71 | NT            |         2
       34 |    1209 |    160207 | O      |          71 | NT            |         2
       34 |    1209 |    160200 | I      |          83 | NT            |         2
       34 |    1209 |    160198 | O      |          83 | NT            |         2
       34 |    1214 |    160316 | I      |          46 | NT            |         2
       34 |    1214 |    160314 | O      |          46 | NT            |         2
       34 |    1214 |    160321 | I      |          61 | NT            |         2
       34 |    1214 |    160311 | O      |          61 | NT            |         2
       34 |    1214 |    160313 | I      |          81 | NT            |         2
       34 |    1214 |    160320 | O      |          81 | NT            |         2
       35 |    1211 |    160237 | I      |          60 | NT            |         2
       35 |    1211 |    160240 | O      |          60 | NT            |         2
       35 |    1211 |    160251 | I      |          74 | NT            |         2
       35 |    1211 |    160250 | O      |          74 | NT            |         2
       35 |    1211 |    160249 | I      |          81 | NT            |         2
       35 |    1211 |    160238 | O      |          81 | NT            |         2
       35 |    1215 |    160338 | I      |          70 | NT            |         2
       35 |    1215 |    160344 | O      |          70 | NT            |         2
       35 |    1215 |    160335 | I      |          77 | NT            |         2
       35 |    1215 |    160336 | O      |          77 | NT            |         2
       35 |    1215 |    160340 | I      |          90 | NT            |         2
       35 |    1215 |    160343 | O      |          90 | NT            |         2
       36 |    1203 |    160068 | I      |          71 | NT            |         2
       36 |    1203 |    160058 | O      |          71 | NT            |         2
       36 |    1203 |    160066 | I      |          87 | NT            |         2
       36 |    1203 |    160067 | O      |          87 | NT            |         2
       36 |    1203 |    160069 | I      |           2 | ST            |         2
       36 |    1203 |    160062 | O      |           2 | ST            |         2
       36 |    1220 |    160458 | I      |          63 | NT            |         2
       36 |    1220 |    160457 | O      |          63 | NT            |         2
       36 |    1220 |    160448 | I      |          70 | NT            |         2
       36 |    1220 |    160454 | O      |          70 | NT            |         2
       36 |    1220 |    160450 | I      |          82 | NT            |         2
       36 |    1220 |    160451 | O      |          82 | NT            |         2
       37 |    1213 |    160289 | I      |         101 | ET            |         1
       37 |    1213 |    160293 | O      |         101 | ET            |         1
       37 |    1213 |    160294 | I      |         104 | ET            |         1
       37 |    1213 |    160288 | O      |         104 | ET            |         1
       37 |    1221 |    160480 | I      |          58 | NT            |         2
       37 |    1221 |    160473 | O      |          58 | NT            |         2
       37 |    1221 |    160479 | I      |          70 | NT            |         2
       37 |    1221 |    160481 | O      |          70 | NT            |         2
       37 |    1221 |    160474 | I      |          77 | NT            |         2
       37 |    1221 |    160472 | O      |          77 | NT            |         2
       38 |    1212 |    160276 | I      |          69 | NT            |         2
       38 |    1212 |    160272 | O      |          69 | NT            |         2
       38 |    1212 |    160275 | I      |          79 | NT            |         2
       38 |    1212 |    160271 | O      |          79 | NT            |         2
       38 |    1212 |    160270 | I      |          84 | NT            |         2
       38 |    1212 |    160262 | O      |          84 | NT            |         2
       38 |    1224 |    160550 | I      |          55 | NT            |         2
       38 |    1224 |    160551 | O      |          55 | NT            |         2
       38 |    1224 |    160546 | I      |          63 | NT            |         2
       38 |    1224 |    160543 | O      |          63 | NT            |         2
       39 |    1204 |    160092 | I      |         110 | ET            |         2
       39 |    1204 |    160086 | O      |         110 | ET            |         2
       39 |    1204 |    160090 | I      |         120 | ET            |         2
       39 |    1204 |    160073 | O      |         120 | ET            |         2
       39 |    1204 |    160089 | I      |          88 | NT            |         2
       39 |    1204 |    160091 | O      |          88 | NT            |         2
       39 |    1214 |    160313 | I      |         117 | ET            |         2
       39 |    1214 |    160310 | O      |         117 | ET            |         2
       39 |    1214 |    160316 | I      |          50 | NT            |         2
       39 |    1214 |    160311 | O      |          50 | NT            |         2
       39 |    1214 |    160321 | I      |          87 | NT            |         2
       39 |    1214 |    160313 | O      |          87 | NT            |         2
       40 |    1207 |    160151 | I      |          46 | NT            |         2
       40 |    1207 |    160152 | O      |          46 | NT            |         2
       40 |    1207 |    160158 | I      |          73 | NT            |         2
       40 |    1207 |    160159 | O      |          73 | NT            |         2
       40 |    1207 |    160157 | I      |           2 | ST            |         2
       40 |    1207 |    160151 | O      |           2 | ST            |         2
       40 |    1215 |    160345 | I      |          65 | NT            |         2
       40 |    1215 |    160344 | O      |          65 | NT            |         2
       40 |    1215 |    160331 | I      |          68 | NT            |         2
       40 |    1215 |    160337 | O      |          68 | NT            |         2
       40 |    1215 |    160335 | I      |          71 | NT            |         2
       40 |    1215 |    160336 | O      |          71 | NT            |         2
       41 |    1208 |    160167 | I      |          72 | NT            |         2
       41 |    1208 |    160184 | I      |          72 | NT            |         2
       41 |    1208 |    160165 | O      |          72 | NT            |         2
       41 |    1208 |    160173 | O      |          72 | NT            |         2
       41 |    1208 |    160180 | I      |          76 | NT            |         2
       41 |    1208 |    160175 | O      |          76 | NT            |         2
       41 |    1218 |    160404 | I      |          46 | NT            |         2
       41 |    1218 |    160411 | O      |          46 | NT            |         2
       41 |    1218 |    160414 | I      |          64 | NT            |         2
       41 |    1218 |    160412 | O      |          64 | NT            |         2
       41 |    1218 |    160399 | I      |          84 | NT            |         2
       41 |    1218 |    160396 | O      |          84 | NT            |         2
       42 |    1203 |    160058 | I      |          70 | NT            |         2
       42 |    1203 |    160068 | O      |          70 | NT            |         2
       42 |    1203 |    160065 | I      |          76 | NT            |         2
       42 |    1203 |    160067 | O      |          76 | NT            |         2
       42 |    1203 |    160061 | I      |          81 | NT            |         2
       42 |    1203 |    160062 | O      |          81 | NT            |         2
       42 |    1209 |    160196 | I      |          46 | NT            |         2
       42 |    1209 |    160203 | O      |          46 | NT            |         2
       42 |    1209 |    160205 | I      |          75 | NT            |         2
       42 |    1209 |    160195 | O      |          75 | NT            |         2
       42 |    1209 |    160201 | I      |          79 | NT            |         2
       42 |    1209 |    160191 | O      |          79 | NT            |         2
       43 |    1211 |    160245 | I      |          54 | NT            |         2
       43 |    1211 |    160242 | O      |          54 | NT            |         2
       43 |    1211 |    160251 | I      |          82 | NT            |         2
       43 |    1211 |    160248 | O      |          82 | NT            |         2
       43 |    1211 |    160237 | I      |          84 | NT            |         2
       43 |    1211 |    160243 | O      |          84 | NT            |         2
       43 |    1219 |    160433 | I      |          46 | NT            |         2
       43 |    1219 |    160436 | O      |          46 | NT            |         2
       43 |    1219 |    160434 | I      |          70 | NT            |         2
       43 |    1219 |    160435 | O      |          70 | NT            |         2
       43 |    1219 |    160437 | I      |          81 | NT            |         2
       43 |    1219 |    160433 | O      |          81 | NT            |         2
       44 |    1206 |    160133 | I      |          46 | NT            |         2
       44 |    1206 |    160128 | O      |          46 | NT            |         2
       44 |    1206 |    160138 | I      |          60 | NT            |         2
       44 |    1206 |    160132 | O      |          60 | NT            |         2
       44 |    1206 |    160135 | I      |          87 | NT            |         2
       44 |    1206 |    160136 | O      |          87 | NT            |         2
       44 |    1210 |    160211 | I      |          76 | NT            |         2
       44 |    1210 |    160230 | O      |          76 | NT            |         2
       44 |    1210 |    160219 | I      |          89 | NT            |         2
       44 |    1210 |    160226 | O      |          89 | NT            |         2
       45 |    1213 |    160289 | I      |          98 | ET            |         1
       45 |    1213 |    160293 | O      |          98 | ET            |         1
       45 |    1213 |    160290 | I      |          82 | NT            |         2
       45 |    1213 |    160288 | O      |          82 | NT            |         2
       45 |    1214 |    160313 | I      |          96 | ET            |         1
       45 |    1214 |    160318 | O      |          96 | ET            |         1
       45 |    1214 |    160314 | I      |          70 | NT            |         2
       45 |    1214 |    160310 | O      |          70 | NT            |         2
       45 |    1214 |    160321 | I      |          80 | NT            |         2
       45 |    1214 |    160313 | O      |          80 | NT            |         2
       46 |    1203 |    160061 | I      |          46 | NT            |         2
       46 |    1203 |    160058 | O      |          46 | NT            |         2
       46 |    1203 |    160068 | I      |          75 | NT            |         2
       46 |    1203 |    160054 | O      |          75 | NT            |         2
       46 |    1203 |    160065 | I      |          83 | NT            |         2
       46 |    1203 |    160067 | O      |          83 | NT            |         2
       46 |    1224 |    160542 | I      |          78 | NT            |         2
       46 |    1224 |    160543 | O      |          78 | NT            |         2
       46 |    1224 |    160551 | I      |          80 | NT            |         2
       46 |    1224 |    160550 | O      |          80 | NT            |         2
       46 |    1224 |    160534 | I      |          90 | NT            |         2
       46 |    1224 |    160544 | O      |          90 | NT            |         2
       47 |    1208 |    160180 | I      |          16 | NT            |         1
       47 |    1208 |    160175 | O      |          16 | NT            |         1
       47 |    1208 |    160173 | I      |          72 | NT            |         2
       47 |    1208 |    160182 | O      |          72 | NT            |         2
       47 |    1211 |    160253 | I      |         120 | ET            |         2
       47 |    1211 |    160236 | O      |         120 | ET            |         2
       47 |    1211 |    160237 | I      |          86 | NT            |         2
       47 |    1211 |    160243 | O      |          86 | NT            |         2
       47 |    1211 |    160251 | I      |         108 | NT            |         2
       47 |    1211 |    160248 | O      |         108 | NT            |         2
       48 |    1207 |    160158 | I      |          60 | NT            |         2
       48 |    1207 |    160159 | O      |          60 | NT            |         2
       48 |    1207 |    160146 | I      |          72 | NT            |         2
       48 |    1207 |    160145 | O      |          72 | NT            |         2
       48 |    1207 |    160151 | I      |          80 | NT            |         2
       48 |    1207 |    160154 | O      |          80 | NT            |         2
       48 |    1210 |    160214 | I      |          46 | NT            |         2
       48 |    1210 |    160227 | I      |          46 | NT            |         2
       48 |    1210 |    160220 | O      |          46 | NT            |         2
       48 |    1210 |    160226 | O      |          46 | NT            |         2
       48 |    1210 |    160228 | I      |          83 | NT            |         2
       48 |    1210 |    160230 | O      |          83 | NT            |         2
       49 |    1214 |    160311 | I      |          74 | NT            |         2
       49 |    1214 |    160316 | O      |          74 | NT            |         2
       49 |    1214 |    160314 | I      |          79 | NT            |         2
       49 |    1214 |    160310 | O      |          79 | NT            |         2
       49 |    1214 |    160321 | I      |          86 | NT            |         2
       49 |    1214 |    160320 | O      |          86 | NT            |         2
       49 |    1224 |    160551 | I      |          58 | NT            |         2
       49 |    1224 |    160543 | O      |          58 | NT            |         2
       49 |    1224 |    160548 | I      |          63 | NT            |         2
       49 |    1224 |    160550 | O      |          63 | NT            |         2
       49 |    1224 |    160546 | I      |          66 | NT            |         2
       49 |    1224 |    160534 | O      |          66 | NT            |         2
       50 |    1207 |    160152 | I      |          71 | NT            |         2
       50 |    1207 |    160154 | O      |          71 | NT            |         2
       50 |    1207 |    160158 | I      |          78 | NT            |         2
       50 |    1207 |    160159 | O      |          78 | NT            |         2
       50 |    1207 |    160150 | I      |           2 | ST            |         2
       50 |    1207 |    160160 | O      |           2 | ST            |         2
       50 |    1208 |    160170 | I      |          61 | NT            |         2
       50 |    1208 |    160165 | O      |          61 | NT            |         2
       50 |    1208 |    160174 | I      |          67 | NT            |         2
       50 |    1208 |    160172 | O      |          67 | NT            |         2
       50 |    1208 |    160178 | I      |          79 | NT            |         2
       50 |    1208 |    160180 | O      |          79 | NT            |         2
       51 |    1207 |    160161 | I      |         110 | ET            |         2
       51 |    1207 |    160154 | O      |         110 | ET            |         2
       51 |    1207 |    160151 | I      |          58 | NT            |         2
       51 |    1207 |    160154 | O      |          58 | NT            |         2
       51 |    1207 |    160158 | I      |          78 | NT            |         2
       51 |    1207 |    160159 | O      |          78 | NT            |         2
       51 |    1214 |    160321 | I      |          25 | NT            |         1
       51 |    1214 |    160322 | O      |          25 | NT            |         1
       51 |    1214 |    160314 | I      |          66 | NT            |         2
       51 |    1214 |    160310 | O      |          66 | NT            |         2
       51 |    1214 |    160319 | I      |          79 | NT            |         2
       51 |    1214 |    160316 | O      |          79 | NT            |         2	   	

Sample Solution:

-- This SQL query calculates the count of rows in the 'player_in_out' table where the 'play_schedule' column has the value 'ET'
-- and the 'in_out' column has the value 'I'.

SELECT COUNT(*) 
-- COUNT(*) is an aggregate function that counts the number of rows in a result set.
FROM  player_in_out 
-- 'player_in_out' is the name of the table being queried.
WHERE play_schedule='ET'
-- The WHERE clause filters rows where the 'play_schedule' column has the value 'ET'.
AND in_out='I';
-- Further filters rows where the 'in_out' column has the value 'I'.

Sample Output:

 count
-------
     9
(1 row)

Code Explanation:

The said query in SQL that returns a single row with a single column containing the count of players who entered the game ("I") during Extra Time ("ET") from the player_in_out table.

Relational Algebra Expression:

Relational Algebra Expression: Find the total number of players replaced in the extra time of play.

Relational Algebra Tree:

Relational Algebra Tree: Find the total number of players replaced in the extra time of play.

Practice Online



Sample Database: soccer

soccer database relationship structure

Query Visualization:

Duration:

Query visualization of Find the total number of players replaced in the extra time of play - Duration

Rows:

Query visualization of Find the total number of players replaced in the extra time of play - Rows

Cost:

Query visualization of Find the total number of players replaced in the extra time of play - Cost

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Total number of goalless draws in the tournament.
Next SQL Exercise: Number of substitutes for each stage of the tournament.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql-exercises/soccer-database-exercise/sql-basic-exercise-soccer-database-20.php