w3resource

SQL Exercise: Find the number of self-goals scored in EURO cup 2016

SQL soccer Database: Basic Exercise-7 with Solution

7. From the following table, write a SQL query to find the number of self-goals scored during the 2016 European Championship.

Sample table: goal_details
 goal_id | match_no | player_id | team_id | goal_time | goal_type | play_stage | goal_schedule | goal_half
---------+----------+-----------+---------+-----------+-----------+------------+---------------+-----------
       1 |        1 |    160159 |    1207 |        57 | N         | G          | NT            |         2
       2 |        1 |    160368 |    1216 |        65 | P         | G          | NT            |         2
       3 |        1 |    160154 |    1207 |        89 | N         | G          | NT            |         2
       4 |        2 |    160470 |    1221 |         5 | N         | G          | NT            |         1
       5 |        3 |    160547 |    1224 |        10 | N         | G          | NT            |         1
       6 |        3 |    160403 |    1218 |        61 | N         | G          | NT            |         2
       7 |        3 |    160550 |    1224 |        81 | N         | G          | NT            |         2
       8 |        4 |    160128 |    1206 |        73 | N         | G          | NT            |         2
       9 |        4 |    160373 |    1217 |        93 | N         | G          | ST            |         2
      10 |        5 |    160084 |    1204 |        41 | N         | G          | NT            |         1
      11 |        6 |    160298 |    1213 |        51 | N         | G          | NT            |         2
      12 |        7 |    160183 |    1208 |        19 | N         | G          | NT            |         1
      13 |        7 |    160180 |    1208 |        93 | N         | G          | ST            |         2
      14 |        8 |    160423 |    1219 |        87 | N         | G          | NT            |         2
      15 |        9 |    160335 |    1215 |        48 | N         | G          | NT            |         2
      16 |        9 |    160327 |    1215 |        71 | O         | G          | NT            |         2
      17 |       10 |    160244 |    1211 |        32 | N         | G          | NT            |         1
      18 |       10 |    160252 |    1211 |        93 | N         | G          | ST            |         2
      19 |       11 |    160207 |    1209 |        62 | N         | G          | NT            |         2
      20 |       11 |    160200 |    1209 |        87 | N         | G          | NT            |         2
      21 |       12 |    160320 |    1214 |        31 | N         | G          | NT            |         1
      22 |       12 |    160221 |    1210 |        50 | N         | G          | NT            |         2
      23 |       13 |    160411 |    1218 |        32 | N         | G          | NT            |         1
      24 |       13 |    160405 |    1218 |        45 | N         | G          | NT            |         1
      25 |       13 |    160380 |    1217 |        80 | N         | G          | NT            |         2
      26 |       14 |    160368 |    1216 |        18 | P         | G          | NT            |         1
      27 |       14 |    160481 |    1221 |        57 | N         | G          | NT            |         2
      28 |       15 |    160160 |    1207 |        90 | N         | G          | NT            |         2
      29 |       15 |    160154 |    1207 |        96 | N         | G          | ST            |         2
      30 |       16 |    160547 |    1224 |        42 | N         | G          | NT            |         1
      31 |       16 |    160138 |    1206 |        56 | N         | G          | NT            |         2
      32 |       16 |    160137 |    1206 |        93 | N         | G          | ST            |         2
      33 |       17 |    160262 |    1212 |        49 | N         | G          | NT            |         2
      34 |       17 |    160275 |    1212 |        96 | N         | G          | ST            |         2
      35 |       19 |    160248 |    1211 |        88 | N         | G          | NT            |         2
      36 |       20 |    160085 |    1204 |        37 | N         | G          | NT            |         1
      37 |       20 |    160086 |    1204 |        59 | N         | G          | NT            |         2
      38 |       20 |    160115 |    1205 |        76 | N         | G          | NT            |         2
      39 |       20 |    160114 |    1205 |        89 | P         | G          | NT            |         2
      40 |       21 |    160435 |    1219 |        34 | N         | G          | NT            |         1
      41 |       21 |    160436 |    1219 |        37 | N         | G          | NT            |         1
      42 |       21 |    160435 |    1219 |        48 | N         | G          | NT            |         2
      43 |       22 |    160067 |    1203 |        48 | N         | G          | NT            |         2
      44 |       22 |    160064 |    1203 |        61 | N         | G          | NT            |         2
      45 |       22 |    160067 |    1203 |        70 | N         | G          | NT            |         2
      46 |       23 |    160224 |    1210 |        40 | P         | G          | NT            |         1
      47 |       23 |    160216 |    1210 |        88 | O         | G          | NT            |         2
      48 |       25 |    160023 |    1201 |        43 | N         | G          | NT            |         1
      49 |       27 |    160544 |    1224 |        11 | N         | G          | NT            |         1
      50 |       27 |    160538 |    1224 |        20 | N         | G          | NT            |         1
      51 |       27 |    160547 |    1224 |        67 | N         | G          | NT            |         2
      52 |       29 |    160287 |    1213 |        54 | N         | G          | NT            |         2
      53 |       30 |    160182 |    1208 |        30 | N         | G          | NT            |         1
      54 |       31 |    160504 |    1222 |        10 | N         | G          | NT            |         1
      55 |       31 |    160500 |    1222 |        65 | N         | G          | NT            |         2
      56 |       32 |    160435 |    1219 |         7 | N         | G          | NT            |         1
      57 |       32 |    160089 |    1204 |        45 | N         | G          | NT            |         1
      58 |       32 |    160085 |    1204 |        87 | N         | G          | NT            |         2
      59 |       33 |    160226 |    1210 |        18 | N         | G          | NT            |         1
      60 |       33 |    160042 |    1202 |        60 | N         | G          | NT            |         2
      61 |       33 |    160226 |    1210 |        94 | N         | G          | ST            |         2
      62 |       34 |    160203 |    1209 |        19 | N         | G          | NT            |         1
      63 |       34 |    160320 |    1214 |        42 | N         | G          | NT            |         1
      64 |       34 |    160202 |    1209 |        47 | N         | G          | NT            |         2
      65 |       34 |    160322 |    1214 |        50 | N         | G          | NT            |         2
      66 |       34 |    160202 |    1209 |        55 | N         | G          | NT            |         2
      67 |       34 |    160322 |    1214 |        62 | N         | G          | NT            |         2
      68 |       35 |    160333 |    1215 |        85 | N         | G          | NT            |         2
      69 |       36 |    160063 |    1203 |        84 | N         | G          | NT            |         2
      70 |       37 |    160287 |    1213 |        39 | N         | R          | NT            |         1
      71 |       37 |    160476 |    1221 |        82 | N         | R          | NT            |         2
      72 |       38 |    160262 |    1212 |        75 | O         | R          | NT            |         2
      73 |       39 |    160321 |    1214 |       117 | N         | R          | ET            |         2
      74 |       40 |    160333 |    1215 |         2 | P         | R          | NT            |         1
      75 |       40 |    160160 |    1207 |        58 | N         | R          | NT            |         2
      76 |       40 |    160160 |    1207 |        61 | N         | R          | NT            |         2
      77 |       41 |    160165 |    1208 |         8 | N         | R          | NT            |         1
      78 |       41 |    160182 |    1208 |        43 | N         | R          | NT            |         1
      79 |       41 |    160173 |    1208 |        63 | N         | R          | NT            |         2
      80 |       42 |    160050 |    1203 |        10 | N         | R          | NT            |         1
      81 |       42 |    160065 |    1203 |        78 | N         | R          | NT            |         2
      82 |       42 |    160062 |    1203 |        80 | N         | R          | NT            |         2
      83 |       42 |    160058 |    1203 |        90 | N         | R          | NT            |         2
      84 |       43 |    160236 |    1211 |        33 | N         | R          | NT            |         1
      85 |       43 |    160252 |    1211 |        91 | N         | R          | ST            |         2
      86 |       44 |    160136 |    1206 |         4 | P         | R          | NT            |         1
      87 |       44 |    160219 |    1210 |         6 | N         | R          | NT            |         1
      88 |       44 |    160230 |    1210 |        18 | N         | R          | NT            |         1
      89 |       45 |    160297 |    1213 |         2 | N         | Q          | NT            |         1
      90 |       45 |    160316 |    1214 |        33 | N         | Q          | NT            |         1
      91 |       46 |    160063 |    1203 |        13 | N         | Q          | NT            |         1
      92 |       46 |    160539 |    1224 |        31 | N         | Q          | NT            |         1
      93 |       46 |    160550 |    1224 |        55 | N         | Q          | NT            |         2
      94 |       46 |    160551 |    1224 |        86 | N         | Q          | NT            |         2
      95 |       47 |    160177 |    1208 |        65 | N         | Q          | NT            |         2
      96 |       47 |    160235 |    1211 |        78 | P         | Q          | NT            |         2
      97 |       48 |    160159 |    1207 |        12 | N         | Q          | NT            |         1
      98 |       48 |    160155 |    1207 |        20 | N         | Q          | NT            |         1
      99 |       48 |    160154 |    1207 |        43 | N         | Q          | NT            |         1
     100 |       48 |    160160 |    1207 |        45 | N         | Q          | NT            |         1
     101 |       48 |    160230 |    1210 |        56 | N         | Q          | NT            |         2
     102 |       48 |    160159 |    1207 |        59 | N         | Q          | NT            |         2
     103 |       48 |    160221 |    1210 |        84 | N         | Q          | NT            |         2
     104 |       49 |    160322 |    1214 |        50 | N         | S          | NT            |         2
     105 |       49 |    160320 |    1214 |        53 | N         | S          | NT            |         2
     106 |       50 |    160160 |    1207 |        47 | P         | S          | ST            |         1
     107 |       50 |    160160 |    1207 |        72 | N         | S          | NT            |         2
     108 |       51 |    160319 |    1214 |       109 | N         | F          | ET            |         2

