Database: farm

CREATE TABLE "city" (
"City_ID" int,
"Official_Name" text,
"Status" text,
"Area_km_2" real,
"Population" real,
"Census_Ranking" text,
PRIMARY KEY ("City_ID")
);
CREATE TABLE "farm" (
"Farm_ID" int,
"Year" int,
"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" int,
"Year" int,
"Theme" text,
"Host_city_ID" int,
"Hosts" text,
PRIMARY KEY ("Competition_ID"),
FOREIGN KEY (`Host_city_ID`) REFERENCES `city`(`City_ID`)
);
CREATE TABLE "competition_record" (
"Competition_ID" int,
"Farm_ID" int,
"Rank" int,
PRIMARY KEY ("Competition_ID","Farm_ID"),
FOREIGN KEY (`Competition_ID`) REFERENCES `farm_competition`(`Competition_ID`),
FOREIGN KEY (`Farm_ID`) REFERENCES `farm`(`Farm_ID`)
);

Question: How many farms are there?
SQL: SELECT count(*) FROM farm

Question: Count the number of farms.
SQL: SELECT count(*) FROM farm

Question: List the total number of horses on farms in ascending order.
SQL: SELECT Total_Horses FROM farm ORDER BY Total_Horses ASC

Question: What is the total horses record for each farm, sorted ascending?
SQL: SELECT Total_Horses FROM farm ORDER BY Total_Horses ASC

Question: What are the hosts of competitions whose theme is not "Aliens"?
SQL: SELECT Hosts FROM farm_competition WHERE Theme !=  'Aliens'

Question: Return the hosts of competitions for which the theme is not Aliens?
SQL: SELECT Hosts FROM farm_competition WHERE Theme !=  'Aliens'

Question: What are the themes of farm competitions sorted by year in ascending order?
SQL: SELECT Theme FROM farm_competition ORDER BY YEAR ASC

Question: Return the themes of farm competitions, sorted by year ascending.
SQL: SELECT Theme FROM farm_competition ORDER BY YEAR ASC

Question: What is the average number of working horses of farms with more than 5000 total number of horses?
SQL: SELECT avg(Working_Horses) FROM farm WHERE Total_Horses  >  5000

Question: Give the average number of working horses on farms with more than 5000 total horses.
SQL: SELECT avg(Working_Horses) FROM farm WHERE Total_Horses  >  5000

Question: What are the maximum and minimum number of cows across all farms.
SQL: SELECT max(Cows) ,  min(Cows) FROM farm

Question: Return the maximum and minimum number of cows across all farms.
SQL: SELECT max(Cows) ,  min(Cows) FROM farm

Question: How many different statuses do cities have?
SQL: SELECT count(DISTINCT Status) FROM city

Question: Count the number of different statuses.
SQL: SELECT count(DISTINCT Status) FROM city

Question: List official names of cities in descending order of population.
SQL: SELECT Official_Name FROM city ORDER BY Population DESC

Question: What are the official names of cities, ordered descending by population?
SQL: SELECT Official_Name FROM city ORDER BY Population DESC

Question: List the official name and status of the city with the largest population.
SQL: SELECT Official_Name ,  Status FROM city ORDER BY Population DESC LIMIT 1

Question: What is the official name and status of the city with the most residents?
SQL: SELECT Official_Name ,  Status FROM city ORDER BY Population DESC LIMIT 1

Question: Show the years and the official names of the host cities of competitions.
SQL: SELECT T2.Year ,  T1.Official_Name FROM city AS T1 JOIN farm_competition AS T2 ON T1.City_ID  =  T2.Host_city_ID

Question: Give the years and official names of the cities of each competition.
SQL: SELECT T2.Year ,  T1.Official_Name FROM city AS T1 JOIN farm_competition AS T2 ON T1.City_ID  =  T2.Host_city_ID

