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


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

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.

SQL: Tips of the Day

What is the best way to paginate results in SQL Server?

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

Database: SQL Server

Ref: https://bit.ly/3MGrNlk

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook