SQL Exercise: Find the number of booking happened in stoppage time
SQL soccer Database: Exercise-28 with Solution
28. From the following table, write a SQL query to count the number of bookings during stoppage time.
Sample table: player_booked
match_no | team_id | player_id | booking_time | sent_off | play_schedule | play_half
----------+---------+-----------+--------------+----------+---------------+-----------
1 | 1216 | 160349 | 32 | | NT | 1
1 | 1216 | 160355 | 45 | | NT | 1
1 | 1207 | 160159 | 69 | Y | NT | 2
1 | 1216 | 160360 | 78 | | NT | 2
2 | 1221 | 160470 | 14 | | NT | 1
2 | 1201 | 160013 | 23 | | NT | 1
2 | 1201 | 160013 | 36 | | NT | 1
2 | 1201 | 160014 | 63 | | NT | 2
2 | 1221 | 160472 | 66 | | NT | 2
2 | 1201 | 160015 | 89 | | NT | 2
2 | 1201 | 160009 | 93 | | NT | 2
3 | 1218 | 160401 | 2 | | ST | 2
3 | 1218 | 160406 | 31 | | NT | 1
3 | 1218 | 160408 | 78 | | NT | 2
3 | 1218 | 160411 | 80 | | NT | 2
3 | 1218 | 160407 | 83 | | NT | 2
4 | 1206 | 160120 | 62 | | NT | 2
4 | 1217 | 160377 | 72 | | NT | 2
5 | 1222 | 160505 | 31 | | NT | 1
5 | 1222 | 160490 | 48 | | NT | 2
5 | 1204 | 160077 | 80 | | NT | 2
5 | 1222 | 160502 | 91 | | NT | 2
6 | 1213 | 160290 | 65 | | NT | 2
6 | 1212 | 160258 | 69 | | NT | 2
6 | 1213 | 160284 | 89 | | NT | 2
7 | 1223 | 160518 | 68 | | NT | 2
8 | 1205 | 160100 | 61 | | NT | 2
9 | 1215 | 160336 | 43 | | NT | 1
9 | 1220 | 160445 | 61 | | NT | 2
9 | 1215 | 160341 | 77 | | NT | 2
10 | 1211 | 160236 | 65 | | NT | 2
10 | 1211 | 160248 | 75 | | NT | 2
10 | 1211 | 160235 | 78 | | NT | 2
10 | 1211 | 160245 | 84 | | NT | 2
10 | 1203 | 160057 | 93 | | NT | 2
11 | 1202 | 160027 | 33 | | NT | 1
11 | 1202 | 160027 | 66 | Y | NT | 2
11 | 1209 | 160204 | 80 | | NT | 2
12 | 1210 | 160227 | 2 | | ST | 2
12 | 1210 | 160221 | 55 | | NT | 2
13 | 1218 | 160395 | 46 | | NT | 2
14 | 1221 | 160480 | 2 | | ST | 2
14 | 1216 | 160361 | 22 | | NT | 1
14 | 1216 | 160357 | 24 | | NT | 1
14 | 1216 | 160367 | 37 | | NT | 1
14 | 1221 | 160477 | 50 | | NT | 2
14 | 1216 | 160352 | 76 | | NT | 2
15 | 1201 | 160015 | 55 | | NT | 2
15 | 1201 | 160011 | 81 | | NT | 2
15 | 1207 | 160152 | 88 | | NT | 2
16 | 1224 | 160535 | 61 | | NT | 2
17 | 1223 | 160528 | 40 | | NT | 1
17 | 1212 | 160272 | 63 | | NT | 2
17 | 1223 | 160523 | 67 | | NT | 2
17 | 1212 | 160266 | 87 | | NT | 2
17 | 1212 | 160259 | 90 | | NT | 2
18 | 1208 | 160175 | 3 | | NT | 1
18 | 1213 | 160294 | 3 | | ST | 2
18 | 1208 | 160177 | 34 | | NT | 1
18 | 1213 | 160293 | 45 | | NT | 1
18 | 1213 | 160288 | 55 | | NT | 2
18 | 1208 | 160165 | 67 | | NT | 2
19 | 1211 | 160242 | 69 | | NT | 2
19 | 1220 | 160447 | 89 | | NT | 2
19 | 1211 | 160231 | 94 | | NT | 2
20 | 1204 | 160080 | 14 | | NT | 1
20 | 1205 | 160101 | 72 | | NT | 2
20 | 1204 | 160081 | 74 | | NT | 2
20 | 1204 | 160078 | 88 | | NT | 2
21 | 1219 | 160424 | 2 | | NT | 1
21 | 1222 | 160504 | 9 | | NT | 1
21 | 1222 | 160500 | 41 | | NT | 1
22 | 1215 | 160334 | 42 | | NT | 1
22 | 1203 | 160056 | 49 | | NT | 2
23 | 1209 | 160199 | 2 | | ST | 2
23 | 1210 | 160229 | 42 | | NT | 1
23 | 1210 | 160227 | 75 | | NT | 2
23 | 1210 | 160216 | 77 | | NT | 2
23 | 1209 | 160192 | 81 | | NT | 2
23 | 1209 | 160197 | 83 | | NT | 2
24 | 1202 | 160028 | 6 | | NT | 1
24 | 1214 | 160321 | 31 | | NT | 1
24 | 1214 | 160307 | 40 | | NT | 1
24 | 1202 | 160037 | 47 | | NT | 2
24 | 1202 | 160029 | 78 | | NT | 2
24 | 1202 | 160042 | 86 | | NT | 2
25 | 1201 | 160012 | 6 | | NT | 1
25 | 1216 | 160353 | 54 | | NT | 2
25 | 1201 | 160017 | 85 | | NT | 2
25 | 1216 | 160356 | 85 | | NT | 2
25 | 1216 | 160364 | 91 | | NT | 2
25 | 1201 | 160007 | 95 | | NT | 2
26 | 1207 | 160147 | 25 | | NT | 1
26 | 1207 | 160145 | 83 | | NT | 2
27 | 1224 | 160551 | 16 | | NT | 1
27 | 1217 | 160383 | 64 | | NT | 2
28 | 1218 | 160409 | 24 | | NT | 1
28 | 1206 | 160119 | 52 | | NT | 2
29 | 1223 | 160520 | 25 | | NT | 1
29 | 1223 | 160513 | 38 | | NT | 1
29 | 1213 | 160290 | 60 | | NT | 2
31 | 1222 | 160491 | 35 | | NT | 1
31 | 1205 | 160108 | 36 | | NT | 1
31 | 1205 | 160107 | 39 | Y | NT | 1
31 | 1222 | 160490 | 50 | | NT | 2
31 | 1205 | 160112 | 87 | | NT | 2
32 | 1204 | 160087 | 29 | | NT | 1
32 | 1204 | 160079 | 70 | | NT | 2
32 | 1204 | 160076 | 70 | | NT | 2
32 | 1204 | 160085 | 88 | | NT | 2
33 | 1210 | 160218 | 36 | | NT | 1
33 | 1210 | 160230 | 51 | | NT | 2
33 | 1202 | 160045 | 70 | | NT | 2
33 | 1210 | 160220 | 78 | | NT | 2
33 | 1210 | 160208 | 82 | | NT | 2
34 | 1209 | 160190 | 13 | | NT | 1
34 | 1209 | 160191 | 28 | | NT | 1
34 | 1209 | 160203 | 34 | | NT | 1
34 | 1209 | 160202 | 56 | | NT | 2
35 | 1211 | 160233 | 39 | | NT | 1
35 | 1215 | 160343 | 39 | | NT | 1
35 | 1215 | 160332 | 73 | | NT | 2
35 | 1211 | 160234 | 78 | | NT | 2
35 | 1211 | 160253 | 87 | | NT | 2
35 | 1211 | 160251 | 93 | | NT | 2
36 | 1203 | 160064 | 1 | | ST | 1
36 | 1203 | 160055 | 30 | | NT | 1
36 | 1220 | 160451 | 72 | | NT | 2
37 | 1221 | 160470 | 55 | | NT | 2
37 | 1213 | 160282 | 58 | | NT | 2
38 | 1212 | 160266 | 44 | | NT | 1
38 | 1224 | 160538 | 58 | | NT | 2
38 | 1212 | 160267 | 67 | | NT | 2
38 | 1224 | 160544 | 92 | | NT | 2
39 | 1214 | 160318 | 78 | | NT | 2
40 | 1215 | 160328 | 25 | | NT | 1
40 | 1207 | 160152 | 27 | | NT | 1
40 | 1215 | 160334 | 41 | | NT | 1
40 | 1207 | 160147 | 44 | | NT | 1
40 | 1215 | 160329 | 66 | Y | NT | 2
40 | 1215 | 160343 | 72 | | NT | 2
41 | 1218 | 160407 | 2 | | ST | 2
41 | 1218 | 160401 | 13 | | NT | 1
41 | 1208 | 160169 | 46 | | NT | 2
41 | 1208 | 160168 | 67 | | NT | 2
42 | 1209 | 160192 | 34 | | NT | 1
42 | 1209 | 160194 | 47 | | NT | 2
42 | 1209 | 160196 | 61 | | NT | 2
42 | 1203 | 160056 | 67 | | NT | 2
42 | 1203 | 160065 | 89 | | NT | 2
42 | 1203 | 160061 | 91 | | NT | 2
42 | 1209 | 160207 | 92 | | NT | 2
43 | 1219 | 160431 | 2 | | ST | 2
43 | 1211 | 160238 | 24 | | NT | 1
43 | 1219 | 160436 | 41 | | NT | 1
43 | 1211 | 160252 | 54 | | NT | 2
43 | 1211 | 160245 | 89 | | NT | 2
43 | 1219 | 160427 | 89 | | NT | 2
43 | 1219 | 160421 | 89 | | NT | 2
44 | 1210 | 160208 | 38 | | NT | 1
44 | 1206 | 160137 | 47 | | NT | 2
44 | 1210 | 160222 | 65 | | NT | 2
45 | 1214 | 160318 | 2 | | ST | 2
45 | 1213 | 160282 | 42 | | NT | 1
45 | 1213 | 160281 | 66 | | NT | 2
45 | 1214 | 160310 | 70 | | NT | 2
45 | 1213 | 160290 | 89 | | NT | 2
46 | 1224 | 160535 | 5 | | NT | 1
46 | 1224 | 160533 | 16 | | NT | 1
46 | 1224 | 160536 | 24 | | NT | 1
46 | 1203 | 160061 | 59 | | NT | 2
46 | 1224 | 160544 | 75 | | NT | 2
46 | 1203 | 160050 | 85 | | NT | 2
47 | 1211 | 160247 | 56 | | NT | 2
47 | 1211 | 160238 | 57 | | NT | 2
47 | 1211 | 160246 | 59 | | NT | 2
47 | 1208 | 160168 | 90 | | NT | 2
47 | 1208 | 160180 | 112 | | NT | 2
48 | 1210 | 160221 | 58 | | NT | 2
48 | 1207 | 160149 | 75 | | NT | 2
49 | 1224 | 160540 | 8 | | NT | 1
49 | 1224 | 160533 | 62 | | NT | 2
49 | 1214 | 160303 | 71 | | NT | 2
49 | 1214 | 160322 | 72 | | NT | 2
49 | 1224 | 160547 | 88 | | NT | 2
50 | 1208 | 160177 | 1 | | ST | 1
50 | 1208 | 160172 | 36 | | NT | 1
50 | 1207 | 160143 | 43 | | NT | 1
50 | 1208 | 160180 | 45 | | NT | 1
50 | 1208 | 160173 | 50 | | NT | 2
50 | 1207 | 160152 | 75 | | NT | 2
51 | 1214 | 160304 | 34 | | NT | 1
51 | 1214 | 160313 | 62 | | NT | 2
51 | 1207 | 160149 | 80 | | NT | 2
51 | 1214 | 160308 | 95 | | ET | 1
51 | 1207 | 160153 | 97 | | ET | 1
51 | 1214 | 160318 | 98 | | ET | 1
51 | 1207 | 160145 | 107 | | ET | 2
51 | 1207 | 160155 | 115 | | ET | 2
51 | 1214 | 160306 | 119 | | ET | 2
51 | 1214 | 160302 | 122 | | ET | 2
Sample Solution:
SQL Code:
-- This SQL query calculates the count of rows in the 'player_booked' table where the 'play_schedule' column has the value 'ST'.
SELECT COUNT(*)
-- COUNT(*) is an aggregate function that counts the number of rows in a result set.
FROM player_booked
-- 'player_booked' is the name of the table being queried.
WHERE play_schedule='ST';
-- The WHERE clause filters rows where the 'play_schedule' column has the value 'ST'.
Sample Output:
count
-------
10
(1 row)
Code Explanation:
The said query in SQL that determine the number of players who have been booked during stoppage time of a game.
The WHERE clause specifies a condition to filter the rows where the value of "play_schedule" is equal to 'ST'.
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: Within each half of play, find the number of bookings.
Next SQL Exercise: Find the number of booking happened in extra time.
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://w3resource.com/sql-exercises/soccer-database-exercise/sql-basic-exercise-soccer-database-28.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics