Database: world_1
Schema: CREATE TABLE "country" ( "Code" char(3),  "Name" char(52),  "Continent" TEXT,  "Region" char(26),  "SurfaceArea" float(10,2),  "IndepYear" INTEGER,  "Population" INTEGER,  "LifeExpectancy" float(3,1),  "GNP" float(10,2),  "GNPOld" float(10,2),  "LocalName" char(45),  "GovernmentForm" char(45),  "HeadOfState" char(60),  "Capital" INTEGER,  "Code2" char(2), PRIMARY KEY ("Code") ); CREATE TABLE "city" ( "ID" INTEGER,  "Name" char(35),  "CountryCode" char(3),  "District" char(20),  "Population" INTEGER PRIMARY KEY ("ID"), FOREIGN KEY ("CountryCode") REFERENCES "country"("Code") ); ALTER TABLE "country"  ADD FOREIGN KEY ("Capital") REFERENCES "city"("ID"); CREATE TABLE "countrylanguage" ( "CountryCode" char(3),  "Language" char(30),  "IsOfficial" TEXT,  "Percentage" float(4,1) PRIMARY KEY ("CountryCode","Language"), FOREIGN KEY ("CountryCode") REFERENCES "country"("Code") );

Question (easy): What are the names of all the countries that became independent after 1950?
SQL: SELECT Name FROM country WHERE IndepYear  >  1950

Question (easy): Give the names of the nations that were founded after 1950.
SQL: SELECT Name FROM country WHERE IndepYear  >  1950

Question (easy): How many countries have a republic as their form of government?
SQL: SELECT count(*) FROM country WHERE GovernmentForm  =  "Republic"

Question (easy): How many countries have governments that are republics?
SQL: SELECT count(*) FROM country WHERE GovernmentForm  =  "Republic"

Question (easy): What is the total surface area of the countries in the Caribbean region?
SQL: SELECT sum(SurfaceArea) FROM country WHERE Region  =  "Caribbean"

Question (easy): How much surface area do the countires in the Carribean cover together?
SQL: SELECT sum(SurfaceArea) FROM country WHERE Region  =  "Caribbean"

Question (easy): Which continent is Anguilla in?
SQL: SELECT Continent FROM country WHERE Name  =  "Anguilla"

Question (easy): What is the continent name which Anguilla belongs to?
SQL: SELECT Continent FROM country WHERE Name  =  "Anguilla"

Question (medium): Which region is the city Kabul located in?
SQL: SELECT Region FROM country AS T1 JOIN city AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Name  =  "Kabul"

Question (medium): What region is Kabul in?
SQL: SELECT Region FROM country AS T1 JOIN city AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Name  =  "Kabul"

Question (extra): Which language is the most popular in Aruba?
SQL: SELECT T2.Language FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T1.Name  =  "Aruba" ORDER BY Percentage DESC LIMIT 1

Question (extra): What language is predominantly spoken in Aruba?
SQL: SELECT T2.Language FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T1.Name  =  "Aruba" ORDER BY Percentage DESC LIMIT 1

Question (medium): What are the population and life expectancies in Brazil?
SQL: SELECT Population ,  LifeExpectancy FROM country WHERE Name  =  "Brazil"

Question (medium): Give me Brazil’s population and life expectancies.
SQL: SELECT Population ,  LifeExpectancy FROM country WHERE Name  =  "Brazil"

Question (medium): What are the region and population of Angola?
SQL: SELECT Population ,  Region FROM country WHERE Name  =  "Angola"

Question (medium): What region does Angola belong to and what is its population?
SQL: SELECT Population ,  Region FROM country WHERE Name  =  "Angola"

Question (easy): What is the average expected life expectancy for countries in the region of Central Africa?
SQL: SELECT avg(LifeExpectancy) FROM country WHERE Region  =  "Central Africa"

Question (easy): How long is the people’s average life expectancy in Central Africa?
SQL: SELECT avg(LifeExpectancy) FROM country WHERE Region  =  "Central Africa"

Question (hard): What is the name of country that has the shortest life expectancy in Asia?
SQL: SELECT Name FROM country WHERE Continent  =  "Asia" ORDER BY LifeExpectancy LIMIT 1

Question (hard): Give the name of the country in Asia with the lowest life expectancy.
SQL: SELECT Name FROM country WHERE Continent  =  "Asia" ORDER BY LifeExpectancy LIMIT 1

Question (medium): What is the total population and maximum GNP in Asia?
SQL: SELECT sum(Population) ,  max(GNP) FROM country WHERE Continent  =  "Asia"

Question (medium): How many people live in Asia, and what is the largest GNP among them?
SQL: SELECT sum(Population) ,  max(GNP) FROM country WHERE Continent  =  "Asia"

Question (medium): What is the average life expectancy in African countries that are republics?
SQL: SELECT avg(LifeExpectancy) FROM country WHERE Continent  =  "Africa" AND GovernmentForm  =  "Republic"

Question (medium): Give the average life expectancy for countries in Africa which are republics?
SQL: SELECT avg(LifeExpectancy) FROM country WHERE Continent  =  "Africa" AND GovernmentForm  =  "Republic"

Question (medium): What is the total surface area of the continents Asia and Europe?
SQL: SELECT sum(SurfaceArea) FROM country WHERE Continent  =  "Asia" OR Continent  =  "Europe"

Question (medium): Give the total surface area covered by countries in Asia or Europe.
SQL: SELECT sum(SurfaceArea) FROM country WHERE Continent  =  "Asia" OR Continent  =  "Europe"

Question (easy): How many people live in Gelderland district?
SQL: SELECT sum(Population) FROM city WHERE District  =  "Gelderland"

Question (easy): What is the total population of Gelderland district?
SQL: SELECT sum(Population) FROM city WHERE District  =  "Gelderland"

Question (medium): What is the average GNP and total population in all nations whose government is US territory?
SQL: SELECT avg(GNP) ,  sum(population) FROM country WHERE GovernmentForm  =  "US Territory"

Question (medium): Give the mean GNP and total population of nations which are considered US territory.
SQL: SELECT avg(GNP) ,  sum(population) FROM country WHERE GovernmentForm  =  "US Territory"

Question (easy): How many unique languages are spoken in the world?
SQL: SELECT count(DISTINCT LANGUAGE) FROM countrylanguage

Question (easy): What is the number of distinct languages used around the world?
SQL: SELECT count(DISTINCT LANGUAGE) FROM countrylanguage

Question (easy): How many type of governments are in Africa?
SQL: SELECT count(DISTINCT GovernmentForm) FROM country WHERE Continent  =  "Africa"

Question (easy): How many different forms of governments are there in Africa?
SQL: SELECT count(DISTINCT GovernmentForm) FROM country WHERE Continent  =  "Africa"

Question (medium): What is the total number of languages used in Aruba?
SQL: SELECT COUNT(T2.Language) FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T1.Name  =  "Aruba"

Question (medium): How many languages are spoken in Aruba?
SQL: SELECT COUNT(T2.Language) FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T1.Name  =  "Aruba"

Question (medium): How many official languages does Afghanistan have?
SQL: SELECT COUNT(*) FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T1.Name  =  "Afghanistan" AND IsOfficial  =  "T"

Question (medium): How many official languages are spoken in Afghanistan?
SQL: SELECT COUNT(*) FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T1.Name  =  "Afghanistan" AND IsOfficial  =  "T"

Question (extra): What is name of the country that speaks the largest number of languages?
SQL: SELECT T1.Name FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode GROUP BY T1.Name ORDER BY COUNT(*) DESC LIMIT 1

Question (extra): Give the name of the nation that uses the greatest amount of languages.
SQL: SELECT T1.Name FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode GROUP BY T1.Name ORDER BY COUNT(*) DESC LIMIT 1

Question (extra): Which continent has the most diverse languages?
SQL: SELECT T1.Continent FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode GROUP BY T1.Continent ORDER BY COUNT(*) DESC LIMIT 1

Question (extra): Which continent speaks the most languages?
SQL: SELECT T1.Continent FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode GROUP BY T1.Continent ORDER BY COUNT(*) DESC LIMIT 1

Question (easy): How many countries speak both English and Dutch?
SQL: SELECT COUNT(*) FROM (SELECT T1.Name FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "English" INTERSECT SELECT T1.Name FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "Dutch")

Question (easy): What is the number of nations that use English and Dutch?
SQL: SELECT COUNT(*) FROM (SELECT T1.Name FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "English" INTERSECT SELECT T1.Name FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "Dutch")

Question (extra): What are the names of nations speak both English and French?
SQL: SELECT T1.Name FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "English" INTERSECT SELECT T1.Name FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "French"

Question (extra): Give the names of nations that speak both English and French.
SQL: SELECT T1.Name FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "English" INTERSECT SELECT T1.Name FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "French"

Question (extra): What are the names of nations where both English and French are official languages?
SQL: SELECT T1.Name FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "English" AND T2.IsOfficial  =  "T" INTERSECT SELECT T1.Name FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "French" AND T2.IsOfficial  =  "T"

Question (extra): Give the names of countries with English and French as official languages.
SQL: SELECT T1.Name FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "English" AND T2.IsOfficial  =  "T" INTERSECT SELECT T1.Name FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "French" AND T2.IsOfficial  =  "T"

Question (medium): What is the number of distinct continents where Chinese is spoken?
SQL: SELECT COUNT( DISTINCT Continent) FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "Chinese"

Question (medium): How many continents speak Chinese?
SQL: SELECT COUNT( DISTINCT Continent) FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "Chinese"

Question (hard): What are the regions that use English or Dutch?
SQL: SELECT DISTINCT T1.Region FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "English" OR T2.Language  =  "Dutch"

