﻿ SQL: Number of substitutes for each stage of the tournament

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.

﻿