 | Idx | Table      | Column | Primary Key | Foreign Key | 
 | ----------- | ----------- | ----------- | ----------- | ----------- | 
  | 0 |  | * |   |   | 
 | 1 | **city** | ID | + |   | 
 | 2 |   | Name |   |   | 
 | 3 |   | CountryCode |   | --> 8 | 
 | 4 |   | District |   |   | 
 | 5 |   | Population |   |   | 
 | 6 | **sqlite_sequence** | name |   |   | 
 | 7 |   | seq |   |   | 
 | 8 | **country** | Code | + |   | 
 | 9 |   | Name |   |   | 
 | 10 |   | Continent |   |   | 
 | 11 |   | Region |   |   | 
 | 12 |   | SurfaceArea |   |   | 
 | 13 |   | IndepYear |   |   | 
 | 14 |   | Population |   |   | 
 | 15 |   | LifeExpectancy |   |   | 
 | 16 |   | GNP |   |   | 
 | 17 |   | GNPOld |   |   | 
 | 18 |   | LocalName |   |   | 
 | 19 |   | GovernmentForm |   |   | 
 | 20 |   | HeadOfState |   |   | 
 | 21 |   | Capital |   |   | 
 | 22 |   | Code2 |   |   | 
 | 23 | **countrylanguage** | CountryCode | + | --> 8 | 
 | 24 |   | Language |   |   | 
 | 25 |   | IsOfficial |   |   | 
 | 26 |   | Percentage |   |   | 
 
  | Index | Question  | SQL | gold QDMR | pred QDMR | Exec | SQL hardness |
  | ----------- | ----------- | ----------- |  ----------- | ----------- | ----------- | ----------- | 
 | SPIDER_dev_702 | What are the names of all the countries that became independent after 1950? | SELECT Name FROM country WHERE IndepYear  >  1950 |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​>:​1950:​col:​city:​ID] <br>3. PROJECT[col:​country:​Name, #2] <br> | - | easy | 
  | SPIDER_dev_703 | Give the names of the nations that were founded after 1950. | SELECT Name FROM country WHERE IndepYear  >  1950 |  | 1. SELECT[tbl:​sqlite_sequence] <br>2. COMPARATIVE[#1, #1, comparative:​>:​1950:​col:​city:​ID] <br>3. PROJECT[col:​country:​Name, #2] <br> | - | easy | 
  | SPIDER_dev_704 | How many countries have a republic as their form of government? | SELECT count(*) FROM country WHERE GovernmentForm  =  "Republic" |  | 1. SELECT[tbl:​country] <br>2. PROJECT[col:​country:​GovernmentForm, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​Republic:​col:​country:​GovernmentForm] <br>4. AGGREGATE[count, #3] <br> | + | easy | 
  | SPIDER_dev_705 | How many countries have governments that are republics? | SELECT count(*) FROM country WHERE GovernmentForm  =  "Republic" |  | 1. SELECT[tbl:​country] <br>2. PROJECT[col:​country:​GovernmentForm, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​Republic:​col:​country:​GovernmentForm] <br>4. AGGREGATE[count, #3] <br> | + | easy | 
  | SPIDER_dev_706 | What is the total surface area of the countries in the Caribbean region? | SELECT sum(SurfaceArea) FROM country WHERE Region  =  "Caribbean" |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Caribbean:​col:​country:​Region] <br>3. PROJECT[col:​country:​SurfaceArea, #2] <br>4. AGGREGATE[sum, #3] <br> | + | easy | 
  | SPIDER_dev_707 | How much surface area do the countires in the Carribean cover together? | SELECT sum(SurfaceArea) FROM country WHERE Region  =  "Caribbean" |  | 1. SELECT[tbl:​country] <br>2. PROJECT[col:​country:​SurfaceArea, #1] <br>3. AGGREGATE[count, #2] <br> | - | easy | 
  | SPIDER_dev_708 | Which continent is Anguilla in? | SELECT Continent FROM country WHERE Name  =  "Anguilla" |  | 1. SELECT[col:​countrylanguage:​Language] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Anguilla:​col:​country:​Name] <br> | - | easy | 
  | SPIDER_dev_709 | What is the continent name which Anguilla belongs to? | SELECT Continent FROM country WHERE Name  =  "Anguilla" |  | 1. SELECT[val:​country:​LocalName:​Anguilla] <br>2. PROJECT[col:​country:​Continent, #1] <br> | + | easy | 
  | SPIDER_dev_710 | Which region is the city Kabul located in? | SELECT Region FROM country AS T1 JOIN city AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Name  =  "Kabul" |  | 1. SELECT[val:​city:​Name:​Kabul] <br>2. PROJECT[tbl:​city, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​Kabul:​col:​city:​Name] <br>4. PROJECT[col:​city:​District, #3] <br> | - | medium | 
  | SPIDER_dev_711 | What region is Kabul in? | SELECT Region FROM country AS T1 JOIN city AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Name  =  "Kabul" |  | 1. SELECT[val:​city:​Name:​Kabul] <br>2. PROJECT[col:​country:​Region, #1] <br> | + | medium | 
  | SPIDER_dev_712 | Which language is the most popular in Aruba? | 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 |  | 1. SELECT[val:​country:​LocalName:​Aruba] <br>2. PROJECT[col:​countrylanguage:​Language, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #2, #2] <br> | + | extra | 
  | SPIDER_dev_713 | What language is predominantly spoken in Aruba? | 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 |  | 1. SELECT[val:​country:​LocalName:​Aruba] <br>2. PROJECT[col:​countrylanguage:​Language, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #2, #2] <br> | + | extra | 
  | SPIDER_dev_714 | What are the population and life expectancies in Brazil? | SELECT Population ,  LifeExpectancy FROM country WHERE Name  =  "Brazil" |  | 1. SELECT[val:​country:​Name:​Brazil] <br>2. PROJECT[col:​country:​Population, #1] <br>3. PROJECT[col:​country:​LifeExpectancy, #1] <br>4. UNION[#2, #3] <br> | + | medium | 
  | SPIDER_dev_715 | Give me Brazil’s population and life expectancies. | SELECT Population ,  LifeExpectancy FROM country WHERE Name  =  "Brazil" |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Brazil:​col:​country:​Name] <br>3. PROJECT[col:​country:​Population, #2] <br>4. PROJECT[col:​country:​LifeExpectancy, #2] <br>5. UNION[#3, #4] <br> | + | medium | 
  | SPIDER_dev_716 | What are the region and population of Angola? | SELECT Population ,  Region FROM country WHERE Name  =  "Angola" |  | 1. SELECT[val:​country:​LocalName:​Angola] <br>2. PROJECT[col:​country:​Region, #1] <br>3. PROJECT[col:​country:​Population, #1] <br>4. UNION[#2, #3] <br> | + | medium | 
  | SPIDER_dev_717 | What region does Angola belong to and what is its population? | SELECT Population ,  Region FROM country WHERE Name  =  "Angola" |  | 1. SELECT[val:​country:​LocalName:​Angola] <br>2. PROJECT[col:​country:​Region, #1] <br>3. PROJECT[col:​country:​Population, #1] <br>4. UNION[#2, #3] <br> | + | medium | 
  | SPIDER_dev_718 | What is the average expected life expectancy for countries in the region of Central Africa? | SELECT avg(LifeExpectancy) FROM country WHERE Region  =  "Central Africa" |  | 1. SELECT[tbl:​country] <br>2. PROJECT[col:​country:​Region, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​Central Africa:​col:​country:​Region] <br>4. PROJECT[col:​country:​LifeExpectancy, #3] <br>5. AGGREGATE[avg, #4] <br> | + | easy | 
  | SPIDER_dev_719 | How long is the people’s average life expectancy in Central Africa? | SELECT avg(LifeExpectancy) FROM country WHERE Region  =  "Central Africa" |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Central Africa:​col:​country:​Region] <br>3. PROJECT[col:​country:​LifeExpectancy, #2] <br>4. AGGREGATE[avg, #3] <br> | + | easy | 
  | SPIDER_dev_720 | What is the name of country that has the shortest life expectancy in Asia? | SELECT Name FROM country WHERE Continent  =  "Asia" ORDER BY LifeExpectancy LIMIT 1 |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Asia:​col:​country:​Continent] <br>3. PROJECT[col:​country:​LifeExpectancy, #2] <br>4. SUPERLATIVE[comparative:​min:​None, #1, #3] <br>5. PROJECT[col:​country:​Name, #4] <br> | + | hard | 
  | SPIDER_dev_721 | Give the name of the country in Asia with the lowest life expectancy. | SELECT Name FROM country WHERE Continent  =  "Asia" ORDER BY LifeExpectancy LIMIT 1 |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Asia:​col:​country:​Continent] <br>3. PROJECT[col:​country:​LifeExpectancy, #2] <br>4. SUPERLATIVE[comparative:​min:​None, #2, #3] <br>5. PROJECT[col:​country:​Name, #4] <br> | + | hard | 
  | SPIDER_dev_722 | What is the total population and maximum GNP in Asia? | SELECT sum(Population) ,  max(GNP) FROM country WHERE Continent  =  "Asia" |  | 1. SELECT[val:​country:​Continent:​Asia] <br>2. PROJECT[col:​country:​Population, #1] <br>3. AGGREGATE[sum, #2] <br>4. AGGREGATE[max, #2] <br>5. UNION[#3, #4] <br> | - | medium | 
  | SPIDER_dev_723 | How many people live in Asia, and what is the largest GNP among them? | SELECT sum(Population) ,  max(GNP) FROM country WHERE Continent  =  "Asia" |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Asia:​col:​country:​Continent] <br>3. PROJECT[col:​country:​GNP, #2] <br>4. AGGREGATE[max, #3] <br>5. UNION[#4, #4] <br> | - | medium | 
  | SPIDER_dev_724 | What is the average life expectancy in African countries that are republics? | SELECT avg(LifeExpectancy) FROM country WHERE Continent  =  "Africa" AND GovernmentForm  =  "Republic" |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Republic:​col:​country:​GovernmentForm] <br>3. PROJECT[col:​country:​LifeExpectancy, #2] <br>4. AGGREGATE[avg, #3] <br> | - | medium | 
  | SPIDER_dev_725 | Give the average life expectancy for countries in Africa which are republics? | SELECT avg(LifeExpectancy) FROM country WHERE Continent  =  "Africa" AND GovernmentForm  =  "Republic" |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Africa:​col:​country:​Continent] <br>3. COMPARATIVE[#2, #2, comparative:​=:​Republic:​col:​country:​GovernmentForm] <br>4. PROJECT[col:​country:​LifeExpectancy, #3] <br>5. AGGREGATE[avg, #4] <br> | + | medium | 
  | SPIDER_dev_726 | What is the total surface area of the continents Asia and Europe? | SELECT sum(SurfaceArea) FROM country WHERE Continent  =  "Asia" OR Continent  =  "Europe" |  | 1. SELECT[col:​country:​Continent] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Asia:​col:​country:​Continent] <br>3. COMPARATIVE[#1, #1, comparative:​=:​Europe:​col:​country:​Continent] <br>4. INTERSECTION[#1, #2, #3] <br>5. PROJECT[col:​country:​SurfaceArea, #4] <br>6. AGGREGATE[sum, #5] <br> | - | medium | 
  | SPIDER_dev_727 | Give the total surface area covered by countries in Asia or Europe. | SELECT sum(SurfaceArea) FROM country WHERE Continent  =  "Asia" OR Continent  =  "Europe" |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Asia:​col:​country:​Continent] <br>3. COMPARATIVE[#1, #1, comparative:​=:​Europe:​col:​country:​Continent] <br>4. UNION[#2, #3] <br>5. PROJECT[col:​country:​SurfaceArea, #4] <br>6. AGGREGATE[sum, #5] <br> | + | medium | 
  | SPIDER_dev_728 | How many people live in Gelderland district? | SELECT sum(Population) FROM city WHERE District  =  "Gelderland" |  | 1. SELECT[val:​city:​District:​Gelderland] <br>2. PROJECT[tbl:​city, #1] <br>3. COMPARATIVE[#2, #1, comparative:​=:​Gelderland:​col:​city:​District] <br>4. AGGREGATE[count, #3] <br> | - | easy | 
  | SPIDER_dev_729 | What is the total population of Gelderland district? | SELECT sum(Population) FROM city WHERE District  =  "Gelderland" |  | 1. SELECT[val:​city:​District:​Gelderland] <br>2. PROJECT[col:​city:​Population, #1] <br>3. AGGREGATE[sum, #2] <br> | + | easy | 
  | SPIDER_dev_730 | What is the average GNP and total population in all nations whose government is US territory? | SELECT avg(GNP) ,  sum(population) FROM country WHERE GovernmentForm  =  "US Territory" |  | 1. SELECT[tbl:​country] <br>2. PROJECT[col:​country:​GovernmentForm, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​US Territory:​col:​country:​GovernmentForm] <br>4. PROJECT[col:​country:​GNP, #3] <br>5. AGGREGATE[avg, #4] <br>6. PROJECT[col:​country:​Population, #3] <br>7. AGGREGATE[avg, #6] <br>8. AGGREGATE[sum, #5] <br>9. UNION[#6, #7] <br> | - | medium | 
  | SPIDER_dev_731 | Give the mean GNP and total population of nations which are considered US territory. | SELECT avg(GNP) ,  sum(population) FROM country WHERE GovernmentForm  =  "US Territory" |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​=:​US:​col:​country:​Code2] <br>3. COMPARATIVE[#2, #2, comparative:​=:​US Territory:​col:​country:​GovernmentForm] <br>4. PROJECT[col:​country:​GNP, #3] <br>5. AGGREGATE[avg, #4] <br>6. AGGREGATE[sum, #4] <br>7. UNION[#5, #6] <br> | - | medium | 
  | SPIDER_dev_732 | How many unique languages are spoken in the world? | SELECT count(DISTINCT LANGUAGE) FROM countrylanguage |  | 1. SELECT[col:​countrylanguage:​Language] <br>2. PROJECT[None, #1] <br>3. AGGREGATE[count, #2] <br> | + | easy | 
  | SPIDER_dev_733 | What is the number of distinct languages used around the world? | SELECT count(DISTINCT LANGUAGE) FROM countrylanguage |  | 1. SELECT[col:​countrylanguage:​Language] <br>2.*(distinct)* PROJECT[None, #1] <br>3. AGGREGATE[count, #2] <br> | + | easy | 
  | SPIDER_dev_734 | How many type of governments are in Africa? | SELECT count(DISTINCT GovernmentForm) FROM country WHERE Continent  =  "Africa" |  | 1. SELECT[tbl:​country] <br>2. PROJECT[col:​country:​GovernmentForm, #1] <br>3. COMPARATIVE[#2, #1, comparative:​=:​Africa:​col:​country:​Continent] <br>4. AGGREGATE[count, #3] <br> | - | easy | 
  | SPIDER_dev_735 | How many different forms of governments are there in Africa? | SELECT count(DISTINCT GovernmentForm) FROM country WHERE Continent  =  "Africa" |  | 1. SELECT[col:​country:​GovernmentForm] <br>2. PROJECT[col:​country:​GovernmentForm, #1] <br>3. COMPARATIVE[#2, #1, comparative:​=:​Africa:​col:​country:​Continent] <br>4.*(distinct)* PROJECT[None, #3] <br>5. AGGREGATE[count, #4] <br> | + | easy | 
  | SPIDER_dev_736 | What is the total number of languages used in Aruba? | SELECT COUNT(T2.Language) FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T1.Name  =  "Aruba" |  | 1. SELECT[val:​country:​LocalName:​Aruba] <br>2. PROJECT[tbl:​countrylanguage, #1] <br>3. AGGREGATE[count, #2] <br> | + | medium | 
  | SPIDER_dev_737 | How many languages are spoken in Aruba? | SELECT COUNT(T2.Language) FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T1.Name  =  "Aruba" |  | 1. SELECT[val:​country:​LocalName:​Aruba] <br>2. PROJECT[col:​countrylanguage:​Language, #1] <br>3. AGGREGATE[count, #2] <br> | + | medium | 
  | SPIDER_dev_738 | How many official languages does Afghanistan have? | SELECT COUNT(*) FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T1.Name  =  "Afghanistan" AND IsOfficial  =  "T" |  | 1. SELECT[col:​countrylanguage:​IsOfficial] <br>2. PROJECT[col:​countrylanguage:​IsOfficial, #1] <br>3. COMPARATIVE[#2, #1, comparative:​=:​Afghanistan:​col:​country:​Name] <br>4. AGGREGATE[count, #3] <br> | + | medium | 
  | SPIDER_dev_739 | How many official languages are spoken in Afghanistan? | SELECT COUNT(*) FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T1.Name  =  "Afghanistan" AND IsOfficial  =  "T" |  | 1. SELECT[tbl:​countrylanguage] <br>2. PROJECT[tbl:​countrylanguage, #1] <br>3. COMPARATIVE[#2, #1, comparative:​=:​Afghanistan:​col:​country:​Name] <br>4. AGGREGATE[count, #3] <br> | - | medium | 
  | SPIDER_dev_740 | What is name of the country that speaks the largest number of languages? | 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 |  | 1. SELECT[tbl:​country] <br>2. PROJECT[tbl:​countrylanguage, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​country:​Name, #4] <br> | + | extra | 
  | SPIDER_dev_741 | Give the name of the nation that uses the greatest amount of languages. | 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 |  | 1. SELECT[tbl:​country] <br>2. PROJECT[tbl:​countrylanguage, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​country:​Name, #4] <br> | + | extra | 
  | SPIDER_dev_742 | Which continent has the most diverse languages? | 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 |  | 1. SELECT[col:​country:​Continent] <br>2. PROJECT[None, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br> | + | extra | 
  | SPIDER_dev_743 | Which continent speaks the most languages? | 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 |  | 1. SELECT[col:​country:​Continent] <br>2. PROJECT[None, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br> | + | extra | 
  | SPIDER_dev_744 | How many countries speak both English and Dutch? | 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") |  | 1. SELECT[tbl:​country] <br>2. PROJECT[tbl:​countrylanguage, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​English:​col:​countrylanguage:​Language] <br>4. COMPARATIVE[#1, #2, comparative:​=:​Dutch:​col:​countrylanguage:​Language] <br>5. INTERSECTION[#1, #3, #4] <br>6. AGGREGATE[count, #5] <br> | + | easy | 
  | SPIDER_dev_745 | What is the number of nations that use English and Dutch? | 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") |  | 1. SELECT[tbl:​countrylanguage] <br>2. COMPARATIVE[#1, #1, comparative:​=:​English:​col:​countrylanguage:​Language] <br>3. COMPARATIVE[#1, #1, comparative:​=:​Dutch:​col:​countrylanguage:​Language] <br>4. INTERSECTION[#1, #2, #3] <br>5. AGGREGATE[count, #4] <br> | - | easy | 
  | SPIDER_dev_746 | What are the names of nations speak both English and French? | 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" |  | 1. SELECT[tbl:​countrylanguage] <br>2. PROJECT[col:​countrylanguage:​Language, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​English:​col:​countrylanguage:​Language] <br>4. COMPARATIVE[#1, #2, comparative:​=:​French:​col:​countrylanguage:​Language] <br>5. INTERSECTION[#1, #3, #4] <br>6. PROJECT[col:​country:​Name, #5] <br> | - | extra | 
  | SPIDER_dev_747 | Give the names of nations that speak both English and French. | 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" |  | 1. SELECT[tbl:​countrylanguage] <br>2. COMPARATIVE[#1, #1, comparative:​=:​English:​col:​countrylanguage:​Language] <br>3. COMPARATIVE[#2, #2, comparative:​=:​French:​col:​countrylanguage:​Language] <br>4. PROJECT[col:​country:​Name, #3] <br> | - | extra | 
  | SPIDER_dev_748 | What are the names of nations where both English and French are official languages? | 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" |  | 1. SELECT[tbl:​countrylanguage] <br>2. PROJECT[col:​countrylanguage:​Language, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​English:​col:​countrylanguage:​Language] <br>4. COMPARATIVE[#1, #2, comparative:​=:​French:​col:​countrylanguage:​Language] <br>5. INTERSECTION[#1, #3, #4] <br>6. PROJECT[col:​country:​Name, #5] <br> | - | extra | 
  | SPIDER_dev_749 | Give the names of countries with English and French as official languages. | 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" |  | 1. SELECT[tbl:​country] <br>2. PROJECT[tbl:​countrylanguage, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​English:​col:​countrylanguage:​Language] <br>4. COMPARATIVE[#1, #2, comparative:​=:​French:​col:​countrylanguage:​Language] <br>5. INTERSECTION[#1, #3, #4] <br>6. PROJECT[col:​country:​Name, #5] <br> | - | extra | 
  | SPIDER_dev_750 | What is the number of distinct continents where Chinese is spoken? | SELECT COUNT( DISTINCT Continent) FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "Chinese" |  | 1. SELECT[col:​country:​Continent] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Chinese:​col:​countrylanguage:​Language] <br>3.*(distinct)* PROJECT[None, #2] <br>4. AGGREGATE[count, #3] <br> | + | medium | 
  | SPIDER_dev_751 | How many continents speak Chinese? | SELECT COUNT( DISTINCT Continent) FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T2.Language  =  "Chinese" |  | 1. SELECT[col:​country:​Continent] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Chinese:​col:​countrylanguage:​Language] <br>3. AGGREGATE[count, #2] <br> | + | medium | 
  | SPIDER_dev_752 | What are the regions that use English or Dutch? | 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" |  | 1. SELECT[col:​country:​Region] <br>2. COMPARATIVE[#1, #1, comparative:​=:​English:​col:​countrylanguage:​Language] <br>3. COMPARATIVE[#1, #1, comparative:​=:​Dutch:​col:​countrylanguage:​Language] <br>4. UNION[#2, #3] <br> | + | hard | 
  | SPIDER_dev_753 | Which regions speak Dutch or English? | 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" |  | 1. SELECT[col:​country:​Region] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Dutch:​col:​countrylanguage:​Language] <br>3. COMPARATIVE[#1, #1, comparative:​=:​English:​col:​countrylanguage:​Language] <br>4. UNION[#2, #3] <br> | + | hard | 
  | SPIDER_dev_754 | What are the countries where either English or Dutch is the official language? | 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" |  | 1. SELECT[col:​country:​Name] <br>2. PROJECT[col:​countrylanguage:​Language, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​English:​col:​countrylanguage:​Language] <br>4. COMPARATIVE[#1, #2, comparative:​=:​Dutch:​col:​countrylanguage:​Language] <br>5. UNION[#3, #4] <br> | - | extra | 
  | SPIDER_dev_755 | Which countries have either English or Dutch as an official language? | 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" |  | 1. SELECT[col:​country:​Name] <br>2. PROJECT[tbl:​countrylanguage, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​English:​col:​countrylanguage:​Language] <br>4. COMPARATIVE[#1, #2, comparative:​=:​Dutch:​col:​countrylanguage:​Language] <br>5. UNION[#3, #4] <br> | - | extra | 
  | SPIDER_dev_756 | Which language is the most popular on the Asian continent? | 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 |  | 1. SELECT[col:​country:​Region] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Asia:​col:​country:​Continent] <br>3. SELECT[col:​country:​Region] <br>4. GROUP[count, #2, #3] <br>5. SUPERLATIVE[comparative:​max:​None, #1, #4] <br> | - | extra | 
  | SPIDER_dev_757 | What is the language that is used by the largest number of Asian nations? | 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 |  | 1. SELECT[col:​countrylanguage:​Language] <br>2. PROJECT[tbl:​countrylanguage, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br> | - | extra | 
  | SPIDER_dev_758 | Which languages are spoken by only one country in republic governments? | 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 |  | 1. SELECT[col:​countrylanguage:​Language] <br>2. PROJECT[tbl:​country, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​Republic:​col:​country:​GovernmentForm] <br> | - | hard | 
  | SPIDER_dev_759 | What languages are only used by a single country with a republic government? | 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 |  | 1. SELECT[col:​countrylanguage:​Language] <br>2. PROJECT[tbl:​country, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​Republic:​col:​country:​GovernmentForm] <br> | - | hard | 
  | SPIDER_dev_760 | Find the city with the largest population that uses English. | 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 |  | 1. SELECT[tbl:​city] <br>2. COMPARATIVE[#1, #1, comparative:​=:​English:​col:​countrylanguage:​Language] <br>3. PROJECT[col:​city:​Population, #2] <br>4. SUPERLATIVE[comparative:​max:​None, #2, #3] <br> | - | extra | 
  | SPIDER_dev_761 | What is the most populace city that speaks English? | 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 |  | 1. SELECT[tbl:​city] <br>2. COMPARATIVE[#1, #1, comparative:​=:​English:​col:​countrylanguage:​Language] <br>3. PROJECT[col:​city:​Population, #2] <br>4. SUPERLATIVE[comparative:​max:​None, #2, #3] <br> | - | extra | 
  | SPIDER_dev_762 | Find the name, population and expected life length of asian country with the largest area? | SELECT Name ,  Population ,  LifeExpectancy FROM country WHERE Continent  =  "Asia" ORDER BY SurfaceArea DESC LIMIT 1 |  | 1. SELECT[tbl:​country] <br>2. PROJECT[col:​country:​SurfaceArea, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. PROJECT[col:​country:​Name, #3] <br>5. PROJECT[col:​country:​Population, #3] <br>6. PROJECT[col:​country:​LifeExpectancy, #3] <br>7. UNION[#4, #5, #6] <br> | - | hard | 
  | SPIDER_dev_763 | What are the name, population, and life expectancy of the largest Asian country by land? | SELECT Name ,  Population ,  LifeExpectancy FROM country WHERE Continent  =  "Asia" ORDER BY SurfaceArea DESC LIMIT 1 |  | 1. SELECT[tbl:​country] <br>2. PROJECT[col:​country:​Population, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​country:​Name, #4] <br>6. PROJECT[col:​country:​Population, #4] <br>7. PROJECT[col:​country:​LifeExpectancy, #4] <br>8. UNION[#5, #6, #7] <br> | + | hard | 
  | SPIDER_dev_764 | What is average life expectancy in the countries where English is not the official language? | 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") |  | 1. SELECT[tbl:​country] <br>2. PROJECT[col:​countrylanguage:​Language, #1] <br>3. COMPARATIVE[#1, #2, comparative:​!=:​English:​col:​countrylanguage:​Language] <br>4. PROJECT[col:​country:​LifeExpectancy, #3] <br>5. AGGREGATE[avg, #4] <br> | - | extra | 
  | SPIDER_dev_765 | Give the mean life expectancy of countries in which English is not the official language. | 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") |  | 1. SELECT[tbl:​country] <br>2. PROJECT[tbl:​countrylanguage, #1] <br>3. COMPARATIVE[#1, #2, comparative:​!=:​English:​col:​countrylanguage:​Language] <br>4. PROJECT[col:​country:​LifeExpectancy, #3] <br>5. AGGREGATE[avg, #4] <br> | - | extra | 
  | SPIDER_dev_766 | What is the total number of people living in the nations that do not use English? | 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") |  | 1. SELECT[tbl:​countrylanguage] <br>2. COMPARATIVE[#1, #1, comparative:​!=:​English:​col:​countrylanguage:​Language] <br>3. AGGREGATE[count, #2] <br> | - | extra | 
  | SPIDER_dev_767 | How many people live in countries that do not speak English? | 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") |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​!=:​English:​col:​countrylanguage:​Language] <br>3. AGGREGATE[count, #2] <br> | - | extra | 
  | SPIDER_dev_768 | What is the official language spoken in the country whose head of state is Beatrix? | SELECT T2.Language FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T1.HeadOfState  =  "Beatrix" AND T2.IsOfficial  =  "T" |  | 1. SELECT[tbl:​country] <br>2. PROJECT[col:​country:​HeadOfState, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​Beatrix:​col:​country:​HeadOfState] <br>4. PROJECT[col:​countrylanguage:​Language, #3] <br> | - | medium | 
  | SPIDER_dev_769 | What is the official language used in the country the name of whose head of state is Beatrix. | SELECT T2.Language FROM country AS T1 JOIN countrylanguage AS T2 ON T1.Code  =  T2.CountryCode WHERE T1.HeadOfState  =  "Beatrix" AND T2.IsOfficial  =  "T" |  | 1. SELECT[tbl:​country] <br>2. PROJECT[col:​country:​HeadOfState, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​Beatrix:​col:​country:​HeadOfState] <br>4. PROJECT[col:​countrylanguage:​Language, #3] <br> | - | medium | 
  | SPIDER_dev_770 | What is the total number of unique official languages spoken in the countries that are founded before 1930? | 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" |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​<:​1930:​col:​city:​ID] <br>3. PROJECT[col:​countrylanguage:​Language, #2] <br>4. AGGREGATE[count, #3] <br> | - | medium | 
  | SPIDER_dev_771 | For the countries founded before 1930, what is the total number of distinct official languages? | 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" |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​<:​1930:​col:​city:​ID] <br>3.*(distinct)* PROJECT[None, #2] <br>4. AGGREGATE[sum, #3] <br> | - | medium | 
  | SPIDER_dev_772 | What are the countries that have greater surface area than any country in Europe? | SELECT Name FROM country WHERE SurfaceArea  >  (SELECT min(SurfaceArea) FROM country WHERE Continent  =  "Europe") |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Europe:​col:​country:​Continent] <br>3. PROJECT[col:​country:​SurfaceArea, #2] <br>4. AGGREGATE[max, #3] <br>5. PROJECT[col:​country:​SurfaceArea, #1] <br>6. COMPARATIVE[#1, #5, comparative:​>:​Europe:​col:​country:​Continent] <br> | - | hard | 
  | SPIDER_dev_773 | Which countries have greater area than that of any country in Europe? | SELECT Name FROM country WHERE SurfaceArea  >  (SELECT min(SurfaceArea) FROM country WHERE Continent  =  "Europe") |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Europe:​col:​country:​Continent] <br>3. PROJECT[col:​country:​SurfaceArea, #2] <br>4. AGGREGATE[max, #3] <br>5. PROJECT[col:​country:​SurfaceArea, #1] <br>6. COMPARATIVE[#1, #5, comparative:​>:​Europe:​col:​country:​Continent] <br> | - | hard | 
  | SPIDER_dev_774 | What are the African countries that have a  population less than any country in Asia? | SELECT Name FROM country WHERE Continent  =  "Africa"  AND population  <  (SELECT max(population) FROM country WHERE Continent  =  "Asia") |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Asia:​col:​country:​Continent] <br>3. PROJECT[col:​country:​Population, #2] <br>4. AGGREGATE[max, #3] <br>5. SUPERLATIVE[comparative:​max:​None, #1, #4] <br> | - | extra | 
  | SPIDER_dev_775 | Which African countries have a smaller population than that of any country in Asia? | SELECT Name FROM country WHERE Continent  =  "Africa"  AND population  <  (SELECT min(population) FROM country WHERE Continent  =  "Asia") |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Asia:​col:​country:​Continent] <br>3. PROJECT[col:​country:​Population, #2] <br>4. AGGREGATE[max, #3] <br>5. PROJECT[col:​country:​Population, #1] <br>6. COMPARATIVE[#1, #5, comparative:​<:​Asia:​col:​country:​Continent] <br>7. UNION[#4, #6] <br> | - | extra | 
  | SPIDER_dev_776 | Which Asian countries have a population that is larger than any country in Africa? | SELECT Name FROM country WHERE Continent  =  "Asia"  AND population  >  (SELECT max(population) FROM country WHERE Continent  =  "Africa") |  | 1. SELECT[tbl:​country] <br>2. PROJECT[col:​country:​Population, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​Africa:​col:​country:​Continent] <br>4. PROJECT[col:​country:​Population, #3] <br>5. AGGREGATE[max, #4] <br>6. COMPARATIVE[#1, #2, comparative:​>:​Africa:​col:​country:​Continent] <br> | - | extra | 
  | SPIDER_dev_777 | What are the Asian countries which have a population larger than that of any country in Africa? | SELECT Name FROM country WHERE Continent  =  "Asia"  AND population  >  (SELECT min(population) FROM country WHERE Continent  =  "Africa") |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Africa:​col:​country:​Continent] <br>3. PROJECT[col:​country:​Population, #2] <br>4. AGGREGATE[max, #3] <br>5. PROJECT[col:​country:​Population, #1] <br>6. COMPARATIVE[#1, #5, comparative:​>:​Africa:​col:​country:​Continent] <br> | - | extra | 
  | SPIDER_dev_778 | What are the country codes for countries that do not speak English? | SELECT CountryCode FROM countrylanguage EXCEPT SELECT CountryCode FROM countrylanguage WHERE LANGUAGE  =  "English" |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​!=:​English:​col:​countrylanguage:​Language] <br>3. PROJECT[col:​country:​Code, #2] <br> | - | hard | 
  | SPIDER_dev_779 | Return the country codes for countries that do not speak English. | SELECT CountryCode FROM countrylanguage EXCEPT SELECT CountryCode FROM countrylanguage WHERE LANGUAGE  =  "English" |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​!=:​English:​col:​countrylanguage:​Language] <br>3. PROJECT[col:​country:​Code, #2] <br> | - | hard | 
  | SPIDER_dev_780 | What are the country codes of countries where people use languages other than English? | SELECT DISTINCT CountryCode FROM countrylanguage WHERE LANGUAGE != "English" |  | 1. SELECT[col:​countrylanguage:​CountryCode] <br>2. PROJECT[tbl:​countrylanguage, #1] <br>3. COMPARATIVE[#1, #2, comparative:​!=:​English:​col:​countrylanguage:​Language] <br>4. PROJECT[col:​countrylanguage:​CountryCode, #3] <br> | + | easy | 
  | SPIDER_dev_781 | Give the country codes for countries in which people speak langauges that are not English. | SELECT DISTINCT CountryCode FROM countrylanguage WHERE LANGUAGE != "English" |  | 1. SELECT[col:​countrylanguage:​CountryCode] <br>2. PROJECT[tbl:​countrylanguage, #1] <br>3. COMPARATIVE[#1, #2, comparative:​!=:​English:​col:​countrylanguage:​Language] <br>4. PROJECT[col:​countrylanguage:​CountryCode, #3] <br> | + | easy | 
  | SPIDER_dev_782 | What are the codes of the countries that do not speak English and whose government forms are not Republic? | SELECT Code FROM country WHERE GovernmentForm != "Republic" EXCEPT SELECT CountryCode FROM countrylanguage WHERE LANGUAGE  =  "English" |  | 1. SELECT[tbl:​country] <br>2. PROJECT[col:​countrylanguage:​Language, #1] <br>3. COMPARATIVE[#1, #2, comparative:​!=:​English:​col:​countrylanguage:​Language] <br>4. COMPARATIVE[#1, #2, comparative:​!=:​Republic:​col:​country:​GovernmentForm] <br>5. INTERSECTION[#1, #3, #4] <br>6. PROJECT[col:​country:​Code, #5] <br> | - | hard | 
  | SPIDER_dev_783 | Return the codes of countries that do not speak English and do not have Republics for governments. | SELECT Code FROM country WHERE GovernmentForm != "Republic" EXCEPT SELECT CountryCode FROM countrylanguage WHERE LANGUAGE  =  "English" |  | 1. SELECT[tbl:​country] <br>2. PROJECT[col:​country:​GovernmentForm, #1] <br>3. COMPARATIVE[#1, #2, comparative:​!=:​English:​col:​countrylanguage:​Language] <br>4. PROJECT[col:​country:​GovernmentForm, #1] <br>5. COMPARATIVE[#1, #4, comparative:​!=:​Republic:​col:​country:​GovernmentForm] <br>6. INTERSECTION[#1, #3, #5] <br>7. PROJECT[col:​country:​Code, #6] <br> | - | hard | 
  | SPIDER_dev_784 | Which cities are in European countries where English is not the official language? | 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') |  | 1. SELECT[tbl:​country] <br>2. PROJECT[tbl:​countrylanguage, #1] <br>3. COMPARATIVE[#1, #2, comparative:​!=:​English:​col:​countrylanguage:​Language] <br>4. PROJECT[col:​country:​Region, #3] <br> | - | extra | 
  | SPIDER_dev_785 | What are the names of cities in Europe for which English is not the official language? | 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') |  | 1. SELECT[tbl:​city] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Europe:​col:​country:​Continent] <br>3. COMPARATIVE[#2, #2, comparative:​!=:​English:​col:​countrylanguage:​Language] <br>4. PROJECT[col:​city:​Name, #3] <br> | - | extra | 
  | SPIDER_dev_786 | Which unique cities are in Asian countries where Chinese is the official language? | 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" |  | 1. SELECT[tbl:​city] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Chinese:​col:​countrylanguage:​Language] <br>3. PROJECT[col:​city:​Name, #2] <br> | - | hard | 
  | SPIDER_dev_787 | Return the different names of cities that are in Asia and for which Chinese is the official language. | 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" |  | 1. SELECT[tbl:​city] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Asia:​col:​country:​Continent] <br>3. COMPARATIVE[#2, #2, comparative:​=:​Chinese:​col:​countrylanguage:​Language] <br>4.*(distinct)* PROJECT[col:​city:​Name, #3] <br> | - | hard | 
  | SPIDER_dev_788 | What are the name, independence year, and surface area of the country with the smallest population? | SELECT Name ,  SurfaceArea ,  IndepYear FROM country ORDER BY Population LIMIT 1 |  | 1. SELECT[tbl:​country] <br>2. PROJECT[col:​country:​Population, #1] <br>3. SUPERLATIVE[comparative:​min:​None, #1, #2] <br>4. PROJECT[col:​country:​Name, #3] <br>5. PROJECT[col:​country:​IndepYear, #3] <br>6. PROJECT[col:​country:​SurfaceArea, #3] <br>7. UNION[#4, #5, #6] <br> | - | medium | 
  | SPIDER_dev_789 | Give the name, year of independence, and surface area of the country that has the lowest population. | SELECT Name ,  SurfaceArea ,  IndepYear FROM country ORDER BY Population LIMIT 1 |  | 1. SELECT[tbl:​country] <br>2. PROJECT[col:​country:​Population, #1] <br>3. SUPERLATIVE[comparative:​min:​None, #1, #2] <br>4. PROJECT[col:​country:​Name, #3] <br>5. PROJECT[col:​country:​IndepYear, #3] <br>6. PROJECT[col:​country:​SurfaceArea, #3] <br>7. UNION[#4, #5, #6] <br> | - | medium | 
  | SPIDER_dev_790 | What are the population, name and leader of the country with the largest area? | SELECT Name ,  population ,  HeadOfState FROM country ORDER BY SurfaceArea DESC LIMIT 1 |  | 1. SELECT[tbl:​country] <br>2. PROJECT[col:​country:​SurfaceArea, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. PROJECT[col:​country:​Population, #3] <br>5. PROJECT[col:​country:​Name, #3] <br>6. PROJECT[col:​country:​HeadOfState, #3] <br>7. UNION[#4, #5, #6] <br> | + | medium | 
  | SPIDER_dev_791 | Give the name, population, and head of state for the country that has the largest area. | SELECT Name ,  population ,  HeadOfState FROM country ORDER BY SurfaceArea DESC LIMIT 1 |  | 1. SELECT[tbl:​country] <br>2. PROJECT[col:​country:​SurfaceArea, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. PROJECT[col:​country:​Name, #3] <br>5. PROJECT[col:​country:​Population, #3] <br>6. PROJECT[col:​country:​HeadOfState, #3] <br>7. UNION[#4, #5, #6] <br> | + | medium | 
  | SPIDER_dev_792 | Return the country name and the numbers of languages spoken for each country that speaks at least 3 languages. | 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 |  | 1. SELECT[tbl:​country] <br>2. PROJECT[tbl:​countrylanguage, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>=:​3] <br>5. PROJECT[col:​country:​Name, #4] <br>6. PROJECT[tbl:​countrylanguage, #4] <br>7. GROUP[count, #6, #4] <br>8. UNION[#5, #7] <br> | + | medium | 
  | SPIDER_dev_793 | What are the names of countries that speak more than 2 languages, as well as how many languages they speak? | 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 |  | 1. SELECT[tbl:​country] <br>2. PROJECT[tbl:​countrylanguage, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​2] <br>5. PROJECT[col:​country:​Name, #4] <br>6. PROJECT[tbl:​countrylanguage, #4] <br>7. GROUP[count, #6, #4] <br>8. UNION[#5, #7] <br> | + | medium | 
  | SPIDER_dev_794 | Find the number of cities in each district whose population is greater than the average population of cities? | SELECT count(*) ,  District FROM city WHERE Population  >  (SELECT avg(Population) FROM city) GROUP BY District |  | 1. SELECT[col:​city:​District] <br>2. PROJECT[tbl:​city, #1] <br>3. AGGREGATE[avg, #2] <br>4. COMPARATIVE[#1, #2, comparative:​>:​Sindh:​col:​city:​District] <br>5. PROJECT[tbl:​city, #4] <br>6. GROUP[count, #5, #4] <br>7. UNION[#3, #6] <br> | - | extra | 
  | SPIDER_dev_795 | How many cities in each district have a population that is above the average population across all cities? | SELECT count(*) ,  District FROM city WHERE Population  >  (SELECT avg(Population) FROM city) GROUP BY District |  | 1. SELECT[col:​city:​District] <br>2. PROJECT[tbl:​city, #1] <br>3. PROJECT[col:​city:​Population, #2] <br>4. AGGREGATE[avg, #3] <br>5. GROUP[avg, #3, #1] <br>6. COMPARATIVE[#1, #5, comparative:​>:​Amman:​col:​city:​District] <br>7. UNION[#4, #6] <br> | - | extra | 
  | SPIDER_dev_796 | Find the government form name and total population for each government form whose average life expectancy is longer than 72. | SELECT sum(Population) ,  GovernmentForm FROM country GROUP BY GovernmentForm HAVING avg(LifeExpectancy)  >  72 |  | 1. SELECT[col:​country:​GovernmentForm] <br>2. PROJECT[col:​country:​LifeExpectancy, #1] <br>3. GROUP[sum, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​72:​col:​city:​ID] <br>5. PROJECT[col:​country:​GovernmentForm, #4] <br>6. PROJECT[col:​country:​Population, #4] <br>7. GROUP[avg, #6, #4] <br>8. UNION[#5, #7] <br> | - | medium | 
  | SPIDER_dev_797 | 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? | SELECT sum(Population) ,  GovernmentForm FROM country GROUP BY GovernmentForm HAVING avg(LifeExpectancy)  >  72 |  | 1.*(distinct)* SELECT[col:​country:​GovernmentForm] <br>2. PROJECT[col:​country:​LifeExpectancy, #1] <br>3. GROUP[avg, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​72] <br>5.*(distinct)* PROJECT[col:​country:​Population, #4] <br>6. GROUP[sum, #5, #4] <br>7. UNION[#4, #6] <br> | + | medium | 
  | SPIDER_dev_798 | Find the average life expectancy and total population for each continent where the average life expectancy is shorter than 72? | SELECT sum(Population) ,  avg(LifeExpectancy) ,  Continent FROM country GROUP BY Continent HAVING avg(LifeExpectancy)  <  72 |  | 1. SELECT[col:​country:​Continent] <br>2. PROJECT[col:​country:​LifeExpectancy, #1] <br>3. GROUP[sum, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​<:​72:​col:​city:​ID] <br>5. PROJECT[col:​country:​LifeExpectancy, #4] <br>6. GROUP[sum, #5, #4] <br>7. UNION[#4, #6] <br> | - | medium | 
  | SPIDER_dev_799 | 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? | SELECT sum(Population) ,  avg(LifeExpectancy) ,  Continent FROM country GROUP BY Continent HAVING avg(LifeExpectancy)  <  72 |  | 1. SELECT[col:​country:​Continent] <br>2. PROJECT[col:​country:​LifeExpectancy, #1] <br>3. GROUP[avg, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​<:​72:​col:​city:​ID] <br>5. PROJECT[col:​country:​LifeExpectancy, #4] <br>6. GROUP[sum, #5, #4] <br>7. PROJECT[col:​country:​LifeExpectancy, #4] <br>8. GROUP[sum, #7, #4] <br>9. AGGREGATE[avg, #6] <br>10. AGGREGATE[avg, #8] <br>11. UNION[#9, #10] <br> | - | medium | 
  | SPIDER_dev_800 | What are the names and areas of countries with the top 5 largest area? | SELECT Name ,  SurfaceArea FROM country ORDER BY SurfaceArea DESC LIMIT 5 |  | 1. SELECT[tbl:​country] <br>2. PROJECT[col:​country:​SurfaceArea, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. PROJECT[col:​country:​Name, #3] <br>5. PROJECT[col:​country:​SurfaceArea, #3] <br>6. UNION[#4, #5] <br> | - | medium | 
  | SPIDER_dev_801 | Return the names and surface areas of the 5 largest countries. | SELECT Name ,  SurfaceArea FROM country ORDER BY SurfaceArea DESC LIMIT 5 |  | 1. SELECT[tbl:​country] <br>2. PROJECT[col:​country:​SurfaceArea, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. PROJECT[col:​country:​Name, #3] <br>5. PROJECT[col:​country:​SurfaceArea, #3] <br>6. UNION[#4, #5] <br> | - | medium | 
  | SPIDER_dev_802 | What are names of countries with the top 3 largest population? | SELECT Name FROM country ORDER BY Population DESC LIMIT 3 |  | 1. SELECT[tbl:​country] <br>2. PROJECT[col:​country:​Population, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. PROJECT[col:​country:​Name, #3] <br> | - | medium | 
  | SPIDER_dev_803 | Return the names of the 3 most populated countries. | SELECT Name FROM country ORDER BY Population DESC LIMIT 3 |  | 1. SELECT[tbl:​country] <br>2. PROJECT[col:​country:​Population, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. PROJECT[col:​country:​Name, #3] <br> | - | medium | 
  | SPIDER_dev_804 | What are the names of the nations with the 3 lowest populations? | SELECT Name FROM country ORDER BY Population ASC LIMIT 3 |  | 1. SELECT[tbl:​sqlite_sequence] <br>2. PROJECT[col:​country:​Population, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​min:​None, #1, #3] <br>5. PROJECT[col:​country:​Name, #4] <br> | - | medium | 
  | SPIDER_dev_805 | Return the names of the 3 countries with the fewest people. | SELECT Name FROM country ORDER BY Population ASC LIMIT 3 |  | 1. SELECT[tbl:​country] <br>2. PROJECT[col:​country:​Population, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​min:​None, #1, #3] <br>5. PROJECT[col:​country:​Name, #4] <br> | - | medium | 
  | SPIDER_dev_806 | how many countries are in Asia? | SELECT count(*) FROM country WHERE continent  =  "Asia" |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Asia:​col:​country:​Continent] <br>3. AGGREGATE[count, #2] <br> | + | easy | 
  | SPIDER_dev_807 | Count the number of countries in Asia. | SELECT count(*) FROM country WHERE continent  =  "Asia" |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Asia:​col:​country:​Continent] <br>3. AGGREGATE[count, #2] <br> | + | easy | 
  | SPIDER_dev_808 | What are the names of the countries that are in the continent of Europe and have a population of 80000? | SELECT Name FROM country WHERE continent  =  "Europe" AND Population  =  "80000" |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Europe:​col:​country:​Continent] <br>3. PROJECT[col:​country:​Population, #2] <br>4. COMPARATIVE[#2, #3, comparative:​=:​80000:​col:​country:​Population] <br>5. PROJECT[col:​country:​Name, #4] <br> | + | medium | 
  | SPIDER_dev_809 | Give the names of countries that are in Europe and have a population equal to 80000. | SELECT Name FROM country WHERE continent  =  "Europe" AND Population  =  "80000" |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Europe:​col:​country:​Continent] <br>3. PROJECT[col:​country:​Population, #2] <br>4. COMPARATIVE[#2, #3, comparative:​=:​80000:​col:​country:​Population] <br>5. PROJECT[col:​country:​Name, #4] <br> | + | medium | 
  | SPIDER_dev_810 | What is the total population and average area of countries in the continent of North America whose area is bigger than 3000? | select sum(population) ,  avg(surfacearea) from country where continent  =  "north america" and surfacearea  >  3000 |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​=:​North America:​col:​country:​Continent] <br>3. PROJECT[tbl:​country, #2] <br>4. COMPARATIVE[#3, #3, comparative:​>:​3000:​col:​city:​ID] <br>5. PROJECT[col:​country:​Population, #4] <br>6. AGGREGATE[sum, #5] <br>7. AGGREGATE[avg, #6] <br>8. UNION[#7, #7] <br> | - | hard | 
  | SPIDER_dev_811 | Give the total population and average surface area corresponding to countries in North America that have a surface area greater than 3000. | select sum(population) ,  avg(surfacearea) from country where continent  =  "north america" and surfacearea  >  3000 |  | 1. SELECT[tbl:​country] <br>2. COMPARATIVE[#1, #1, comparative:​=:​North America:​col:​country:​Region] <br>3. PROJECT[col:​country:​SurfaceArea, #2] <br>4. COMPARATIVE[#2, #3, comparative:​>:​3000:​col:​city:​ID] <br>5. PROJECT[col:​country:​Population, #4] <br>6. AGGREGATE[sum, #5] <br>7. AGGREGATE[avg, #6] <br>8. UNION[#7, #7] <br> | - | hard | 
  | SPIDER_dev_812 | What are the cities whose population is between 160000 and 900000? | SELECT name FROM city WHERE Population BETWEEN 160000 AND 900000 |  | 1. SELECT[col:​city:​Name] <br>2. PROJECT[col:​city:​Population, #1] <br>3. COMPARATIVE[#1, #2, comparative:​<=:​900000:​col:​city:​Population] <br>4. COMPARATIVE[#1, #2, comparative:​<=:​160000:​col:​city:​Population] <br>5. INTERSECTION[#1, #3, #4] <br> | - | easy | 
  | SPIDER_dev_813 | Return the names of cities that have a population between 160000 and 900000. | select name from city where population between 160000 and 900000 |  | 1. SELECT[tbl:​city] <br>2. PROJECT[col:​city:​Population, #1] <br>3. COMPARATIVE[#1, #2, comparative:​<=:​160000:​col:​city:​Population] <br>4. COMPARATIVE[#1, #2, comparative:​<=:​900000:​col:​city:​Population] <br>5. INTERSECTION[#1, #3, #4] <br>6. PROJECT[col:​city:​Name, #5] <br> | - | easy | 
  | SPIDER_dev_814 | Which language is spoken by the largest number of countries? | SELECT LANGUAGE FROM countrylanguage GROUP BY LANGUAGE ORDER BY count(*) DESC LIMIT 1 |  | 1. SELECT[col:​countrylanguage:​Language] <br>2. PROJECT[tbl:​countrylanguage, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br> | + | hard | 
  | SPIDER_dev_815 | Give the language that is spoken in the most countries. | SELECT LANGUAGE FROM countrylanguage GROUP BY LANGUAGE ORDER BY count(*) DESC LIMIT 1 |  | 1. SELECT[col:​countrylanguage:​Language] <br>2. PROJECT[tbl:​countrylanguage, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br> | + | hard | 
  | SPIDER_dev_816 | What is the language spoken by the largest percentage of people in each country? | SELECT LANGUAGE ,  CountryCode ,  max(Percentage) FROM countrylanguage GROUP BY CountryCode |  | 1. SELECT[col:​countrylanguage:​Language] <br>2. PROJECT[tbl:​countrylanguage, #1] <br>3. PROJECT[col:​countrylanguage:​Percentage, #2] <br>4. GROUP[count, #3, #1] <br>5. SUPERLATIVE[comparative:​max:​None, #1, #4] <br> | - | medium | 
  | SPIDER_dev_817 | What are the country codes of the different countries, and what are the languages spoken by the greatest percentage of people for each? | SELECT LANGUAGE ,  CountryCode ,  max(Percentage) FROM countrylanguage GROUP BY CountryCode |  | 1.*(distinct)* SELECT[col:​countrylanguage:​CountryCode] <br>2. PROJECT[col:​countrylanguage:​CountryCode, #1] <br>3. PROJECT[tbl:​countrylanguage, #2] <br>4. GROUP[count, #3, #1] <br>5. SUPERLATIVE[comparative:​max:​None, #1, #4] <br>6. PROJECT[col:​countrylanguage:​CountryCode, #5] <br>7. PROJECT[col:​countrylanguage:​Language, #5] <br>8. UNION[#6, #7] <br> | - | medium | 
  | SPIDER_dev_819 | Count the number of countries for which Spanish is the predominantly spoken language. | SELECT count(*) ,   max(Percentage) FROM countrylanguage WHERE LANGUAGE  =  "Spanish" GROUP BY CountryCode |  | 1. SELECT[tbl:​country] <br>2. PROJECT[tbl:​countrylanguage, #1] <br>3. COMPARATIVE[#2, #2, comparative:​=:​Spanish:​col:​countrylanguage:​Language] <br>4. GROUP[count, #3, #1] <br>5. SUPERLATIVE[comparative:​max:​None, #1, #4] <br>6. AGGREGATE[count, #5] <br> | - | extra | 
  | SPIDER_dev_820 | What are the codes of countries where Spanish is spoken by the largest percentage of people? | SELECT CountryCode ,  max(Percentage) FROM countrylanguage WHERE LANGUAGE  =  "Spanish" GROUP BY CountryCode |  | 1. SELECT[col:​countrylanguage:​CountryCode] <br>2. PROJECT[col:​countrylanguage:​Language, #1] <br>3. COMPARATIVE[#2, #2, comparative:​=:​Spanish:​col:​countrylanguage:​Language] <br>4. PROJECT[col:​countrylanguage:​Percentage, #3] <br>5. SUPERLATIVE[comparative:​max:​None, #1, #4] <br>6. PROJECT[col:​countrylanguage:​CountryCode, #5] <br> | - | medium | 
  | SPIDER_dev_821 | Return the codes of countries for which Spanish is the predominantly spoken language. | SELECT CountryCode ,  max(Percentage) FROM countrylanguage WHERE LANGUAGE  =  "Spanish" GROUP BY CountryCode |  | 1. SELECT[col:​countrylanguage:​CountryCode] <br>2. PROJECT[tbl:​countrylanguage, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​Spanish:​col:​countrylanguage:​Language] <br>4. PROJECT[col:​countrylanguage:​CountryCode, #3] <br>5. SUPERLATIVE[comparative:​max:​None, #1, #4] <br>6. PROJECT[col:​countrylanguage:​CountryCode, #5] <br> | - | medium | 
 ***
 Exec acc: **0.3950**
