Database: soccer_1

CREATE TABLE "Player_Attributes" (
	`id`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`player_fifa_api_id`	INTEGER,
	`player_api_id`	INTEGER,
	`date`	TEXT,
	`overall_rating`	INTEGER,
	`potential`	INTEGER,
	`preferred_foot`	TEXT,
	`attacking_work_rate`	TEXT,
	`defensive_work_rate`	TEXT,
	`crossing`	INTEGER,
	`finishing`	INTEGER,
	`heading_accuracy`	INTEGER,
	`short_passing`	INTEGER,
	`volleys`	INTEGER,
	`dribbling`	INTEGER,
	`curve`	INTEGER,
	`free_kick_accuracy`	INTEGER,
	`long_passing`	INTEGER,
	`ball_control`	INTEGER,
	`acceleration`	INTEGER,
	`sprint_speed`	INTEGER,
	`agility`	INTEGER,
	`reactions`	INTEGER,
	`balance`	INTEGER,
	`shot_power`	INTEGER,
	`jumping`	INTEGER,
	`stamina`	INTEGER,
	`strength`	INTEGER,
	`long_shots`	INTEGER,
	`aggression`	INTEGER,
	`interceptions`	INTEGER,
	`positioning`	INTEGER,
	`vision`	INTEGER,
	`penalties`	INTEGER,
	`marking`	INTEGER,
	`standing_tackle`	INTEGER,
	`sliding_tackle`	INTEGER,
	`gk_diving`	INTEGER,
	`gk_handling`	INTEGER,
	`gk_kicking`	INTEGER,
	`gk_positioning`	INTEGER,
	`gk_reflexes`	INTEGER,
	FOREIGN KEY(`player_fifa_api_id`) REFERENCES `Player`(`player_fifa_api_id`),
	FOREIGN KEY(`player_api_id`) REFERENCES `Player`(`player_api_id`)
);
CREATE TABLE `Player` (
	`id`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`player_api_id`	INTEGER UNIQUE,
	`player_name`	TEXT,
	`player_fifa_api_id`	INTEGER UNIQUE,
	`birthday`	TEXT,
	`height`	INTEGER,
	`weight`	INTEGER
);
CREATE TABLE `Match` (
	`id`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`country_id`	INTEGER,
	`league_id`	INTEGER,
	`season`	TEXT,
	`stage`	INTEGER,
	`date`	TEXT,
	`match_api_id`	INTEGER UNIQUE,
	`home_team_api_id`	INTEGER,
	`away_team_api_id`	INTEGER,
	`home_team_goal`	INTEGER,
	`away_team_goal`	INTEGER,
	`home_player_X1`	INTEGER,
	`home_player_X2`	INTEGER,
	`home_player_X3`	INTEGER,
	`home_player_X4`	INTEGER,
	`home_player_X5`	INTEGER,
	`home_player_X6`	INTEGER,
	`home_player_X7`	INTEGER,
	`home_player_X8`	INTEGER,
	`home_player_X9`	INTEGER,
	`home_player_X10`	INTEGER,
	`home_player_X11`	INTEGER,
	`away_player_X1`	INTEGER,
	`away_player_X2`	INTEGER,
	`away_player_X3`	INTEGER,
	`away_player_X4`	INTEGER,
	`away_player_X5`	INTEGER,
	`away_player_X6`	INTEGER,
	`away_player_X7`	INTEGER,
	`away_player_X8`	INTEGER,
	`away_player_X9`	INTEGER,
	`away_player_X10`	INTEGER,
	`away_player_X11`	INTEGER,
	`home_player_Y1`	INTEGER,
	`home_player_Y2`	INTEGER,
	`home_player_Y3`	INTEGER,
	`home_player_Y4`	INTEGER,
	`home_player_Y5`	INTEGER,
	`home_player_Y6`	INTEGER,
	`home_player_Y7`	INTEGER,
	`home_player_Y8`	INTEGER,
	`home_player_Y9`	INTEGER,
	`home_player_Y10`	INTEGER,
	`home_player_Y11`	INTEGER,
	`away_player_Y1`	INTEGER,
	`away_player_Y2`	INTEGER,
	`away_player_Y3`	INTEGER,
	`away_player_Y4`	INTEGER,
	`away_player_Y5`	INTEGER,
	`away_player_Y6`	INTEGER,
	`away_player_Y7`	INTEGER,
	`away_player_Y8`	INTEGER,
	`away_player_Y9`	INTEGER,
	`away_player_Y10`	INTEGER,
	`away_player_Y11`	INTEGER,
	`home_player_1`	INTEGER,
	`home_player_2`	INTEGER,
	`home_player_3`	INTEGER,
	`home_player_4`	INTEGER,
	`home_player_5`	INTEGER,
	`home_player_6`	INTEGER,
	`home_player_7`	INTEGER,
	`home_player_8`	INTEGER,
	`home_player_9`	INTEGER,
	`home_player_10`	INTEGER,
	`home_player_11`	INTEGER,
	`away_player_1`	INTEGER,
	`away_player_2`	INTEGER,
	`away_player_3`	INTEGER,
	`away_player_4`	INTEGER,
	`away_player_5`	INTEGER,
	`away_player_6`	INTEGER,
	`away_player_7`	INTEGER,
	`away_player_8`	INTEGER,
	`away_player_9`	INTEGER,
	`away_player_10`	INTEGER,
	`away_player_11`	INTEGER,
	`goal`	TEXT,
	`shoton`	TEXT,
	`shotoff`	TEXT,
	`foulcommit`	TEXT,
	`card`	TEXT,
	`cross`	TEXT,
	`corner`	TEXT,
	`possession`	TEXT,
	`B365H`	NUMERIC,
	`B365D`	NUMERIC,
	`B365A`	NUMERIC,
	`BWH`	NUMERIC,
	`BWD`	NUMERIC,
	`BWA`	NUMERIC,
	`IWH`	NUMERIC,
	`IWD`	NUMERIC,
	`IWA`	NUMERIC,
	`LBH`	NUMERIC,
	`LBD`	NUMERIC,
	`LBA`	NUMERIC,
	`PSH`	NUMERIC,
	`PSD`	NUMERIC,
	`PSA`	NUMERIC,
	`WHH`	NUMERIC,
	`WHD`	NUMERIC,
	`WHA`	NUMERIC,
	`SJH`	NUMERIC,
	`SJD`	NUMERIC,
	`SJA`	NUMERIC,
	`VCH`	NUMERIC,
	`VCD`	NUMERIC,
	`VCA`	NUMERIC,
	`GBH`	NUMERIC,
	`GBD`	NUMERIC,
	`GBA`	NUMERIC,
	`BSH`	NUMERIC,
	`BSD`	NUMERIC,
	`BSA`	NUMERIC,
	FOREIGN KEY(`country_id`) REFERENCES `Country`(`id`),
	FOREIGN KEY(`league_id`) REFERENCES `League`(`id`),
	FOREIGN KEY(`home_team_api_id`) REFERENCES `Team`(`team_api_id`),
	FOREIGN KEY(`away_team_api_id`) REFERENCES `Team`(`team_api_id`),
	FOREIGN KEY(`home_player_1`) REFERENCES `Player`(`player_api_id`),
	FOREIGN KEY(`home_player_2`) REFERENCES `Player`(`player_api_id`),
	FOREIGN KEY(`home_player_3`) REFERENCES `Player`(`player_api_id`),
	FOREIGN KEY(`home_player_4`) REFERENCES `Player`(`player_api_id`),
	FOREIGN KEY(`home_player_5`) REFERENCES `Player`(`player_api_id`),
	FOREIGN KEY(`home_player_6`) REFERENCES `Player`(`player_api_id`),
	FOREIGN KEY(`home_player_7`) REFERENCES `Player`(`player_api_id`),
	FOREIGN KEY(`home_player_8`) REFERENCES `Player`(`player_api_id`),
	FOREIGN KEY(`home_player_9`) REFERENCES `Player`(`player_api_id`),
	FOREIGN KEY(`home_player_10`) REFERENCES `Player`(`player_api_id`),
	FOREIGN KEY(`home_player_11`) REFERENCES `Player`(`player_api_id`),
	FOREIGN KEY(`away_player_1`) REFERENCES `Player`(`player_api_id`),
	FOREIGN KEY(`away_player_2`) REFERENCES `Player`(`player_api_id`),
	FOREIGN KEY(`away_player_3`) REFERENCES `Player`(`player_api_id`),
	FOREIGN KEY(`away_player_4`) REFERENCES `Player`(`player_api_id`),
	FOREIGN KEY(`away_player_5`) REFERENCES `Player`(`player_api_id`),
	FOREIGN KEY(`away_player_6`) REFERENCES `Player`(`player_api_id`),
	FOREIGN KEY(`away_player_7`) REFERENCES `Player`(`player_api_id`),
	FOREIGN KEY(`away_player_8`) REFERENCES `Player`(`player_api_id`),
	FOREIGN KEY(`away_player_9`) REFERENCES `Player`(`player_api_id`),
	FOREIGN KEY(`away_player_10`) REFERENCES `Player`(`player_api_id`),
	FOREIGN KEY(`away_player_11`) REFERENCES `Player`(`player_api_id`)
);
CREATE TABLE `League` (
	`id`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`country_id`	INTEGER,
	`name`	TEXT UNIQUE,
	FOREIGN KEY(`country_id`) REFERENCES `Country`(`id`)
);
CREATE TABLE `Country` (
	`id`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`name`	TEXT UNIQUE
);
CREATE TABLE "Team" (
	`id`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`team_api_id`	INTEGER UNIQUE,
	`team_fifa_api_id`	INTEGER,
	`team_long_name`	TEXT,
	`team_short_name`	TEXT
);
CREATE TABLE `Team_Attributes` (
	`id`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`team_fifa_api_id`	INTEGER,
	`team_api_id`	INTEGER,
	`date`	TEXT,
	`buildUpPlaySpeed`	INTEGER,
	`buildUpPlaySpeedClass`	TEXT,
	`buildUpPlayDribbling`	INTEGER,
	`buildUpPlayDribblingClass`	TEXT,
	`buildUpPlayPassing`	INTEGER,
	`buildUpPlayPassingClass`	TEXT,
	`buildUpPlayPositioningClass`	TEXT,
	`chanceCreationPassing`	INTEGER,
	`chanceCreationPassingClass`	TEXT,
	`chanceCreationCrossing`	INTEGER,
	`chanceCreationCrossingClass`	TEXT,
	`chanceCreationShooting`	INTEGER,
	`chanceCreationShootingClass`	TEXT,
	`chanceCreationPositioningClass`	TEXT,
	`defencePressure`	INTEGER,
	`defencePressureClass`	TEXT,
	`defenceAggression`	INTEGER,
	`defenceAggressionClass`	TEXT,
	`defenceTeamWidth`	INTEGER,
	`defenceTeamWidthClass`	TEXT,
	`defenceDefenderLineClass`	TEXT,
	FOREIGN KEY(`team_fifa_api_id`) REFERENCES `Team`(`team_fifa_api_id`),
	FOREIGN KEY(`team_api_id`) REFERENCES `Team`(`team_api_id`)
);

