SQL Exercise: Matches end in a goalless draw in group stage of play
SQL soccer Database: Joins Exercise-22 with Solution
22. From the following tables, write a SQL query to find the matches that ended in a goalless draw at the group stage. Return match number, country name.
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:
-- This query selects specific columns from the match_details table and the soccer_country table.
SELECT
match_no, -- Selecting the match number from the match_details table
country_name -- Selecting the country name from the soccer_country table
FROM
match_details -- Specifying the match_details table
JOIN
soccer_country ON soccer_country.country_id = match_details.team_id -- Joining the soccer_country table with the match_details table based on the team_id
WHERE
win_lose = 'D' -- Filtering rows where the match resulted in a draw
AND goal_score = 0 -- Filtering rows where the goal score is zero
AND play_stage = 'G' -- Filtering rows where the play stage is 'G' (presumably group stage)
ORDER BY
-- Ordering the results by match number
match_no;
Sample Output:
match_no | country_name
----------+--------------
18 | Germany
18 | Poland
24 | Austria
24 | Portugal
26 | Switzerland
26 | France
28 | England
28 | Slovakia
(8 rows)
Code Explanation:
The said query in SQL that selects the match number and country name of all soccer matches from the tables 'match_details' and 'soccer_country' where the result was a draw, no goals were scored , and the match was played during the group stage . The results are ordered by match number.
The INNER JOIN is used to joins the 'match_details' and 'soccer_country' tables based on their common column 'team_id' and 'country_id', respectively.
The WHERE clause filters the results and include those rows for goal scored is 0 and match ended with a draw and the match was in group stage. The ORDER BY clause sorts the results by match number.
Alternative Solutions:
Subquery in WHERE Clause:
-- This query selects specific columns from the match_details table and the soccer_country table.
SELECT
match_no, -- Selecting the match number from the match_details table
country_name -- Selecting the country name from the soccer_country table
FROM
match_details -- Specifying the match_details table
JOIN
soccer_country ON soccer_country.country_id = match_details.team_id -- Joining the soccer_country table with the match_details table based on the team_id
WHERE
win_lose = 'D' -- Filtering rows where the match resulted in a draw
AND goal_score = 0 -- Filtering rows where the goal score is zero
AND play_stage = 'G' -- Filtering rows where the play stage is 'G' (presumably group stage)
AND match_no IN ( -- Ensuring that the match number is present in the subquery result
SELECT
match_no -- Selecting the match numbers
FROM
match_details -- Specifying the match_details table for the subquery
WHERE
win_lose = 'D' -- Filtering rows where the match resulted in a draw
AND goal_score = 0 -- Filtering rows where the goal score is zero
AND play_stage = 'G' -- Filtering rows where the play stage is 'G' (presumably group stage)
)
ORDER BY
-- Ordering the results by match number
match_no;
Explanation:
This query uses a subquery in the WHERE clause to filter the matches. The subquery selects match numbers where the conditions win_lose='D', goal_score=0, and play_stage='G' are met. The main query then checks if the match number is in the list returned by the subquery.
Using EXISTS:
-- This query selects specific columns from the match_details table and the soccer_country table.
SELECT
md.match_no, -- Selecting the match number from the match_details table
sc.country_name -- Selecting the country name from the soccer_country table
FROM
match_details md -- Specifying the match_details table with an alias 'md'
JOIN
soccer_country sc ON md.team_id = sc.country_id -- Joining the soccer_country table with the match_details table based on the team_id
WHERE
win_lose = 'D' -- Filtering rows where the match resulted in a draw
AND goal_score = 0 -- Filtering rows where the goal score is zero
AND play_stage = 'G' -- Filtering rows where the play stage is 'G' (presumably group stage)
AND EXISTS ( -- Ensuring that there exists a record in the subquery matching certain conditions
SELECT
1 -- Selecting a constant value '1' to indicate existence
FROM
match_details md2 -- Specifying the match_details table for the subquery with an alias 'md2'
WHERE
md2.match_no = md.match_no -- Matching the match numbers between the main query and the subquery
AND md2.win_lose = 'D' -- Filtering rows where the match resulted in a draw in the subquery
AND md2.goal_score = 0 -- Filtering rows where the goal score is zero in the subquery
AND md2.play_stage = 'G' -- Filtering rows where the play stage is 'G' (presumably group stage) in the subquery
)
ORDER BY
-- Ordering the results by match number
md.match_no;
Explanation:
This query uses an EXISTS subquery to check if there's at least one record in match_details with the specified conditions for each match. It joins match_details and soccer_country, and then uses the EXISTS clause to filter the results.
Using INNER JOIN with Subquery:
-- This query selects specific columns from the match_details table and the soccer_country table.
SELECT
md.match_no, -- Selecting the match number from the match_details table
sc.country_name -- Selecting the country name from the soccer_country table
FROM
( -- Subquery to filter matches meeting certain conditions and group them by match number
SELECT
match_no -- Selecting match numbers
FROM
match_details -- Specifying the match_details table
WHERE
win_lose = 'D' -- Filtering rows where the match resulted in a draw
AND goal_score = 0 -- Filtering rows where the goal score is zero
AND play_stage = 'G' -- Filtering rows where the play stage is 'G' (presumably group stage)
GROUP BY
match_no -- Grouping the matches by match number
) as filtered_matches -- Aliasing the subquery as 'filtered_matches'
JOIN
match_details md ON filtered_matches.match_no = md.match_no -- Joining the filtered matches with match_details table based on match number
JOIN
soccer_country sc ON md.team_id = sc.country_id -- Joining the soccer_country table with the match_details table based on the team_id
ORDER BY
-- Ordering the results by match number
md.match_no;
Explanation:
This query first creates a subquery (filtered_matches) to select match numbers where the specified conditions are met. It then joins this subquery with match_details and soccer_country to retrieve the desired columns.
Relational Algebra Expression:
Relational Algebra Tree:
Practice Online
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Matches with most stoppage time added in the 2nd half.
Next SQL Exercise: 2nd highest stoppage time in the 2nd half of matches.
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-joins-exercise-soccer-database-22.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics