SQL Exercise: Number of substitutes for each stage of the tournament
SQL soccer Database: Basic Exercise-21 with Solution
21. From the following table, write a SQL query to count the number of substitutes during various stages of the tournament. Sort the result-set in ascending order by play-half, play-schedule and number of substitute happened. Return play-half, play-schedule, number of substitute happened.
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 retrieves the 'play_half', 'play_schedule', and the count of rows for each group from the 'player_in_out' table,
-- where the 'in_out' column has the value 'I'. It then groups the results by 'play_half' and 'play_schedule',
-- and orders the result set in descending order based on the count.
SELECT play_half, play_schedule, COUNT(*)
-- Selects the 'play_half', 'play_schedule', and the count of rows for each group.
FROM player_in_out
-- 'player_in_out' is the name of the table being queried.
WHERE in_out='I'
-- The WHERE clause filters rows where the 'in_out' column has the value 'I'.
GROUP BY play_half, play_schedule
-- Groups the results by 'play_half' and 'play_schedule'.
ORDER BY play_half, play_schedule, COUNT(*) DESC;
-- Orders the result set first by 'play_half' and 'play_schedule', and then by the count of rows in descending order.
Sample Output:
play_half | play_schedule | count
-----------+---------------+-------
1 | ET | 4
1 | NT | 3
2 | ET | 5
2 | NT | 272
2 | ST | 9
(5 rows)
Code Explanation:
The said query in SQL that returns multiple rows with columns play_half, play_schedule, and the count of players who entered the game ("I") in each group. The results will be grouped first by play half and then by play schedule, and sorted in ascending order by play half, play schedule, and descending order by the count of players in each group.
Relational Algebra Expression:
Relational Algebra Tree:
Practice Online
Sample Database: soccer
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Number of players replaced in the extra time of play.
Next SQL Exercise: Number of shots taken in penalty shootout matches.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
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-21.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics