SQL exercises on soccer Database: Find the number of goal scored by each team in every match within normal play schedule

SQL soccer Database: Joins Exercise-2 with Solution

2. From the following tables, write a SQL query to find the number of goal scored by each team in every match within normal play schedule. 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)

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: From the following tables, write a SQL query to find the venue where EURO cup 2016 final match held. Return venue name, city.
Next: From the following tables, write a SQL query to count the number of goals scored by each player within normal play schedule. Group the result set on player name and country name and sorts the result-set according to the highest to the lowest scorer. Return player name, number of goals and country name.