w3resource

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 Expression: Find the matchs ending with a goalless draw in group stage of play.

Relational Algebra Tree:

Relational Algebra Tree: Find the matchs ending with a goalless draw in group stage of play.

Practice Online


Sample Database: soccer

soccer database relationship structure

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.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

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