SQL Project — Working on Soccer Database

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_capacity — 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

1. From the soccer_venue table, count the number of venues for EURO cup 2016. Return number of venues.

2. From the player_mast table, count the number of countries participated in the EURO cup 2016.

3. From the goal_details table, find the number of goals scored in EURO cup 2016 within normal time schedule.

4. From the match_mast table, find the number of matches ended with a result.

5. From the match_mast table, find the number of matches ended with draws.

6. From the match_mast table, find the date when Football EURO cup 2016 begins.

7. From the goal_details table, find the number of self-goals scored in EURO cup 2016.

8. From the match_mast table, count the number of matches ended with a win results in-group stage.

9. From the penalty_shootout table, find the number of matches got a result by penalty shootout.

10. From the match_mast table, find the number of matches decided by penalties in the Round 16.

11. From the goal_details table, find the number of goal scored in every match within normal play schedule. Sort the result-set on match number. Return match number, number of goal scored.

12. From the match_mast table, find those matches where no stoppage time added in the first half of play. Return match no, date of play, and goal scored.

13. From the match_details table, count the number of matches ending with a goalless draw in-group stage of play. Return number of matches.

14. From the match_details table, count the number of matches ending with only one goal win, except those matches, which was decided by penalty shoot-out. Return number of matches.

15. From the player_in_out table, count the number of players replaced in the tournament. Return number of players as “Player Replaced”.

16. From the player_in_out table, count the total number of players replaced within normal time of play. Return number of players as “Player Replaced”.

17. From the player_in_out table, count the number of players replaced in the stoppage time. Return number of players as “Player Replaced”.

18. From the player_in_out table, count the total number of players replaced in the first half of play. Return number of players as “Player Replaced”.

19. From the match_details table, count the total number of goalless draws have there in the entire tournament. Return number of goalless draws.

20. From the player_in_out table, count the total number of players replaced in the extra time of play.

21. From the player_in_out table, count the number of substitute happened in various stage of play for the entire Tournament. Sort the result-set in ascending order by play-half, play-schedule and number of substitute happened. Return play-half, play-schedule, number of substitute happened.

22. From the penalty_shootout table, count the number of shots taken in penalty shootout matches. Number of shots as “Number of Penalty Kicks”.

23. From the penalty_shootout table, count the number of shots scored goal in penalty shootout matches. Return number of shots scored goal as “Goal Scored by Penalty Kicks”.

24. From the penalty_shootout table, count the number of shots missed or saved in penalty shootout matches. Return number of shots missed as “Goal missed or saved by Penalty Kicks”.

25. From the penalty_shootout and player_mast tables, find the players with shot number they taken in penalty shootout matches. Return match_no, Team, player_name, jersey_no, score_goal, kick_no.

26. From the soccer_country and penalty_shootout tables, count the number of penalty shots taken by the teams. Return country name, number of shots as “Number of Shots”.

27. From the player_booked table, count the number of booking happened in each half of play within normal play schedule. Return play_half, play_schedule, number of booking happened.

28. From the player_booked table, count the number of booking happened in stoppage time.

29. From the player_booked table, count the number of booking happened in extra time.

30. From the match_details and soccer_country tables, find the teams played the first match of EURO cup 2016. Return match number, country name.

31. From the soccer_country and match_details tables, find the winner of EURO cup 2016. Return country name.

32. From the match_mast table, find the most watched match in the world. Return match_no, play_stage, goal_score, audience.

33. From the match_details and soccer_country tables, find the match number in which Germany played against Poland. Group the result set on match number. Return match number.

34. From the match_mast, match_details, and soccer_country tables, find the result of the match where Portugal played against Hungary. Return match_no, play_stage, play_date, results, goal_score.

35. From the goal_details, soccer_country, and player_mast tables, find those players who scored number of goals in every match. Group the result set on match number, country name and player name. Sort the result-set in ascending order by match number. Return match number, country name, player name and number of matches.

36. From the soccer_country, goal_details, and match_mast tables, find the highest audience match. Return country name of the teams.

37. From the player_mast, goal_details, match_details, and soccer_country tables, find the player who scored the last goal for Portugal against Hungary. Return player name.

38. From the match_mast table, find the second-highest stoppage time, which had been added, in the second half of play.

39. From the soccer_country, match_details, and match_mast tables, find the teams played the match where second highest stoppage time had been added in second half of play. Return country name of the teams.

40. From the match_mast table, find the teams played the match where second highest stoppage time had been added in second half of play. Return match_no, play_date, stop2_sec.

41. From the soccer_country and match_details tables, find the team, which was defeated by Portugal in EURO cup 2016 final. Return the country name of the team.

42. From the player_mast table, find the club, which supplied the most number of players to the 2016-EURO cup. Return club name, number of players.

43. From the player_mast and goal_details tables, find the player who scored the first penalty of the tournament. Return player name and Jersey number.

44. From the player_mast, soccer_country, and goal_details tables, find the player who scored the first penalty in the tournament. Return player name, Jersey number and country name.

45. From the player_mast, penalty_gk, and soccer_country tables, find the goalkeeper for Italy in penalty shootout against Germany in Football EURO cup 2016. Return goalkeeper name.

46. From the goal_details and soccer_country tables, find the number of goals Germany scored at the tournament.

47. From the player_mast and soccer_country tables, find the players who were the goalkeepers of England squad in 2016-EURO cup. Return player name, jersey number, club name.

48. From the player_mast and soccer_country tables, find the players under contract to Liverpool were in the Squad of England in 2016-EURO cup. Return player name, jersey number, position to play, age.

49. From the player_mast, goal_details, and soccer_country tables, find the players who scored the last goal in the 2nd semi-final, i.e., 50th match in EURO cup 2016. Return player name, goal time, goal half, country name.

50. From the player_mast, match_captain, and match_details tables, find the captain of the EURO cup 2016 winning team from Portugal. Return the captain name.

51. From the player_in_out, soccer_country, and match_mast tables, count the number of players played for ‘France’ in the final. Return ‘Number of players shared fields’.

52. From the player_mast, match_details, and soccer_country tables, find the Germany goalkeeper who didn’t concede any goal in their group stage matches. Return goalkeeper name, jersey number.

53. From the soccer_country and match_details tables, find the runners-up in Football EURO cup 2016. Return country name.

54. From the soccer_country and penalty_shootout tables, find the maximum penalty shots taken by the teams. Return country name, maximum penalty shots.

55. From the player_mast, soccer_country, and penalty_shootout tables, find the maximum number of penalty shots taken by the players. Return country name, player name, jersey number and number of penalty shots.

56. From the penalty_shootout table, find those match where the highest number of penalty shots taken.

57. From the penalty_shootout and soccer_country tables, find the match number where highest number of penalty shots had been taken. Return match number, country name.

58. From the penalty_shootout, soccer_country, and player_mast tables, find the player of ‘Portugal’ who taken the seventh kick against ‘Poland’. Return match number, player name and kick number.

59. From the match_mast and penalty_shootout tables, find the stage of match where penalty kick number 23 had been taken. Return match number, play_stage.

60. From the soccer_venue, match_mast, and penalty_shootout tables, find the venues where penalty shoot-out matches played. Return venue name.

61. From the match_mast and penalty_shootout tables, find the date when penalty shootout matches played. Return playing date.

62. From the goal_details table, find the quickest goal at the EURO cup 2016, after 5 minutes. Return ‘Quickest goal after 5 minutes’.

63. From the soccer_venue, soccer_city, and match_mast tables, find the venue where EURO cup 2016 final match held. Return venue name, city.

64. From the match_details and soccer_country tables, find the number of goal scored by each team in every match within normal play schedule. Return match number, country name and goal score.

65. From the player_mast, goal_details, and soccer_country tables, count the number of goals scored by each player within normal play schedule. Group the result set on player name and country name and sorts the result-set according to the highest to the lowest scorer. Return player name, number of goals and country name.

66. From the player_mast, goal_details, and soccer_country tables, find the highest individual scorer in EURO cup 2016. Return player name, country name and highest individual scorer.

67. From the player_mast, soccer_country, and goal_details tables, find the scorer in the final of EURO cup 2016. Return player name, jersey number and country name.

68. From the soccer_venue, soccer_city, and soccer_country tables, find the country where Football EURO cup 2016 held. Return country name.

69. From the player_mast, soccer_country, and goal_details tables, find the player who scored first goal of EURO cup 2016. Return player_name, jersey_no, country_name, goal_time, goal_schedule, play_stage, goal_half.

70. From the referee_mast, soccer_country, and match_mast tables, find the referee who managed the opening match. Return referee name, country name.

71. From the referee_mast, soccer_country, and match_mast tables, find the referee who managed the final match. Return referee name, country name.

72. From the asst_referee_mast, soccer_country, and match_details tables, find the referee who assisted the referee in the opening match. Return associated referee name, country name.

73. From the asst_referee_mast, soccer_country, and match_details tables, find the referee who assisted the referee in the final match. Return associated referee name, country name.

74. From the soccer_venue, soccer_city, and match_mast tables, find the city where the opening match of EURO cup 2016 played. Return venue name, city.

75. From the soccer_venue, soccer_city, and match_mast tables, find the stadium hosted the final match of EURO cup 2016. Return venue_name, city, aud_capacity, audience.

76. From the soccer_venue, soccer_city, and match_mast tables, count the number of matches played in each venue. Sort the result-set on venue name. Return Venue name, city, and number of matches.

77. From the player_booked, player_mast, and soccer_country tables, find the player who was the first player to be sent off at the tournament EURO cup 2016. Return match Number, country name and player name.

78. From the soccer_team and soccer_country tables, find those teams that scored only one goal to the tournament. Return country_name as “Team”, team in the group, goal_for.

79. From the soccer_venue, match_mast, and goal_details tables, count number of goals that has been scored at each venue. Return venue name and number of goals.

80. From the match_details, match_mast, and soccer_country tables, find the match where no stoppage time added in first half of play. Return match number, country name.

81. From the soccer_country and soccer_team tables, find the team(s) who conceded the most goals in EURO cup 2016. Return country name, team group and match played.

82. From the match_mast, match_details, and soccer_country tables, find those matches where highest stoppage time added in 2nd half of play. Return match number, country name, stoppage time(sec.)

83. From the match_details and soccer_country tables, find those matches ending with a goalless draw in-group stage of play. Return match number, country name.

84. From the match_mast, match_details, and soccer_country tables, find those match(s) where the 2nd highest stoppage time had been added in the second half of play. Return match number, country name and stoppage time.

85. From the player_mast, soccer_country, and match_details tables, find the number of matches played a player as a goalkeeper for his team. Return country name, player name, number of matches played as a goalkeeper.

86. From the soccer_venue, match_mast, and goal_details tables, find the venue that has seen the most number of goals. Return venue name, number of goals.

87. From the player_mast and soccer_country tables, find the oldest player appeared in a EURO cup 2016 match. Return country name, player name, jersey number and age.

88. From the match_details and soccer_country tables, find those two teams, scored three goals in a single game in this tournament. Return match number and country name.

89. From the soccer_team and soccer_country tables, find those teams that finished bottom of their respective groups after conceding four times in three games. Return country name, team group and match played.

90. From the player_mast, soccer_country, and match_details tables, find those players, who contracted to ‘Lyon’ club and participated in the EURO cup 2016 Finals. Return player name, jersey_no, position to play, age, country name.

91. From the soccer_country and match_details tables, find the final four teams in the tournament. Return country name.

92. From the player_mast, soccer_country, and match_captain tables, find the captains of the top four teams that participated in the semi-finals (match 48 and 49) in the tournament. Return country name, player name, jersey number and position to play.

93. From the player_mast, soccer_country, and match_captain tables, find the captains of all the matches in the tournament. Return match number, country name, player name, jersey number and position to play.

94. From the player_mast, match_captain, soccer_country, and match_details tables, find the captain and goalkeeper of all the matches. Return match number, Captain, Goal Keeper and country name.

95. From the player_mast and soccer_country tables, find the player who was selected for the ‘Man of the Match’ award in the finals of EURO cup 2016.Return player name, country name.

96. From the player_in_out, soccer_country, and player_mast tables, find the substitute players who came into the field in the first half of play within normal play schedule. Return match_no, country_name, player_name, jersey_no and time_in_out.

97. From the match_mast, player_mast, and soccer_country tables, prepare a list for the player of the match against each match. Return match number, play date, country name, player of the Match, jersey number.

98. From the penalty_shootout, soccer_country, and player_mast tables, find the player who taken the penalty shot number 26. Return match number, country name, player name.

99. From the penalty_shootout and soccer_country tables, find the team against which the penalty shot number 26 had been taken. Return match number, country name.

100. From the match_captain, soccer_country, and player_mast tables, find the captain who was also the goalkeeper. Return match number, country name, player name and jersey number.

101. From the match_captain, soccer_country, and player_mast tables, find the number of captains who was also the goalkeeper. Return number of captains.

102. From the player_mast, soccer_country, and player_booked tables, find the players along with their team booked number of times in the tournament. Show the result according to the team and number of times booked in descending order. Return country name, player name, and team booked number of times.

103. From the player_mast and player_booked tables, count the players who booked the most number of times. Return player name, number of players who booked most number of times.

104. From the soccer_country and player_booked tables, find the number of players booked for each team. Return country name, number of players booked.

105. From the player_booked table, find those matches where most number of cards shown. Return match number, number of cards shown.

106. From the match_details, asst_referee_mast, and soccer_country tables, find the assistant referees. Return match number, country name, assistant referee name.

107. From the match_details, asst_referee_mast, and soccer_country tables, find the assistant referees of each country assists the number of matches. Sort the result-set in descending order on number of matches. Return country name, number of matches.

108. From the match_mast, referee_mast, and soccer_country tables, find the name of referees for each match. Sort the result-set on match number. Return match number, country name, referee name.

109. From the match_mast, referee_mast, and soccer_country tables, count the number of matches managed by referees of each country. Return country name, number of matches.

110. From the match_mast, referee_mast, and soccer_country tables, find the number of matches managed by each referee. Return referee name, country name, number of matches.

111. From the match_mast, referee_mast, and soccer_country tables, find those referees who managed most of the matches. Return referee name, country name and number of matches.

112. From the match_mast, referee_mast, soccer_country, and soccer_venue tables, find those referees who managed the number of matches in each venue. Return referee name, country name, venue name, number of matches.

113. From the referee_mast, match_mast, and player_booked tables, find the referees and number of booked they made. Return referee name, number of matches.

114. From the player_mast and soccer_country tables, find those players of each team who wore jersey number 10. Return country name, player name, position to play, age and playing club.

115. From the player_mast, soccer_country, and goal_details tables, find those defenders who scored goal for their team. Return player name, jersey number, country name, age and playing club.

116. From the player_mast, soccer_country, and goal_details tables, find those players who accidentally scores against his own team. Return player name, jersey number, country name, age, position to play, and playing club.

117. From the match_details and soccer_country tables, find the results of penalty shootout matches. Return match number, play stage, country name and penalty score.

118. From the player_mast, soccer_country, and goal_details tables, find the goal scored by the players according to their playing position. Return country name, position to play, number of goals.

119. From the player_in_out, soccer_country, and player_mast tables, find those players who came into the field at the last time of play. Return match number, country name, player name, jersey number and time in out.

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store