Question (hard): Which regions speak Dutch or English?
SQL: SELECT DISTINCT T1.Region FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "English" OR T2.Language  =  "Dutch"

Question (extra): What are the countries where either English or Dutch is the official language ?
SQL: select t1.name from country as t1 join countrylanguage as t2 on t1.code  =  t2.countrycode where t2.language  =  "english" and isofficial  =  "t" union select t1.name from country as t1 join countrylanguage as t2 on t1.code  =  t2.countrycode where t2.language  =  "dutch" and isofficial  =  "t"

Question (extra): Which countries have either English or Dutch as an official language?
SQL: SELECT * FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "English" AND IsOfficial  =  "T" UNION SELECT * FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "Dutch" AND IsOfficial  =  "T"

Question (extra): Which language is the most popular on the Asian continent?
SQL: SELECT T2.Language FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T1.Continent  =  "Asia" GROUP BY T2.Language ORDER BY COUNT (*) DESC LIMIT 1

Question (extra): What is the language that is used by the largest number of Asian nations?
SQL: SELECT T2.Language FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T1.Continent  =  "Asia" GROUP BY T2.Language ORDER BY COUNT (*) DESC LIMIT 1

Question (hard): Which languages are spoken by only one country in republic governments?
SQL: SELECT T2.Language FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T1.GovernmentForm  =  "Republic" GROUP BY T2.Language HAVING COUNT(*)  =  1

Question (hard): What languages are only used by a single country with a republic government?
SQL: SELECT T2.Language FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T1.GovernmentForm  =  "Republic" GROUP BY T2.Language HAVING COUNT(*)  =  1

Question (extra): Find the city with the largest population that uses English.
SQL: SELECT T1.Name ,  T1.Population FROM city AS T1 JOIN countrylanguage AS T2 ON T1.CountryCode  =  T2.CountryCode WHERE T2.Language  =  "English" ORDER BY T1.Population DESC LIMIT 1

Question (extra): What is the most populace city that speaks English?
SQL: SELECT T1.Name ,  T1.Population FROM city AS T1 JOIN countrylanguage AS T2 ON T1.CountryCode  =  T2.CountryCode WHERE T2.Language  =  "English" ORDER BY T1.Population DESC LIMIT 1

Question (hard): Find the name, population and expected life length of asian country with the largest area?
SQL: SELECT Name ,  Population ,  LifeExpectancy FROM country WHERE Continent  =  "Asia" ORDER BY SurfaceArea DESC LIMIT 1

Question (hard): What are the name, population, and life expectancy of the largest Asian country by land?
SQL: SELECT Name ,  Population ,  LifeExpectancy FROM country WHERE Continent  =  "Asia" ORDER BY SurfaceArea DESC LIMIT 1

Question (extra): What is average life expectancy in the countries where English is not the official language?
SQL: SELECT avg(LifeExpectancy) FROM country WHERE Name NOT IN (SELECT T1.Name FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "English" AND T2.IsOfficial  =  "T")

Question (extra): Give the mean life expectancy of countries in which English is not the official language.
SQL: SELECT avg(LifeExpectancy) FROM country WHERE Name NOT IN (SELECT T1.Name FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "English" AND T2.IsOfficial  =  "T")

Question (extra): What is the total number of people living in the nations that do not use English?
SQL: SELECT sum(Population) FROM country WHERE Name NOT IN (SELECT T1.Name FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "English")

Question (extra): How many people live in countries that do not speak English?
SQL: SELECT sum(Population) FROM country WHERE Name NOT IN (SELECT T1.Name FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "English")

Question (medium): What is the official language spoken in the country 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 (medium): 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 (medium): 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 (medium): 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 (hard): 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 (hard): 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 (extra): 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 (extra): 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 (extra): 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 (extra): 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 (hard): 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 (hard): 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 (easy): What are the country codes of countries where people use languages other than English?
SQL: SELECT DISTINCT CountryCode FROM countrylanguage WHERE LANGUAGE != "English"

Question (easy): 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 (hard): 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 (hard): 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 (extra): 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 (extra): 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 (hard): 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 (hard): 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 (medium): 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 (medium): 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 (medium): 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 (medium): 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 (medium): 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 (medium): 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 (extra): 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 (extra): 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 (medium): 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

Question (medium): 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 (medium): 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 (medium): 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 (medium): 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 (medium): Return the names and surface areas of the 5 largest countries.
SQL: SELECT Name ,  SurfaceArea FROM country ORDER BY SurfaceArea DESC LIMIT 5

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

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

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

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

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

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

Question (medium): 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 (medium): 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 (hard): 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 (hard): 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 (easy): What are the cities whose population is between 160000 and 900000?
SQL: SELECT name FROM city WHERE Population BETWEEN 160000 AND 900000

Question (easy): 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 (hard): 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 (hard): 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 (medium): 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 (medium): 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 (extra): 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 (extra): 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 (medium): 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 (medium): 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