SQL Exercise: Bottom of their groups and conceded 4 goals in 3 games
28. From the following tables, write a SQL query to find which teams finished at the bottom of their respective groups after conceding four goals in three games. Return country name, team group and match played.
Sample table: soccer_team
team_id | team_group | match_played | won | draw | lost | goal_for | goal_agnst | goal_diff | points | group_position
---------+------------+--------------+-----+------+------+----------+------------+-----------+--------+----------------
1201 | A | 3 | 1 | 0 | 2 | 1 | 3 | -2 | 3 | 3
1202 | F | 3 | 0 | 1 | 2 | 1 | 4 | -3 | 1 | 4
1203 | E | 3 | 2 | 0 | 1 | 4 | 2 | 2 | 6 | 2
1204 | D | 3 | 2 | 1 | 0 | 5 | 3 | 2 | 7 | 1
1205 | D | 3 | 0 | 1 | 2 | 2 | 5 | -3 | 1 | 4
1206 | B | 3 | 1 | 2 | 0 | 3 | 2 | 1 | 5 | 2
1207 | A | 3 | 2 | 1 | 0 | 4 | 1 | 3 | 7 | 1
1208 | C | 3 | 2 | 1 | 0 | 3 | 0 | 3 | 7 | 1
1209 | F | 3 | 1 | 2 | 0 | 6 | 4 | 2 | 5 | 1
1210 | F | 3 | 1 | 2 | 0 | 4 | 3 | 1 | 5 | 2
1211 | E | 3 | 2 | 0 | 1 | 3 | 1 | 2 | 6 | 1
1212 | C | 3 | 1 | 0 | 2 | 2 | 2 | 0 | 3 | 3
1213 | C | 3 | 2 | 1 | 0 | 2 | 0 | 2 | 7 | 2
1214 | F | 3 | 0 | 3 | 0 | 4 | 4 | 0 | 3 | 3
1215 | E | 3 | 1 | 1 | 1 | 2 | 4 | -2 | 4 | 3
1216 | A | 3 | 0 | 1 | 2 | 2 | 4 | -2 | 1 | 4
1217 | B | 3 | 0 | 1 | 2 | 2 | 6 | -4 | 1 | 4
1218 | B | 3 | 1 | 1 | 1 | 3 | 3 | 0 | 4 | 3
1219 | D | 3 | 2 | 0 | 1 | 5 | 2 | 3 | 6 | 2
1220 | E | 3 | 0 | 1 | 2 | 1 | 3 | -2 | 1 | 4
1221 | A | 3 | 1 | 2 | 0 | 2 | 1 | 1 | 5 | 2
1222 | D | 3 | 1 | 0 | 2 | 2 | 4 | -2 | 3 | 3
1223 | C | 3 | 0 | 0 | 3 | 0 | 5 | -5 | 0 | 4
1224 | B | 3 | 2 | 0 | 1 | 6 | 3 | 3 | 6 | 1
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 team information such as country name, team group, matches played, goals conceded, and group position.
SELECT
a.country_name as Team, -- Selecting the country name from the soccer_country table aliased as 'a' and aliasing it as 'Team'
b.team_group, -- Selecting the team group from the soccer_team table aliased as 'b'
b.match_played, -- Selecting the number of matches played from the soccer_team table aliased as 'b'
b.goal_agnst, -- Selecting the number of goals conceded from the soccer_team table aliased as 'b'
b.group_position -- Selecting the group position from the soccer_team table aliased as 'b'
FROM
soccer_country a -- Specifying the soccer_country table with an alias 'a'
JOIN
soccer_team b ON a.country_id = b.team_id -- Joining the soccer_team table with the soccer_country table based on the team_id
WHERE
goal_agnst = 4 -- Filtering teams that conceded 4 goals
AND group_position = 4 -- Filtering teams that are in the 4th group position
-- Ordering the results by team group
ORDER BY
team_group;
Sample Output:
team | team_group | match_played | goal_agnst | group_position ---------+------------+--------------+------------+---------------- Romania | A | 3 | 4 | 4 Austria | F | 3 | 4 | 4 (2 rows)
Code Explanation:
The said query in SQL which selects the country name, team group, match played, goals against, and group position from the tables 'soccer_country' and 'soccer_team'.
The WHERE clause filters the results to only include teams that have conceded four goals and finished fourth in their respective groups, then orders the results by team group.
Alternative Solutions:
Using JOIN with WHERE Clause:
-- This query selects team information such as country name, team group, matches played, goals conceded, and group position.
SELECT
a.country_name as Team, -- Selecting the country name from the soccer_country table aliased as 'a' and aliasing it as 'Team'
b.team_group, -- Selecting the team group from the soccer_team table aliased as 'b'
b.match_played, -- Selecting the number of matches played from the soccer_team table aliased as 'b'
b.goal_agnst, -- Selecting the number of goals conceded from the soccer_team table aliased as 'b'
b.group_position -- Selecting the group position from the soccer_team table aliased as 'b'
FROM
soccer_country a -- Specifying the soccer_country table with an alias 'a'
JOIN
soccer_team b ON a.country_id = b.team_id -- Joining the soccer_team table with the soccer_country table based on the team_id
WHERE
b.goal_agnst = 4 -- Filtering teams that conceded 4 goals
AND b.group_position = 4 -- Filtering teams that are in the 4th group position
AND b.team_group IS NOT NULL -- Filtering out teams with a non-null team group
-- Ordering the results by team group
ORDER BY
b.team_group;
Explanation:
This query uses a WHERE clause to filter teams with 4 goals against and in 4th position. It also checks that team_group is not null to ensure completeness of data. The result is then ordered by team_group.
Using a Subquery in JOIN:
-- This query selects team information such as country name, team group, matches played, goals conceded, and group position.
SELECT
a.country_name as Team, -- Selecting the country name from the soccer_country table aliased as 'a' and aliasing it as 'Team'
b.team_group, -- Selecting the team group from the subquery aliased as 'b'
b.match_played, -- Selecting the number of matches played from the subquery aliased as 'b'
b.goal_agnst, -- Selecting the number of goals conceded from the subquery aliased as 'b'
b.group_position -- Selecting the group position from the subquery aliased as 'b'
FROM
soccer_country a -- Specifying the soccer_country table with an alias 'a'
JOIN
(
-- Subquery to select teams with goals conceded equal to 4 and in the 4th group position
SELECT * -- Selecting all columns
FROM
soccer_team -- Specifying the soccer_team table for the subquery
WHERE
goal_agnst = 4 AND group_position = 4 -- Filtering teams with 4 goals conceded and in the 4th group position
) b -- Aliasing the subquery as 'b'
ON
a.country_id = b.team_id -- Joining the main query with the subquery based on the team_id
-- Ordering the results by team group
ORDER BY
b.team_group;
Explanation:
This query uses a subquery to first filter the soccer_team table for teams with 4 goals against and in 4th position. Then, it joins the result of the subquery with the soccer_country table based on the team_id. The final result is ordered by team_group.
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: Two teams that scored three goals in a single game.
Next SQL Exercise: Find 3 Lyon players participated in the EURO Finals.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.