CREATE TABLE city (
    City_ID NUMBER PRIMARY KEY,
    Official_Name TEXT,
    Status TEXT,
    Area_km_2 NUMBER,
    Population NUMBER,
    Census_Ranking TEXT
);
CREATE TABLE farm (
    Farm_ID NUMBER PRIMARY KEY,
    Year NUMBER,
    Total_Horses NUMBER,
    Working_Horses NUMBER,
    Total_Cattle NUMBER,
    Oxen NUMBER,
    Bulls NUMBER,
    Cows NUMBER,
    Pigs NUMBER,
    Sheep_and_Goats NUMBER
);
CREATE TABLE farm_competition (
    Competition_ID NUMBER PRIMARY KEY,
    Year NUMBER,
    Theme TEXT,
    Host_city_ID NUMBER,
    Hosts TEXT,
    FOREIGN KEY ("Host_city_ID") REFERENCES city("City_ID")
);
CREATE TABLE competition_record (
    Competition_ID NUMBER PRIMARY KEY,
    Farm_ID NUMBER,
    Rank NUMBER,
    FOREIGN KEY ("Farm_ID") REFERENCES farm("Farm_ID"),
    FOREIGN KEY ("Competition_ID") REFERENCES farm_competition("Competition_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"