w3resource

SQL Exercise: Bottom of their groups and conceded 4 goals in 3 games

SQL soccer Database: Joins Exercise-28 with Solution

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 Expression: Find the teams with other information that finished bottom of their respective groups after conceding four times in three games.

Relational Algebra Tree:

Relational Algebra Tree: Find the teams with other information that finished bottom of their respective groups after conceding four times in three games.

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: 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.



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-28.php