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

SQL soccer Database: Basic Exercise-11 with Solution

11. From the following table, write a SQL query to find the number of goal scored in every match within normal play schedule. Sort the result-set on match number. Return match number, number of goal scored.

Sample table: goal_details

Sample Solution:

SELECT match_no,COUNT(*) 
FROM goal_details 
GROUP BY match_no 
ORDER BY match_no;

Sample Output:

 match_no | count
----------+-------
        1 |     3
        2 |     1
        3 |     3
        4 |     2
        5 |     1
        6 |     1
        7 |     2
        8 |     1
        9 |     2
       10 |     2
       11 |     2
       12 |     2
       13 |     3
       14 |     2
       15 |     2
       16 |     3
       17 |     2
       19 |     1
       20 |     4
       21 |     3
       22 |     3
       23 |     2
       25 |     1
       27 |     3
       29 |     1
       30 |     1
       31 |     2
       32 |     3
       33 |     3
       34 |     6
       35 |     1
       36 |     1
       37 |     2
       38 |     1
       39 |     1
       40 |     3
       41 |     3
       42 |     4
       43 |     2
       44 |     3
       45 |     2
       46 |     4
       47 |     2
       48 |     7
       49 |     2
       50 |     2
       51 |     1
(47 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 table, write a SQL query to find the number of matches decided by penalties in the Round 16.
Next: From the following table, write a SQL query to find those matches where no stoppage time added in the first half of play. Return match no, date of play, and goal scored.