SQL Exercise: Italy Goalkeeper in 2016 penalty shootout with Germany
16. From the following tables, write a SQL query to find the goalkeeper for Italy in penalty shootout against Germany in Football EURO cup 2016. Return goalkeeper name.
Sample table: player_mast
player_id | team_id | jersey_no | player_name | posi_to_play | dt_of_bir | age | playing_club
-----------+---------+-----------+-------------------------+--------------+------------+-----+---------------------
160001 | 1201 | 1 | Etrit Berisha | GK | 1989-03-10 | 27 | Lazio
160008 | 1201 | 2 | Andi Lila | DF | 1986-02-12 | 30 | Giannina
160016 | 1201 | 3 | Ermir Lenjani | MF | 1989-08-05 | 26 | Nantes
160007 | 1201 | 4 | Elseid Hysaj | DF | 1994-02-20 | 22 | Napoli
160013 | 1201 | 5 | Lorik Cana | MF | 1983-07-27 | 32 | Nantes
160010 | 1201 | 6 | Frederic Veseli | DF | 1992-11-20 | 23 | Lugano
160004 | 1201 | 7 | Ansi Agolli | DF | 1982-10-11 | 33 | Qarabag
160012 | 1201 | 8 | Migjen Basha | MF | 1987-01-05 | 29 | Como
160017 | 1201 | 9 | Ledian Memushaj | MF | 1986-12-17 | 29 | Pescara
......
160548 | 1224 | 23 | Simon Church | FD | 1988-12-10 | 27 | MK Dons
Sample table: penalty_gk
match_no | team_id | player_gk
----------+---------+-----------
37 | 1221 | 160463
37 | 1213 | 160278
45 | 1213 | 160278
45 | 1214 | 160302
47 | 1208 | 160163
47 | 1211 | 160231
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
.......
1229 | NOR | Norway
Sample Solution:
SQL Code:
-- Selecting player_name from the player_mast table
SELECT player_name
-- Filtering by player_id
FROM player_mast
-- Subquery to find player_id based on penalty_gk
WHERE player_id=(
-- Selecting player_gk from penalty_gk table
SELECT player_gk
-- Subquery to find match_no from penalty_gk
FROM penalty_gk
-- Filtering match_no based on team_id
WHERE match_no=(
-- Subquery to find match_no based on countries Italy and Germany
SELECT match_no
-- Subquery to find match_no based on countries Italy and Germany
FROM penalty_gk
-- Filtering team_id based on countries Italy and Germany
WHERE team_id=(
-- Subquery to find country_id of Italy
SELECT country_id
FROM soccer_country
WHERE country_name='Italy')
OR team_id=(
-- Subquery to find country_id of Germany
SELECT country_id
FROM soccer_country
WHERE country_name='Germany')
-- Grouping match_no and counting distinct team_id to ensure both Italy and Germany are participating in the match
GROUP BY match_no
HAVING COUNT(DISTINCT team_id)=2
)
-- Filtering by team_id of Italy
AND team_id=(
-- Subquery to find country_id of Italy
SELECT country_id
FROM soccer_country
WHERE country_name='Italy')
);
Sample Output:
player_name
-------------------
Gianluigi Buffon
(1 row)
Code Explanation:
The said query in SQL that retrieves the player name of the goalkeeper who played for Italy in a penalty shootout match against Germany.
1. The outermost query selects the player name from the player_mast table.
2. The WHERE clause filters the results to only include the player with a matching player_id.
3. The player_id is obtained from a subquery that selects the player_gk from the penalty_gk table.
4. The penalty_gk table is filtered to only include the match_no where the teams involved are Italy and Germany.
5. The match_no is obtained from a subquery that selects it from the penalty_gk table, using the same filter as in step 4.
6. The subquery in step 4 also ensures that both Italy and Germany are involved in the match by using a GROUP BY clause to group the results by match_no, and a HAVING clause to filter the results to only include matches where there are exactly two distinct team_ids.
7. Finally, the subquery in step 3 filters the results to only include the goalkeeper who played for Italy in the match by using another WHERE clause that matches the team_id to the country_id of Italy in the soccer_country table.
Alternative Solution:
Using JOIN and Subqueries:
-- Selecting player_name from the player_mast table
SELECT pm.player_name
-- Joining player_mast and penalty_gk tables based on player_id and player_gk respectively
FROM player_mast pm
JOIN penalty_gk pg ON pm.player_id = pg.player_gk
-- Filtering by match_no and team_id
WHERE pg.match_no = (
-- Subquery to find match_no based on teams Italy and Germany
SELECT pg2.match_no
FROM penalty_gk pg2
-- Joining penalty_gk and soccer_country tables based on team_id and country_id respectively
JOIN soccer_country sc ON pg2.team_id = sc.country_id
-- Filtering countries Italy and Germany
WHERE sc.country_name IN ('Italy', 'Germany')
-- Grouping by match_no and counting distinct country_id to ensure both Italy and Germany are participating in the match
GROUP BY pg2.match_no
HAVING COUNT(DISTINCT sc.country_id) = 2
)
-- Filtering by team_id of Italy
AND pg.team_id = (
-- Subquery to find country_id of Italy
SELECT country_id
FROM soccer_country
WHERE country_name = 'Italy'
);
Explanation:
This query uses subqueries with JOINs to connect the necessary tables. It first identifies the match_no based on the specified teams, and then finds the goalkeeper player_id for that match.
Go to:
PREV : First penalty along with his team and jersey number.
NEXT : Number of goals Germany scored at the tournament.
Practice Online
Sample Database: soccer
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.