 | Idx | Table      | Column | Primary Key | Foreign Key | 
 | ----------- | ----------- | ----------- | ----------- | ----------- | 
  | 0 |  | * |   |   | 
 | 1 | **continents** | ContId | + |   | 
 | 2 |   | Continent |   |   | 
 | 3 | **countries** | CountryId | + |   | 
 | 4 |   | CountryName |   |   | 
 | 5 |   | Continent |   | --> 1 | 
 | 6 | **car_makers** | Id | + |   | 
 | 7 |   | Maker |   |   | 
 | 8 |   | FullName |   |   | 
 | 9 |   | Country |   | --> 3 | 
 | 10 | **model_list** | ModelId | + |   | 
 | 11 |   | Maker |   | --> 6 | 
 | 12 |   | Model |   |   | 
 | 13 | **car_names** | MakeId | + |   | 
 | 14 |   | Model |   | --> 12 | 
 | 15 |   | Make |   |   | 
 | 16 | **cars_data** | Id | + | --> 13 | 
 | 17 |   | MPG |   |   | 
 | 18 |   | Cylinders |   |   | 
 | 19 |   | Edispl |   |   | 
 | 20 |   | Horsepower |   |   | 
 | 21 |   | Weight |   |   | 
 | 22 |   | Accelerate |   |   | 
 | 23 |   | Year |   |   | 
 
  | Index | Question  | SQL | gold QDMR | pred QDMR | Exec | SQL hardness |
  | ----------- | ----------- | ----------- |  ----------- | ----------- | ----------- | ----------- | 
 | SPIDER_dev_87 | How many continents are there? | SELECT count(*) FROM CONTINENTS; | 1. SELECT[tbl:​continents] <br>2. AGGREGATE[count, #1] <br> | 1. SELECT[tbl:​continents] <br>2. AGGREGATE[count, #1] <br> | + | easy | 
  | SPIDER_dev_88 | What is the number of continents? | SELECT count(*) FROM CONTINENTS; | 1. SELECT[tbl:​continents] <br>2. AGGREGATE[count, #1] <br> | 1. SELECT[tbl:​continents] <br>2. AGGREGATE[count, #1] <br> | + | easy | 
  | SPIDER_dev_89 | How many countries does each continent have? List the continent id, continent name and the number of countries. | SELECT T1.ContId ,  T1.Continent ,  count(*) FROM CONTINENTS AS T1 JOIN COUNTRIES AS T2 ON T1.ContId  =  T2.Continent GROUP BY T1.ContId; | 1. SELECT[tbl:​continents] <br>2. PROJECT[tbl:​countries, #1] <br>3. GROUP[count, #2, #1] <br>4. PROJECT[col:​continents:​ContId, #1] <br>5. PROJECT[col:​continents:​Continent, #1] <br>6. UNION[#4, #5, #3] <br> | 1. SELECT[tbl:​continents] <br>2. PROJECT[col:​continents:​ContId, #1] <br>3. PROJECT[col:​continents:​Continent, #1] <br>4. PROJECT[tbl:​countries, #1] <br>5. GROUP[count, #4, #1] <br>6. UNION[#2, #3, #5] <br> | + | medium | 
  | SPIDER_dev_90 | For each continent, list its id, name, and how many countries it has? | SELECT T1.ContId ,  T1.Continent ,  count(*) FROM CONTINENTS AS T1 JOIN COUNTRIES AS T2 ON T1.ContId  =  T2.Continent GROUP BY T1.ContId; | 1. SELECT[tbl:​continents] <br>2. PROJECT[col:​continents:​ContId, #1] <br>3. PROJECT[col:​continents:​Continent, #1] <br>4. PROJECT[tbl:​countries, #1] <br>5. GROUP[count, #4, #1] <br>6. UNION[#2, #3, #5] <br> | 1. SELECT[col:​countries:​Continent] <br>2. PROJECT[col:​countries:​Continent, #1] <br>3. PROJECT[col:​continents:​Continent, #1] <br>4. PROJECT[tbl:​countries, #1] <br>5. GROUP[count, #4, #1] <br>6. UNION[#2, #3, #5] <br> | + | medium | 
  | SPIDER_dev_91 | How many countries are listed? | SELECT count(*) FROM COUNTRIES; | 1. SELECT[tbl:​countries] <br>2. FILTER[#1, that are listed] <br>3. AGGREGATE[count, #2] <br> | 1. SELECT[tbl:​countries] <br>2. AGGREGATE[count, #1] <br> | + | easy | 
  | SPIDER_dev_92 | How many countries exist? | SELECT count(*) FROM COUNTRIES; | 1. SELECT[tbl:​countries] <br>2. AGGREGATE[count, #1] <br> | 1. SELECT[tbl:​countries] <br>2. AGGREGATE[count, #1] <br> | + | easy | 
  | SPIDER_dev_93 | How many models does each car maker produce? List maker full name, id and the number. | SELECT T1.FullName ,  T1.Id ,  count(*) FROM CAR_MAKERS AS T1 JOIN MODEL_LIST AS T2 ON T1.Id  =  T2.Maker GROUP BY T1.Id; | 1. SELECT[tbl:​car_makers] <br>2. PROJECT[tbl:​model_list, #1] <br>3. GROUP[count, #2, #1] <br>4. PROJECT[col:​car_makers:​FullName, #1] <br>5. PROJECT[col:​car_makers:​Id, #1] <br>6. UNION[#4, #5, #3] <br> | 1. SELECT[col:​model_list:​Maker] <br>2. PROJECT[tbl:​model_list, #1] <br>3. GROUP[count, #2, #1] <br>4. PROJECT[col:​car_makers:​FullName, #1] <br>5. PROJECT[col:​car_makers:​Id, #1] <br>6. UNION[#4, #5, #3] <br> | + | medium | 
  | SPIDER_dev_94 | What is the full name of each car maker, along with its id and how many models it produces? | SELECT T1.FullName ,  T1.Id ,  count(*) FROM CAR_MAKERS AS T1 JOIN MODEL_LIST AS T2 ON T1.Id  =  T2.Maker GROUP BY T1.Id; | 1. SELECT[tbl:​car_makers] <br>2. PROJECT[tbl:​model_list, #1] <br>3. GROUP[count, #2, #1] <br>4. PROJECT[col:​car_makers:​FullName, #1] <br>5. PROJECT[col:​car_makers:​Id, #1] <br>6. UNION[#4, #5, #3] <br> | 1. SELECT[col:​model_list:​Maker] <br>2. PROJECT[col:​car_makers:​FullName, #1] <br>3. PROJECT[col:​car_makers:​Id, #1] <br>4. PROJECT[tbl:​model_list, #1] <br>5. GROUP[count, #4, #1] <br>6. UNION[#2, #3, #5] <br> | + | medium | 
  | SPIDER_dev_95 | Which model of the car has the minimum horsepower? | SELECT T1.Model FROM CAR_NAMES AS T1 JOIN CARS_DATA AS T2 ON T1.MakeId  =  T2.Id WHERE T2.horsepower = ( SELECT MIN( horsepower ) FROM CARS_DATA); | 1. SELECT[tbl:​car_names] <br>2. PROJECT[col:​cars_data:​Horsepower, #1] <br>3. AGGREGATE[min, #2] <br>4. COMPARATIVE[#1, #2, comparative:​=:​#3] <br>5. PROJECT[col:​car_names:​Model, #4] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Horsepower, #1] <br>3. SUPERLATIVE[comparative:​min:​None, #1, #2] <br>4. PROJECT[col:​car_names:​Model, #3] <br> | + | extra | 
  | SPIDER_dev_96 | What is the model of the car with the smallest amount of horsepower? | SELECT T1.Model FROM CAR_NAMES AS T1 JOIN CARS_DATA AS T2 ON T1.MakeId  =  T2.Id WHERE T2.horsepower = ( SELECT MIN(horsepower) FROM CARS_DATA); | 1. SELECT[tbl:​car_names] <br>2. PROJECT[col:​cars_data:​Horsepower, #1] <br>3. SUPERLATIVE[comparative:​min:​None, #1, #2] <br>4. PROJECT[col:​car_names:​Model, #3] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Horsepower, #1] <br>3. SUPERLATIVE[comparative:​min:​None, #1, #2] <br>4. PROJECT[col:​car_names:​Model, #3] <br> | + | extra | 
  | SPIDER_dev_97 | Find the model of the car whose weight is below the average weight. | SELECT T1.model FROM CAR_NAMES AS T1 JOIN CARS_DATA AS T2 ON T1.MakeId  =  T2.Id WHERE T2.Weight  <  (SELECT avg(Weight) FROM CARS_DATA) | 1. SELECT[tbl:​car_names] <br>2. PROJECT[col:​car_names:​Model, #1] <br>3. PROJECT[col:​cars_data:​Weight, #2] <br>4. AGGREGATE[avg, #3] <br>5. COMPARATIVE[#2, #3, comparative:​<:​#4] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Weight, #1] <br>3. AGGREGATE[avg, #2] <br>4. COMPARATIVE[#1, #2, comparative:​<:​Fiat:​col:​car_makers:​FullName] <br>5. PROJECT[col:​car_names:​Model, #4] <br> | - | extra | 
  | SPIDER_dev_98 | What is the model for the car with a weight smaller than the average? | SELECT T1.model FROM CAR_NAMES AS T1 JOIN CARS_DATA AS T2 ON T1.MakeId  =  T2.Id WHERE T2.Weight  <  (SELECT avg(Weight) FROM CARS_DATA) | 1. SELECT[tbl:​car_names] <br>2. PROJECT[col:​cars_data:​Weight, #1] <br>3. AGGREGATE[avg, #2] <br>4. COMPARATIVE[#1, #2, comparative:​<:​#3] <br>5. PROJECT[col:​car_names:​Model, #4] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Weight, #1] <br>3. AGGREGATE[avg, #2] <br>4. COMPARATIVE[#1, #2, comparative:​<:​capri:​col:​car_names:​Model] <br>5. PROJECT[col:​car_names:​Model, #4] <br> | - | extra | 
  | SPIDER_dev_99 | Find the name of the makers that produced some cars in the year of 1970? | SELECT DISTINCT T1.Maker FROM CAR_MAKERS AS T1 JOIN MODEL_LIST AS T2 ON T1.Id  =  T2.Maker JOIN CAR_NAMES AS T3 ON T2.model  =  T3.model JOIN CARS_DATA AS T4 ON T3.MakeId  =  T4.id WHERE T4.year  =  '1970'; | 1. SELECT[tbl:​car_makers] <br>2. FILTER[#1, comparative:​=:​1970:​col:​cars_data:​Year] <br>3. PROJECT[tbl:​car_makers, #2] <br>4. PROJECT[col:​car_makers:​Maker, #3] <br> | 1. SELECT[col:​car_makers:​Maker] <br>2. COMPARATIVE[#1, #1, comparative:​=:​1970:​col:​cars_data:​Year] <br>3. PROJECT[col:​car_makers:​Maker, #2] <br> | + | extra | 
  | SPIDER_dev_100 | What is the name of the different car makers who produced a car in 1970? | SELECT DISTINCT T1.Maker FROM CAR_MAKERS AS T1 JOIN MODEL_LIST AS T2 ON T1.Id  =  T2.Maker JOIN CAR_NAMES AS T3 ON T2.model  =  T3.model JOIN CARS_DATA AS T4 ON T3.MakeId  =  T4.id WHERE T4.year  =  '1970'; | 1. SELECT[tbl:​car_makers] <br>2. FILTER[#1, tbl:​car_makers] <br>3. FILTER[#2, comparative:​=:​1970:​col:​cars_data:​Year] <br>4. PROJECT[col:​car_makers:​Maker, #3] <br> | 1. SELECT[tbl:​car_makers] <br>2. COMPARATIVE[#1, #1, comparative:​=:​1970:​col:​cars_data:​Year] <br>3. PROJECT[col:​car_makers:​Maker, #2] <br> | + | extra | 
  | SPIDER_dev_101 | Find the make and production time of the cars that were produced in the earliest year? | SELECT T2.Make ,  T1.Year FROM CARS_DATA AS T1 JOIN CAR_NAMES AS T2 ON T1.Id  =  T2.MakeId WHERE T1.Year  =  (SELECT min(YEAR) FROM CARS_DATA); | 1. SELECT[tbl:​car_names] <br>2. PROJECT[col:​cars_data:​Year, #1] <br>3. COMPARATIVE[#1, #2, comparative:​min:​None] <br>4. PROJECT[col:​car_names:​Make, #3] <br>5. PROJECT[col:​cars_data:​Year, #3] <br>6. UNION[#4, #5] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Year, #1] <br>3. SUPERLATIVE[comparative:​min:​None, #1, #2] <br>4. PROJECT[col:​car_names:​Make, #3] <br>5. PROJECT[col:​car_makers:​Maker, #3] <br>6. UNION[#4, #5] <br> | - | extra | 
  | SPIDER_dev_102 | What is the maker of the cars produced in the earliest year and what year was it? | SELECT T2.Make ,  T1.Year FROM CARS_DATA AS T1 JOIN CAR_NAMES AS T2 ON T1.Id  =  T2.MakeId WHERE T1.Year  =  (SELECT min(YEAR) FROM CARS_DATA); | 1. SELECT[col:​cars_data:​Year] <br>2. PROJECT[col:​cars_data:​Year, #1] <br>3. SUPERLATIVE[comparative:​min:​None, #1, #2] <br>4. PROJECT[col:​car_names:​Make, #3] <br>5. UNION[#4, #3] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Year, #1] <br>3. SUPERLATIVE[comparative:​min:​None, #1, #2] <br>4. PROJECT[col:​car_makers:​Maker, #3] <br>5. PROJECT[col:​cars_data:​Year, #3] <br>6. UNION[#4, #5] <br> | - | extra | 
  | SPIDER_dev_103 | Which distinct car models are the produced after 1980? | SELECT DISTINCT T1.model FROM MODEL_LIST AS T1 JOIN CAR_NAMES AS T2 ON T1.model  =  T2.model JOIN CARS_DATA AS T3 ON T2.MakeId  =  T3.id WHERE T3.year  >  1980; | 1. SELECT[col:​model_list:​Model] <br>2. FILTER[#1, comparative:​>:​1980:​col:​cars_data:​Year] <br>3.*(distinct)* PROJECT[distinct #REF, #2] <br> | 1. SELECT[col:​model_list:​Model] <br>2. COMPARATIVE[#1, #1, comparative:​>:​1980:​col:​cars_data:​Year] <br> | + | hard | 
  | SPIDER_dev_104 | What are the different models for the cars produced after 1980? | SELECT DISTINCT T1.model FROM MODEL_LIST AS T1 JOIN CAR_NAMES AS T2 ON T1.model  =  T2.model JOIN CARS_DATA AS T3 ON T2.MakeId  =  T3.id WHERE T3.year  >  1980; | 1. SELECT[tbl:​car_names] <br>2. FILTER[#1, comparative:​>:​1980:​col:​cars_data:​Year] <br>3.*(distinct)* PROJECT[col:​model_list:​Model, #2] <br> | 1. SELECT[tbl:​cars_data] <br>2. COMPARATIVE[#1, #1, comparative:​>:​1980:​col:​cars_data:​Year] <br>3.*(distinct)* PROJECT[col:​model_list:​Model, #2] <br> | + | hard | 
  | SPIDER_dev_105 | How many car makers are there in each continents? List the continent name and the count. | SELECT T1.Continent ,  count(*) FROM CONTINENTS AS T1 JOIN COUNTRIES AS T2 ON T1.ContId  =  T2.continent JOIN car_makers AS T3 ON T2.CountryId  =  T3.Country GROUP BY T1.Continent; | 1. SELECT[col:​continents:​Continent] <br>2. PROJECT[tbl:​car_makers, #1] <br>3. GROUP[count, #2, #1] <br>4. UNION[#1, #3] <br> | 1. SELECT[col:​continents:​Continent] <br>2. PROJECT[tbl:​car_makers, #1] <br>3. GROUP[count, #2, #1] <br>4. PROJECT[col:​continents:​Continent, #1] <br>5. UNION[#4, #3] <br> | + | hard | 
  | SPIDER_dev_106 | What is the name of each continent and how many car makers are there in each one? | SELECT T1.Continent ,  count(*) FROM CONTINENTS AS T1 JOIN COUNTRIES AS T2 ON T1.ContId  =  T2.continent JOIN car_makers AS T3 ON T2.CountryId  =  T3.Country GROUP BY T1.Continent; | 1. SELECT[col:​continents:​Continent] <br>2. PROJECT[col:​continents:​Continent, #1] <br>3. PROJECT[tbl:​car_makers, #1] <br>4. GROUP[count, #3, #1] <br>5. UNION[#2, #4] <br> | 1. SELECT[col:​countries:​Continent] <br>2. PROJECT[col:​continents:​Continent, #1] <br>3. PROJECT[tbl:​car_makers, #1] <br>4. GROUP[count, #3, #1] <br>5. UNION[#2, #4] <br> | + | hard | 
  | SPIDER_dev_107 | Which of the countries has the most car makers? List the country name. | SELECT T2.CountryName FROM CAR_MAKERS AS T1 JOIN COUNTRIES AS T2 ON T1.Country  =  T2.CountryId GROUP BY T1.Country ORDER BY Count(*) DESC LIMIT 1; | 1. SELECT[tbl:​countries] <br>2. PROJECT[tbl:​car_makers, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​countries:​CountryName, #4] <br> | 1. SELECT[tbl:​countries] <br>2. PROJECT[tbl:​car_makers, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​countries:​CountryName, #4] <br> | + | extra | 
  | SPIDER_dev_108 | What is the name of the country with the most car makers? | SELECT T2.CountryName FROM CAR_MAKERS AS T1 JOIN COUNTRIES AS T2 ON T1.Country  =  T2.CountryId GROUP BY T1.Country ORDER BY Count(*) DESC LIMIT 1; | 1. SELECT[tbl:​car_makers] <br>2. PROJECT[tbl:​countries, #1] <br>3. GROUP[count, #1, #2] <br>4. COMPARATIVE[#2, #3, comparative:​max:​None] <br>5. PROJECT[col:​countries:​CountryName, #4] <br> | 1. SELECT[tbl:​countries] <br>2. PROJECT[tbl:​car_makers, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​countries:​CountryName, #4] <br> | + | extra | 
  | SPIDER_dev_109 | How many car models are produced by each maker? Only list the count and the maker full name. | select count(*) ,  t2.fullname from model_list as t1 join car_makers as t2 on t1.maker  =  t2.id group by t2.id; | 1. SELECT[tbl:​car_makers] <br>2. PROJECT[tbl:​model_list, #1] <br>3. GROUP[count, #2, #1] <br>4. PROJECT[col:​car_makers:​FullName, #1] <br>5. UNION[#3, #4] <br> | 1. SELECT[col:​model_list:​Maker] <br>2. PROJECT[tbl:​car_makers, #1] <br>3. GROUP[count, #2, #1] <br>4. PROJECT[col:​car_makers:​FullName, #1] <br>5. UNION[#3, #4] <br> | + | medium | 
  | SPIDER_dev_110 | What is the number of car models that are produced by each maker and what is the id and full name of each maker? | SELECT Count(*) ,  T2.id ,  T2.FullName FROM MODEL_LIST AS T1 JOIN CAR_MAKERS AS T2 ON T1.Maker  =  T2.Id GROUP BY T2.id; | 1. SELECT[tbl:​car_makers] <br>2. PROJECT[tbl:​model_list, #1] <br>3. GROUP[count, #2, #1] <br>4. PROJECT[col:​car_makers:​Id, #1] <br>5. PROJECT[col:​car_makers:​FullName, #1] <br>6. UNION[#3, #4, #5] <br> | 1. SELECT[col:​car_makers:​Maker] <br>2. PROJECT[tbl:​car_makers, #1] <br>3. GROUP[count, #2, #1] <br>4. PROJECT[col:​car_makers:​Id, #1] <br>5. PROJECT[col:​car_makers:​FullName, #1] <br>6. UNION[#3, #4, #5] <br> | - | medium | 
  | SPIDER_dev_111 | What is the accelerate of the car make amc hornet sportabout (sw)? | SELECT T1.Accelerate FROM CARS_DATA AS T1 JOIN CAR_NAMES AS T2 ON T1.Id  =  T2.MakeId WHERE T2.Make  =  'amc hornet sportabout (sw)'; | 1. SELECT[tbl:​car_names] <br>2. FILTER[#1, comparative:​=:​amc hornet sportabout (sw):​col:​car_names:​Make] <br>3. PROJECT[col:​cars_data:​Accelerate, #2] <br> | 1. SELECT[val:​car_names:​Make:​amc hornet] <br>2. COMPARATIVE[#1, #1, comparative:​=:​amc hornet:​col:​car_names:​Make] <br>3. PROJECT[col:​cars_data:​Accelerate, #2] <br> | - | medium | 
  | SPIDER_dev_112 | How much does the car accelerate that makes amc hornet sportabout (sw)? | SELECT T1.Accelerate FROM CARS_DATA AS T1 JOIN CAR_NAMES AS T2 ON T1.Id  =  T2.MakeId WHERE T2.Make  =  'amc hornet sportabout (sw)'; | 1. SELECT[val:​car_names:​Make:​amc hornet sportabout (sw)] <br>2. PROJECT[col:​cars_data:​Accelerate, #1] <br> | 1. SELECT[tbl:​car_names] <br>2. COMPARATIVE[#1, #1, comparative:​=:​amc hornet:​col:​car_names:​Make] <br>3. COMPARATIVE[#2, #2, comparative:​=:​amc hornet sportabout (sw):​col:​car_names:​Make] <br>4. PROJECT[col:​cars_data:​Accelerate, #3] <br>5. AGGREGATE[count, #4] <br> | - | medium | 
  | SPIDER_dev_113 | How many car makers are there in france? | SELECT count(*) FROM CAR_MAKERS AS T1 JOIN COUNTRIES AS T2 ON T1.Country  =  T2.CountryId WHERE T2.CountryName  =  'france'; | 1. SELECT[tbl:​car_makers] <br>2. FILTER[#1, comparative:​=:​france:​col:​countries:​CountryName] <br>3. AGGREGATE[count, #2] <br> | 1. SELECT[tbl:​car_makers] <br>2. COMPARATIVE[#1, #1, comparative:​=:​france:​col:​countries:​CountryName] <br>3. AGGREGATE[count, #2] <br> | + | medium | 
  | SPIDER_dev_114 | What is the number of makers of cars in France? | SELECT count(*) FROM CAR_MAKERS AS T1 JOIN COUNTRIES AS T2 ON T1.Country  =  T2.CountryId WHERE T2.CountryName  =  'france'; | 1. SELECT[tbl:​car_makers] <br>2. PROJECT[tbl:​car_makers, #1] <br>3. FILTER[#2, comparative:​=:​france:​col:​countries:​CountryName] <br>4. AGGREGATE[count, #3] <br> | 1. SELECT[tbl:​car_makers] <br>2. COMPARATIVE[#1, #1, comparative:​=:​france:​col:​countries:​CountryName] <br>3. AGGREGATE[count, #2] <br> | + | medium | 
  | SPIDER_dev_115 | How many car models are produced in the usa? | SELECT count(*) FROM MODEL_LIST AS T1 JOIN CAR_MAKERS AS T2 ON T1.Maker  =  T2.Id JOIN COUNTRIES AS T3 ON T2.Country  =  T3.CountryId WHERE T3.CountryName  =  'usa'; | 1. SELECT[tbl:​model_list] <br>2. FILTER[#1, comparative:​=:​usa:​col:​countries:​CountryName] <br>3. AGGREGATE[count, #2] <br> | 1. SELECT[col:​model_list:​Model] <br>2. COMPARATIVE[#1, #1, comparative:​=:​usa:​col:​countries:​CountryName] <br>3. AGGREGATE[count, #2] <br> | + | hard | 
  | SPIDER_dev_116 | What is the count of the car models produced in the United States? | SELECT count(*) FROM MODEL_LIST AS T1 JOIN CAR_MAKERS AS T2 ON T1.Maker  =  T2.Id JOIN COUNTRIES AS T3 ON T2.Country  =  T3.CountryId WHERE T3.CountryName  =  'usa'; | 1. SELECT[tbl:​model_list] <br>2. FILTER[#1, comparative:​=:​usa:​col:​countries:​CountryName] <br>3. AGGREGATE[count, #2] <br> | 1. SELECT[col:​model_list:​Model] <br>2. COMPARATIVE[#1, #1, tbl:​model_list] <br>3. AGGREGATE[count, #2] <br> | - | hard | 
  | SPIDER_dev_117 | What is the average miles per gallon(mpg) of the cars with 4 cylinders? | SELECT avg(mpg) FROM CARS_DATA WHERE Cylinders  =  4; | 1. SELECT[tbl:​cars_data] <br>2. SELECT[col:​cars_data:​Cylinders] <br>3. GROUP[col:​cars_data:​Cylinders, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​=:​4:​col:​cars_data:​Cylinders] <br>5. PROJECT[col:​cars_data:​MPG, #4] <br>6. AGGREGATE[avg, #5] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Cylinders, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​4:​col:​cars_data:​Cylinders] <br>4. PROJECT[col:​cars_data:​MPG, #3] <br>5. AGGREGATE[avg, #4] <br> | + | easy | 
  | SPIDER_dev_118 | What is the average miles per gallon of all the cars with 4 cylinders? | SELECT avg(mpg) FROM CARS_DATA WHERE Cylinders  =  4; | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Cylinders, #1] <br>3. GROUP[col:​cars_data:​Cylinders, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​=:​4:​col:​cars_data:​Cylinders] <br>5. PROJECT[col:​cars_data:​MPG, #4] <br>6. AGGREGATE[avg, #5] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Cylinders, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​4:​col:​cars_data:​Cylinders] <br>4. PROJECT[col:​cars_data:​MPG, #3] <br>5. AGGREGATE[avg, #4] <br> | + | easy | 
  | SPIDER_dev_119 | What is the smallest weight of the car produced with 8 cylinders on 1974? | select min(weight) from cars_data where cylinders  =  8 and year  =  1974 | 1. SELECT[tbl:​cars_data] <br>2. SELECT[col:​cars_data:​Cylinders] <br>3. GROUP[col:​cars_data:​Cylinders, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​=:​8:​col:​cars_data:​Cylinders] <br>5. FILTER[#4, comparative:​=:​1974:​col:​cars_data:​Year] <br>6. PROJECT[col:​cars_data:​Weight, #5] <br>7. AGGREGATE[min, #6] <br> | 1. SELECT[tbl:​cars_data] <br>2. COMPARATIVE[#1, #1, comparative:​=:​1974:​col:​cars_data:​Year] <br>3. PROJECT[col:​cars_data:​Cylinders, #2] <br>4. COMPARATIVE[#2, #3, comparative:​=:​8:​col:​cars_data:​Cylinders] <br>5. PROJECT[col:​cars_data:​Weight, #4] <br>6. AGGREGATE[min, #5] <br> | + | medium | 
  | SPIDER_dev_120 | What is the minimum weight of the car with 8 cylinders produced in 1974? | select min(weight) from cars_data where cylinders  =  8 and year  =  1974 | 1. SELECT[tbl:​cars_data] <br>2. SELECT[col:​cars_data:​Cylinders] <br>3. GROUP[col:​cars_data:​Cylinders, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​=:​8:​col:​cars_data:​Cylinders] <br>5. FILTER[#4, comparative:​=:​1974:​col:​cars_data:​Year] <br>6. PROJECT[col:​cars_data:​Weight, #5] <br>7. AGGREGATE[min, #6] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Cylinders, #1] <br>3. COMPARATIVE[#2, #2, comparative:​=:​1974:​col:​cars_data:​Year] <br>4. PROJECT[col:​cars_data:​Cylinders, #2] <br>5. GROUP[sum, #4, #1] <br>6. COMPARATIVE[#1, #5, comparative:​=:​8:​col:​cars_data:​Cylinders] <br>7. INTERSECTION[#1, #3, #6] <br>8. PROJECT[col:​cars_data:​Weight, #7] <br> | - | medium | 
  | SPIDER_dev_121 | What are all the makers and models? | SELECT Maker ,  Model FROM MODEL_LIST; | 1. SELECT[col:​model_list:​Model] <br>2. PROJECT[col:​model_list:​Maker, #1] <br>3. UNION[#2, #1] <br> | 1. SELECT[col:​model_list:​Maker] <br>2. PROJECT[col:​model_list:​Model, #1] <br>3. UNION[#1, #2] <br> | + | medium | 
  | SPIDER_dev_122 | What are the makers and models? | SELECT Maker ,  Model FROM MODEL_LIST; | 1. SELECT[col:​model_list:​Maker] <br>2. SELECT[col:​model_list:​Model] <br>3. UNION[#1, #2] <br> | 1. SELECT[col:​model_list:​Maker] <br>2. PROJECT[col:​model_list:​Model, #1] <br>3. UNION[#1, #2] <br> | + | medium | 
  | SPIDER_dev_123 | What are the countries having at least one car maker? List name and id. | SELECT T1.CountryName ,  T1.CountryId FROM COUNTRIES AS T1 JOIN CAR_MAKERS AS T2 ON T1.CountryId  =  T2.Country GROUP BY T1.CountryId HAVING count(*)  >=  1; | 1. SELECT[tbl:​countries] <br>2. PROJECT[tbl:​car_makers, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>=:​1] <br>5. PROJECT[col:​countries:​CountryName, #4] <br>6. PROJECT[col:​countries:​CountryId, #4] <br>7. UNION[#5, #6] <br> | 1. SELECT[tbl:​countries] <br>2. PROJECT[tbl:​car_makers, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>=:​1] <br>5. PROJECT[col:​countries:​CountryName, #4] <br>6. PROJECT[col:​countries:​CountryId, #4] <br>7. UNION[#5, #6] <br> | + | medium | 
  | SPIDER_dev_124 | What are the names and ids of all countries with at least one car maker? | SELECT T1.CountryName ,  T1.CountryId FROM COUNTRIES AS T1 JOIN CAR_MAKERS AS T2 ON T1.CountryId  =  T2.Country GROUP BY T1.CountryId HAVING count(*)  >=  1; | 1. SELECT[tbl:​countries] <br>2. FILTER[#1, tbl:​car_makers] <br>3. PROJECT[col:​countries:​CountryName, #2] <br>4. PROJECT[col:​countries:​CountryId, #2] <br>5. UNION[#3, #4] <br> | 1. SELECT[tbl:​countries] <br>2. PROJECT[tbl:​car_makers, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>=:​1] <br>5. PROJECT[col:​countries:​CountryName, #4] <br>6. PROJECT[col:​countries:​CountryId, #4] <br>7. UNION[#5, #6] <br> | + | medium | 
  | SPIDER_dev_125 | What is the number of the cars with horsepower more than 150? | SELECT count(*) FROM CARS_DATA WHERE horsepower  >  150; | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Horsepower, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​150:​col:​cars_data:​Horsepower] <br>4. AGGREGATE[count, #3] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Horsepower, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​150:​col:​cars_data:​Horsepower] <br>4. AGGREGATE[count, #3] <br> | + | easy | 
  | SPIDER_dev_126 | What is the number of cars with a horsepower greater than 150? | SELECT count(*) FROM CARS_DATA WHERE horsepower  >  150; | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Horsepower, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​150:​col:​cars_data:​Horsepower] <br>4. AGGREGATE[count, #3] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Horsepower, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​150:​col:​cars_data:​Horsepower] <br>4. AGGREGATE[count, #3] <br> | + | easy | 
  | SPIDER_dev_127 | What is the average weight of cars each year? | SELECT YEAR ,  avg(Weight) FROM CARS_DATA GROUP BY YEAR; | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Year, #1] <br>3. PROJECT[col:​cars_data:​Weight, #1] <br>4. GROUP[avg, #3, #2] <br>5. UNION[#2, #4] <br> | 1. SELECT[col:​cars_data:​Year] <br>2. PROJECT[col:​cars_data:​Weight, #1] <br>3. AGGREGATE[avg, #2] <br> | - | medium | 
  | SPIDER_dev_128 | What is the average weight and year for each year? | SELECT YEAR ,  avg(Weight) FROM CARS_DATA GROUP BY YEAR; | 1. SELECT[col:​cars_data:​Year] <br>2. PROJECT[col:​cars_data:​Weight, #1] <br>3. GROUP[avg, #2, #1] <br>4. UNION[#1, #3] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Weight, #1] <br>3. PROJECT[col:​cars_data:​Year, #1] <br>4. AGGREGATE[avg, #2] <br>5. UNION[#3, #4] <br> | - | medium | 
  | SPIDER_dev_129 | Which countries in europe have at least 3 car manufacturers? | SELECT T1.CountryName FROM COUNTRIES AS T1 JOIN CONTINENTS AS T2 ON T1.Continent  =  T2.ContId JOIN CAR_MAKERS AS T3 ON T1.CountryId  =  T3.Country WHERE T2.Continent  =  'europe' GROUP BY T1.CountryName HAVING count(*)  >=  3; | 1. SELECT[col:​countries:​CountryName] <br>2. FILTER[#1, comparative:​=:​europe:​col:​continents:​Continent] <br>3. PROJECT[tbl:​car_makers, #2] <br>4. GROUP[count, #3, #2] <br>5. COMPARATIVE[#2, #4, comparative:​>=:​3] <br> | 1. SELECT[col:​countries:​CountryName] <br>2. COMPARATIVE[#1, #1, comparative:​=:​europe:​col:​continents:​Continent] <br>3. PROJECT[tbl:​car_makers, #2] <br>4. GROUP[count, #3, #2] <br>5. COMPARATIVE[#2, #4, comparative:​>=:​3] <br> | + | extra | 
  | SPIDER_dev_133 | Which model saves the most gasoline? That is to say, have the maximum miles per gallon. | SELECT T1.Model FROM CAR_NAMES AS T1 JOIN CARS_DATA AS T2 ON T1.MakeId  =  T2.Id ORDER BY T2.mpg DESC LIMIT 1; | 1. SELECT[col:​car_names:​Model] <br>2. PROJECT[col:​cars_data:​MPG, #1] <br>3. COMPARATIVE[#1, #2, comparative:​max:​None] <br> | 1. SELECT[col:​model_list:​Model] <br>2. PROJECT[col:​cars_data:​MPG, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br> | + | hard | 
  | SPIDER_dev_134 | What is the car model with the highest mpg? | select t1.model from car_names as t1 join cars_data as t2 on t1.makeid  =  t2.id order by t2.mpg desc limit 1; | 1. SELECT[col:​car_names:​Model] <br>2. PROJECT[col:​cars_data:​MPG, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br> | 1. SELECT[col:​model_list:​Model] <br>2. PROJECT[col:​cars_data:​MPG, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br> | + | hard | 
  | SPIDER_dev_135 | What is the average horsepower of the cars before 1980? | SELECT avg(horsepower) FROM CARS_DATA WHERE YEAR  <  1980; | 1. SELECT[tbl:​cars_data] <br>2. FILTER[#1, comparative:​<:​1980:​col:​cars_data:​Year] <br>3. PROJECT[col:​cars_data:​Horsepower, #2] <br>4. AGGREGATE[avg, #3] <br> | 1. SELECT[tbl:​cars_data] <br>2. COMPARATIVE[#1, #1, comparative:​<:​1980:​col:​cars_data:​Year] <br>3. PROJECT[col:​cars_data:​Horsepower, #2] <br>4. AGGREGATE[avg, #3] <br> | + | easy | 
  | SPIDER_dev_136 | What is the average horsepower for all cars produced before 1980? | select avg(horsepower) from cars_data where year  <  1980; | 1. SELECT[tbl:​cars_data] <br>2. FILTER[#1, comparative:​<:​1980:​col:​cars_data:​Year] <br>3. PROJECT[col:​cars_data:​Horsepower, #2] <br>4. AGGREGATE[avg, #3] <br> | 1. SELECT[tbl:​cars_data] <br>2. COMPARATIVE[#1, #1, comparative:​<:​1980:​col:​cars_data:​Year] <br>3. PROJECT[col:​cars_data:​Horsepower, #2] <br>4. AGGREGATE[avg, #3] <br> | + | easy | 
  | SPIDER_dev_137 | What is the average edispl of the cars of model volvo? | SELECT avg(T2.edispl) FROM CAR_NAMES AS T1 JOIN CARS_DATA AS T2 ON T1.MakeId  =  T2.Id WHERE T1.Model  =  'volvo'; | 1. SELECT[tbl:​car_names] <br>2. FILTER[#1, comparative:​=:​volvo:​col:​car_names:​Model] <br>3. PROJECT[col:​cars_data:​Edispl, #2] <br>4. AGGREGATE[avg, #3] <br> | 1. SELECT[val:​car_names:​Model:​volvo] <br>2. PROJECT[tbl:​cars_data, #1] <br>3. PROJECT[col:​cars_data:​Edispl, #2] <br>4. AGGREGATE[avg, #3] <br> | + | medium | 
  | SPIDER_dev_138 | What is the average edispl for all volvos? | SELECT avg(T2.edispl) FROM CAR_NAMES AS T1 JOIN CARS_DATA AS T2 ON T1.MakeId  =  T2.Id WHERE T1.Model  =  'volvo'; | 1. SELECT[val:​car_names:​Model:​volvo] <br>2. PROJECT[col:​cars_data:​Edispl, #1] <br>3. AGGREGATE[avg, #2] <br> | 1. SELECT[val:​car_names:​Model:​volvo] <br>2. PROJECT[col:​cars_data:​Edispl, #1] <br>3. AGGREGATE[avg, #2] <br> | + | medium | 
  | SPIDER_dev_139 | What is the maximum accelerate for different number of cylinders? | SELECT Cylinders ,  max(Accelerate) FROM CARS_DATA GROUP BY Cylinders; | 1.*(distinct)* SELECT[col:​cars_data:​Cylinders] <br>2. PROJECT[col:​cars_data:​Accelerate, #1] <br>3. GROUP[max, #2, #1] <br>4. UNION[#1, #3] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Accelerate, #1] <br>3. GROUP[count, #2, #1] <br>4. AGGREGATE[max, #3] <br>5. UNION[#4, #4] <br> | - | medium | 
  | SPIDER_dev_140 | What is the maximum accelerate for all the different cylinders? | SELECT Cylinders ,  max(Accelerate) FROM CARS_DATA GROUP BY Cylinders; | 1.*(distinct)* SELECT[col:​cars_data:​Cylinders] <br>2. PROJECT[col:​cars_data:​Accelerate, #1] <br>3. GROUP[max, #2, #1] <br>4. UNION[#1, #3] <br> | 1. SELECT[tbl:​cars_data] <br>2.*(distinct)* PROJECT[None, #1] <br>3. AGGREGATE[max, #2] <br> | - | medium | 
  | SPIDER_dev_141 | Which model has the most version(make) of cars? | SELECT Model FROM CAR_NAMES GROUP BY Model ORDER BY count(*) DESC LIMIT 1; | 1. SELECT[tbl:​car_names] <br>2. SELECT[col:​car_names:​Model] <br>3. PROJECT[version of #REF, #1] <br>4. GROUP[count, #3, #2] <br>5. SUPERLATIVE[comparative:​max:​None, #2, #4] <br> | 1. SELECT[col:​car_names:​Model] <br>2. PROJECT[tbl:​car_names, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br> | + | hard | 
  | SPIDER_dev_142 | What model has the most different versions? | SELECT Model FROM CAR_NAMES GROUP BY Model ORDER BY count(*) DESC LIMIT 1; | 1. SELECT[col:​car_names:​Model] <br>2. PROJECT[different versions of #REF, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br> | 1. SELECT[col:​model_list:​Model] <br>2. PROJECT[None, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br> | + | hard | 
  | SPIDER_dev_143 | How many cars have more than 4 cylinders? | SELECT count(*) FROM CARS_DATA WHERE Cylinders  >  4; | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Cylinders, #1] <br>3. GROUP[col:​cars_data:​Cylinders, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​4:​col:​cars_data:​Cylinders] <br>5. AGGREGATE[count, #4] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Cylinders, #1] <br>3. GROUP[sum, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​4:​col:​cars_data:​Cylinders] <br>5. AGGREGATE[count, #4] <br> | + | easy | 
  | SPIDER_dev_144 | What is the number of cars with more than 4 cylinders? | SELECT count(*) FROM CARS_DATA WHERE Cylinders  >  4; | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Cylinders, #1] <br>3. GROUP[col:​cars_data:​Cylinders, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​4:​col:​cars_data:​Cylinders] <br>5. AGGREGATE[count, #4] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Cylinders, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​4:​col:​cars_data:​Cylinders] <br>4. AGGREGATE[count, #3] <br> | + | easy | 
  | SPIDER_dev_145 | how many cars were produced in 1980? | SELECT count(*) FROM CARS_DATA WHERE YEAR  =  1980; | 1. SELECT[tbl:​cars_data] <br>2. FILTER[#1, comparative:​=:​1980:​col:​cars_data:​Year] <br>3. AGGREGATE[count, #2] <br> | 1. SELECT[tbl:​cars_data] <br>2. COMPARATIVE[#1, #1, comparative:​=:​1980:​col:​cars_data:​Year] <br>3. AGGREGATE[count, #2] <br> | + | easy | 
  | SPIDER_dev_146 | In 1980, how many cars were made? | SELECT count(*) FROM CARS_DATA WHERE YEAR  =  1980; | 1. SELECT[tbl:​cars_data] <br>2. FILTER[#1, comparative:​=:​1980:​col:​cars_data:​Year] <br>3. AGGREGATE[count, #2] <br> | 1. SELECT[val:​cars_data:​Year:​1980] <br>2. PROJECT[tbl:​car_names, #1] <br>3. GROUP[count, #2, #1] <br>4. UNION[#1, #3] <br> | - | easy | 
  | SPIDER_dev_147 | How many car models were produced by the maker with full name American Motor Company? | SELECT count(*) FROM CAR_MAKERS AS T1 JOIN MODEL_LIST AS T2 ON T1.Id  =  T2.Maker WHERE T1.FullName  =  'American Motor Company'; | 1. SELECT[tbl:​car_makers] <br>2. PROJECT[col:​car_makers:​FullName, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​American Motor Company:​col:​car_makers:​FullName] <br>4. PROJECT[tbl:​model_list, #3] <br>5. AGGREGATE[count, #4] <br> | 1. SELECT[tbl:​car_makers] <br>2. PROJECT[tbl:​car_makers, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​American Motor Company:​col:​car_makers:​FullName] <br>4. PROJECT[tbl:​car_makers, #3] <br>5. AGGREGATE[count, #4] <br> | + | medium | 
  | SPIDER_dev_148 | What is the number of car models created by the car maker American Motor Company? | SELECT count(*) FROM CAR_MAKERS AS T1 JOIN MODEL_LIST AS T2 ON T1.Id  =  T2.Maker WHERE T1.FullName  =  'American Motor Company'; | 1. SELECT[tbl:​model_list] <br>2. FILTER[#1, comparative:​=:​American Motor Company:​col:​car_makers:​FullName] <br>3. AGGREGATE[count, #2] <br> | 1. SELECT[val:​car_makers:​FullName:​American Motor Company] <br>2. COMPARATIVE[#1, #1, comparative:​=:​American Motor Company:​col:​car_makers:​FullName] <br>3. PROJECT[col:​model_list:​Model, #2] <br>4. AGGREGATE[count, #3] <br> | + | medium | 
  | SPIDER_dev_149 | Which makers designed more than 3 car models? List full name and the id. | SELECT T1.FullName ,  T1.Id FROM CAR_MAKERS AS T1 JOIN MODEL_LIST AS T2 ON T1.Id  =  T2.Maker GROUP BY T1.Id HAVING count(*)  >  3; | 1. SELECT[tbl:​car_makers] <br>2. PROJECT[tbl:​model_list, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​3] <br>5. PROJECT[col:​car_makers:​FullName, #4] <br>6. PROJECT[col:​car_makers:​Id, #4] <br>7. UNION[#5, #6] <br> | 1. SELECT[col:​car_makers:​Maker] <br>2. PROJECT[tbl:​model_list, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​3] <br>5. PROJECT[col:​car_makers:​FullName, #4] <br>6. PROJECT[col:​car_makers:​Id, #4] <br>7. UNION[#5, #6] <br> | + | medium | 
  | SPIDER_dev_150 | What are the names and ids of all makers with more than 3 models? | SELECT T1.FullName ,  T1.Id FROM CAR_MAKERS AS T1 JOIN MODEL_LIST AS T2 ON T1.Id  =  T2.Maker GROUP BY T1.Id HAVING count(*)  >  3; | 1. SELECT[tbl:​car_makers] <br>2. PROJECT[tbl:​model_list, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​3] <br>5. PROJECT[col:​car_makers:​FullName, #4] <br>6. PROJECT[col:​car_makers:​Id, #4] <br>7. UNION[#5, #6] <br> | 1. SELECT[col:​model_list:​Maker] <br>2. PROJECT[tbl:​model_list, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​3] <br>5. PROJECT[col:​car_makers:​Maker, #4] <br>6. PROJECT[col:​car_makers:​Id, #4] <br>7. UNION[#5, #6] <br> | - | medium | 
  | SPIDER_dev_153 | In which years cars were produced weighing no less than 3000 and no more than 4000? | select distinct year from cars_data where weight between 3000 and 4000; | 1. SELECT[col:​cars_data:​Year] <br>2. SELECT[tbl:​cars_data] <br>3. COMPARATIVE[#1, #2, comparative:​<=:​4000:​col:​cars_data:​Weight] <br>4. COMPARATIVE[#3, #2, comparative:​>=:​3000:​col:​cars_data:​Weight] <br>5. PROJECT[col:​cars_data:​Year, #4] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Weight, #1] <br>3. COMPARATIVE[#1, #2, comparative:​<:​3000:​col:​cars_data:​Weight] <br>4. COMPARATIVE[#1, #2, comparative:​>:​4000:​col:​cars_data:​Weight] <br>5. PROJECT[col:​cars_data:​Year, #1] <br>6. INTERSECTION[#5, #3, #4] <br> | - | easy | 
  | SPIDER_dev_154 | What are the different years in which there were cars produced that weighed less than 4000 and also cars that weighted more than 3000? | select distinct year from cars_data where weight between 3000 and 4000; | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Weight, #1] <br>3. COMPARATIVE[#1, #2, comparative:​<=:​4000:​col:​cars_data:​Weight] <br>4. COMPARATIVE[#1, #2, comparative:​>=:​3000:​col:​cars_data:​Weight] <br>5.*(distinct)* PROJECT[col:​cars_data:​Year, #1] <br>6. INTERSECTION[#5, #3, #4] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[tbl:​cars_data, #1] <br>3. GROUP[sum, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​<:​4000:​col:​cars_data:​Weight] <br>5. COMPARATIVE[#1, #3, comparative:​>:​3000:​col:​cars_data:​Weight] <br>6.*(distinct)* PROJECT[col:​cars_data:​Year, #1] <br>7. INTERSECTION[#6, #4, #5] <br> | + | easy | 
  | SPIDER_dev_155 | What is the horsepower of the car with the largest accelerate? | SELECT T1.horsepower FROM CARS_DATA AS T1 ORDER BY T1.accelerate DESC LIMIT 1; | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Accelerate, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. PROJECT[col:​cars_data:​Horsepower, #3] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Accelerate, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. PROJECT[col:​cars_data:​Horsepower, #3] <br> | + | medium | 
  | SPIDER_dev_156 | What is the horsepower of the car with the greatest accelerate? | SELECT T1.horsepower FROM CARS_DATA AS T1 ORDER BY T1.accelerate DESC LIMIT 1; | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Accelerate, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. PROJECT[col:​cars_data:​Horsepower, #3] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Accelerate, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. PROJECT[col:​cars_data:​Horsepower, #3] <br> | + | medium | 
  | SPIDER_dev_157 | For model volvo, how many cylinders does the car with the least accelerate have? | SELECT T1.cylinders FROM CARS_DATA AS T1 JOIN CAR_NAMES AS T2 ON T1.Id  =  T2.MakeId WHERE T2.Model  =  'volvo' ORDER BY T1.accelerate ASC LIMIT 1; | 1. SELECT[tbl:​car_names] <br>2. PROJECT[col:​car_names:​Model, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​volvo:​col:​car_names:​Model] <br>4. PROJECT[col:​cars_data:​Accelerate, #3] <br>5. SUPERLATIVE[comparative:​min:​None, #3, #4] <br>6. PROJECT[col:​cars_data:​Cylinders, #5] <br>7. AGGREGATE[col:​cars_data:​Cylinders, #6] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Accelerate, #1] <br>3. SUPERLATIVE[comparative:​min:​None, #1, #2] <br>4. PROJECT[col:​cars_data:​Cylinders, #3] <br>5. GROUP[count, #4, #3] <br>6. UNION[#3, #5] <br> | - | extra | 
  | SPIDER_dev_158 | For a volvo model, how many cylinders does the version with least accelerate have? | SELECT T1.cylinders FROM CARS_DATA AS T1 JOIN CAR_NAMES AS T2 ON T1.Id  =  T2.MakeId WHERE T2.Model  =  'volvo' ORDER BY T1.accelerate ASC LIMIT 1; | 1. SELECT[val:​car_names:​Model:​volvo] <br>2. PROJECT[versions of #REF, #1] <br>3. PROJECT[col:​cars_data:​Accelerate, #2] <br>4. SUPERLATIVE[comparative:​min:​None, #2, #3] <br>5. PROJECT[col:​cars_data:​Cylinders, #4] <br>6. AGGREGATE[col:​cars_data:​Cylinders, #5] <br> | 1. SELECT[col:​model_list:​Model] <br>2. PROJECT[col:​cars_data:​Accelerate, #1] <br>3. SUPERLATIVE[comparative:​min:​None, #1, #2] <br>4. PROJECT[col:​cars_data:​Cylinders, #3] <br>5. GROUP[count, #4, #3] <br>6. UNION[#3, #5] <br> | - | extra | 
  | SPIDER_dev_159 | How many cars have a larger accelerate than the car with the largest horsepower? | SELECT COUNT(*) FROM CARS_DATA WHERE Accelerate  >  ( SELECT Accelerate FROM CARS_DATA ORDER BY Horsepower DESC LIMIT 1 ); | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Horsepower, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. PROJECT[col:​cars_data:​Accelerate, #1] <br>5. PROJECT[col:​cars_data:​Accelerate, #3] <br>6. COMPARATIVE[#1, #4, comparative:​>:​#5] <br>7. AGGREGATE[count, #6] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Horsepower, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. AGGREGATE[max, #2] <br>5. UNION[#3, #4] <br> | - | hard | 
  | SPIDER_dev_160 | What is the number of cars with a greater accelerate than the one with the most horsepower? | SELECT COUNT(*) FROM CARS_DATA WHERE Accelerate  >  ( SELECT Accelerate FROM CARS_DATA ORDER BY Horsepower DESC LIMIT 1 ); | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Horsepower, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. PROJECT[col:​cars_data:​Accelerate, #1] <br>5. PROJECT[col:​cars_data:​Accelerate, #3] <br>6. COMPARATIVE[#1, #4, comparative:​>:​#5] <br>7. AGGREGATE[count, #6] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Horsepower, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. AGGREGATE[max, #3] <br>5. PROJECT[col:​cars_data:​Accelerate, #3] <br>6. UNION[#4, #5] <br> | - | hard | 
  | SPIDER_dev_163 | How many cars has over 6 cylinders? | SELECT COUNT(*) FROM CARS_DATA WHERE Cylinders  >  6; | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Cylinders, #1] <br>3. GROUP[col:​cars_data:​Cylinders, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​6:​col:​cars_data:​Cylinders] <br>5. AGGREGATE[count, #4] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Cylinders, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​6:​col:​cars_data:​Cylinders] <br>4. AGGREGATE[count, #3] <br> | + | easy | 
  | SPIDER_dev_164 | What is the number of cars with over 6 cylinders? | SELECT COUNT(*) FROM CARS_DATA WHERE Cylinders  >  6; | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Cylinders, #1] <br>3. GROUP[col:​cars_data:​Cylinders, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​6:​col:​cars_data:​Cylinders] <br>5. AGGREGATE[count, #4] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Cylinders, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​6:​col:​cars_data:​Cylinders] <br>4. AGGREGATE[count, #3] <br> | + | easy | 
  | SPIDER_dev_165 | For the cars with 4 cylinders, which model has the largest horsepower? | SELECT T1.Model FROM CAR_NAMES AS T1 JOIN CARS_DATA AS T2 ON T1.MakeId  =  T2.Id WHERE T2.Cylinders  =  4 ORDER BY T2.horsepower DESC LIMIT 1; | 1. SELECT[tbl:​car_names] <br>2. PROJECT[col:​cars_data:​Cylinders, #1] <br>3. GROUP[col:​cars_data:​Cylinders, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​=:​4:​col:​cars_data:​Cylinders] <br>5. PROJECT[col:​car_names:​Model, #4] <br>6. PROJECT[col:​cars_data:​Horsepower, #5] <br>7. SUPERLATIVE[comparative:​max:​None, #5, #6] <br> | 1. SELECT[col:​model_list:​Model] <br>2. PROJECT[col:​cars_data:​Cylinders, #1] <br>3. COMPARATIVE[#2, #2, comparative:​=:​4:​col:​cars_data:​Cylinders] <br>4. PROJECT[col:​cars_data:​Horsepower, #3] <br>5. SUPERLATIVE[comparative:​max:​None, #1, #4] <br> | + | extra | 
  | SPIDER_dev_166 | For all of the 4 cylinder cars, which model has the most horsepower? | SELECT T1.Model FROM CAR_NAMES AS T1 JOIN CARS_DATA AS T2 ON T1.MakeId  =  T2.Id WHERE T2.Cylinders  =  4 ORDER BY T2.horsepower DESC LIMIT 1; | 1. SELECT[tbl:​car_names] <br>2. PROJECT[col:​cars_data:​Cylinders, #1] <br>3. GROUP[col:​cars_data:​Cylinders, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​=:​4:​col:​cars_data:​Cylinders] <br>5. PROJECT[col:​cars_data:​Horsepower, #4] <br>6. PROJECT[col:​car_names:​Model, #4] <br>7. SUPERLATIVE[comparative:​max:​None, #6, #5] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Horsepower, #1] <br>3. COMPARATIVE[#2, #2, comparative:​=:​4:​col:​cars_data:​Cylinders] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br> | - | extra | 
  | SPIDER_dev_167 | Among the cars with more than lowest horsepower, which ones do not have more than 3 cylinders? List the car makeid and make name. | SELECT T2.MakeId ,  T2.Make FROM CARS_DATA AS T1 JOIN CAR_NAMES AS T2 ON T1.Id  =  T2.MakeId WHERE T1.Horsepower  >  (SELECT min(Horsepower) FROM CARS_DATA) AND T1.Cylinders  <=  3; | 1. SELECT[tbl:​car_names] <br>2. PROJECT[col:​cars_data:​Horsepower, #1] <br>3. SUPERLATIVE[comparative:​min:​None, #1, #2] <br>4. DISCARD[#1, #3] <br>5. PROJECT[col:​cars_data:​Cylinders, #4] <br>6. GROUP[col:​cars_data:​Cylinders, #5, #4] <br>7. COMPARATIVE[#4, #6, comparative:​<=:​3:​col:​cars_data:​Cylinders] <br>8. PROJECT[col:​car_names:​MakeId, #7] <br>9. PROJECT[col:​car_names:​Make, #7] <br>10. UNION[#8, #9] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Cylinders, #1] <br>3. COMPARATIVE[#2, #2, comparative:​>:​3:​col:​cars_data:​Cylinders] <br>4. SUPERLATIVE[comparative:​min:​None, #1, #3] <br>5. PROJECT[col:​cars_data:​Horsepower, #4] <br>6. PROJECT[col:​car_names:​Make, #4] <br>7. UNION[#5, #6] <br> | - | extra | 
  | SPIDER_dev_168 | Among the cars that do not have the minimum horsepower, what are the make ids and names of all those with less than 4 cylinders? | select t2.makeid ,  t2.make from cars_data as t1 join car_names as t2 on t1.id  =  t2.makeid where t1.horsepower  >  (select min(horsepower) from cars_data) and t1.cylinders  <  4; | 1. SELECT[tbl:​car_names] <br>2. PROJECT[col:​cars_data:​Horsepower, #1] <br>3. AGGREGATE[min, #2] <br>4. COMPARATIVE[#1, #2, comparative:​>:​#3] <br>5. PROJECT[col:​cars_data:​Cylinders, #4] <br>6. COMPARATIVE[#4, #5, comparative:​<:​4:​col:​cars_data:​Cylinders] <br>7. PROJECT[col:​car_names:​MakeId, #6] <br>8. PROJECT[col:​car_names:​Make, #6] <br>9. UNION[#7, #8] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Cylinders, #1] <br>3. GROUP[sum, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​<:​4:​col:​cars_data:​Cylinders] <br>5. PROJECT[col:​car_names:​MakeId, #4] <br>6. PROJECT[col:​car_names:​Make, #4] <br>7. UNION[#5, #6] <br> | + | extra | 
  | SPIDER_dev_169 | What is the maximum miles per gallon of the car with 8 cylinders or produced before 1980? | select max(mpg) from cars_data where cylinders  =  8 or year  <  1980 | 1. SELECT[tbl:​cars_data] <br>2. FILTER[#1, comparative:​=:​8:​col:​cars_data:​Cylinders] <br>3. FILTER[#1, comparative:​<:​1980:​col:​cars_data:​Year] <br>4. UNION[#2, #3] <br>5. PROJECT[col:​cars_data:​MPG, #4] <br>6. AGGREGATE[max, #5] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Cylinders, #1] <br>3. COMPARATIVE[#1, #2, comparative:​<:​1980:​col:​cars_data:​Year] <br>4. PROJECT[col:​cars_data:​Cylinders, #1] <br>5. COMPARATIVE[#1, #4, comparative:​<:​8:​col:​cars_data:​Cylinders] <br>6. UNION[#3, #5] <br>7. PROJECT[col:​cars_data:​MPG, #6] <br>8. AGGREGATE[max, #7] <br> | - | medium | 
  | SPIDER_dev_170 | What is the maximum mpg of the cars that had 8 cylinders or that were produced before 1980? | select max(mpg) from cars_data where cylinders  =  8 or year  <  1980 | 1. SELECT[tbl:​cars_data] <br>2. FILTER[#1, comparative:​=:​8:​col:​cars_data:​Cylinders] <br>3. FILTER[#1, comparative:​<:​1980:​col:​cars_data:​Year] <br>4. UNION[#2, #3] <br>5. PROJECT[col:​cars_data:​MPG, #4] <br>6. AGGREGATE[max, #5] <br> | 1. SELECT[tbl:​cars_data] <br>2. PROJECT[col:​cars_data:​Cylinders, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​8:​col:​cars_data:​Cylinders] <br>4. COMPARATIVE[#1, #1, comparative:​<:​1980:​col:​cars_data:​Year] <br>5. UNION[#4, #3] <br>6. PROJECT[col:​cars_data:​MPG, #5] <br>7. AGGREGATE[max, #6] <br> | + | medium | 
  | SPIDER_dev_171 | Which models are lighter than 3500 but not built by the 'Ford Motor Company'? | SELECT DISTINCT T1.model FROM MODEL_LIST AS T1 JOIN CAR_NAMES AS T2 ON T1.Model  =  T2.Model JOIN CARS_DATA AS T3 ON T2.MakeId  =  T3.Id JOIN CAR_MAKERS AS T4 ON T1.Maker  =  T4.Id WHERE T3.weight  <  3500 AND T4.FullName != 'Ford Motor Company'; | 1. SELECT[col:​model_list:​Model] <br>2. FILTER[#1, comparative:​<:​3500:​col:​cars_data:​Weight] <br>3. FILTER[#1, comparative:​!=:​Ford Motor Company:​col:​car_makers:​FullName] <br>4. INTERSECTION[#1, #2, #3] <br> | 1. SELECT[col:​model_list:​Model] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Ford Motor Company:​col:​car_makers:​FullName] <br>3. COMPARATIVE[#2, #2, comparative:​<:​ford:​col:​model_list:​Model] <br>4. DISCARD[#2, #3] <br> | - | extra | 
  | SPIDER_dev_173 | What are the name of the countries where there is not a single car maker? | SELECT CountryName FROM countries EXCEPT SELECT T1.CountryName FROM countries AS T1 JOIN CAR_MAKERS AS T2 ON T1.countryId  =  T2.Country; | 1. SELECT[tbl:​countries] <br>2. FILTER[#1, tbl:​car_makers] <br>3. DISCARD[#1, #2] <br>4. PROJECT[col:​countries:​CountryName, #3] <br> | 1. SELECT[tbl:​countries] <br>2. COMPARATIVE[#1, #1, tbl:​car_makers] <br>3. DISCARD[#1, #2] <br>4. PROJECT[col:​countries:​CountryName, #3] <br> | + | hard | 
  | SPIDER_dev_175 | Which are the car makers which produce at least 2 models and more than 3 car makers? List the id and the maker. | select t1.id ,  t1.maker from car_makers as t1 join model_list as t2 on t1.id  =  t2.maker group by t1.id having count(*)  >=  2 intersect select t1.id ,  t1.maker from car_makers as t1 join model_list as t2 on t1.id  =  t2.maker join car_names as t3 on t2.model  =  t3.model group by t1.id having count(*)  >  3; | 1. SELECT[tbl:​car_makers] <br>2. PROJECT[tbl:​model_list, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>=:​2] <br>5. PROJECT[tbl:​car_names, #1] <br>6. GROUP[count, #5, #1] <br>7. COMPARATIVE[#1, #6, comparative:​>:​3] <br>8. INTERSECTION[#1, #4, #7] <br>9. PROJECT[col:​car_makers:​Maker, #8] <br>10. UNION[#8, #9] <br> | 1. SELECT[tbl:​car_makers] <br>2. PROJECT[tbl:​model_list, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>=:​3] <br>5. PROJECT[col:​car_makers:​Id, #4] <br>6. PROJECT[col:​car_makers:​Maker, #4] <br>7. UNION[#5, #6] <br> | - | extra | 
  | SPIDER_dev_176 | What are the ids and makers of all car makers that produce at least 2 models and make more than 3 cars? | SELECT T1.Id ,  T1.Maker FROM CAR_MAKERS AS T1 JOIN MODEL_LIST AS T2 ON T1.Id  =  T2.Maker GROUP BY T1.Id HAVING count(*)  >=  2 INTERSECT SELECT T1.Id ,  T1.Maker FROM CAR_MAKERS AS T1 JOIN MODEL_LIST AS T2 ON T1.Id  =  T2.Maker JOIN CAR_NAMES AS T3 ON T2.model  =  T3.model GROUP BY T1.Id HAVING count(*)  >  3; | 1. SELECT[tbl:​car_makers] <br>2. PROJECT[tbl:​model_list, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>=:​2] <br>5. PROJECT[tbl:​car_names, #4] <br>6. GROUP[count, #5, #4] <br>7. COMPARATIVE[#4, #6, comparative:​>:​3] <br>8. PROJECT[col:​car_makers:​Id, #7] <br>9. PROJECT[col:​car_makers:​Maker, #7] <br>10. UNION[#8, #9] <br> | 1. SELECT[tbl:​car_makers] <br>2. PROJECT[tbl:​model_list, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>=:​3] <br>5. COMPARATIVE[#1, #3, comparative:​>=:​3] <br>6. INTERSECTION[#1, #4, #5] <br>7. PROJECT[col:​car_makers:​Id, #6] <br>8. PROJECT[col:​car_makers:​Maker, #6] <br>9. UNION[#7, #8] <br> | - | extra | 
  | SPIDER_dev_177 | What are the id and names of the countries which have more than 3 car makers or produce the 'fiat' model? | SELECT T1.countryId ,  T1.CountryName FROM Countries AS T1 JOIN CAR_MAKERS AS T2 ON T1.CountryId  =  T2.Country GROUP BY T1.countryId HAVING count(*)  >  3 UNION SELECT T1.countryId ,  T1.CountryName FROM Countries AS T1 JOIN CAR_MAKERS AS T2 ON T1.CountryId  =  T2.Country JOIN MODEL_LIST AS T3 ON T2.Id  =  T3.Maker WHERE T3.Model  =  'fiat'; | 1. SELECT[tbl:​countries] <br>2. PROJECT[tbl:​car_makers, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​3] <br>5. PROJECT[tbl:​model_list, #1] <br>6. COMPARATIVE[#1, #5, comparative:​=:​fiat:​col:​model_list:​Model] <br>7. UNION[#4, #6] <br>8. PROJECT[col:​countries:​CountryId, #7] <br>9. PROJECT[col:​countries:​CountryName, #7] <br>10. UNION[#8, #9] <br> | 1. SELECT[tbl:​countries] <br>2. PROJECT[tbl:​car_makers, #1] <br>3. COMPARATIVE[#2, #2, comparative:​=:​fiat:​col:​car_makers:​Maker] <br>4. GROUP[count, #3, #1] <br>5. COMPARATIVE[#1, #4, comparative:​>:​3] <br>6. UNION[#3, #5] <br>7. PROJECT[col:​countries:​CountryId, #6] <br>8. PROJECT[col:​countries:​CountryName, #6] <br>9. UNION[#7, #8] <br> | - | extra | 
  | SPIDER_dev_178 | What are the ids and names of all countries that either have more than 3 car makers or produce fiat model? | select t1.countryid ,  t1.countryname from countries as t1 join car_makers as t2 on t1.countryid  =  t2.country group by t1.countryid having count(*)  >  3 union select t1.countryid ,  t1.countryname from countries as t1 join car_makers as t2 on t1.countryid  =  t2.country join model_list as t3 on t2.id  =  t3.maker where t3.model  =  'fiat'; | 1. SELECT[tbl:​countries] <br>2. PROJECT[tbl:​car_makers, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​3] <br>5. FILTER[#1, comparative:​=:​fiat:​col:​model_list:​Model] <br>6. UNION[#4, #5] <br>7. PROJECT[col:​countries:​CountryId, #6] <br>8. PROJECT[col:​countries:​CountryName, #6] <br>9. UNION[#7, #8] <br> | 1. SELECT[tbl:​countries] <br>2. PROJECT[tbl:​car_makers, #1] <br>3. COMPARATIVE[#2, #2, comparative:​=:​Fiat:​col:​car_makers:​FullName] <br>4. GROUP[count, #3, #1] <br>5. COMPARATIVE[#1, #4, comparative:​>:​3] <br>6. UNION[#3, #5] <br>7. PROJECT[col:​countries:​CountryId, #6] <br>8. PROJECT[col:​countries:​CountryName, #6] <br>9. UNION[#7, #8] <br> | - | extra | 
 ***
 Exec acc: **0.6627**
