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 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 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.
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://w3resource.com/sql-exercises/soccer-database-exercise/sql-basic-exercise-soccer-database-7.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics