human
Schema:
Player_Attributes (id, player_fifa_api_id, player_api_id, date, overall_rating, potential, preferred_foot, attacking_work_rate, defensive_work_rate, crossing, finishing, heading_accuracy, short_passing, volleys, dribbling, curve, free_kick_accuracy, long_passing, ball_control, acceleration, sprint_speed, agility, reactions, balance, shot_power, jumping, stamina, strength, long_shots, aggression, interceptions, positioning, vision, penalties, marking, standing_tackle, sliding_tackle, gk_diving, gk_handling, gk_kicking, gk_positioning, gk_reflexes)
sqlite_sequence (name, seq)
Player (id, player_api_id, player_name, player_fifa_api_id, birthday, height, weight)
League (id, country_id, name)
Country (id, name)
Team (id, team_api_id, team_fifa_api_id, team_long_name, team_short_name)
Team_Attributes (id, team_fifa_api_id, team_api_id, date, buildUpPlaySpeed, buildUpPlaySpeedClass, buildUpPlayDribbling, buildUpPlayDribblingClass, buildUpPlayPassing, buildUpPlayPassingClass, buildUpPlayPositioningClass, chanceCreationPassing, chanceCreationPassingClass, chanceCreationCrossing, chanceCreationCrossingClass, chanceCreationShooting, chanceCreationShootingClass, chanceCreationPositioningClass, defencePressure, defencePressureClass, defenceAggression, defenceAggressionClass, defenceTeamWidth, defenceTeamWidthClass, defenceDefenderLineClass)
Please answer with SQL.
assistant
Sure, I understand.
human
What are the names of players who have the best dribbling?
assistant
SELECT DISTINCT T1.player_name FROM Player AS T1 JOIN Player_Attributes AS T2 ON T1.player_api_id = T2.player_api_id WHERE T2.dribbling = ( SELECT max(overall_rating) FROM Player_Attributes)
human
List all country and league names.
assistant
SELECT T1.name , T2.name FROM Country AS T1 JOIN League AS T2 ON T1.id = T2.country_id
human
List all of the player ids with a height of at least 180cm and an overall rating higher than 85.
assistant
SELECT player_api_id FROM Player WHERE height >= 180 INTERSECT SELECT player_api_id FROM Player_Attributes WHERE overall_rating > 85
human
Who are the top 3 players in terms of overall rating?
assistant
SELECT DISTINCT T1.player_name FROM Player AS T1 JOIN Player_Attributes AS T2 ON T1.player_api_id = T2.player_api_id ORDER BY overall_rating DESC LIMIT 3
human
List all player names who have an overall rating higher than the average.
assistant
SELECT DISTINCT T1.player_name FROM Player AS T1 JOIN Player_Attributes AS T2 ON T1.player_api_id = T2.player_api_id WHERE T2.overall_rating > ( SELECT avg(overall_rating) FROM Player_Attributes )
human
List the names and birthdays of the top five players in terms of potential.
assistant
SELECT DISTINCT T1.player_name , T1.birthday FROM Player AS T1 JOIN Player_Attributes AS T2 ON T1.player_api_id = T2.player_api_id ORDER BY potential DESC LIMIT 5 ;