Question: List all country and league names.
SQL: SELECT T1.name ,  T2.name FROM Country AS T1 JOIN League AS T2 ON T1.id  =  T2.country_id

Question: How many leagues are there in England?
SQL: SELECT count(*) FROM Country AS T1 JOIN League AS T2 ON T1.id  =  T2.country_id WHERE T1.name  =  "England"

Question: What is the average weight of all players?
SQL: SELECT avg(weight) FROM Player

Question: What is the maximum and minimum height of all players?
SQL: SELECT max(weight) ,  min(weight) FROM Player

Question: List all player names who have an overall rating higher than the average.
SQL: 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 )

Question: What are the names of players who have the best dribbling?
SQL: 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)

Question: List the names of all players who have a crossing score higher than 90 and prefer their right foot.
SQL: 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"

Question: List the names of all left-footed players who have overall rating between 85 and 90.
SQL: 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

Question: What is the average rating for right-footed players and left-footed players?
SQL: SELECT preferred_foot ,  avg(overall_rating) FROM Player_Attributes GROUP BY preferred_foot

Question: Of all players with an overall rating greater than 80, how many are right-footed and left-footed?
SQL: SELECT preferred_foot ,  count(*) FROM Player_Attributes WHERE overall_rating  >  80 GROUP BY preferred_foot

Question: List all of the player ids with a height of at least 180cm and an overall rating higher than 85.
SQL: SELECT player_api_id FROM Player WHERE height  >=  180 INTERSECT SELECT player_api_id FROM Player_Attributes WHERE overall_rating  >  85

Question: List all of the ids for left-footed players with a height between 180cm and 190cm.
SQL: SELECT player_api_id FROM Player WHERE height  >=  180 AND height  <=  190 INTERSECT SELECT player_api_id FROM Player_Attributes WHERE preferred_foot  =  "left"

Question: Who are the top 3 players in terms of overall rating?
SQL: 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

Question: List the names and birthdays of the top five players in terms of potential.
SQL: 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