SQL Exercise: Find where the penalty kick number 23 had been taken
SQL soccer Database: Subqueries Exercise-30 with Solution
From the following table, write a SQL query to find the stage of the match where penalty kick number 23 was taken. Return match number, play_stage.
Sample table: match_mast
match_no | play_stage | play_date | results | decided_by | goal_score | venue_id | referee_id | audence | plr_of_match | stop1_sec | stop2_sec
----------+------------+------------+---------+------------+------------+----------+------------+---------+--------------+-----------+-----------
1 | G | 2016-06-11 | WIN | N | 2-1 | 20008 | 70007 | 75113 | 160154 | 131 | 242
2 | G | 2016-06-11 | WIN | N | 0-1 | 20002 | 70012 | 33805 | 160476 | 61 | 182
3 | G | 2016-06-11 | WIN | N | 2-1 | 20001 | 70017 | 37831 | 160540 | 64 | 268
4 | G | 2016-06-12 | DRAW | N | 1-1 | 20005 | 70011 | 62343 | 160128 | 0 | 185
5 | G | 2016-06-12 | WIN | N | 0-1 | 20007 | 70006 | 43842 | 160084 | 125 | 325
6 | G | 2016-06-12 | WIN | N | 1-0 | 20006 | 70014 | 33742 | 160291 | 2 | 246
7 | G | 2016-06-13 | WIN | N | 2-0 | 20003 | 70002 | 43035 | 160176 | 89 | 188
8 | G | 2016-06-13 | WIN | N | 1-0 | 20010 | 70009 | 29400 | 160429 | 360 | 182
9 | G | 2016-06-13 | DRAW | N | 1-1 | 20008 | 70010 | 73419 | 160335 | 67 | 194
10 | G | 2016-06-14 | WIN | N | 0-2 | 20004 | 70005 | 55408 | 160244 | 63 | 189
11 | G | 2016-06-14 | WIN | N | 0-2 | 20001 | 70018 | 34424 | 160197 | 61 | 305
12 | G | 2016-06-15 | DRAW | N | 1-1 | 20009 | 70004 | 38742 | 160320 | 15 | 284
13 | G | 2016-06-15 | WIN | N | 1-2 | 20003 | 70001 | 38989 | 160405 | 62 | 189
14 | G | 2016-06-15 | DRAW | N | 1-1 | 20007 | 70015 | 43576 | 160477 | 74 | 206
15 | G | 2016-06-16 | WIN | N | 2-0 | 20005 | 70013 | 63670 | 160154 | 71 | 374
16 | G | 2016-06-16 | WIN | N | 2-1 | 20002 | 70003 | 34033 | 160540 | 62 | 212
17 | G | 2016-06-16 | WIN | N | 0-2 | 20004 | 70016 | 51043 | 160262 | 7 | 411
18 | G | 2016-06-17 | DRAW | N | 0-0 | 20008 | 70008 | 73648 | 160165 | 6 | 208
19 | G | 2016-06-17 | WIN | N | 1-0 | 20010 | 70007 | 29600 | 160248 | 2 | 264
20 | G | 2016-06-17 | DRAW | N | 2-2 | 20009 | 70005 | 38376 | 160086 | 71 | 280
21 | G | 2016-06-18 | WIN | N | 3-0 | 20006 | 70010 | 33409 | 160429 | 84 | 120
22 | G | 2016-06-18 | WIN | N | 3-0 | 20001 | 70004 | 39493 | 160064 | 11 | 180
23 | G | 2016-06-18 | DRAW | N | 1-1 | 20005 | 70015 | 60842 | 160230 | 61 | 280
24 | G | 2016-06-19 | DRAW | N | 0-0 | 20007 | 70011 | 44291 | 160314 | 3 | 200
25 | G | 2016-06-20 | WIN | N | 0-1 | 20004 | 70016 | 49752 | 160005 | 125 | 328
26 | G | 2016-06-20 | DRAW | N | 0-0 | 20003 | 70001 | 45616 | 160463 | 60 | 122
27 | G | 2016-06-21 | WIN | N | 0-3 | 20010 | 70006 | 28840 | 160544 | 62 | 119
28 | G | 2016-06-21 | DRAW | N | 0-0 | 20009 | 70012 | 39051 | 160392 | 62 | 301
29 | G | 2016-06-21 | WIN | N | 0-1 | 20005 | 70017 | 58874 | 160520 | 29 | 244
30 | G | 2016-06-21 | WIN | N | 0-1 | 20007 | 70018 | 44125 | 160177 | 21 | 195
31 | G | 2016-06-22 | WIN | N | 0-2 | 20002 | 70013 | 32836 | 160504 | 60 | 300
32 | G | 2016-06-22 | WIN | N | 2-1 | 20001 | 70008 | 37245 | 160085 | 70 | 282
33 | G | 2016-06-22 | WIN | N | 2-1 | 20008 | 70009 | 68714 | 160220 | 7 | 244
34 | G | 2016-06-22 | DRAW | N | 3-3 | 20004 | 70002 | 55514 | 160322 | 70 | 185
35 | G | 2016-06-23 | WIN | N | 0-1 | 20003 | 70014 | 44268 | 160333 | 79 | 221
36 | G | 2016-06-23 | WIN | N | 0-1 | 20006 | 70003 | 34011 | 160062 | 63 | 195
37 | R | 2016-06-25 | WIN | P | 1-1 | 20009 | 70005 | 38842 | 160476 | 126 | 243
38 | R | 2016-06-25 | WIN | N | 1-0 | 20007 | 70002 | 44342 | 160547 | 5 | 245
39 | R | 2016-06-26 | WIN | N | 0-1 | 20002 | 70012 | 33523 | 160316 | 61 | 198
40 | R | 2016-06-26 | WIN | N | 2-1 | 20004 | 70011 | 56279 | 160160 | 238 | 203
41 | R | 2016-06-26 | WIN | N | 3-0 | 20003 | 70009 | 44312 | 160173 | 62 | 124
42 | R | 2016-06-27 | WIN | N | 0-4 | 20010 | 70010 | 28921 | 160062 | 3 | 133
43 | R | 2016-06-27 | WIN | N | 2-0 | 20008 | 70004 | 76165 | 160235 | 63 | 243
44 | R | 2016-06-28 | WIN | N | 1-2 | 20006 | 70001 | 33901 | 160217 | 5 | 199
45 | Q | 2016-07-01 | WIN | P | 1-1 | 20005 | 70003 | 62940 | 160316 | 58 | 181
46 | Q | 2016-07-02 | WIN | N | 3-1 | 20003 | 70001 | 45936 | 160550 | 14 | 182
47 | Q | 2016-07-03 | WIN | P | 1-1 | 20001 | 70007 | 38764 | 160163 | 63 | 181
48 | Q | 2016-07-04 | WIN | N | 5-2 | 20008 | 70008 | 76833 | 160159 | 16 | 125
49 | S | 2016-07-07 | WIN | N | 2-0 | 20004 | 70006 | 55679 | 160322 | 2 | 181
50 | S | 2016-07-08 | WIN | N | 2-0 | 20005 | 70011 | 64078 | 160160 | 126 | 275
51 | F | 2016-07-11 | WIN | N | 1-0 | 20008 | 70005 | 75868 | 160307 | 161 | 181
Sample table: penalty_shootout
kick_id | match_no | team_id | player_id | score_goal | kick_no
---------+----------+---------+-----------+------------+---------
1 | 37 | 1221 | 160467 | Y | 1
2 | 37 | 1213 | 160297 | Y | 2
3 | 37 | 1221 | 160477 | N | 3
4 | 37 | 1213 | 160298 | Y | 4
5 | 37 | 1221 | 160476 | Y | 5
6 | 37 | 1213 | 160281 | Y | 6
7 | 37 | 1221 | 160470 | Y | 7
8 | 37 | 1213 | 160287 | Y | 8
9 | 37 | 1221 | 160469 | Y | 9
10 | 37 | 1213 | 160291 | Y | 10
11 | 45 | 1214 | 160322 | Y | 1
12 | 45 | 1213 | 160297 | Y | 2
13 | 45 | 1214 | 160316 | Y | 3
14 | 45 | 1213 | 160298 | Y | 4
15 | 45 | 1214 | 160314 | Y | 5
16 | 45 | 1213 | 160281 | Y | 6
17 | 45 | 1214 | 160320 | Y | 7
18 | 45 | 1213 | 160287 | N | 8
19 | 45 | 1214 | 160321 | Y | 9
20 | 47 | 1211 | 160251 | Y | 1
21 | 47 | 1208 | 160176 | Y | 2
22 | 47 | 1211 | 160253 | N | 3
23 | 47 | 1208 | 160183 | N | 4
24 | 47 | 1211 | 160234 | Y | 5
25 | 47 | 1208 | 160177 | N | 6
26 | 47 | 1211 | 160252 | N | 7
27 | 47 | 1208 | 160173 | Y | 8
28 | 47 | 1211 | 160235 | N | 9
29 | 47 | 1208 | 160180 | N | 10
30 | 47 | 1211 | 160244 | Y | 11
31 | 47 | 1208 | 160168 | Y | 12
32 | 47 | 1211 | 160246 | Y | 13
33 | 47 | 1208 | 160169 | Y | 14
34 | 47 | 1211 | 160238 | Y | 15
35 | 47 | 1208 | 160165 | Y | 16
36 | 47 | 1211 | 160237 | N | 17
37 | 47 | 1208 | 160166 | Y | 18
Sample Solution:
SQL Code:
SELECT match_no,
play_stage
FROM match_mast
WHERE match_no=
(SELECT match_no
FROM penalty_shootout
WHERE kick_id=23);
Sample Output:
match_no | play_stage
----------+------------
47 | Q
(1 row)
Code Explanation:
The said query in SQL that selects the match number and play stage for a specific match from the match_mast table in which a penalty kick with ID 23 was taken.
The condition compares the match number in the match_mast table to a subquery that selects the match number from the penalty_shootout table where the kick ID is 23. The subquery is enclosed in parentheses to indicate that it returns a single value. The overall effect of this condition is to retrieve data for the match in which the specified penalty kick was taken.
Alternative Solution:
JOIN with Subquery:
SELECT m.match_no, m.play_stage
FROM match_mast m
JOIN (
SELECT match_no
FROM penalty_shootout
WHERE kick_id = 23
) p ON m.match_no = p.match_no;
Explanation:
This query uses an inner join between the match_mast table and a subquery that selects match_no from penalty_shootout where kick_id is 23. The result is filtered based on matching match_no.
Using EXISTS:
SELECT match_no, play_stage
FROM match_mast m
WHERE EXISTS (
SELECT 1
FROM penalty_shootout p
WHERE p.match_no = m.match_no
AND p.kick_id = 23
);
Explanation:
This query uses the EXISTS keyword to check if there exists at least one row in the penalty_shootout table where both match_no and kick_id match the corresponding columns in match_mast.
Using IN:
SELECT match_no, play_stage
FROM match_mast
WHERE match_no IN (
SELECT match_no
FROM penalty_shootout
WHERE kick_id = 23
);
Explanation:
This query uses the IN operator to filter the match_no in match_mast based on the set of match_no values returned by the subquery that selects match_no where kick_id is 23 in the penalty_shootout table.
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: Player of portugal taken the 7th kick against poland.
Next SQL Exercise: Find the venues where penalty shootout matches played.
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://www.w3resource.com/sql-exercises/soccer-database-exercise/sql-subqueries-exercise-soccer-database-30.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics