
CREATE TABLE city (
	"City_ID" INTEGER, 
	"Official_Name" TEXT, 
	"Status" TEXT, 
	"Area_km_2" REAL, 
	"Population" REAL, 
	"Census_Ranking" TEXT, 
	PRIMARY KEY ("City_ID")
)


CREATE TABLE competition_record (
	"Competition_ID" INTEGER, 
	"Farm_ID" INTEGER, 
	"Rank" INTEGER, 
	PRIMARY KEY ("Competition_ID", "Farm_ID"), 
	FOREIGN KEY("Farm_ID") REFERENCES farm ("Farm_ID"), 
	FOREIGN KEY("Competition_ID") REFERENCES farm_competition ("Competition_ID")
)


CREATE TABLE farm (
	"Farm_ID" INTEGER, 
	"Year" INTEGER, 
	"Total_Horses" REAL, 
	"Working_Horses" REAL, 
	"Total_Cattle" REAL, 
	"Oxen" REAL, 
	"Bulls" REAL, 
	"Cows" REAL, 
	"Pigs" REAL, 
	"Sheep_and_Goats" REAL, 
	PRIMARY KEY ("Farm_ID")
)


CREATE TABLE farm_competition (
	"Competition_ID" INTEGER, 
	"Year" INTEGER, 
	"Theme" TEXT, 
	"Host_city_ID" INTEGER, 
	"Hosts" TEXT, 
	PRIMARY KEY ("Competition_ID"), 
	FOREIGN KEY("Host_city_ID") REFERENCES city ("City_ID")
)

CREATE TABLE city (
	"City_ID" INTEGER, 
	"Official_Name" TEXT, 
	"Status" TEXT, 
	"Area_km_2" REAL, 
	"Population" REAL, 
	"Census_Ranking" TEXT, 
	PRIMARY KEY ("City_ID")
)

/*
2 rows from city table:
City_ID	Official_Name	Status	Area_km_2	Population	Census_Ranking
1	Grand Falls/Grand-Sault	Town	18.06	5706.0	636 of 5008
2	Perth-Andover	Village	8.89	1778.0	1442 of 5,008
*/


CREATE TABLE competition_record (
	"Competition_ID" INTEGER, 
	"Farm_ID" INTEGER, 
	"Rank" INTEGER, 
	PRIMARY KEY ("Competition_ID", "Farm_ID"), 
	FOREIGN KEY("Farm_ID") REFERENCES farm ("Farm_ID"), 
	FOREIGN KEY("Competition_ID") REFERENCES farm_competition ("Competition_ID")
)

/*
2 rows from competition_record table:
Competition_ID	Farm_ID	Rank
1	8	1
1	2	2
*/


CREATE TABLE farm (
	"Farm_ID" INTEGER, 
	"Year" INTEGER, 
	"Total_Horses" REAL, 
	"Working_Horses" REAL, 
	"Total_Cattle" REAL, 
	"Oxen" REAL, 
	"Bulls" REAL, 
	"Cows" REAL, 
	"Pigs" REAL, 
	"Sheep_and_Goats" REAL, 
	PRIMARY KEY ("Farm_ID")
)

/*
2 rows from farm table:
Farm_ID	Year	Total_Horses	Working_Horses	Total_Cattle	Oxen	Bulls	Cows	Pigs	Sheep_and_Goats
1	1927	5056.5	3900.1	8374.5	805.5	31.6	3852.1	4412.4	7956.3
2	1928	5486.9	4090.5	8604.8	895.3	32.8	3987.0	6962.9	8112.2
*/


CREATE TABLE farm_competition (
	"Competition_ID" INTEGER, 
	"Year" INTEGER, 
	"Theme" TEXT, 
	"Host_city_ID" INTEGER, 
	"Hosts" TEXT, 
	PRIMARY KEY ("Competition_ID"), 
	FOREIGN KEY("Host_city_ID") REFERENCES city ("City_ID")
)

/*
2 rows from farm_competition table:
Competition_ID	Year	Theme	Host_city_ID	Hosts
1	2013	Carnival M is back!	1	Miley Cyrus Jared Leto and Karen Mok
2	2006	Codehunters	2	Leehom Wang and Kelly Rowland
*/
Give the average number of working horses on farms with more than 5000 total horses.
SELECT avg(Working_Horses) FROM farm WHERE Total_Horses  >  5000
[{'avg(Working_Horses)': 3977.7500000000005}]