SQL Exercise: Two teams that scored three goals in a single game
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
.......
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 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 Tree:
Go to:
PREV : Oldest player to have played in a EURO cup 2016 match.
NEXT : Bottom of their groups and conceded 4 goals in 3 games.
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.