SQL Exercise: Find the results of penalty shootout matches
59. From the following table, write a SQL query to find the results of penalty shootout matches. Return match number, play stage, country name and penalty score.
Sample table: match_details
match_no | play_stage | team_id | win_lose | decided_by | goal_score | penalty_score | ass_ref | player_gk
----------+------------+---------+----------+------------+------------+---------------+---------+-----------
1 | G | 1207 | W | N | 2 | | 80016 | 160140
1 | G | 1216 | L | N | 1 | | 80020 | 160348
2 | G | 1201 | L | N | 0 | | 80003 | 160001
2 | G | 1221 | W | N | 1 | | 80023 | 160463
3 | G | 1224 | W | N | 2 | | 80031 | 160532
3 | G | 1218 | L | N | 1 | | 80025 | 160392
4 | G | 1206 | D | N | 1 | | 80008 | 160117
4 | G | 1217 | D | N | 1 | | 80019 | 160369
5 | G | 1222 | L | N | 0 | | 80011 | 160486
5 | G | 1204 | W | N | 1 | | 80022 | 160071
6 | G | 1213 | W | N | 1 | | 80036 | 160279
6 | G | 1212 | L | N | 0 | | 80029 | 160256
7 | G | 1208 | W | N | 2 | | 80014 | 160163
7 | G | 1223 | L | N | 0 | | 80006 | 160508
8 | G | 1219 | W | N | 1 | | 80018 | 160416
8 | G | 1205 | L | N | 0 | | 80012 | 160093
9 | G | 1215 | D | N | 1 | | 80017 | 160324
9 | G | 1220 | D | N | 1 | | 80010 | 160439
10 | G | 1203 | L | N | 0 | | 80004 | 160047
10 | G | 1211 | W | N | 2 | | 80007 | 160231
11 | G | 1202 | L | N | 0 | | 80026 | 160024
11 | G | 1209 | W | N | 2 | | 80028 | 160187
12 | G | 1214 | D | N | 1 | | 80009 | 160302
12 | G | 1210 | D | N | 1 | | 80015 | 160208
13 | G | 1217 | L | N | 1 | | 80001 | 160369
13 | G | 1218 | W | N | 2 | | 80002 | 160392
14 | G | 1216 | D | N | 1 | | 80030 | 160348
14 | G | 1221 | D | N | 1 | | 80032 | 160463
15 | G | 1207 | W | N | 2 | | 80033 | 160140
15 | G | 1201 | L | N | 0 | | 80027 | 160001
16 | G | 1206 | W | N | 2 | | 80005 | 160117
16 | G | 1224 | L | N | 1 | | 80013 | 160531
17 | G | 1223 | L | N | 0 | | 80035 | 160508
17 | G | 1212 | W | N | 2 | | 80034 | 160256
18 | G | 1208 | D | N | 0 | | 80021 | 160163
18 | G | 1213 | D | N | 0 | | 80024 | 160278
19 | G | 1211 | W | N | 1 | | 80016 | 160231
19 | G | 1220 | L | N | 0 | | 80020 | 160439
20 | G | 1205 | D | N | 2 | | 80004 | 160093
20 | G | 1204 | D | N | 2 | | 80007 | 160071
21 | G | 1219 | W | N | 3 | | 80017 | 160416
21 | G | 1222 | L | N | 0 | | 80010 | 160486
22 | G | 1203 | W | N | 3 | | 80009 | 160047
22 | G | 1215 | L | N | 0 | | 80015 | 160324
23 | G | 1210 | D | N | 1 | | 80030 | 160208
23 | G | 1209 | D | N | 1 | | 80032 | 160187
24 | G | 1214 | D | N | 0 | | 80008 | 160302
24 | G | 1202 | D | N | 0 | | 80019 | 160024
25 | G | 1216 | L | N | 0 | | 80035 | 160348
25 | G | 1201 | W | N | 1 | | 80034 | 160001
26 | G | 1221 | D | N | 0 | | 80001 | 160463
26 | G | 1207 | D | N | 0 | | 80002 | 160140
27 | G | 1217 | L | N | 0 | | 80011 | 160369
27 | G | 1224 | W | N | 3 | | 80022 | 160531
28 | G | 1218 | D | N | 0 | | 80003 | 160392
28 | G | 1206 | D | N | 0 | | 80023 | 160117
29 | G | 1223 | L | N | 0 | | 80031 | 160508
29 | G | 1213 | W | N | 1 | | 80025 | 160278
30 | G | 1212 | L | N | 0 | | 80026 | 160256
30 | G | 1208 | W | N | 1 | | 80028 | 160163
31 | G | 1205 | L | N | 0 | | 80033 | 160093
31 | G | 1222 | W | N | 2 | | 80027 | 160486
32 | G | 1204 | W | N | 2 | | 80021 | 160071
32 | G | 1219 | L | N | 1 | | 80024 | 160416
33 | G | 1210 | W | N | 2 | | 80018 | 160208
33 | G | 1202 | L | N | 1 | | 80012 | 160024
34 | G | 1209 | D | N | 3 | | 80014 | 160187
34 | G | 1214 | D | N | 3 | | 80006 | 160302
35 | G | 1211 | L | N | 0 | | 80036 | 160233
35 | G | 1215 | W | N | 1 | | 80029 | 160324
36 | G | 1220 | L | N | 0 | | 80005 | 160439
36 | G | 1203 | W | N | 1 | | 80013 | 160047
37 | R | 1221 | L | P | 1 | 4 | 80004 | 160463
37 | R | 1213 | W | P | 1 | 5 | 80007 | 160278
38 | R | 1224 | W | N | 1 | | 80014 | 160531
38 | R | 1212 | L | N | 0 | | 80006 | 160256
39 | R | 1204 | L | N | 0 | | 80003 | 160071
39 | R | 1214 | W | N | 1 | | 80023 | 160302
40 | R | 1207 | W | N | 2 | | 80008 | 160140
40 | R | 1215 | L | N | 1 | | 80019 | 160324
41 | R | 1208 | W | N | 3 | | 80018 | 160163
41 | R | 1218 | L | N | 0 | | 80012 | 160392
42 | R | 1209 | L | N | 0 | | 80017 | 160187
42 | R | 1203 | W | N | 4 | | 80010 | 160047
43 | R | 1211 | W | N | 2 | | 80009 | 160231
43 | R | 1219 | L | N | 0 | | 80015 | 160416
44 | R | 1206 | L | N | 1 | | 80001 | 160117
44 | R | 1210 | W | N | 2 | | 80002 | 160208
45 | Q | 1213 | L | P | 1 | 3 | 80005 | 160278
45 | Q | 1214 | W | P | 1 | 5 | 80013 | 160302
46 | Q | 1224 | W | N | 3 | | 80001 | 160531
46 | Q | 1203 | L | N | 1 | | 80002 | 160047
47 | Q | 1208 | W | P | 1 | 6 | 80016 | 160163
47 | Q | 1211 | L | P | 1 | 5 | 80020 | 160231
48 | Q | 1207 | W | N | 5 | | 80021 | 160140
48 | Q | 1210 | L | N | 2 | | 80024 | 160208
49 | S | 1214 | W | N | 2 | | 80011 | 160302
49 | S | 1224 | L | N | 0 | | 80022 | 160531
50 | S | 1207 | W | N | 2 | | 80008 | 160140
50 | S | 1208 | L | N | 1 | | 80019 | 160163
51 | F | 1214 | W | N | 1 | | 80004 | 160302
51 | F | 1207 | L | N | 0 | | 80007 | 160140
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:
-- Selecting match information including match number, play stage, country name, and penalty score
SELECT
match_no, -- Selecting the match number
play_stage, -- Selecting the stage of play
country_name, -- Selecting the name of the country
penalty_score -- Selecting the penalty score
FROM
match_details a -- Selecting from the match_details table with alias 'a'
JOIN
soccer_country b ON a.team_id = b.country_id -- Joining with the soccer_country table with alias 'b' based on team ID
WHERE
decided_by = 'P' -- Filtering records where the match was decided by penalty
-- Ordering the results by match number
ORDER BY
match_no;
Sample Output:
match_no | play_stage | country_name | penalty_score
----------+------------+--------------+---------------
37 | R | Poland | 5
37 | R | Switzerland | 4
45 | Q | Poland | 3
45 | Q | Portugal | 5
47 | Q | Germany | 6
47 | Q | Italy | 5
(6 rows)
Code Explanation:
The said query in SQL that selects a list of matches that were decided by penalty shootouts, along with their relevant information such as the stage of play, the name of the country that played in the match, and the penalty score.
The JOIN clause combines match_details and soccer_country tables based on the team_id and country_id columns.
The WHERE clause filters the results to only include matches that were decided by penalties. The 'P' is an abbreviation for penalty shootouts.
The results are sorted by match number in ascending order.
Alternative Solutions:
Using a Subquery with IN Clause:
-- Selecting match information including match number, play stage, country name, and penalty score
SELECT
match_no, -- Selecting the match number
play_stage, -- Selecting the stage of play
country_name, -- Selecting the name of the country
penalty_score -- Selecting the penalty score
FROM
match_details a -- Selecting from the match_details table with alias 'a'
JOIN
soccer_country b ON a.team_id = b.country_id -- Joining with the soccer_country table with alias 'b' based on team ID
WHERE
decided_by = 'P' -- Filtering records where the match was decided by penalty
AND a.team_id IN ( --Subquery to filter team IDs present in the soccer_country table
SELECT
country_id -- Selecting the country IDs
FROM
soccer_country -- Subquery selecting from the soccer_country table
)
-- Ordering the results by match number
ORDER BY
match_no;
Explanation:
This query uses a subquery with the IN clause to filter the results based on the country_id. It ensures that the country_id is present in the soccer_country table.
Using EXISTS Clause:
-- Selecting match information including match number, play stage, country name, and penalty score
SELECT
match_no, -- Selecting the match number
play_stage, -- Selecting the stage of play
country_name, -- Selecting the name of the country
penalty_score -- Selecting the penalty score
FROM
match_details a -- Selecting from the match_details table with alias 'a'
JOIN
soccer_country b ON a.team_id = b.country_id -- Joining with the soccer_country table with alias 'b' based on team ID
WHERE
decided_by = 'P' -- Filtering records where the match was decided by penalty
AND EXISTS ( -- Checking for existence of matching records in soccer_country table
SELECT
1 -- Dummy column value to indicate existence
FROM
soccer_country c -- Subquery selecting from the soccer_country table with alias 'c'
WHERE
a.team_id = c.country_id -- Matching team IDs
)
-- Ordering the results by match number
ORDER BY
match_no;
Explanation:
This query uses the EXISTS clause to check for the existence of a matching country_id in the soccer_country table.
Relational Algebra Expression:
Relational Algebra Tree:
Go to:
PREV : Find the position of the player who scored an own goal.
NEXT : Goals scored by players based on their position.
Practice Online
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.