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_shootoutkick_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 | 18Sample 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://www.w3resource.com/sql-exercises/soccer-database-exercise/sql-joins-exercise-soccer-database-38.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics