SQL Exercise: Number of players played for france in the final
SQL soccer Database: Subqueries Exercise-22 with Solution
22. From the following tables, write a SQL query to count the number of players played for 'France' in the final. Return 'Number of players shared fields'.
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 table: match_mast
match_no | play_stage | play_date | results | decided_by | goal_score | venue_id | referee_id | audence | plr_of_match | stop1_sec | stop2_sec
----------+------------+------------+---------+------------+------------+----------+------------+---------+--------------+-----------+-----------
1 | G | 2016-06-11 | WIN | N | 2-1 | 20008 | 70007 | 75113 | 160154 | 131 | 242
2 | G | 2016-06-11 | WIN | N | 0-1 | 20002 | 70012 | 33805 | 160476 | 61 | 182
3 | G | 2016-06-11 | WIN | N | 2-1 | 20001 | 70017 | 37831 | 160540 | 64 | 268
4 | G | 2016-06-12 | DRAW | N | 1-1 | 20005 | 70011 | 62343 | 160128 | 0 | 185
5 | G | 2016-06-12 | WIN | N | 0-1 | 20007 | 70006 | 43842 | 160084 | 125 | 325
6 | G | 2016-06-12 | WIN | N | 1-0 | 20006 | 70014 | 33742 | 160291 | 2 | 246
7 | G | 2016-06-13 | WIN | N | 2-0 | 20003 | 70002 | 43035 | 160176 | 89 | 188
8 | G | 2016-06-13 | WIN | N | 1-0 | 20010 | 70009 | 29400 | 160429 | 360 | 182
9 | G | 2016-06-13 | DRAW | N | 1-1 | 20008 | 70010 | 73419 | 160335 | 67 | 194
10 | G | 2016-06-14 | WIN | N | 0-2 | 20004 | 70005 | 55408 | 160244 | 63 | 189
11 | G | 2016-06-14 | WIN | N | 0-2 | 20001 | 70018 | 34424 | 160197 | 61 | 305
12 | G | 2016-06-15 | DRAW | N | 1-1 | 20009 | 70004 | 38742 | 160320 | 15 | 284
13 | G | 2016-06-15 | WIN | N | 1-2 | 20003 | 70001 | 38989 | 160405 | 62 | 189
14 | G | 2016-06-15 | DRAW | N | 1-1 | 20007 | 70015 | 43576 | 160477 | 74 | 206
15 | G | 2016-06-16 | WIN | N | 2-0 | 20005 | 70013 | 63670 | 160154 | 71 | 374
16 | G | 2016-06-16 | WIN | N | 2-1 | 20002 | 70003 | 34033 | 160540 | 62 | 212
17 | G | 2016-06-16 | WIN | N | 0-2 | 20004 | 70016 | 51043 | 160262 | 7 | 411
18 | G | 2016-06-17 | DRAW | N | 0-0 | 20008 | 70008 | 73648 | 160165 | 6 | 208
19 | G | 2016-06-17 | WIN | N | 1-0 | 20010 | 70007 | 29600 | 160248 | 2 | 264
20 | G | 2016-06-17 | DRAW | N | 2-2 | 20009 | 70005 | 38376 | 160086 | 71 | 280
21 | G | 2016-06-18 | WIN | N | 3-0 | 20006 | 70010 | 33409 | 160429 | 84 | 120
22 | G | 2016-06-18 | WIN | N | 3-0 | 20001 | 70004 | 39493 | 160064 | 11 | 180
23 | G | 2016-06-18 | DRAW | N | 1-1 | 20005 | 70015 | 60842 | 160230 | 61 | 280
24 | G | 2016-06-19 | DRAW | N | 0-0 | 20007 | 70011 | 44291 | 160314 | 3 | 200
25 | G | 2016-06-20 | WIN | N | 0-1 | 20004 | 70016 | 49752 | 160005 | 125 | 328
26 | G | 2016-06-20 | DRAW | N | 0-0 | 20003 | 70001 | 45616 | 160463 | 60 | 122
27 | G | 2016-06-21 | WIN | N | 0-3 | 20010 | 70006 | 28840 | 160544 | 62 | 119
28 | G | 2016-06-21 | DRAW | N | 0-0 | 20009 | 70012 | 39051 | 160392 | 62 | 301
29 | G | 2016-06-21 | WIN | N | 0-1 | 20005 | 70017 | 58874 | 160520 | 29 | 244
30 | G | 2016-06-21 | WIN | N | 0-1 | 20007 | 70018 | 44125 | 160177 | 21 | 195
31 | G | 2016-06-22 | WIN | N | 0-2 | 20002 | 70013 | 32836 | 160504 | 60 | 300
32 | G | 2016-06-22 | WIN | N | 2-1 | 20001 | 70008 | 37245 | 160085 | 70 | 282
33 | G | 2016-06-22 | WIN | N | 2-1 | 20008 | 70009 | 68714 | 160220 | 7 | 244
34 | G | 2016-06-22 | DRAW | N | 3-3 | 20004 | 70002 | 55514 | 160322 | 70 | 185
35 | G | 2016-06-23 | WIN | N | 0-1 | 20003 | 70014 | 44268 | 160333 | 79 | 221
36 | G | 2016-06-23 | WIN | N | 0-1 | 20006 | 70003 | 34011 | 160062 | 63 | 195
37 | R | 2016-06-25 | WIN | P | 1-1 | 20009 | 70005 | 38842 | 160476 | 126 | 243
38 | R | 2016-06-25 | WIN | N | 1-0 | 20007 | 70002 | 44342 | 160547 | 5 | 245
39 | R | 2016-06-26 | WIN | N | 0-1 | 20002 | 70012 | 33523 | 160316 | 61 | 198
40 | R | 2016-06-26 | WIN | N | 2-1 | 20004 | 70011 | 56279 | 160160 | 238 | 203
41 | R | 2016-06-26 | WIN | N | 3-0 | 20003 | 70009 | 44312 | 160173 | 62 | 124
42 | R | 2016-06-27 | WIN | N | 0-4 | 20010 | 70010 | 28921 | 160062 | 3 | 133
43 | R | 2016-06-27 | WIN | N | 2-0 | 20008 | 70004 | 76165 | 160235 | 63 | 243
44 | R | 2016-06-28 | WIN | N | 1-2 | 20006 | 70001 | 33901 | 160217 | 5 | 199
45 | Q | 2016-07-01 | WIN | P | 1-1 | 20005 | 70003 | 62940 | 160316 | 58 | 181
46 | Q | 2016-07-02 | WIN | N | 3-1 | 20003 | 70001 | 45936 | 160550 | 14 | 182
47 | Q | 2016-07-03 | WIN | P | 1-1 | 20001 | 70007 | 38764 | 160163 | 63 | 181
48 | Q | 2016-07-04 | WIN | N | 5-2 | 20008 | 70008 | 76833 | 160159 | 16 | 125
49 | S | 2016-07-07 | WIN | N | 2-0 | 20004 | 70006 | 55679 | 160322 | 2 | 181
50 | S | 2016-07-08 | WIN | N | 2-0 | 20005 | 70011 | 64078 | 160160 | 126 | 275
51 | F | 2016-07-11 | WIN | N | 1-0 | 20008 | 70005 | 75868 | 160307 | 161 | 181
Sample table: soccer_country
country_id | country_abbr | country_name
------------+--------------+---------------------
1201 | ALB | Albania
1202 | AUT | Austria
1203 | BEL | Belgium
1204 | CRO | Croatia
1205 | CZE | Czech Republic
1206 | ENG | England
1207 | FRA | France
1208 | GER | Germany
1209 | HUN | Hungary
1210 | ISL | Iceland
1211 | ITA | Italy
1212 | NIR | Northern Ireland
1213 | POL | Poland
1214 | POR | Portugal
1215 | IRL | Republic of Ireland
1216 | ROU | Romania
1217 | RUS | Russia
1218 | SVK | Slovakia
1219 | ESP | Spain
1220 | SWE | Sweden
1221 | SUI | Switzerland
1222 | TUR | Turkey
1223 | UKR | Ukraine
1224 | WAL | Wales
1225 | SLO | Slovenia
1226 | NED | Netherlands
1227 | SRB | Serbia
1228 | SCO | Scotland
1229 | NOR | Norway
Sample Solution:
SQL Code:
SELECT COUNT(*)+11 as "Number of players shared fields"
FROM player_in_out
WHERE match_no=(
SELECT match_no
FROM match_mast
WHERE play_stage='F')
AND in_out='I'
AND team_id=(
SELECT country_id
FROM soccer_country
WHERE country_name='France');
Sample Output:
Number of players shared fields
---------------------------------
14
(1 row)
Code Explanation:
The said query in SQL that returns the total number of players who played in the final match for the French team, including the starting eleven.
The subqueries have been used to find the match number of the final match, the team ID of the French team, and filters the 'player_in_out' table to only include players who were substituted in during the match.
The inner most subquery returns the ID of the French team,
and the subquery out of the inner most returns the match number for the final match, and the outer query counts the number of players who match these criteria and adds 11 for the starting eleven.
Alternative Solution:
Using JOINs and Subqueries:
SELECT COUNT(*) + 11 as "Number of players shared fields"
FROM player_in_out pio
JOIN match_mast mm ON pio.match_no = mm.match_no
JOIN soccer_country sc ON pio.team_id = sc.country_id
WHERE mm.play_stage = 'F'
AND pio.in_out = 'I'
AND sc.country_name = 'France';
Explanation:
This query uses JOIN operations to connect the player_in_out table with match_mast and soccer_country based on their respective IDs. It then applies the specified conditions to filter the results.
Using JOINs with Derived Tables:
SELECT COUNT(*) + 11 as "Number of players shared fields"
FROM (
SELECT *
FROM player_in_out
WHERE match_no = (
SELECT match_no
FROM match_mast
WHERE play_stage='F'
)
AND in_out='I'
AND team_id = (
SELECT country_id
FROM soccer_country
WHERE country_name='France'
)
) AS filtered_players;
Explanation:
This query uses derived tables to first filter the player_in_out table based on the specified conditions. Then, the outer query counts the number of rows in this filtered set and adds 11.
Using EXISTS:
SELECT COUNT(*) + 11 as "Number of players shared fields"
FROM player_in_out pio
WHERE EXISTS (
SELECT 1
FROM match_mast mm
JOIN soccer_country sc ON pio.team_id = sc.country_id
WHERE pio.match_no = mm.match_no
AND mm.play_stage = 'F'
AND pio.in_out = 'I'
AND sc.country_name = 'France'
);
Explanation:
This query uses the EXISTS keyword with a subquery to check if there exists a record in match_mast and soccer_country that satisfies the specified conditions.
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: The captain of Portugal EURO cup 2016 winning team.
Next SQL Exercise: German player who did not concede a goal in group stage.
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-subqueries-exercise-soccer-database-22.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics