SQL Exercise: Number of goals scored by each team in each match
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
match_no | play_stage | team_id | win_lose | decided_by | goal_score | penalty_score | ass_ref | player_gk
----------+------------+---------+----------+------------+------------+---------------+---------+-----------
1 | G | 1207 | W | N | 2 | | 80016 | 160140
1 | G | 1216 | L | N | 1 | | 80020 | 160348
2 | G | 1201 | L | N | 0 | | 80003 | 160001
2 | G | 1221 | W | N | 1 | | 80023 | 160463
3 | G | 1224 | W | N | 2 | | 80031 | 160532
3 | G | 1218 | L | N | 1 | | 80025 | 160392
4 | G | 1206 | D | N | 1 | | 80008 | 160117
4 | G | 1217 | D | N | 1 | | 80019 | 160369
5 | G | 1222 | L | N | 0 | | 80011 | 160486
..........
51 | F | 1207 | L | N | 0 | | 80007 | 160140
Sample table: soccer_country
country_id | country_abbr | country_name
------------+--------------+---------------------
1201 | ALB | Albania
1202 | AUT | Austria
1203 | BEL | Belgium
1204 | CRO | Croatia
1205 | CZE | Czech Republic
1206 | ENG | England
1207 | FRA | France
1208 | GER | Germany
1209 | HUN | Hungary
........
1229 | NOR | Norway
Sample Solution:
SQL Code:
-- Selecting match_no, country_name, and goal_score
SELECT match_no, country_name, goal_score
-- Joining match_details with soccer_country using team_id and country_id
FROM match_details a
JOIN soccer_country b ON a.team_id = b.country_id
-- Filtering the results to include only matches that were not decided
WHERE decided_by = 'N'
-- Sorting the results based on match_no
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.
Alternative Solutions:
Using EXISTS Subquery:
-- Selecting match_no, country_name, and goal_score
SELECT match_no, country_name, goal_score
-- Joining match_details with soccer_country using team_id and country_id
FROM match_details md
JOIN soccer_country sc ON md.team_id = sc.country_id
-- Filtering the results to include only matches that were not decided
WHERE md.decided_by = 'N'
-- Using EXISTS to check if there is a corresponding country_id in soccer_country
AND EXISTS (
SELECT 1
FROM soccer_country
WHERE country_id = md.team_id
)
-- Sorting the results based on match_no
ORDER BY match_no;
Explanation:
This query uses an EXISTS subquery to ensure that there is a matching country_id in the soccer_country table. The WHERE clause filters the results where decided_by is 'N', and the final result is ordered by match_no.
Using JOIN with ON Clause and WHERE Clause:
-- Selecting match_no, country_name, and goal_score
SELECT a.match_no, b.country_name, a.goal_score
-- Joining match_details with soccer_country using team_id and country_id
FROM match_details a
JOIN soccer_country b ON a.team_id = b.country_id
-- Filtering the results to include only matches that were not decided and have a valid country_id
WHERE a.decided_by = 'N'
AND b.country_id IS NOT NULL
-- Sorting the results based on match_no
ORDER BY a.match_no;
Explanation:
This query combines the match_details and soccer_country tables using a JOIN with an ON clause. The WHERE clause filters the results where decided_by is 'N' and ensures that there is a non-null country_id. The final result is ordered by match_no.
Relational Algebra Expression:
Relational Algebra Tree:
Go to:
PREV : Where was the final match of the EURO cup 2016 held?.
NEXT : Goals scored by each player during normal play.
Practice Online
Sample Database: soccer
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.