SQL Exercise: Teams that scored only one goal to the torunament
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
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.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics