SQL Exercise: Find the scorer of only goal along with his country
5. From the following table, write a SQL query to find out who scored in the final of the 2016 Euro Cup. Return player name, jersey number and country name.
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
..........
108 | 51 | 160319 | 1214 | 109 | N | F | ET | 2
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: 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
.......
1229 | NOR | Norway
Sample Solution:
SQL Code:
-- Selecting player_name, jersey_no, and country_name
SELECT player_name, jersey_no, country_name
-- Joining goal_details with player_mast using player_id
FROM goal_details a
JOIN player_mast b ON a.player_id = b.player_id
-- Joining the result with soccer_country using team_id and country_id
JOIN soccer_country c ON a.team_id = c.country_id
-- Filtering the results to include only goals in the final play stage
WHERE play_stage = 'F';
Sample Output:
player_name | jersey_no | country_name -------------+-----------+-------------- Eder | 9 | Portugal (1 row)
Code Explanation:
The above query in SQL that selects the player name, jersey number, and country name of all players who scored a goal in the final stage of a soccer tournament.
The JOIN clause joins the goal_details and player_mast tables based on the player_id column, and the goal_details and soccer_country tables using their respective columns team_id and country_id.
The results then filters to include only those goals scored in the final stage of the tournament.
Alternative Solutions:
Using Implicit JOIN:
-- Selecting player_name, jersey_no, and country_name
SELECT b.player_name, b.jersey_no, c.country_name
-- From clause with implicit joins between goal_details, player_mast, and soccer_country
FROM goal_details a, player_mast b, soccer_country c
-- Conditions for joining the tables based on player_id, team_id, and play_stage
WHERE a.player_id = b.player_id
AND a.team_id = c.country_id
AND a.play_stage = 'F';
Explanation:
This query uses implicit (comma-based) joins to combine the tables goal_details, player_mast, and soccer_country. It applies the necessary conditions in the WHERE clause to filter the results.
Using EXISTS:
-- Selecting player_name, jersey_no, and country_name
SELECT player_name, jersey_no, country_name
-- Joining player_mast with soccer_country using team_id and country_id
FROM player_mast pm
JOIN soccer_country sc ON pm.team_id = sc.country_id
-- Using EXISTS to filter results based on a subquery condition
WHERE EXISTS (
-- Subquery to check if there is a corresponding goal in the final play stage for the player and country
SELECT 1
FROM goal_details gd
WHERE gd.player_id = pm.player_id
AND gd.team_id = sc.country_id
AND gd.play_stage = 'F'
);
Explanation:
This query uses an EXISTS subquery to check if there exists a row in goal_details for a player and country with play_stage as 'F'. It joins the player_mast and soccer_country tables to retrieve the player details.
Using IN:
-- Selecting player_name, jersey_no, and country_name
SELECT player_name, jersey_no, country_name
-- Joining player_mast with soccer_country using team_id and country_id
FROM player_mast pm
JOIN soccer_country sc ON pm.team_id = sc.country_id
-- Filtering the results to include only players who scored in the final play stage
WHERE (pm.player_id, sc.country_id) IN (
-- Subquery to select player_id and team_id from goal_details for the final play stage
SELECT gd.player_id, gd.team_id
FROM goal_details gd
WHERE gd.play_stage = 'F'
);
Explanation:
This query uses an IN subquery to check if a pair of player_id and team_id exists in the result of the inner subquery. It joins the player_mast and soccer_country tables to retrieve the player details.
Relational Algebra Expression:
Relational Algebra Tree:
Go to:
PREV : Find the highest individual scorer in EURO cup 2016.
NEXT : Find the country where Football EURO cup 2016 held.
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.