﻿ SQL: Number of goals scored by each team in each match

# 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

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