SQL Exercise: Find the player who socred first goal of EURO cup 2016
7. From the following tables, write a SQL query to find out who scored the first goal of the 2016 European Championship. Return player_name, jersey_no, country_name, goal_time, play_stage, goal_schedule, goal_half.
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 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: 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 Solution:
SQL Code:
-- Selecting player information, country name, goal details
SELECT a.player_name, a.jersey_no, b.country_name, c.goal_time,
c.play_stage, c.goal_schedule, c.goal_half
-- From clause to select from player_mast
FROM player_mast a
-- Joining player_mast with soccer_country using team_id and country_id
JOIN soccer_country b ON a.team_id = b.country_id
-- Joining the result with goal_details using player_id
JOIN goal_details c ON c.player_id = a.player_id
-- Filtering the results to include only the goal with goal_id equal to 1
WHERE goal_id = 1;
Sample Output:
player_name | jersey_no | country_name | goal_time | play_stage | goal_schedule | goal_half -----------------+-----------+--------------+-----------+------------+---------------+----------- Olivier Giroud | 9 | France | 57 | G | NT | 2 (1 row)
Code Explanation:
The provided query in SQL that retrieves information about the player who scored the goal with goal_id equal to 1, including their name, jersey number, country name, and details about the goal from the tables player_mast, soccer_country, and goal_details.
The JOIN clause joins the tables player_mast and soccer_country based on the team_id and country_id columns, respectively, while player_mast and goal_details are joined on the player_id column.
This condition filters only rows for the goal with goal_id equal to 1 .
Alternative Solutions:
Using Implicit JOIN:
-- Selecting player information, country name, goal details
SELECT a.player_name, a.jersey_no, b.country_name, c.goal_time,
c.play_stage, c.goal_schedule, c.goal_half
-- From clause with implicit joins between player_mast, soccer_country, and goal_details
FROM player_mast a, soccer_country b, goal_details c
-- Conditions for joining the tables based on team_id, country_id, player_id, and goal_id
WHERE a.team_id = b.country_id
AND a.player_id = c.player_id
AND c.goal_id = 1;
Explanation:
This query uses implicit joins to combine the tables player_mast, soccer_country, and goal_details. It applies the necessary conditions in the WHERE clause.
Using Subquery with JOIN:
-- Selecting player information, country name, goal details
SELECT a.player_name, a.jersey_no, b.country_name, c.goal_time,
c.play_stage, c.goal_schedule, c.goal_half
-- Subquery to select all columns from player_mast (not necessary in this case)
FROM (
SELECT *
FROM player_mast
) a
-- Joining the result with soccer_country using team_id and country_id
JOIN soccer_country b ON a.team_id = b.country_id
-- Joining the result with goal_details using player_id
JOIN goal_details c ON a.player_id = c.player_id
-- Filtering the results to include only the goal with goal_id equal to 1
WHERE c.goal_id = 1;
Explanation:
This query uses a subquery to select all columns from player_mast and then joins it with soccer_country and goal_details. It applies the necessary conditions on goal_id.
Relational Algebra Expression:
Relational Algebra Tree:
Go to:
PREV : Find the country where Football EURO cup 2016 held.
NEXT : Name and country of referee who managed the first game.
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.