SQL Exercise: Most quickest goal at EURO Cup 2016, after 5 minutes
SQL soccer Database: Subqueries Exercise-33 with Solution
From the following table, write a SQL query to find the fastest goal at the EURO cup 2016, after 5 minutes. Return ‘Quickest goal after 5 minutes’.
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:
SQL Code:
SELECT min(goal_time) AS "Most quickest goal after 5 minutes"
FROM
(SELECT match_no,
goal_time
FROM goal_details
WHERE goal_time>5
GROUP BY match_no,
goal_time
ORDER BY goal_time) hh;
Sample Output:
Most quickest goal after 5 minutes
------------------------------------
6
(1 row)
Code Explanation:
The said query in SQL that retrieves the minimum goal time for each match where a goal was scored after the 5th minute, and then selects the overall minimum value and gives it an alias as "Most quickest goal after 5 minutes".
The subquery selects the match number and goal time from the goal_details table where the goal time is greater than 5 minutes, groups the results by match number and goal time, orders them by goal time in ascending order. The outer query selects the overall minimum goal time from the subquery.
Alternative Solution:
Using Subquery with LIMIT:
SELECT MIN(goal_time) AS "Most quickest goal after 5 minutes"
FROM (
SELECT match_no, MIN(goal_time) as goal_time
FROM goal_details
WHERE goal_time > 5
GROUP BY match_no
) hh;
Explanation:
This query use a subquery to first find the minimum goal_time for each match_no where the goal_time is greater than 5 minutes. Then, in the outer query, we find the overall minimum goal_time among these results.
Using MIN with WHERE Clause:
SELECT MIN(goal_time) AS "Most quickest goal after 5 minutes"
FROM goal_details
WHERE goal_time > 5;
Explanation:
This query uses a simple MIN function along with a WHERE clause to filter for goal_time greater than 5. The minimum goal_time is then returned.
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 penalty shootout matches played.
Next SQL Exercise: SQL JOINS Exercises on Soccer Database
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics