w3resource

SQL Exercise: Teams that scored only one goal to the torunament

SQL soccer Database: Joins Exercise-16 with Solution

16. From the following tables, write a SQL query to find the teams that have scored one goal in the tournament. Return country_name as "Team", team in the group, goal_for.

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:

-- Selecting country_name (aliased as "Team"), team_group, and goal_for from soccer_team
SELECT country_name as "Team" , team_group, goal_for
-- From clause with JOIN between soccer_team and soccer_country
FROM soccer_team
JOIN soccer_country 
-- Joining soccer_team and soccer_country based on team_id and country_id
ON soccer_team.team_id = soccer_country.country_id
-- Condition for filtering results where goal_for is equal to 1
AND goal_for = 1;

Sample Output:

  Team   | team_group | goal_for
---------+------------+----------
 Albania | A          |        1
 Austria | F          |        1
 Sweden  | E          |        1
(3 rows)

Code Explanation:

The said query in SQL that returns a list of all teams that scored only 1 goal, along with their team group and country name from the tables 'soccer_team' and 'soccer_country'.
The JOIN clause is used to combine the two tables where the team ID from the 'soccer_team' table matches the country ID from the 'soccer_country' table.
The WHERE clause filters the results to include only those rows where the number of goals scored is equal to 1.

Alternative Solutions:

Using a Subquery:

-- Selecting country_name (aliased as "Team"), team_group, and goal_for from soccer_team
SELECT country_name as "Team", team_group, goal_for
-- 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 with a subquery to filter results based on teams with goal_for equal to 1
WHERE team_id IN (
    -- Subquery to select team_id from soccer_team where goal_for is equal to 1
    SELECT team_id
    FROM soccer_team
    WHERE goal_for = 1
);

Explanation:

This query uses a subquery to find the team_id values where goal_for is 1. It then uses these team_id values to filter the main query.

Using a JOIN with Filter:

-- Selecting country_name (aliased as "Team"), team_group, and goal_for from soccer_team
SELECT country_name as "Team", team_group, goal_for
-- 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 with conditions to filter results based on goal_for values
WHERE goal_for = 1
  -- Additional condition to ensure the goal_for value in soccer_team is also equal to 1
  AND soccer_team.goal_for = 1;

Explanation:

This query combines the JOIN condition with the WHERE clause to filter the results. Both conditions ensure that goal_for is 1.

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: Which player was the first to be sent off at Euro 2016.
Next SQL Exercise: Find the yellow cards received by each country.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.