w3resource
Run SQL Query

SQL Exercise: Number of goals scored during a 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 Expression: Find the number of goal scored in every match within normal play schedule.

Relational Algebra Tree:

Relational Algebra Tree: Find the number of goal scored 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 in every match within normal play schedule - Duration

Rows:

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

Cost:

Query visualization of Find the number of goal scored 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: Matches decided by penalties in the Round of 16.
Next SQL Exercise: Matches, first half with no stoppage time added.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

Can a foreign key be NULL and/or duplicate?

First remember a Foreign key simply requires that the value in that field must exist first in a different table (the parent table). That is all an Foreign key is by definition. Null by definition is not a value. Null means that we do not yet know what the value is.

Ref: https://bit.ly/3uVu3OS