
CREATE TABLE "Country" (
	id INTEGER, 
	name TEXT, 
	PRIMARY KEY (id)
)


CREATE TABLE "League" (
	id INTEGER, 
	country_id INTEGER, 
	name TEXT, 
	PRIMARY KEY (id), 
	FOREIGN KEY(country_id) REFERENCES "Country" (id)
)


CREATE TABLE "Player" (
	id INTEGER, 
	player_api_id INTEGER, 
	player_name TEXT, 
	player_fifa_api_id INTEGER, 
	birthday TEXT, 
	height INTEGER, 
	weight INTEGER, 
	PRIMARY KEY (id)
)


CREATE TABLE "Player_Attributes" (
	id INTEGER, 
	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, 
	PRIMARY KEY (id), 
	FOREIGN KEY(player_api_id) REFERENCES "Player" (player_api_id), 
	FOREIGN KEY(player_fifa_api_id) REFERENCES "Player" (player_fifa_api_id)
)


CREATE TABLE "Team" (
	id INTEGER, 
	team_api_id INTEGER, 
	team_fifa_api_id INTEGER, 
	team_long_name TEXT, 
	team_short_name TEXT, 
	PRIMARY KEY (id)
)


CREATE TABLE "Team_Attributes" (
	id INTEGER, 
	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, 
	PRIMARY KEY (id), 
	FOREIGN KEY(team_api_id) REFERENCES "Team" (team_api_id), 
	FOREIGN KEY(team_fifa_api_id) REFERENCES "Team" (team_fifa_api_id)
)

CREATE TABLE "Country" (
	id INTEGER, 
	name TEXT, 
	PRIMARY KEY (id)
)

/*
2 rows from Country table:
id	name
1	Belgium
1729	England
*/


CREATE TABLE "League" (
	id INTEGER, 
	country_id INTEGER, 
	name TEXT, 
	PRIMARY KEY (id), 
	FOREIGN KEY(country_id) REFERENCES "Country" (id)
)

/*
2 rows from League table:
id	country_id	name
1	1	Belgium Jupiler League
1729	1729	England Premier League
*/


CREATE TABLE "Player" (
	id INTEGER, 
	player_api_id INTEGER, 
	player_name TEXT, 
	player_fifa_api_id INTEGER, 
	birthday TEXT, 
	height INTEGER, 
	weight INTEGER, 
	PRIMARY KEY (id)
)

/*
2 rows from Player table:
id	player_api_id	player_name	player_fifa_api_id	birthday	height	weight
1	505942	Aaron Appindangoye	218353	1992-02-29 00:00:00	182.88	187
2	155782	Aaron Cresswell	189615	1989-12-15 00:00:00	170.18	146
*/


CREATE TABLE "Player_Attributes" (
	id INTEGER, 
	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, 
	PRIMARY KEY (id), 
	FOREIGN KEY(player_api_id) REFERENCES "Player" (player_api_id), 
	FOREIGN KEY(player_fifa_api_id) REFERENCES "Player" (player_fifa_api_id)
)

/*
2 rows from Player_Attributes table:
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
1	218353	505942	2016-02-18 00:00:00	67	71	right	medium	medium	49	44	71	61	44	51	45	39	64	49	60	64	59	47	65	55	58	54	76	35	71	70	45	54	48	65	69	69	6	11	10	8	8
2	218353	505942	2015-11-19 00:00:00	67	71	right	medium	medium	49	44	71	61	44	51	45	39	64	49	60	64	59	47	65	55	58	54	76	35	71	70	45	54	48	65	69	69	6	11	10	8	8
*/


CREATE TABLE "Team" (
	id INTEGER, 
	team_api_id INTEGER, 
	team_fifa_api_id INTEGER, 
	team_long_name TEXT, 
	team_short_name TEXT, 
	PRIMARY KEY (id)
)

/*
2 rows from Team table:
id	team_api_id	team_fifa_api_id	team_long_name	team_short_name
1	9987	673	KRC Genk	GEN
2	9993	675	Beerschot AC	BAC
*/


CREATE TABLE "Team_Attributes" (
	id INTEGER, 
	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, 
	PRIMARY KEY (id), 
	FOREIGN KEY(team_api_id) REFERENCES "Team" (team_api_id), 
	FOREIGN KEY(team_fifa_api_id) REFERENCES "Team" (team_fifa_api_id)
)

/*
2 rows from Team_Attributes table:
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
1	434	9930	2010-02-22 00:00:00	60	Balanced	None	Little	50	Mixed	Organised	60	Normal	65	Normal	55	Normal	Organised	50	Medium	55	Press	45	Normal	Cover
2	434	9930	2014-09-19 00:00:00	52	Balanced	48	Normal	56	Mixed	Organised	54	Normal	63	Normal	64	Normal	Organised	47	Medium	44	Press	54	Normal	Cover
*/
List the names of all left-footed players who have overall rating between 85 and 90.
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
[{'player_name': 'Adriano'}, {'player_name': 'Angel Di Maria'}, {'player_name': 'Arjen Robben'}, {'player_name': 'Ashley Cole'}, {'player_name': 'Cristian Chivu'}, {'player_name': 'David Alaba'}, {'player_name': 'David Silva'}, {'player_name': 'Dida'}, {'player_name': 'Esteban Cambiasso'}, {'player_name': 'Florent Malouda'}, {'player_name': 'Gabriel Heinze'}, {'player_name': 'Gabriel Milito'}, {'player_name': 'Gael Givet'}, {'player_name': 'Gareth Bale'}, {'player_name': 'Giorgio Chiellini'}, {'player_name': 'Giuseppe Rossi'}, {'player_name': 'Hugo Lloris'}, {'player_name': 'Hulk'}, {'player_name': 'Iker Casillas'}, {'player_name': 'James Rodriguez'}, {'player_name': 'Jerome Rothen'}, {'player_name': 'Jose Antonio Reyes'}, {'player_name': 'Juan Mata'}, {'player_name': 'Julio Cesar'}, {'player_name': 'Kakha Kaladze'}, {'player_name': 'Lionel Messi'}, {'player_name': 'Luis Garcia'}, {'player_name': 'Lukas Podolski'}, {'player_name': 'Marcelo Jose Bordon'}, {'player_name': 'Marco Materazzi'}, {'player_name': 'Mesut Oezil'}, {'player_name': 'Mladen Krstajic'}, {'player_name': 'Morten Gamst Pedersen'}, {'player_name': 'Nuri Sahin'}, {'player_name': 'Patrice Evra'}, {'player_name': 'Petr Cech'}, {'player_name': 'Rafael van der Vaart'}, {'player_name': 'Robin van Persie'}, {'player_name': 'Ryan Giggs'}, {'player_name': 'Shunsuke Nakamura'}, {'player_name': 'Stephen McManus'}, {'player_name': 'Thibaut Courtois'}, {'player_name': 'Vicente'}, {'player_name': 'Walter Samuel'}, {'player_name': 'Ze Roberto'}]