SQL Exercise: Number of goals scored by each team in each match
SQL soccer Database: Joins Exercise-2 with Solution
2. From the following tables, write a SQL query to find the number of goals scored by each team in each match during normal play. Return match number, country name and goal score.
Sample table: match_details
Sample table: soccer_country
Sample Solution:
SQL Code:
SELECT match_no,country_name,goal_score
FROM match_details a
JOIN soccer_country b
ON a.team_id=b.country_id
WHERE decided_by='N'
ORDER BY match_no;
Sample Output:
match_no | country_name | goal_score ----------+---------------------+------------ 1 | France | 2 1 | Romania | 1 2 | Albania | 0 2 | Switzerland | 1 3 | Wales | 2 3 | Slovakia | 1 4 | England | 1 4 | Russia | 1 5 | Turkey | 0 5 | Croatia | 1 6 | Poland | 1 6 | Northern Ireland | 0 7 | Germany | 2 7 | Ukraine | 0 8 | Spain | 1 8 | Czech Republic | 0 9 | Republic of Ireland | 1 9 | Sweden | 1 10 | Belgium | 0 10 | Italy | 2 11 | Austria | 0 11 | Hungary | 2 12 | Portugal | 1 12 | Iceland | 1 13 | Russia | 1 13 | Slovakia | 2 14 | Romania | 1 14 | Switzerland | 1 15 | France | 2 15 | Albania | 0 16 | England | 2 16 | Wales | 1 17 | Ukraine | 0 17 | Northern Ireland | 2 18 | Germany | 0 18 | Poland | 0 19 | Italy | 1 19 | Sweden | 0 20 | Czech Republic | 2 20 | Croatia | 2 21 | Spain | 3 21 | Turkey | 0 22 | Belgium | 3 22 | Republic of Ireland | 0 23 | Iceland | 1 23 | Hungary | 1 24 | Portugal | 0 24 | Austria | 0 25 | Romania | 0 25 | Albania | 1 26 | Switzerland | 0 26 | France | 0 27 | Russia | 0 27 | Wales | 3 28 | Slovakia | 0 28 | England | 0 29 | Ukraine | 0 29 | Poland | 1 30 | Northern Ireland | 0 30 | Germany | 1 31 | Czech Republic | 0 31 | Turkey | 2 32 | Croatia | 2 32 | Spain | 1 33 | Iceland | 2 33 | Austria | 1 34 | Hungary | 3 34 | Portugal | 3 35 | Italy | 0 35 | Republic of Ireland | 1 36 | Sweden | 0 36 | Belgium | 1 38 | Wales | 1 38 | Northern Ireland | 0 39 | Croatia | 0 39 | Portugal | 1 40 | France | 2 40 | Republic of Ireland | 1 41 | Germany | 3 41 | Slovakia | 0 42 | Hungary | 0 42 | Belgium | 4 43 | Italy | 2 43 | Spain | 0 44 | England | 1 44 | Iceland | 2 46 | Wales | 3 46 | Belgium | 1 48 | France | 5 48 | Iceland | 2 49 | Portugal | 2 49 | Wales | 0 50 | France | 2 50 | Germany | 1 51 | Portugal | 1 51 | France | 0 (96 rows)
Code Explanation:
The said query in SQL that returns a list of match numbers, the corresponding country names, and the goal score for each match by normal goal.
The query uses a JOIN operation to link the match_details and soccer_country tables based on the common country_id column .
The WHERE clause filters the result set to only include rows where the decided_by column equals 'N', which is a normal goal.
The ORDER BY clause sorts the result set in ascending order based on the match_no column.
Relational Algebra Expression:

Relational Algebra Tree:

Practice Online
Sample Database: soccer

Query Visualization:
Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Where was the final match of the EURO cup 2016 held?.
Next SQL Exercise: Goals scored by each player during normal play.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
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
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
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