README:
This project explores a comprehensive database containing detailed information on the 2016 UEFA European Championship (EURO 2016) soccer tournament. The analysis involves leveraging a range of SQL techniques, including basic and advanced SELECT statements, JOIN operations, and subqueries. These queries are designed to extract and present insights on various aspects of the tournament, focusing on the performance and statistics of the participating players and teams.
Description of Tables:
soccer_country:
country_id – this is a unique ID for each country country_abbr – this is the sort name of each country country_name – this is the name of each country
soccer_city:
city_id – this is a unique ID for each city city – this is the name of the city country_id – this is the ID of the country where the cities are located and only those countries will be available which are in soccer_country table
soccer_venue:
venue_id – this is a unique ID for each venue venue_name – this is the name of the venue city_id – this is the ID of the city where the venue is located and only those cities will be available which are in the soccer_city table aud_capicity – this is the capacity of audience for each venue
soccer_team:
team_id – this is the ID for each team. Each teams are representing to a country which are referencing the country_id column of soccer_country table team_group – the name of the group in which the team belongs match_played – how many matches a team played in group stage won – how many matches a team won draw – how many matches a team draws lost – how many matches a team lose goal_for – how many goals a team conceded goal_agnst – how many goals a team scored goal_diff – the difference of goal scored and goal conceded points – how many points a team achieved from their group stage matches group_position – in which position a team finished their group stage matches
playing_position:
position_id – this is a unique ID for each position where a player played position_desc – this is the name of the position where a player played
player_mast:
player_id – this is a unique ID for each player team_id – this is the team where a player played, and only those teams which referencing the country_id column of the table soccer_country jersey_no – the number which labeled on the jersey for each player player_name – name of the player posi_to_play – the position where a player played, and the positions are referencing the position_id column of playing_position table dt_of_bir – date of birth of each player age – approximate age at the time of playing the tournament playing_club – the name of the club for which a player was playing at the time of the tournament
referee_mast:
referee_id – this is the unique ID for each referee referee_name – name of the referee country_id – the country, where a referee belongs and the countries are those which referencing the country_id column of soccer_country table
match_mast:
match_no – this if the unique ID for a match play_stage – this indicates that in which stage a match is going on, i.e. G for Group stage, R for Round of 16 stage, Q for Quarter final stage, S for Semi Final stage, and F for Final play_date – date of the match played results – the result of the match, either win or draw decided_by – how the result of the match has been decided, either N for by normally or P for by penalty shootout goal_score – score for a match venue_id – the venue where the match played and the venue will be one of the venue referencing the venue_id column of soccer_venue table referee_id – ID of the referee who is selected for the match which referencing the referee_id column of referee_mast table audence – number of audience appears to watch the match plr_of_match – this is the player who awarded the player of a particular match and who is selected a 23 men playing squad for a team which referencing the player_id column of player_mast table stop1_sec – how many stoppage time ( in second) have been added for the 1st half of play stop2_sec – how many stoppage time ( in second) have been added for the 2nd half of play
coach_mast:
coach_id – this is the unique ID for a coach coach_name – this is the name of the coach
asst_referee_mast:
ass_ref_id – this is the unique ID for each referee assists the main referee ass_ref_name – name of the assistant referee country_id – the country where an assistant referee belongs and the countries are those which are referencing the country_id column of soccer_country table
match_details:
match_no – number of the match which is referencing the match_no column of match_mast table play_stage - stage of the match, i.e. G for group stage, R for Round of 16, Q for Quarter Final, S for Semi final and F for final team_id – the team which is one of the playing team and it is referencing the country_id column of soccer_country table win_lose – team either win or lose or drawn indicated by the character W, L, or D decided_by - how the result achieved by the team, indicated N for normal score or P for penalty shootout goal_score – how many goal scored by the team penalty_score – how many goal scored by the team in penalty shootout ass_ref – the assistant referee assist the referee which are referencing the ass_ref_id column of asst_referee_mast table player_gk - the player who is keeping the goal for the team, is referencing the player_id column of player_mast table
goal_details:
goal_id – this is the unique ID for each goal match_no – this is match_no which is referencing the match_no column of match_mast table player_id - this is the ID of a player who is selected for the 23 men squad of a team for the tournament and which is referencing the player_id column of player_mast table team_id – this is the ID of each team who are playing in the tournament and referencing the country_id column of soccer_country table goal_time – this is the time when the goal scored goal_type – this is the type of goal which came in normally indicated by N or own goal indicating by O and goal came from penalty indicated by P play_stage – this is the play stage in which goal scored, indicated by G for group stage, R for round of 16 stage, Q for quarter final stage, S for semifinal stage and F for final match goal_schedule – when the goal came, is it normal play session indicated by NT or in stoppage time indicated by ST or in extra time indicated by ET goal_half – in which half of match goal came
penalty_shootout:
kick_id – this is unique ID for each penalty kick match_no - this is the match_no which is referencing the match_no column of match_mast table team_id – this is the ID of each team who is playing in the tournament and referencing the country_id column of soccer_country table player_id - this is the ID of a player who is selected for the 23 men squad of a team for the tournament and which is referencing the player_id column of player_mast table score_goal – this is the flag Y if able to score the goal or N when not kick_no – this is the kick number for the kick of an individual match
player_booked:
match_no - this is the match_no which is referencing the match_no column of match_mast table team_id – this is the ID of each team who are playing in the tournament and referencing the country_id column of soccer_country table player_id - this is the ID of a player who is selected for the 23 men squad of a team for the tournament and which is referencing the player_id column of player_mast table booking_time – this is the time when a player booked sent_off – this is the flag Y when a player sent off play_schedule – when a player booked, is it in normal play session indicated by NT or in stoppage time indicated by ST or in extra time indicated by ET play_half – in which half a player booked
player_in_out:
match_no - this is the match_no which is referencing the match_no column of match_mast table team_id – this is the ID of each team who are playing in the tournament and referencing the country_id column of soccer_country table player_id - this is the ID of a player who is selected for the 23 men squad of a team for the tournament and which is referencing the player_id column of player_mast table in_out – this is the flag I when a player came into the field or O when go out from the field time_in_out – when a player come into the field or go out from the field play_schedule – when a player come in or go out of the field, is it in normal play session indicated by NT or in stoppage time indicated by ST or in extra time indicated by ET play_half - in which half a player come in or go out
match_captain:
match_no - this is the match_no which is referencing the match_no column of match_mast table team_id – this is the ID of each team who are playing in the tournament and referencing the country_id column of soccer_country table player_captain - the player who represents as a captain for a team, is referencing the player_id column of player_mast table
team_coaches:
team_id – this is the ID of a team who is playing in the tournament and referencing the country_id column of soccer_country table coach_id – a team may be one or more coaches, this indicates the coach(s) who is/are coaching the team is referencing the coach_id column of coach_mast table
penalty_gk:
match_no - this is the match_no which is referencing the match_no column of match_mast table team_id – this is the ID of each team who are playing in the tournament and referencing the country_id column of soccer_country table player_gk - the player who kept goal at the time of penalty shootout, is referencing the player_id column of player_mast table
ERD:
-- (1) Determine substitutes during various stages of the tournament
SELECT
play_half,
play_schedule,
COUNT(*) AS 'total_substitutes'
FROM
'player_in_out - Sheet1.csv'
WHERE
in_out = 'I'
GROUP BY
play_half,
play_schedule
ORDER BY
play_half,
play_schedule;-- (2) Determine who scored the first penalty of the tournament
SELECT
p.player_name,
p.jersey_no
FROM
'player_mast - Sheet1.csv' AS p,
'goal_details - Sheet1.csv' AS g
WHERE
p.player_id = g.player_id AND
g.goal_type = 'P'
ORDER BY
g.goal_id
LIMIT 1;-- (3) Determine leading scorers of goals from normal play
SELECT
p.player_name,
COUNT(*) AS goal_count,
c.country_name
FROM
'goal_details - Sheet1.csv' AS g
JOIN
'player_mast - Sheet1.csv' AS p
ON
g.player_id = p.player_id
JOIN
'soccer_country - Sheet1.csv' AS c
ON
p.team_id = c.country_id
WHERE
g.goal_schedule = 'NT'
GROUP BY
p.player_name,
c.country_name
ORDER BY
goal_count DESC;-- (4) Determine which players on England's squad play for Liverpool
SELECT
p.player_name,
p.jersey_no,
p.posi_to_play,
p.age
FROM
'player_mast - Sheet1.csv' AS p
JOIN
'soccer_country - Sheet1.csv' AS c
ON
p.team_id = c.country_id
WHERE
c.country_name = 'England' AND
p.playing_club = 'Liverpool';-- (5) Determine in what stadiums the matches with penalty shoot-outs were played
SELECT
venue_name
FROM
'soccer_venue - Sheet1.csv'
WHERE
venue_id IN(
SELECT venue_id
FROM 'match_mast - Sheet1.csv'
WHERE match_no IN(
SELECT DISTINCT match_no
FROM 'penalty_shootout - Sheet1.csv'));
-- (6) Determine which club teams supplied the most players for the tournament
SELECT
playing_club,
COUNT(playing_club) AS players
FROM
'player_mast - Sheet1.csv'
GROUP BY
playing_club
HAVING
COUNT(playing_club) = (
SELECT MAX(maxcount)
FROM (
SELECT playing_club, COUNT(playing_club) AS maxcount
FROM 'player_mast - Sheet1.csv'
GROUP BY playing_club)
);