SQL Exercise: Matches end in a goalless draw in group stage of play
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
.........
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
........
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:
Go to:
PREV : Matches with most stoppage time added in the 2nd half.
NEXT : 2nd highest stoppage time in the 2nd half of matches.
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.