SQL Exercise: Find the team that took penalty shot number 26
SQL soccer Database: Joins Exercise-38 with Solution
38. From the following tables, write a SQL query to find the team against which the penalty shot number 26 was taken. Return match number, country name.
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 table: soccer_country
country_id | country_abbr | country_name
------------+--------------+---------------------
1201 | ALB | Albania
1202 | AUT | Austria
1203 | BEL | Belgium
1204 | CRO | Croatia
1205 | CZE | Czech Republic
1206 | ENG | England
1207 | FRA | France
1208 | GER | Germany
1209 | HUN | Hungary
1210 | ISL | Iceland
1211 | ITA | Italy
1212 | NIR | Northern Ireland
1213 | POL | Poland
1214 | POR | Portugal
1215 | IRL | Republic of Ireland
1216 | ROU | Romania
1217 | RUS | Russia
1218 | SVK | Slovakia
1219 | ESP | Spain
1220 | SWE | Sweden
1221 | SUI | Switzerland
1222 | TUR | Turkey
1223 | UKR | Ukraine
1224 | WAL | Wales
1225 | SLO | Slovenia
1226 | NED | Netherlands
1227 | SRB | Serbia
1228 | SCO | Scotland
1229 | NOR | Norway
Sample Solution:
SQL Code:
-- This query selects the match number and country name for penalty shootouts where the team is different from the team that took kick with kick_id 26.
SELECT
match_no, -- Selecting the match number
country_name -- Selecting the country name
FROM
penalty_shootout a -- Specifying the penalty_shootout table with an alias 'a'
JOIN
soccer_country c ON a.team_id = c.country_id -- Joining the penalty_shootout table with the soccer_country table based on the team_id
WHERE
match_no = (
-- Subquery to select the match number of the penalty shootout with kick_id 26
SELECT
match_no
FROM
penalty_shootout
WHERE
kick_id = 26
)
AND country_name <> (
-- Subquery to select the country name of the team that took kick with kick_id 26
SELECT
country_name
FROM
soccer_country
WHERE
country_id = (
-- Subquery to select the team ID of the team that took kick with kick_id 26
SELECT
team_id
FROM
penalty_shootout
WHERE
kick_id = 26
)
)
-- Grouping the results by match number and country name
GROUP BY
match_no, country_name;
Sample Output:
match_no | country_name
----------+--------------
47 | Germany
(1 row)
Code Explanation:
The said query in SQL that retrieves the match number and country name from the penalty_shootout table and the soccer_country table respectively, for the same match as the kick with kick_id 26 but for a different team. It also groups the results by match number and country name.
The JOIN clause joins the penalty_shootout table aliased as "a" and the soccer_country table aliased as "c" based on the team_id and country_id columns.
The WHERE clause filters the results to only include rows where the match_no column is equal to the match number of the kick with kick_id 26. This is achieved by using a subquery to select the match number from the penalty_shootout table where kick_id=26.
The another WHERE clause also filters the results to exclude rows where the country_name column is equal to the country name of the team that took the kick with kick_id 26. This is achieved by using a subquery to select the team_id from the penalty_shootout table where kick_id=26, and then using another subquery to select the country_name from the soccer_country table where country_id is equal to the selected team_id.
The GROUP BY statement groups the results by match_no and country_name.
Alternative Solutions:
Using JOINs and IN Clause:
-- This query selects the match number and country name for penalty shootouts where the team is different from the team that took kick with kick_id 26.
SELECT
ps.match_no, -- Selecting the match number from the penalty_shootout table aliased as 'ps'
sc.country_name -- Selecting the country name from the soccer_country table aliased as 'sc'
FROM
penalty_shootout ps -- Specifying the penalty_shootout table with an alias 'ps'
JOIN
soccer_country sc ON ps.team_id = sc.country_id -- Joining the penalty_shootout table with the soccer_country table based on the team_id
WHERE
ps.match_no IN (
-- Subquery to select the match numbers of the penalty shootouts with kick_id 26
SELECT
match_no
FROM
penalty_shootout
WHERE
kick_id = 26
)
AND sc.country_name <> (
-- Subquery to select the country name of the team that took kick with kick_id 26
SELECT
country_name
FROM
soccer_country
WHERE
country_id IN (
-- Subquery to select the team IDs of the teams that took kick with kick_id 26
SELECT
team_id
FROM
penalty_shootout
WHERE
kick_id = 26
)
)
-- Grouping the results by match number and country name
GROUP BY
ps.match_no, sc.country_name;
Explanation:
This query uses JOINs and the IN clause to filter and retrieve the desired results. It selects match numbers and team IDs based on kick_id=26, then compares country names.
Using JOINs and EXISTS Clause:
-- This query retrieves match numbers and country names for penalty shootouts where the team is different from the team that took kick with kick_id 26.
SELECT
ps.match_no, -- Selecting the match number from the penalty_shootout table aliased as 'ps'
sc.country_name -- Selecting the country name from the soccer_country table aliased as 'sc'
FROM
penalty_shootout ps -- Specifying the penalty_shootout table with an alias 'ps'
JOIN
soccer_country sc ON ps.team_id = sc.country_id -- Joining the penalty_shootout table with the soccer_country table based on the team_id
WHERE
EXISTS (
-- Subquery to check if there exists a record with kick_id 26 and the same match number as the outer query
SELECT
1
FROM
penalty_shootout
WHERE
kick_id = 26 AND match_no = ps.match_no
)
AND NOT EXISTS (
-- Subquery to check if there doesn't exist a record in the soccer_country table for the team that took kick with kick_id 26 and has the same country name as the team in the outer query
SELECT
1
FROM
soccer_country
WHERE
country_id = (
-- Subquery to select the team ID of the team that took kick with kick_id 26 and has the same match number as the outer query
SELECT
team_id
FROM
penalty_shootout
WHERE
kick_id = 26 AND match_no = ps.match_no
)
AND country_name = sc.country_name
)
-- Grouping the results by match number and country name
GROUP BY
ps.match_no, sc.country_name;
Explanation:
This query uses JOINs and the EXISTS clause to filter and retrieve the desired results. It checks for the existence of match numbers with kick_id=26 and compares country names.
Practice Online
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Find the player who took the 26th penalty shot.
Next SQL Exercise: Find the captain who was also the goalkeeper.
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-joins-exercise-soccer-database-38.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics