SQL Exercise: Within each half of play, find the number of bookings
SQL soccer Database: Exercise-27 with Solution
27. From the following table, write a SQL query to count the number of bookings in each half of play within the normal play schedule. Return play_half, play_schedule, number of booking happened.
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 bookings for each play half and play schedule in the 'player_booked' table.
SELECT play_half, play_schedule, COUNT(*)
-- Selects the 'play_half', 'play_schedule', and the count of rows for each group.
FROM player_booked
-- 'player_booked' is the name of the table being queried.
WHERE play_schedule='NT'
-- The WHERE clause filters rows where the 'play_schedule' column has the value 'NT'.
GROUP BY play_half, play_schedule;
-- Groups the results by 'play_half' and 'play_schedule'.
Sample Output:
play_half | play_schedule | count
-----------+---------------+-------
1 | NT | 61
2 | NT | 123
(2 rows)
Code Explanation:
The said query in SQL will retrieve "play_half", "play_schedule", and number of players booked in normal time schedule from the 'player_booked' table.
The WHERE clause filter the rows where the value of "play_schedule" is equal to 'NT'
A GROUP BY groups the results by play_half, play_schedule columns.
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: Find the number of penalty shots taken by the teams.
Next SQL Exercise: Find the number of booking happened in stoppage time.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics