w3resource

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
 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
        5 | G          |    1204 | W        | N          |          1 |               |   80022 |    160071
        6 | G          |    1213 | W        | N          |          1 |               |   80036 |    160279
        6 | G          |    1212 | L        | N          |          0 |               |   80029 |    160256
        7 | G          |    1208 | W        | N          |          2 |               |   80014 |    160163
        7 | G          |    1223 | L        | N          |          0 |               |   80006 |    160508
        8 | G          |    1219 | W        | N          |          1 |               |   80018 |    160416
        8 | G          |    1205 | L        | N          |          0 |               |   80012 |    160093
        9 | G          |    1215 | D        | N          |          1 |               |   80017 |    160324
        9 | G          |    1220 | D        | N          |          1 |               |   80010 |    160439
       10 | G          |    1203 | L        | N          |          0 |               |   80004 |    160047
       10 | G          |    1211 | W        | N          |          2 |               |   80007 |    160231
       11 | G          |    1202 | L        | N          |          0 |               |   80026 |    160024
       11 | G          |    1209 | W        | N          |          2 |               |   80028 |    160187
       12 | G          |    1214 | D        | N          |          1 |               |   80009 |    160302
       12 | G          |    1210 | D        | N          |          1 |               |   80015 |    160208
       13 | G          |    1217 | L        | N          |          1 |               |   80001 |    160369
       13 | G          |    1218 | W        | N          |          2 |               |   80002 |    160392
       14 | G          |    1216 | D        | N          |          1 |               |   80030 |    160348
       14 | G          |    1221 | D        | N          |          1 |               |   80032 |    160463
       15 | G          |    1207 | W        | N          |          2 |               |   80033 |    160140
       15 | G          |    1201 | L        | N          |          0 |               |   80027 |    160001
       16 | G          |    1206 | W        | N          |          2 |               |   80005 |    160117
       16 | G          |    1224 | L        | N          |          1 |               |   80013 |    160531
       17 | G          |    1223 | L        | N          |          0 |               |   80035 |    160508
       17 | G          |    1212 | W        | N          |          2 |               |   80034 |    160256
       18 | G          |    1208 | D        | N          |          0 |               |   80021 |    160163
       18 | G          |    1213 | D        | N          |          0 |               |   80024 |    160278
       19 | G          |    1211 | W        | N          |          1 |               |   80016 |    160231
       19 | G          |    1220 | L        | N          |          0 |               |   80020 |    160439
       20 | G          |    1205 | D        | N          |          2 |               |   80004 |    160093
       20 | G          |    1204 | D        | N          |          2 |               |   80007 |    160071
       21 | G          |    1219 | W        | N          |          3 |               |   80017 |    160416
       21 | G          |    1222 | L        | N          |          0 |               |   80010 |    160486
       22 | G          |    1203 | W        | N          |          3 |               |   80009 |    160047
       22 | G          |    1215 | L        | N          |          0 |               |   80015 |    160324
       23 | G          |    1210 | D        | N          |          1 |               |   80030 |    160208
       23 | G          |    1209 | D        | N          |          1 |               |   80032 |    160187
       24 | G          |    1214 | D        | N          |          0 |               |   80008 |    160302
       24 | G          |    1202 | D        | N          |          0 |               |   80019 |    160024
       25 | G          |    1216 | L        | N          |          0 |               |   80035 |    160348
       25 | G          |    1201 | W        | N          |          1 |               |   80034 |    160001
       26 | G          |    1221 | D        | N          |          0 |               |   80001 |    160463
       26 | G          |    1207 | D        | N          |          0 |               |   80002 |    160140
       27 | G          |    1217 | L        | N          |          0 |               |   80011 |    160369
       27 | G          |    1224 | W        | N          |          3 |               |   80022 |    160531
       28 | G          |    1218 | D        | N          |          0 |               |   80003 |    160392
       28 | G          |    1206 | D        | N          |          0 |               |   80023 |    160117
       29 | G          |    1223 | L        | N          |          0 |               |   80031 |    160508
       29 | G          |    1213 | W        | N          |          1 |               |   80025 |    160278
       30 | G          |    1212 | L        | N          |          0 |               |   80026 |    160256
       30 | G          |    1208 | W        | N          |          1 |               |   80028 |    160163
       31 | G          |    1205 | L        | N          |          0 |               |   80033 |    160093
       31 | G          |    1222 | W        | N          |          2 |               |   80027 |    160486
       32 | G          |    1204 | W        | N          |          2 |               |   80021 |    160071
       32 | G          |    1219 | L        | N          |          1 |               |   80024 |    160416
       33 | G          |    1210 | W        | N          |          2 |               |   80018 |    160208
       33 | G          |    1202 | L        | N          |          1 |               |   80012 |    160024
       34 | G          |    1209 | D        | N          |          3 |               |   80014 |    160187
       34 | G          |    1214 | D        | N          |          3 |               |   80006 |    160302
       35 | G          |    1211 | L        | N          |          0 |               |   80036 |    160233
       35 | G          |    1215 | W        | N          |          1 |               |   80029 |    160324
       36 | G          |    1220 | L        | N          |          0 |               |   80005 |    160439
       36 | G          |    1203 | W        | N          |          1 |               |   80013 |    160047
       37 | R          |    1221 | L        | P          |          1 |             4 |   80004 |    160463
       37 | R          |    1213 | W        | P          |          1 |             5 |   80007 |    160278
       38 | R          |    1224 | W        | N          |          1 |               |   80014 |    160531
       38 | R          |    1212 | L        | N          |          0 |               |   80006 |    160256
       39 | R          |    1204 | L        | N          |          0 |               |   80003 |    160071
       39 | R          |    1214 | W        | N          |          1 |               |   80023 |    160302
       40 | R          |    1207 | W        | N          |          2 |               |   80008 |    160140
       40 | R          |    1215 | L        | N          |          1 |               |   80019 |    160324
       41 | R          |    1208 | W        | N          |          3 |               |   80018 |    160163
       41 | R          |    1218 | L        | N          |          0 |               |   80012 |    160392
       42 | R          |    1209 | L        | N          |          0 |               |   80017 |    160187
       42 | R          |    1203 | W        | N          |          4 |               |   80010 |    160047
       43 | R          |    1211 | W        | N          |          2 |               |   80009 |    160231
       43 | R          |    1219 | L        | N          |          0 |               |   80015 |    160416
       44 | R          |    1206 | L        | N          |          1 |               |   80001 |    160117
       44 | R          |    1210 | W        | N          |          2 |               |   80002 |    160208
       45 | Q          |    1213 | L        | P          |          1 |             3 |   80005 |    160278
       45 | Q          |    1214 | W        | P          |          1 |             5 |   80013 |    160302
       46 | Q          |    1224 | W        | N          |          3 |               |   80001 |    160531
       46 | Q          |    1203 | L        | N          |          1 |               |   80002 |    160047
       47 | Q          |    1208 | W        | P          |          1 |             6 |   80016 |    160163
       47 | Q          |    1211 | L        | P          |          1 |             5 |   80020 |    160231
       48 | Q          |    1207 | W        | N          |          5 |               |   80021 |    160140
       48 | Q          |    1210 | L        | N          |          2 |               |   80024 |    160208
       49 | S          |    1214 | W        | N          |          2 |               |   80011 |    160302
       49 | S          |    1224 | L        | N          |          0 |               |   80022 |    160531
       50 | S          |    1207 | W        | N          |          2 |               |   80008 |    160140
       50 | S          |    1208 | L        | N          |          1 |               |   80019 |    160163
       51 | F          |    1214 | W        | N          |          1 |               |   80004 |    160302
       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
       1210 | ISL          | Iceland
       1211 | ITA          | Italy
       1212 | NIR          | Northern Ireland
       1213 | POL          | Poland
       1214 | POR          | Portugal
       1215 | IRL          | Republic of Ireland
       1216 | ROU          | Romania
       1217 | RUS          | Russia
       1218 | SVK          | Slovakia
       1219 | ESP          | Spain
       1220 | SWE          | Sweden
       1221 | SUI          | Switzerland
       1222 | TUR          | Turkey
       1223 | UKR          | Ukraine
       1224 | WAL          | Wales
       1225 | SLO          | Slovenia
       1226 | NED          | Netherlands
       1227 | SRB          | Serbia
       1228 | SCO          | Scotland
       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 Expression: Find the number of goal scored by each team in every match within normal play schedule.

Relational Algebra Tree:

Relational Algebra Tree: Find the number of goal scored by each team in every match within normal play schedule.

Practice Online


Sample Database: soccer

soccer database relationship structure

Query Visualization:

Duration:

Query visualization of Find the number of goal scored by each team in every match within normal play schedule - Duration

Rows:

Query visualization of Find the number of goal scored by each team in every match within normal play schedule - Rows

Cost:

Query visualization of Find the number of goal scored by each team in every match within normal play schedule - 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.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql-exercises/soccer-database-exercise/sql-joins-exercise-soccer-database-2.php