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 maximum and minimum height of all players?
assistant
SELECT max(weight) , min(weight) FROM Player
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
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
What is the average weight of all players?
assistant
SELECT avg(weight) FROM Player
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
How many leagues are there in England?
assistant
SELECT COUNT( * ) FROM Country AS T1 JOIN League AS T2 ON T1.id = T2.country_id WHERE T1.name = "England" ;