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

How to drop all tables from a database with one SQL query?

USE Databasename

SELECT  'DROP TABLE [' + name + '];'
FROM    sys.tables

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


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