Question: Show the official names of the cities that have hosted more than one competition.
SQL: SELECT T1.Official_Name FROM city AS T1 JOIN farm_competition AS T2 ON T1.City_ID  =  T2.Host_city_ID GROUP BY T2.Host_city_ID HAVING COUNT(*)  >  1

Question: What are the official names of cities that have hosted more than one competition?
SQL: SELECT T1.Official_Name FROM city AS T1 JOIN farm_competition AS T2 ON T1.City_ID  =  T2.Host_city_ID GROUP BY T2.Host_city_ID HAVING COUNT(*)  >  1

Question: Show the status of the city that has hosted the greatest number of competitions.
SQL: SELECT T1.Status FROM city AS T1 JOIN farm_competition AS T2 ON T1.City_ID  =  T2.Host_city_ID GROUP BY T2.Host_city_ID ORDER BY COUNT(*) DESC LIMIT 1

Question: What is the status of the city that has hosted the most competitions?
SQL: SELECT T1.Status FROM city AS T1 JOIN farm_competition AS T2 ON T1.City_ID  =  T2.Host_city_ID GROUP BY T2.Host_city_ID ORDER BY COUNT(*) DESC LIMIT 1

Question: Please show the themes of competitions with host cities having populations larger than 1000.
SQL: SELECT T2.Theme FROM city AS T1 JOIN farm_competition AS T2 ON T1.City_ID  =  T2.Host_city_ID WHERE T1.Population  >  1000

Question: What are the themes of competitions that have corresponding host cities with more than 1000 residents?
SQL: SELECT T2.Theme FROM city AS T1 JOIN farm_competition AS T2 ON T1.City_ID  =  T2.Host_city_ID WHERE T1.Population  >  1000

Question: Please show the different statuses of cities and the average population of cities with each status.
SQL: SELECT Status ,  avg(Population) FROM city GROUP BY Status

Question: What are the statuses and average populations of each city?
SQL: SELECT Status ,  avg(Population) FROM city GROUP BY Status

Question: Please show the different statuses, ordered by the number of cities that have each.
SQL: SELECT Status FROM city GROUP BY Status ORDER BY COUNT(*) ASC

Question: Return the different statuses of cities, ascending by frequency.
SQL: SELECT Status FROM city GROUP BY Status ORDER BY COUNT(*) ASC

Question: List the most common type of Status across cities.
SQL: SELECT Status FROM city GROUP BY Status ORDER BY COUNT(*) DESC LIMIT 1

Question: What is the most common status across all cities?
SQL: SELECT Status FROM city GROUP BY Status ORDER BY COUNT(*) DESC LIMIT 1

Question: List the official names of cities that have not held any competition.
SQL: SELECT Official_Name FROM city WHERE City_ID NOT IN (SELECT Host_city_ID FROM farm_competition)

Question: What are the official names of cities that have not hosted a farm competition?
SQL: SELECT Official_Name FROM city WHERE City_ID NOT IN (SELECT Host_city_ID FROM farm_competition)

Question: Show the status shared by cities with population bigger than 1500 and smaller than 500.
SQL: SELECT Status FROM city WHERE Population  >  1500 INTERSECT SELECT Status FROM city WHERE Population  <  500

Question: Which statuses correspond to both cities that have a population over 1500 and cities that have a population lower than 500?
SQL: SELECT Status FROM city WHERE Population  >  1500 INTERSECT SELECT Status FROM city WHERE Population  <  500

Question: Find the official names of cities with population bigger than 1500 or smaller than 500.
SQL: SELECT Official_Name FROM city WHERE Population  >  1500 OR Population  <  500

Question: What are the official names of cities that have population over 1500 or less than 500?
SQL: SELECT Official_Name FROM city WHERE Population  >  1500 OR Population  <  500

Question: Show the census ranking of cities whose status are not "Village".
SQL: SELECT Census_Ranking FROM city WHERE Status !=  "Village"

Question: What are the census rankings of cities that do not have the status "Village"?
SQL: SELECT Census_Ranking FROM city WHERE Status !=  "Village"