w3resource

SQL Exercise: Two teams that scored three goals in a single game

SQL soccer Database: Joins Exercise-27 with Solution

27. From the following tables, write a SQL query to find the two teams in this tournament that have scored three goals in a single game. Return match number and 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 the match number and country name where a team scored 3 goals and the match ended in a draw.

SELECT 
    match_no, -- Selecting the match number from the match_details table aliased as 'a'
    country_name -- Selecting the country name from the soccer_country table aliased as 'b'
FROM 
    match_details a -- Specifying the match_details table with an alias 'a'
JOIN 
    soccer_country b ON a.team_id = b.country_id -- Joining the soccer_country table with the match_details table based on the team_id
WHERE 
    goal_score = 3  -- Filtering rows where the goal score is 3
	-- Filtering rows where the match ended in a draw
    AND win_lose = 'D'; 

Sample Output:

 match_no | country_name
----------+--------------
       34 | Hungary
       34 | Portugal
(2 rows)

Code Explanation:

The given query in SQL that retrieves the match number and country name of all matches where the goal score is 3 and the match resulted in a draw.
The uses of JOIN operation combines the information from two tables, match_details and soccer_country, based on their corresponding columns team_id and country_id, respectively.
The WHERE clause filters the results to only include matches with a goal score of 3 and a win/lose result of 'D' (draw).

Alternative Solutions:

Subquery with WHERE Clause:

-- This query selects the match number and country name where a team scored exactly 3 goals and the match ended in a draw.

SELECT 
    match_no, -- Selecting the match number from the match_details table aliased as 'a'
    country_name -- Selecting the country name from the soccer_country table aliased as 'b'
FROM 
    match_details a -- Specifying the match_details table with an alias 'a'
JOIN 
    soccer_country b ON a.team_id = b.country_id -- Joining the soccer_country table with the match_details table based on the team_id
WHERE 
    ( -- Subquery to count the number of goals scored by the team in each match
        SELECT COUNT(*) 
        FROM goal_details gd 
        WHERE gd.match_no = a.match_no AND gd.team_id = a.team_id
    ) = 3 -- Checking if the team scored exactly 3 goals in the match
	-- Filtering rows where the match ended in a draw
    AND a.win_lose = 'D'; 

Explanation:

This query uses a subquery in the WHERE clause to count the number of goals scored by a specific team in a match. It selects matches where the team scored exactly 3 goals and had a result of a draw.

GROUP BY and HAVING Clause:

-- This query selects the match number and country name where a team scored exactly 3 goals in a match and the match ended in a draw.

SELECT 
    a.match_no, -- Selecting the match number from the match_details table aliased as 'a'
    b.country_name -- Selecting the country name from the soccer_country table aliased as 'b'
FROM 
    match_details a -- Specifying the match_details table with an alias 'a'
JOIN 
    soccer_country b ON a.team_id = b.country_id -- Joining the soccer_country table with the match_details table based on the team_id
JOIN 
    goal_details gd ON a.match_no = gd.match_no AND a.team_id = gd.team_id -- Joining the goal_details table with the match_details table based on match number and team ID
GROUP BY 
    a.match_no, b.country_name, a.win_lose -- Grouping the results by match number, country name, and match result
HAVING 
    COUNT(gd.goal_id) = 3 -- Ensuring that the team scored exactly 3 goals in the match
	-- Filtering rows where the match ended in a draw
    AND a.win_lose = 'D'; 

Explanation:

This query uses GROUP BY and HAVING clauses to group matches by match_no, country_name, and win_lose. It then selects matches where the team scored exactly 3 goals and had a result of a draw.

Using a JOIN with Subquery:

-- This query selects the match number and country name where a team scored exactly 3 goals in a match and the match ended in a draw.

SELECT 
    a.match_no, -- Selecting the match number from the match_details table aliased as 'a'
    b.country_name -- Selecting the country name from the soccer_country table aliased as 'b'
FROM 	
    match_details a -- Specifying the match_details table with an alias 'a'
JOIN 
    soccer_country b ON a.team_id = b.country_id -- Joining the soccer_country table with the match_details table based on the team_id
JOIN 
    (
        -- Subquery to find matches where a team scored exactly 3 goals
        SELECT 
            match_no, team_id
        FROM 
            goal_details
        GROUP BY 
            match_no, team_id
        HAVING 
            COUNT(goal_id) = 3 -- Ensuring that the team scored exactly 3 goals in each match
    ) gd ON a.match_no = gd.match_no AND a.team_id = gd.team_id -- Joining the subquery results with the match_details table based on match number and team ID
	-- Filtering rows where the match ended in a draw
WHERE 
    a.win_lose = 'D'; 

Explanation:

This query uses a subquery to find matches where a team scored exactly 3 goals. It then joins this subquery with match_details and soccer_country to get the desired result.

Using EXISTS Clause:

-- This query selects the match number and country name where a team scored exactly 3 goals in a match and the match ended in a draw.

SELECT 
    match_no, -- Selecting the match number from the match_details table aliased as 'a'
    country_name -- Selecting the country name from the soccer_country table aliased as 'b'
FROM 
    match_details a -- Specifying the match_details table with an alias 'a'
JOIN 
    soccer_country b ON a.team_id = b.country_id -- Joining the soccer_country table with the match_details table based on the team_id
WHERE 
    EXISTS ( -- Checking for the existence of at least one record that satisfies the condition within the subquery
        SELECT 1 -- Selecting 1 as a placeholder value (could be any value since we're using EXISTS)
        FROM 
            goal_details gd -- Specifying the goal_details table with an alias 'gd'
        WHERE 
            gd.match_no = a.match_no -- Matching the match number between the outer query and the subquery
            AND gd.team_id = a.team_id -- Matching the team ID between the outer query and the subquery
        HAVING 
            COUNT(gd.goal_id) = 3 -- Ensuring that the team scored exactly 3 goals in the match
    )
	-- Filtering rows where the match ended in a draw
    AND a.win_lose = 'D'; 

Explanation:

This query uses the EXISTS clause with a subquery to check if there exists a match where a team scored exactly 3 goals. It then filters based on the win_lose condition.

Relational Algebra Expression:

Relational Algebra Expression: Find those two teams which scored three goals in a single game at this tournament.

Relational Algebra Tree:

Relational Algebra Tree: Find those two teams which scored three goals in a single game at this tournament.

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: Oldest player to have played in a EURO cup 2016 match.
Next SQL Exercise: Bottom of their groups and conceded 4 goals in 3 games.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.