SQL Exercise: Team(s) who conceded the most goals in EURO cup 2016

SQL soccer Database: Joins Exercise-20 with Solution

20. From the following tables, write a SQL query to find the team(s) who conceded the most goals in EURO cup 2016. Return country name, team group and match played.

 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

 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:

-- Selecting specific columns from soccer_team and soccer_country tables
SELECT country_name, team_group, match_played, won, lost, goal_for, goal_agnst
-- From clause with JOIN between soccer_team and soccer_country
FROM soccer_team 
JOIN soccer_country ON soccer_team.team_id = soccer_country.country_id
-- Where clause to filter results for teams where goal_agnst is equal to the maximum goal_agnst in the soccer_team table
WHERE goal_agnst = (
    -- Subquery to find the maximum value of goal_agnst in the soccer_team table
    SELECT MAX(goal_agnst) 
    FROM soccer_team	
);

Sample Output:

 country_name | team_group | match_played | won | lost | goal_for | goal_agnst
--------------+------------+--------------+-----+------+----------+------------
 Russia       | B          |            3 |   0 |    2 |        2 |          6
(1 row)

Code Explanation:

The said query in SQL that selects the country name, team group, number of matches played, number of matches won, number of matches lost, number of goals scored for, and number of goals scored against for all teams in the soccer_team table, that have the highest number of goals scored against them.
The JOIN clause joins the tables using the team_id and country_id columns, which are primary keys in the soccer_team and soccer_country tables respectively.
The WHERE clause filters the results to only show teams where the goal_agnst column in the soccer_team table is equal to the maximum value of goal_agnst in the same table. The subquery inside the WHERE clause is used to get the maximum value of goal_agnst from the soccer_team table.

Alternative Solutions:

Using ORDER BY with LIMIT:

-- Selecting specific columns from soccer_team and soccer_country tables
SELECT sc.country_name, st.team_group, st.match_played, st.won, st.lost, st.goal_for, st.goal_agnst
-- From clause with JOIN between soccer_team and soccer_country
FROM soccer_team st
JOIN soccer_country sc ON st.team_id = sc.country_id
-- Ordering the results by goal_agnst in descending order
ORDER BY st.goal_agnst DESC
-- Limiting the result to the first row (team with the maximum goal_agnst)
LIMIT 1;

Explanation:

This query uses ORDER BY to sort the records by goal_agnst in descending order and then uses LIMIT 1 to only retrieve the top record, which will have the maximum goal_agnst.

Using a Subquery in FROM Clause (Derived Table):

-- Selecting specific columns from soccer_team and soccer_country tables
SELECT sc.country_name, st.team_group, st.match_played, st.won, st.lost, st.goal_for, st.goal_agnst
-- From clause with a subquery (st) and JOIN between the subquery and soccer_country
FROM (	
    -- Subquery to select all columns from soccer_team where goal_agnst is equal to the maximum goal_agnst in soccer_team
    SELECT *
    FROM soccer_team
    WHERE goal_agnst = (
        -- Subquery to find the maximum value of goal_agnst in the soccer_team table
        SELECT MAX(goal_agnst) 
        FROM soccer_team
    )
) st
JOIN soccer_country sc ON st.team_id = sc.country_id;

Explanation:

This query uses a subquery in the FROM clause to create a derived table (st) that contains the records where goal_agnst is the maximum. The main query then joins this derived table with soccer_country to get the desired columns.

Practice Online

Sample Database: soccer

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Match where no stoppage time added in 1st half of play.
Next SQL Exercise: Matches with most stoppage time added in the 2nd half.