Sample Solution:

-- This SQL query calculates the count of rows in the 'goal_details' table where the 'goal_type' column has the value 'O'.

SELECT COUNT(*) 
-- COUNT(*) is an aggregate function that counts the number of rows in a result set.
FROM goal_details 
-- 'goal_details' is the name of the table being queried.
WHERE goal_type='O';
-- The WHERE clause filters rows where the 'goal_type' column has the value 'O'.

Sample Output:

 count
-------
     3
(1 row)

Code Explanation:

The said query in SQL that selects the number of rows in the goal_details table where the goal_type column is equal to 'O'.
The result of the query will be a single value representing the count of rows where goal_type is equal to 'O'.

Relational Algebra Expression:

Relational Algebra Expression: Find the number of self-goals scored in EURO cup 2016.

Relational Algebra Tree:

Relational Algebra Tree: Find the number of self-goals scored in EURO cup 2016.

Practice Online



Sample Database: soccer

soccer database relationship structure

Query Visualization:

Duration:

Query visualization of Find the number of self-goals scored in EURO cup 2016 - Duration

Rows:

Query visualization of Find the number of self-goals scored in EURO cup 2016 - Rows

Cost:

Query visualization of Find the number of self-goals scored in EURO cup 2016 - Cost

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Find the date when did Football EURO cup 2016 begin.
Next SQL Exercise: Number of matches ended with a results in group stage.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.