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

Sample table: soccer_country

Sample Solution:

SQL Code:

SELECT country_name as "Team" ,team_group, goal_for
FROM soccer_team
JOIN soccer_country 
ON soccer_team.team_id=soccer_country.country_id
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.

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.