CREATE TABLE city (
    ID NUMBER PRIMARY KEY,
    Name TEXT,
    CountryCode TEXT,
    District TEXT,
    Population NUMBER,
    FOREIGN KEY ("CountryCode") REFERENCES country("Code")
);
CREATE TABLE sqlite_sequence (
    name TEXT,
    seq TEXT
);
CREATE TABLE country (
    Code TEXT PRIMARY KEY,
    Name TEXT,
    Continent TEXT,
    Region TEXT,
    SurfaceArea NUMBER,
    IndepYear NUMBER,
    Population NUMBER,
    LifeExpectancy NUMBER,
    GNP NUMBER,
    GNPOld NUMBER,
    LocalName TEXT,
    GovernmentForm TEXT,
    HeadOfState TEXT,
    Capital NUMBER,
    Code2 TEXT
);
CREATE TABLE countrylanguage (
    CountryCode TEXT PRIMARY KEY,
    Language TEXT,
    IsOfficial TEXT,
    Percentage NUMBER,
    FOREIGN KEY ("CountryCode") REFERENCES country("Code")
);

Question: What is the official language used in the country the name of whose head of state is Beatrix.
SQL: SELECT T2.Language FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T1.HeadOfState  =  "Beatrix" AND T2.IsOfficial  =  "T"

Question: What is the total number of unique official languages spoken in the countries that are founded before 1930?
SQL: SELECT count(DISTINCT T2.Language) FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE  IndepYear  <  1930 AND T2.IsOfficial  =  "T"

Question: For the countries founded before 1930, what is the total number of distinct official languages?
SQL: SELECT count(DISTINCT T2.Language) FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE  IndepYear  <  1930 AND T2.IsOfficial  =  "T"

Question: What are the countries that have greater surface area than any country in Europe?
SQL: SELECT Name FROM country WHERE SurfaceArea  >  (SELECT min(SurfaceArea) FROM country WHERE Continent  =  "Europe")

Question: Which countries have greater area than that of any country in Europe?
SQL: SELECT Name FROM country WHERE SurfaceArea  >  (SELECT min(SurfaceArea) FROM country WHERE Continent  =  "Europe")

Question: What are the African countries that have a  population less than any country in Asia?
SQL: SELECT Name FROM country WHERE Continent  =  "Africa"  AND population  <  (SELECT max(population) FROM country WHERE Continent  =  "Asia")

Question: Which African countries have a smaller population than that of any country in Asia?
SQL: SELECT Name FROM country WHERE Continent  =  "Africa"  AND population  <  (SELECT min(population) FROM country WHERE Continent  =  "Asia")

Question: Which Asian countries have a population that is larger than any country in Africa?
SQL: SELECT Name FROM country WHERE Continent  =  "Asia"  AND population  >  (SELECT max(population) FROM country WHERE Continent  =  "Africa")

Question: What are the Asian countries which have a population larger than that of any country in Africa?
SQL: SELECT Name FROM country WHERE Continent  =  "Asia"  AND population  >  (SELECT min(population) FROM country WHERE Continent  =  "Africa")

Question: What are the country codes for countries that do not speak English?
SQL: SELECT CountryCode FROM countrylanguage EXCEPT SELECT CountryCode FROM countrylanguage WHERE LANGUAGE  =  "English"

Question: Return the country codes for countries that do not speak English.
SQL: SELECT CountryCode FROM countrylanguage EXCEPT SELECT CountryCode FROM countrylanguage WHERE LANGUAGE  =  "English"

Question: What are the country codes of countries where people use languages other than English?
SQL: SELECT DISTINCT CountryCode FROM countrylanguage WHERE LANGUAGE != "English"

Question: Give the country codes for countries in which people speak langauges that are not English.
SQL: SELECT DISTINCT CountryCode FROM countrylanguage WHERE LANGUAGE != "English"

Question: What are the codes of the countries that do not speak English and whose government forms are not Republic?
SQL: SELECT Code FROM country WHERE GovernmentForm != "Republic" EXCEPT SELECT CountryCode FROM countrylanguage WHERE LANGUAGE  =  "English"

Question: Return the codes of countries that do not speak English and do not have Republics for governments.
SQL: SELECT Code FROM country WHERE GovernmentForm != "Republic" EXCEPT SELECT CountryCode FROM countrylanguage WHERE LANGUAGE  =  "English"

Question: Which cities are in European countries where English is not the official language?
SQL: SELECT DISTINCT T2.Name FROM country AS T1 JOIN city AS T2 ON T2.CountryCode  =  T1.Code WHERE T1.Continent  =  'Europe' AND T1.Name NOT IN (SELECT T3.Name FROM country AS T3 JOIN countrylanguage AS T4 ON T3.Code  =  T4.CountryCode WHERE T4.IsOfficial  =  'T' AND T4.Language  =  'English')

Question: What are the names of cities in Europe for which English is not the official language?
SQL: SELECT DISTINCT T2.Name FROM country AS T1 JOIN city AS T2 ON T2.CountryCode  =  T1.Code WHERE T1.Continent  =  'Europe' AND T1.Name NOT IN (SELECT T3.Name FROM country AS T3 JOIN countrylanguage AS T4 ON T3.Code  =  T4.CountryCode WHERE T4.IsOfficial  =  'T' AND T4.Language  =  'English')

Question: Which unique cities are in Asian countries where Chinese is the official language ?
SQL: select distinct t3.name from country as t1 join countrylanguage as t2 on t1.code  =  t2.countrycode join city as t3 on t1.code  =  t3.countrycode where t2.isofficial  =  't' and t2.language  =  'chinese' and t1.continent  =  "asia"

Question: Return the different names of cities that are in Asia and for which Chinese is the official language.
SQL: SELECT DISTINCT T3.Name FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode JOIN city AS T3 ON T1.Code  =  T3.CountryCode WHERE T2.IsOfficial  =  'T' AND T2.Language  =  'Chinese' AND T1.Continent  =  "Asia"

Question: What are the name, independence year, and surface area of the country with the smallest population?
SQL: SELECT Name ,  SurfaceArea ,  IndepYear FROM country ORDER BY Population LIMIT 1

Question: Give the name, year of independence, and surface area of the country that has the lowest population.
SQL: SELECT Name ,  SurfaceArea ,  IndepYear FROM country ORDER BY Population LIMIT 1

Question: What are the population, name and leader of the country with the largest area?
SQL: SELECT Name ,  population ,  HeadOfState FROM country ORDER BY SurfaceArea DESC LIMIT 1

Question: Give the name, population, and head of state for the country that has the largest area.
SQL: SELECT Name ,  population ,  HeadOfState FROM country ORDER BY SurfaceArea DESC LIMIT 1

Question: Return the country name and the numbers of languages spoken for each country that speaks at least 3 languages.
SQL: SELECT COUNT(T2.Language) ,  T1.Name FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode GROUP BY T1.Name HAVING COUNT(*)  >  2

Question: What are the names of countries that speak more than 2 languages, as well as how many languages they speak?
SQL: SELECT COUNT(T2.Language) ,  T1.Name FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode GROUP BY T1.Name HAVING COUNT(*)  >  2

Question: Find the number of cities in each district whose population is greater than the average population of cities?
SQL: SELECT count(*) ,  District FROM city WHERE Population  >  (SELECT avg(Population) FROM city) GROUP BY District

Question: How many cities in each district have a population that is above the average population across all cities?
SQL: SELECT count(*) ,  District FROM city WHERE Population  >  (SELECT avg(Population) FROM city) GROUP BY District

Question: Find the government form name and total population for each government form whose average life expectancy is longer than 72.
SQL: SELECT sum(Population) ,  GovernmentForm FROM country GROUP BY GovernmentForm HAVING avg(LifeExpectancy)  >  72