SQL Exercise: Oldest player to have played in a EURO cup 2016 match
26. From the following tables, write a SQL query to find the oldest player to have appeared in a EURO 2016 match. Return country name, player name, jersey number and age.
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:
-- This query selects the country name, player name, jersey number, and age of players with the maximum age.
SELECT
a.country_name, -- Selecting the country name from the soccer_country table aliased as 'a'
b.player_name, -- Selecting the player name from the player_mast table aliased as 'b'
b.jersey_no, -- Selecting the jersey number from the player_mast table aliased as 'b'
b.age -- Selecting the age from the player_mast table aliased as 'b'
FROM
soccer_country a -- Specifying the soccer_country table with an alias 'a'
JOIN
player_mast b ON a.country_id = b.team_id -- Joining the player_mast table with the soccer_country table based on the team_id
WHERE
b.age IN ( -- Filtering rows where the age of players is in
SELECT
MAX(age) -- The maximum age from the player_mast table
FROM
player_mast -- Specifying the player_mast table for the subquery
);
Sample Output:
country_name | player_name | jersey_no | age
---------------------+---------------+-----------+-----
Hungary | Gabor Kiraly | 1 | 40
Republic of Ireland | Shay Given | 16 | 40
(2 rows)
Code Explanation:
The given query in SQL that selects the name of the country, player name, jersey number, and age of all players who have the maximum age among all players in the player_mast table, and joins the soccer_country table to retrieve the country name for each player based on their team_id.
The JOIN clause joins the tables soccer_country and player_mast based on the country_id and team_id columns.
The WHERE clause selects all rows from the player_mast table where the age is equal to the maximum age in the player_mast table.
Alternative Solutions:
Using a Subquery with MAX() Function:
-- This query selects the country name, player name, jersey number, and age of players with the maximum age.
SELECT
a.country_name, -- Selecting the country name from the soccer_country table aliased as 'a'
b.player_name, -- Selecting the player name from the player_mast table aliased as 'b'
b.jersey_no, -- Selecting the jersey number from the player_mast table aliased as 'b'
b.age -- Selecting the age from the player_mast table aliased as 'b'
FROM
soccer_country a -- Specifying the soccer_country table with an alias 'a'
JOIN
player_mast b ON a.country_id = b.team_id -- Joining the player_mast table with the soccer_country table based on the team_id
WHERE
b.age = ( -- Filtering rows where the age of players equals
SELECT
MAX(age) -- The maximum age from the player_mast table
FROM
player_mast -- Specifying the player_mast table for the subquery
);
Explanation:
This query uses a subquery in the WHERE clause to find the maximum age and then selects players with that maximum age.
Using JOIN with Subquery:
-- This query selects the country name, player name, jersey number, and age of players with the maximum age.
SELECT
a.country_name, -- Selecting the country name from the soccer_country table aliased as 'a'
b.player_name, -- Selecting the player name from the player_mast table aliased as 'b'
b.jersey_no, -- Selecting the jersey number from the player_mast table aliased as 'b'
b.age -- Selecting the age from the player_mast table aliased as 'b'
FROM
soccer_country a -- Specifying the soccer_country table with an alias 'a'
JOIN
player_mast b ON a.country_id = b.team_id -- Joining the player_mast table with the soccer_country table based on the team_id
JOIN
(
SELECT MAX(age) as max_age -- Subquery to find the maximum age
FROM player_mast -- Specifying the player_mast table for the subquery
) as max_age_subquery -- Aliasing the subquery as 'max_age_subquery'
ON
-- Joining the main query with the subquery on the condition of maximum age
b.age = max_age_subquery.max_age;
Explanation:
This query uses a subquery to find the maximum age and then joins it with the player_mast table to filter players with that maximum age.
Using DENSE_RANK() Window Function:
-- This query selects the country name, player name, jersey number, and age of the oldest player from each country.
SELECT
a.country_name, -- Selecting the country name from the soccer_country table aliased as 'a'
b.player_name, -- Selecting the player name from the subquery aliased as 'b'
b.jersey_no, -- Selecting the jersey number from the subquery aliased as 'b'
b.age -- Selecting the age from the subquery aliased as 'b'
FROM
soccer_country a -- Specifying the soccer_country table with an alias 'a'
JOIN
(
-- Subquery to assign a dense rank to each player based on age
SELECT
*, -- Selecting all columns from the player_mast table
DENSE_RANK() OVER (ORDER BY age DESC) as rank -- Assigning a dense rank to each player based on age in descending order
FROM
player_mast -- Specifying the player_mast table for the subquery
) b -- Aliasing the subquery as 'b'
ON
a.country_id = b.team_id -- Joining the soccer_country table with the subquery based on the team_id
-- Filtering out players with the highest rank (oldest players) from each country
WHERE
b.rank = 1;
Explanation:
This query uses the DENSE_RANK() window function to assign a rank to each player based on their age. The outer query then selects players with rank 1, which corresponds to the maximum age.
Go to:
PREV : Find the venue that has seen the most goals.
NEXT : Two teams that scored three goals in a single game.
Practice Online
Sample Database: soccer
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.