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 are the different government forms and what is the total population of each for government forms that have an average life expectancy greater than 72?
SQL: SELECT sum(Population) ,  GovernmentForm FROM country GROUP BY GovernmentForm HAVING avg(LifeExpectancy)  >  72

Question: Find the average life expectancy and total population for each continent where the average life expectancy is shorter than 72?
SQL: SELECT sum(Population) ,  avg(LifeExpectancy) ,  Continent FROM country GROUP BY Continent HAVING avg(LifeExpectancy)  <  72

Question: What are the different continents and the total popuation and average life expectancy corresponding to each, for continents that have an average life expectancy less than 72?
SQL: SELECT sum(Population) ,  avg(LifeExpectancy) ,  Continent FROM country GROUP BY Continent HAVING avg(LifeExpectancy)  <  72

Question: What are the names and areas of countries with the top 5 largest area?
SQL: SELECT Name ,  SurfaceArea FROM country ORDER BY SurfaceArea DESC LIMIT 5

Question: Return the names and surface areas of the 5 largest countries.
SQL: SELECT Name ,  SurfaceArea FROM country ORDER BY SurfaceArea DESC LIMIT 5

Question: What are names of countries with the top 3 largest population?
SQL: SELECT Name FROM country ORDER BY Population DESC LIMIT 3

Question: Return the names of the 3 most populated countries.
SQL: SELECT Name FROM country ORDER BY Population DESC LIMIT 3

Question: What are the names of the nations with the 3 lowest populations?
SQL: SELECT Name FROM country ORDER BY Population ASC LIMIT 3

Question: Return the names of the 3 countries with the fewest people.
SQL: SELECT Name FROM country ORDER BY Population ASC LIMIT 3

Question: how many countries are in Asia?
SQL: SELECT count(*) FROM country WHERE continent  =  "Asia"

Question: Count the number of countries in Asia.
SQL: SELECT count(*) FROM country WHERE continent  =  "Asia"

Question: What are the names of the countries that are in the continent of Europe and have a population of 80000?
SQL: SELECT Name FROM country WHERE continent  =  "Europe" AND Population  =  "80000"

Question: Give the names of countries that are in Europe and have a population equal to 80000.
SQL: SELECT Name FROM country WHERE continent  =  "Europe" AND Population  =  "80000"

Question: What is the total population and average area of countries in the continent of North America whose area is bigger than 3000 ?
SQL: select sum(population) ,  avg(surfacearea) from country where continent  =  "north america" and surfacearea  >  3000

Question: Give the total population and average surface area corresponding to countries in North America that have a surface area greater than 3000 .
SQL: select sum(population) ,  avg(surfacearea) from country where continent  =  "north america" and surfacearea  >  3000

Question: What are the cities whose population is between 160000 and 900000?
SQL: SELECT name FROM city WHERE Population BETWEEN 160000 AND 900000

Question: Return the names of cities that have a population between 160000 and 900000 .
SQL: select name from city where population between 160000 and 900000

Question: Which language is spoken by the largest number of countries?
SQL: SELECT LANGUAGE FROM countrylanguage GROUP BY LANGUAGE ORDER BY count(*) DESC LIMIT 1

Question: Give the language that is spoken in the most countries.
SQL: SELECT LANGUAGE FROM countrylanguage GROUP BY LANGUAGE ORDER BY count(*) DESC LIMIT 1

Question: What is the language spoken by the largest percentage of people in each country?
SQL: SELECT LANGUAGE ,  CountryCode ,  max(Percentage) FROM countrylanguage GROUP BY CountryCode

Question: What are the country codes of the different countries, and what are the languages spoken by the greatest percentage of people for each?
SQL: SELECT LANGUAGE ,  CountryCode ,  max(Percentage) FROM countrylanguage GROUP BY CountryCode

Question: What is the total number of countries where Spanish is spoken by the largest percentage of people?
SQL: SELECT count(*) ,   max(Percentage) FROM countrylanguage WHERE LANGUAGE  =  "Spanish" GROUP BY CountryCode

Question: Count the number of countries for which Spanish is the predominantly spoken language.
SQL: SELECT count(*) ,   max(Percentage) FROM countrylanguage WHERE LANGUAGE  =  "Spanish" GROUP BY CountryCode

Question: What are the codes of countries where Spanish is spoken by the largest percentage of people?
SQL: SELECT CountryCode ,  max(Percentage) FROM countrylanguage WHERE LANGUAGE  =  "Spanish" GROUP BY CountryCode

Question: Return the codes of countries for which Spanish is the predominantly spoken language.
SQL: SELECT CountryCode ,  max(Percentage) FROM countrylanguage WHERE LANGUAGE  =  "Spanish" GROUP BY CountryCode