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 Tree:
Practice Online
Sample Database: soccer
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.
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://w3resource.com/sql-exercises/soccer-database-exercise/sql-joins-exercise-soccer-database-27.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics