SQL Exercise: Find the most number of cards shown in the matches
SQL soccer Database: Joins Exercise-44 with Solution
44. From the following tables, write a SQL query to find the matches in which the most cards are displayed. Return match number, number of cards shown.
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:
-- Selecting the match number and the count of bookings from a derived table
SELECT
match_no, -- Selecting the match number
Booked -- Selecting the count of bookings
FROM
( -- Subquery to calculate the count of bookings for each match
SELECT
match_no, -- Selecting the match number
COUNT(*) AS Booked -- Counting the number of bookings for each match and aliasing it as 'Booked'
FROM
player_booked -- Specifying the player_booked table
GROUP BY
match_no -- Grouping the bookings by match number
) M1 -- Alias for the derived table
WHERE
Booked = ( -- Filtering out matches with the maximum count of bookings
SELECT
MAX(MX1) -- Selecting the maximum count of bookings across all matches
FROM
( -- Subquery to calculate the count of bookings for each match
SELECT
match_no, -- Selecting the match number
COUNT(*) AS MX1 -- Counting the number of bookings for each match and aliasing it as 'MX1'
FROM
player_booked -- Specifying the player_booked table
GROUP BY
match_no -- Grouping the bookings by match number
) M2 -- Alias for the derived table
);
Sample Output:
match_no | booked
----------+--------
51 | 10
(1 row)
Code Explanation:
The said query in SQL that selects the match number and the number of players booked for each match from the table 'player_booked'.
The subquery counts the number of players booked for each match and groups them by the match number.
The outer query selects only those rows where the number of players booked is equal to the maximum number of players booked across all matches. This is done by using a subquery to find the maximum number of players booked (MX1) and then selecting only those rows where the number of players booked is equal to MX1.
Alternative Solutions:
Using a Window Function:
-- Selecting the match number and the count of bookings from a derived table
SELECT
match_no, -- Selecting the match number
Booked -- Selecting the count of bookings
FROM
( -- Subquery to calculate the count of bookings for each match
SELECT
match_no, -- Selecting the match number
COUNT(*) AS Booked, -- Counting the number of bookings for each match and aliasing it as 'Booked'
MAX(COUNT(*)) OVER() AS MaxBooked -- Calculating the maximum count of bookings across all matches and assigning it to each row
FROM
player_booked -- Specifying the player_booked table
GROUP BY
match_no -- Grouping the bookings by match number
) M1 -- Alias for the derived table
-- Filtering out matches with the maximum count of bookings
WHERE
Booked = MaxBooked;
Explanation:
This query uses a window function to calculate the maximum count of bookings (MaxBooked). It then selects the matches where the booked count equals the maximum booked count.
Using a Subquery with GROUP BY and HAVING:
-- Selecting the match number and the count of bookings
SELECT
match_no, -- Selecting the match number
COUNT(*) AS Booked -- Counting the number of bookings for each match and aliasing it as 'Booked'
FROM
player_booked -- Specifying the player_booked table
GROUP BY
match_no -- Grouping the bookings by match number
HAVING
COUNT(*) = ( -- Filtering out matches where the count of bookings equals the maximum count of bookings across all matches
SELECT
MAX(Booked) -- Selecting the maximum count of bookings across all matches
FROM
( -- Subquery to calculate the count of bookings for each match
SELECT
COUNT(*) AS Booked -- Counting the number of bookings for each match and aliasing it as 'Booked'
FROM
player_booked -- Specifying the player_booked table
GROUP BY
match_no -- Grouping the bookings by match number
) M2 -- Alias for the derived table containing counts of bookings for each match
);
Explanation:
This query uses a subquery to first count the bookings for each match. It then selects the matches with the maximum count of bookings.
Using a Correlated Subquery:
-- Selecting the match number and the count of bookings for matches where the count of bookings equals the maximum count of bookings across all matches
SELECT
match_no, -- Selecting the match number
( -- Subquery to calculate the count of bookings for each match
SELECT
COUNT(*) -- Counting the number of bookings for each match
FROM
player_booked b -- Specifying the player_booked table with alias 'b'
WHERE
a.match_no = b.match_no -- Matching the outer match number with the inner match number
) AS Booked -- Aliasing the count of bookings as 'Booked'
FROM
( -- Subquery to select distinct match numbers
SELECT
DISTINCT match_no -- Selecting distinct match numbers
FROM
player_booked -- Specifying the player_booked table
) a -- Alias for the derived table containing distinct match numbers
WHERE
( -- Filtering out matches where the count of bookings equals the maximum count of bookings across all matches
SELECT
COUNT(*) -- Counting the number of bookings for each match
FROM
player_booked b -- Specifying the player_booked table with alias 'b'
WHERE
a.match_no = b.match_no -- Matching the outer match number with the inner match number
) = ( -- Comparing the count of bookings for each match with the maximum count of bookings across all matches
SELECT
MAX(MX1) -- Selecting the maximum count of bookings across all matches
FROM
( -- Subquery to calculate the count of bookings for each match and alias it as 'MX1'
SELECT
match_no, -- Selecting the match number
COUNT(*) AS MX1 -- Counting the number of bookings for each match and aliasing it as 'MX1'
FROM
player_booked -- Specifying the player_booked table
GROUP BY
match_no -- Grouping the bookings by match number
) M2 -- Alias for the derived table containing counts of bookings for each match ('MX1')
);
Explanation:
This query uses a correlated subquery to count the bookings for each match. It then selects the matches where the booked count equals the maximum booked count.
Practice Online
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Find the number of players booked for each team.
Next SQL Exercise: Each match assistant referee and their country.
Test your Programming skills with w3resource's quiz.
What is the difficulty level of this exercise?
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-joins-exercise-soccer-database-44.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics