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 is the average rating for right-footed players and left-footed players?
assistant
SELECT preferred_foot , avg(overall_rating) FROM Player_Attributes GROUP BY preferred_foot
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
List all of the ids for left-footed players with a height between 180cm and 190cm.
assistant
SELECT player_api_id FROM Player WHERE height >= 180 AND height <= 190 INTERSECT SELECT player_api_id FROM Player_Attributes WHERE preferred_foot = 'left'
human
Of all players with an overall rating greater than 80, how many are right-footed and left-footed?
assistant
SELECT preferred_foot , count(*) FROM Player_Attributes WHERE overall_rating > 80 GROUP BY preferred_foot
human
List the names of all left-footed players who have overall rating between 85 and 90.
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.preferred_foot = 'left' AND T2.overall_rating >= 85 AND T2.overall_rating <= 90
human
List the names of all players who have a crossing score higher than 90 and prefer their right foot.
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.crossing > 90 AND T2.preferred_foot = "right" ;