 | Idx | Table      | Column | Primary Key | Foreign Key | 
 | ----------- | ----------- | ----------- | ----------- | ----------- | 
  | 0 |  | * |   |   | 
 | 1 | **stadium** | Stadium_ID | + |   | 
 | 2 |   | Location |   |   | 
 | 3 |   | Name |   |   | 
 | 4 |   | Capacity |   |   | 
 | 5 |   | Highest |   |   | 
 | 6 |   | Lowest |   |   | 
 | 7 |   | Average |   |   | 
 | 8 | **singer** | Singer_ID | + |   | 
 | 9 |   | Name |   |   | 
 | 10 |   | Country |   |   | 
 | 11 |   | Song_Name |   |   | 
 | 12 |   | Song_release_year |   |   | 
 | 13 |   | Age |   |   | 
 | 14 |   | Is_male |   |   | 
 | 15 | **concert** | concert_ID | + |   | 
 | 16 |   | concert_Name |   |   | 
 | 17 |   | Theme |   |   | 
 | 18 |   | Stadium_ID |   | --> 1 | 
 | 19 |   | Year |   |   | 
 | 20 | **singer_in_concert** | concert_ID | + | --> 15 | 
 | 21 |   | Singer_ID |   | --> 8 | 
 
  | Index | Question  | SQL | gold QDMR | pred QDMR | Exec | SQL hardness |
  | ----------- | ----------- | ----------- |  ----------- | ----------- | ----------- | ----------- | 
 | SPIDER_dev_0 | How many singers do we have? | SELECT count(*) FROM singer | 1. SELECT[tbl:​singer] <br>2. AGGREGATE[count, #1] <br> | 1. SELECT[tbl:​singer] <br>2. AGGREGATE[count, #1] <br> | + | easy | 
  | SPIDER_dev_1 | What is the total number of singers? | SELECT count(*) FROM singer | 1. SELECT[tbl:​singer] <br>2. AGGREGATE[count, #1] <br> | 1. SELECT[tbl:​singer] <br>2. AGGREGATE[count, #1] <br> | + | easy | 
  | SPIDER_dev_2 | Show name, country, age for all singers ordered by age from the oldest to the youngest. | SELECT name ,  country ,  age FROM singer ORDER BY age DESC | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Name, #1] <br>3. PROJECT[col:​singer:​Country, #1] <br>4. PROJECT[col:​singer:​Age, #1] <br>5. UNION[#2, #3, #4] <br>6. SORT[#5, #4, sortdir:​descending] <br> | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Name, #1] <br>3. PROJECT[col:​singer:​Country, #1] <br>4. PROJECT[col:​singer:​Age, #1] <br>5. UNION[#2, #3, #4] <br>6. SORT[#5, #4, sortdir:​descending] <br> | + | medium | 
  | SPIDER_dev_3 | What are the names, countries, and ages for every singer in descending order of age? | SELECT name ,  country ,  age FROM singer ORDER BY age DESC | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Name, #1] <br>3. PROJECT[col:​singer:​Country, #1] <br>4. PROJECT[col:​singer:​Age, #1] <br>5. UNION[#2, #3, #4] <br>6. SORT[#5, #4, sortdir:​descending] <br> | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Name, #1] <br>3. PROJECT[col:​singer:​Country, #1] <br>4. PROJECT[col:​singer:​Age, #1] <br>5. UNION[#2, #3, #4] <br>6. SORT[#5, #4, sortdir:​descending] <br> | + | medium | 
  | SPIDER_dev_4 | What is the average, minimum, and maximum age of all singers from France? | SELECT avg(age) ,  min(age) ,  max(age) FROM singer WHERE country  =  'France' | 1. SELECT[tbl:​singer] <br>2. FILTER[#1, comparative:​=:​France:​col:​singer:​Country] <br>3. PROJECT[col:​singer:​Age, #2] <br>4. AGGREGATE[avg, #3] <br>5. AGGREGATE[min, #3] <br>6. AGGREGATE[max, #3] <br>7. UNION[#4, #5, #6] <br> | 1. SELECT[tbl:​singer] <br>2. COMPARATIVE[#1, #1, comparative:​=:​France:​col:​singer:​Country] <br>3. PROJECT[col:​singer:​Age, #2] <br>4. AGGREGATE[avg, #3] <br>5. AGGREGATE[min, #3] <br>6. AGGREGATE[max, #3] <br>7. UNION[#4, #5, #6] <br> | + | medium | 
  | SPIDER_dev_5 | What is the average, minimum, and maximum age for all French singers? | SELECT avg(age) ,  min(age) ,  max(age) FROM singer WHERE country  =  'France' | 1. SELECT[tbl:​singer] <br>2. FILTER[#1, val:​singer:​Country:​France] <br>3. PROJECT[col:​singer:​Age, #2] <br>4. AGGREGATE[avg, #3] <br>5. AGGREGATE[min, #3] <br>6. AGGREGATE[max, #3] <br>7. UNION[#4, #5, #6] <br> | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Age, #1] <br>3. AGGREGATE[avg, #2] <br>4. AGGREGATE[min, #2] <br>5. AGGREGATE[max, #2] <br>6. UNION[#3, #4, #5] <br> | - | medium | 
  | SPIDER_dev_6 | Show the name and the release year of the song by the youngest singer. | SELECT song_name ,  song_release_year FROM singer ORDER BY age LIMIT 1 | 1. SELECT[col:​singer:​Song_Name] <br>2. PROJECT[tbl:​singer, #1] <br>3. PROJECT[col:​singer:​Age, #2] <br>4. SUPERLATIVE[comparative:​min:​None, #1, #3] <br>5. PROJECT[col:​singer:​Song_Name, #4] <br>6. PROJECT[col:​singer:​Song_release_year, #4] <br>7. UNION[#5, #6] <br> | 1. SELECT[tbl:​singer] <br>2. PROJECT[tbl:​singer, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​min:​None, #1, #3] <br>5. PROJECT[col:​singer:​Song_Name, #4] <br>6. PROJECT[col:​singer:​Song_release_year, #4] <br>7. UNION[#5, #6] <br> | + | medium | 
  | SPIDER_dev_7 | What are the names and release years for all the songs of the youngest singer? | SELECT song_name ,  song_release_year FROM singer ORDER BY age LIMIT 1 | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Age, #1] <br>3. SUPERLATIVE[comparative:​min:​None, #1, #2] <br>4. PROJECT[col:​singer:​Song_Name, #3] <br>5. PROJECT[col:​singer:​Song_Name, #4] <br>6. PROJECT[col:​singer:​Song_release_year, #4] <br>7. UNION[#5, #6] <br> | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Is_male, #1] <br>3. SUPERLATIVE[comparative:​min:​None, #1, #2] <br>4. PROJECT[col:​singer:​Name, #3] <br>5. PROJECT[col:​singer:​Song_release_year, #3] <br>6. UNION[#4, #5] <br> | - | medium | 
  | SPIDER_dev_8 | What are all distinct countries where singers above age 20 are from? | SELECT DISTINCT country FROM singer WHERE age  >  20 | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Age, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​20:​col:​singer:​Age] <br>4.*(distinct)* PROJECT[col:​singer:​Country, #3] <br> | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Age, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​20:​col:​singer:​Age] <br>4. PROJECT[col:​singer:​Country, #3] <br>5.*(distinct)* PROJECT[None, #4] <br> | + | easy | 
  | SPIDER_dev_9 | What are  the different countries with singers above age 20? | SELECT DISTINCT country FROM singer WHERE age  >  20 | 1. SELECT[col:​singer:​Country] <br>2. PROJECT[tbl:​singer, #1] <br>3. PROJECT[col:​singer:​Age, #2] <br>4. COMPARATIVE[#1, #3, comparative:​>:​20:​col:​singer:​Age] <br>5.*(distinct)* PROJECT[different #REF, #4] <br> | 1. SELECT[col:​singer:​Country] <br>2. PROJECT[tbl:​singer, #1] <br>3. PROJECT[col:​singer:​Age, #2] <br>4. COMPARATIVE[#1, #3, comparative:​>:​20:​col:​singer:​Age] <br>5.*(distinct)* PROJECT[None, #4] <br> | + | easy | 
  | SPIDER_dev_10 | Show all countries and the number of singers in each country. | SELECT country ,  count(*) FROM singer GROUP BY country | 1. SELECT[col:​singer:​Country] <br>2. PROJECT[tbl:​singer, #1] <br>3. GROUP[count, #2, #1] <br>4. UNION[#1, #3] <br> | 1. SELECT[col:​singer:​Country] <br>2. PROJECT[tbl:​singer, #1] <br>3. GROUP[count, #2, #1] <br>4. UNION[#1, #3] <br> | + | medium | 
  | SPIDER_dev_11 | How many singers are from each country? | SELECT country ,  count(*) FROM singer GROUP BY country | 1. SELECT[col:​singer:​Country] <br>2. PROJECT[tbl:​singer, #1] <br>3. GROUP[count, #2, #1] <br>4. UNION[#1, #3] <br> | 1. SELECT[col:​singer:​Country] <br>2. PROJECT[tbl:​singer, #1] <br>3. GROUP[count, #2, #1] <br>4. UNION[#1, #3] <br> | + | medium | 
  | SPIDER_dev_12 | List all song names by singers above the average age. | SELECT song_name FROM singer WHERE age  >  (SELECT avg(age) FROM singer) | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Age, #1] <br>3. AGGREGATE[avg, #2] <br>4. COMPARATIVE[#1, #2, comparative:​>:​#3] <br>5. PROJECT[col:​singer:​Song_Name, #4] <br>6. PROJECT[col:​singer:​Song_Name, #5] <br> | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Age, #1] <br>3. AGGREGATE[avg, #2] <br>4. COMPARATIVE[#1, #2, comparative:​>:​Dangerous:​col:​singer:​Song_Name] <br>5. PROJECT[col:​singer:​Song_Name, #4] <br> | - | hard | 
  | SPIDER_dev_13 | What are all the song names by singers who are older than average? | SELECT song_name FROM singer WHERE age  >  (SELECT avg(age) FROM singer) | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Age, #1] <br>3. AGGREGATE[avg, #2] <br>4. COMPARATIVE[#1, #2, comparative:​>:​#3] <br>5. PROJECT[col:​singer:​Song_Name, #4] <br>6. PROJECT[col:​singer:​Song_Name, #5] <br> | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​stadium:​Average, #1] <br>3. AGGREGATE[avg, #2] <br>4. COMPARATIVE[#1, #2, comparative:​>:​Dangerous:​col:​singer:​Song_Name] <br>5. PROJECT[col:​singer:​Song_Name, #4] <br> | - | hard | 
  | SPIDER_dev_14 | Show location and name for all stadiums with a capacity between 5000 and 10000. | SELECT LOCATION ,  name FROM stadium WHERE capacity BETWEEN 5000 AND 10000 | 1. SELECT[tbl:​stadium] <br>2. PROJECT[col:​stadium:​Capacity, #1] <br>3. COMPARATIVE[#1, #2, comparative:​<=:​10000:​col:​stadium:​Capacity] <br>4. COMPARATIVE[#1, #2, comparative:​>=:​5000:​col:​stadium:​Capacity] <br>5. INTERSECTION[#1, #3, #4] <br>6. PROJECT[col:​stadium:​Location, #5] <br>7. PROJECT[col:​stadium:​Name, #5] <br>8. UNION[#6, #7] <br> | 1. SELECT[tbl:​stadium] <br>2. PROJECT[col:​stadium:​Capacity, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>=:​5000:​col:​stadium:​Capacity] <br>4. COMPARATIVE[#1, #2, comparative:​<=:​10000:​col:​stadium:​Capacity] <br>5. INTERSECTION[#1, #3, #4] <br>6. PROJECT[col:​stadium:​Location, #5] <br>7. PROJECT[col:​stadium:​Name, #5] <br>8. UNION[#6, #7] <br> | + | medium | 
  | SPIDER_dev_15 | What are the locations and names of all stadiums with capacity between 5000 and 10000? | SELECT LOCATION ,  name FROM stadium WHERE capacity BETWEEN 5000 AND 10000 | 1. SELECT[tbl:​stadium] <br>2. PROJECT[col:​stadium:​Capacity, #1] <br>3. COMPARATIVE[#1, #2, comparative:​<=:​10000:​col:​stadium:​Capacity] <br>4. COMPARATIVE[#1, #2, comparative:​>=:​5000:​col:​stadium:​Capacity] <br>5. INTERSECTION[#1, #3, #4] <br>6. PROJECT[col:​stadium:​Location, #5] <br>7. PROJECT[col:​stadium:​Name, #5] <br>8. UNION[#6, #7] <br> | 1. SELECT[tbl:​stadium] <br>2. PROJECT[col:​stadium:​Capacity, #1] <br>3. COMPARATIVE[#1, #2, comparative:​<=:​5000:​col:​stadium:​Capacity] <br>4. COMPARATIVE[#1, #2, comparative:​<=:​5000:​col:​stadium:​Capacity] <br>5. INTERSECTION[#1, #3, #4] <br>6. PROJECT[col:​stadium:​Location, #5] <br>7. PROJECT[col:​stadium:​Name, #5] <br>8. UNION[#6, #7] <br> | - | medium | 
  | SPIDER_dev_17 | What is the average and maximum capacities for all stadiums? | select avg(capacity) ,  max(capacity) from stadium | 1. SELECT[tbl:​stadium] <br>2. PROJECT[col:​stadium:​Capacity, #1] <br>3. AGGREGATE[avg, #2] <br>4. AGGREGATE[max, #2] <br>5. UNION[#3, #4] <br> | 1. SELECT[tbl:​stadium] <br>2. PROJECT[col:​stadium:​Average, #1] <br>3. AGGREGATE[avg, #2] <br>4. AGGREGATE[max, #2] <br>5. UNION[#3, #4] <br> | - | medium | 
  | SPIDER_dev_18 | What is the name and capacity for the stadium with highest average attendance? | SELECT name ,  capacity FROM stadium ORDER BY average DESC LIMIT 1 | 1. SELECT[tbl:​stadium] <br>2. PROJECT[attendances of #REF, #1] <br>3. GROUP[col:​stadium:​Average, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​stadium:​Name, #4] <br>6. PROJECT[col:​stadium:​Capacity, #4] <br>7. UNION[#5, #6] <br> | 1. SELECT[tbl:​stadium] <br>2. PROJECT[col:​stadium:​Average, #1] <br>3. GROUP[sum, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​stadium:​Name, #4] <br>6. PROJECT[col:​stadium:​Capacity, #4] <br>7. UNION[#5, #6] <br> | + | medium | 
  | SPIDER_dev_19 | What is the name and capacity for the stadium with the highest average attendance? | SELECT name ,  capacity FROM stadium ORDER BY average DESC LIMIT 1 | 1. SELECT[tbl:​stadium] <br>2. PROJECT[attendances of #REF, #1] <br>3. GROUP[col:​stadium:​Average, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​stadium:​Name, #4] <br>6. PROJECT[col:​stadium:​Capacity, #4] <br>7. UNION[#5, #6] <br> | 1. SELECT[tbl:​stadium] <br>2. PROJECT[col:​stadium:​Average, #1] <br>3. GROUP[sum, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​stadium:​Name, #4] <br>6. PROJECT[col:​stadium:​Capacity, #4] <br>7. UNION[#5, #6] <br> | + | medium | 
  | SPIDER_dev_20 | How many concerts are there in year 2014 or 2015? | SELECT count(*) FROM concert WHERE YEAR  =  2014 OR YEAR  =  2015 | 1. SELECT[tbl:​concert] <br>2. FILTER[#1, comparative:​=:​2014:​col:​concert:​Year] <br>3. FILTER[#1, comparative:​=:​2015:​col:​concert:​Year] <br>4. UNION[#2, #3] <br>5. AGGREGATE[count, #4] <br> | 1. SELECT[tbl:​concert] <br>2. PROJECT[col:​concert:​Year, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​2014:​col:​concert:​Year] <br>4. COMPARATIVE[#1, #2, comparative:​=:​2015:​col:​concert:​Year] <br>5. UNION[#3, #4] <br>6. AGGREGATE[count, #5] <br> | + | medium | 
  | SPIDER_dev_21 | How many concerts occurred in 2014 or 2015? | SELECT count(*) FROM concert WHERE YEAR  =  2014 OR YEAR  =  2015 | 1. SELECT[tbl:​concert] <br>2. FILTER[#1, comparative:​=:​2014:​col:​concert:​Year] <br>3. FILTER[#1, comparative:​=:​2015:​col:​concert:​Year] <br>4. UNION[#2, #3] <br>5. AGGREGATE[count, #4] <br> | 1. SELECT[tbl:​concert] <br>2. COMPARATIVE[#1, #1, comparative:​=:​2014:​col:​concert:​Year] <br>3. COMPARATIVE[#1, #1, comparative:​=:​2015:​col:​concert:​Year] <br>4. UNION[#2, #3] <br>5. AGGREGATE[count, #4] <br> | + | medium | 
  | SPIDER_dev_22 | Show the stadium name and the number of concerts in each stadium. | SELECT T2.name ,  count(*) FROM concert AS T1 JOIN stadium AS T2 ON T1.stadium_id  =  T2.stadium_id GROUP BY T1.stadium_id | 1. SELECT[col:​concert:​Stadium_ID] <br>2. PROJECT[col:​stadium:​Name, #1] <br>3. PROJECT[tbl:​concert, #1] <br>4. GROUP[count, #3, #1] <br>5. UNION[#2, #4] <br> | 1. SELECT[col:​concert:​Stadium_ID] <br>2. PROJECT[col:​stadium:​Name, #1] <br>3. PROJECT[tbl:​concert, #1] <br>4. GROUP[count, #3, #1] <br>5. UNION[#2, #4] <br> | + | medium | 
  | SPIDER_dev_23 | For each stadium, how many concerts play there? | SELECT T2.name ,  count(*) FROM concert AS T1 JOIN stadium AS T2 ON T1.stadium_id  =  T2.stadium_id GROUP BY T1.stadium_id | 1. SELECT[col:​stadium:​Name] <br>2. PROJECT[tbl:​concert, #1] <br>3. GROUP[count, #2, #1] <br>4. UNION[#1, #3] <br> | 1. SELECT[col:​stadium:​Name] <br>2. PROJECT[tbl:​concert, #1] <br>3. GROUP[count, #2, #1] <br>4. UNION[#1, #3] <br> | + | medium | 
  | SPIDER_dev_24 | Show the stadium name and capacity with most number of concerts in year 2014 or after. | SELECT T2.name ,  T2.capacity FROM concert AS T1 JOIN stadium AS T2 ON T1.stadium_id  =  T2.stadium_id WHERE T1.year  >=  2014 GROUP BY T2.stadium_id ORDER BY count(*) DESC LIMIT 1 | 1. SELECT[tbl:​stadium] <br>2. PROJECT[tbl:​concert, #1] <br>3. FILTER[#2, comparative:​>=:​2014:​col:​concert:​Year] <br>4. GROUP[count, #3, #1] <br>5. SUPERLATIVE[comparative:​max:​None, #1, #4] <br>6. PROJECT[col:​stadium:​Name, #5] <br>7. PROJECT[col:​stadium:​Capacity, #5] <br>8. UNION[#6, #7] <br> | 1. SELECT[tbl:​stadium] <br>2. PROJECT[tbl:​concert, #1] <br>3. PROJECT[col:​concert:​Year, #2] <br>4. COMPARATIVE[#2, #3, comparative:​>=:​2014:​col:​concert:​Year] <br>5. GROUP[count, #4, #1] <br>6. SUPERLATIVE[comparative:​max:​None, #1, #5] <br>7. PROJECT[col:​stadium:​Name, #6] <br>8. PROJECT[col:​stadium:​Capacity, #6] <br>9. UNION[#7, #8] <br> | + | extra | 
  | SPIDER_dev_25 | What is the name and capacity of the stadium with the most concerts after 2013? | select t2.name ,  t2.capacity from concert as t1 join stadium as t2 on t1.stadium_id  =  t2.stadium_id where t1.year  >  2013 group by t2.stadium_id order by count(*) desc limit 1 | 1. SELECT[tbl:​stadium] <br>2. PROJECT[tbl:​concert, #1] <br>3. FILTER[#2, comparative:​>:​2013:​col:​concert:​Year] <br>4. GROUP[count, #3, #1] <br>5. COMPARATIVE[#1, #4, comparative:​max:​None] <br>6. PROJECT[col:​stadium:​Name, #5] <br>7. PROJECT[col:​stadium:​Capacity, #5] <br>8. UNION[#6, #7] <br> | 1. SELECT[tbl:​stadium] <br>2. PROJECT[tbl:​concert, #1] <br>3. COMPARATIVE[#2, #2, comparative:​>:​2013:​col:​singer:​Song_release_year] <br>4. GROUP[count, #3, #1] <br>5. SUPERLATIVE[comparative:​max:​None, #1, #4] <br>6. PROJECT[col:​stadium:​Name, #5] <br>7. PROJECT[col:​stadium:​Capacity, #5] <br>8. UNION[#6, #7] <br> | + | extra | 
  | SPIDER_dev_26 | Which year has most number of concerts? | SELECT YEAR FROM concert GROUP BY YEAR ORDER BY count(*) DESC LIMIT 1 | 1. SELECT[col:​concert:​Year] <br>2. PROJECT[tbl:​concert, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br> | 1. SELECT[col:​concert:​Year] <br>2. PROJECT[tbl:​concert, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br> | + | hard | 
  | SPIDER_dev_27 | What is the year that had the most concerts? | SELECT YEAR FROM concert GROUP BY YEAR ORDER BY count(*) DESC LIMIT 1 | 1. SELECT[tbl:​concert] <br>2. PROJECT[col:​concert:​Year, #1] <br>3. GROUP[count, #1, #2] <br>4. SUPERLATIVE[comparative:​max:​None, #2, #3] <br> | 1. SELECT[col:​concert:​Year] <br>2. PROJECT[tbl:​concert, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br> | + | hard | 
  | SPIDER_dev_29 | What are the names of the stadiums without any concerts? | SELECT name FROM stadium WHERE stadium_id NOT IN (SELECT stadium_id FROM concert) | 1. SELECT[tbl:​stadium] <br>2. FILTER[#1, tbl:​concert] <br>3. DISCARD[#1, #2] <br>4. PROJECT[col:​stadium:​Name, #3] <br> | 1. SELECT[tbl:​stadium] <br>2. COMPARATIVE[#1, #1, tbl:​concert] <br>3. DISCARD[#1, #2] <br>4. PROJECT[col:​stadium:​Name, #3] <br> | + | hard | 
  | SPIDER_dev_30 | Show countries where a singer above age 40 and a singer below 30 are from. | SELECT country FROM singer WHERE age  >  40 INTERSECT SELECT country FROM singer WHERE age  <  30 | 1. SELECT[col:​singer:​Country] <br>2. PROJECT[tbl:​singer, #1] <br>3. PROJECT[col:​singer:​Age, #2] <br>4. COMPARATIVE[#1, #3, comparative:​>:​40:​col:​singer:​Age] <br>5. COMPARATIVE[#1, #3, comparative:​<:​30:​col:​singer:​Age] <br>6. INTERSECTION[#1, #4, #5] <br> | 1. SELECT[tbl:​singer] <br>2. PROJECT[tbl:​singer, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​40:​col:​singer:​Age] <br>4. PROJECT[col:​singer:​Age, #1] <br>5. COMPARATIVE[#1, #4, comparative:​<:​30:​col:​singer:​Age] <br>6. PROJECT[col:​singer:​Country, #1] <br>7. INTERSECTION[#6, #3, #5] <br> | + | hard | 
  | SPIDER_dev_31 | Show names for all stadiums except for stadiums having a concert in year 2014. | SELECT name FROM stadium EXCEPT SELECT T2.name FROM concert AS T1 JOIN stadium AS T2 ON T1.stadium_id  =  T2.stadium_id WHERE T1.year  =  2014 | 1. SELECT[tbl:​stadium] <br>2. FILTER[#1, tbl:​concert] <br>3. FILTER[#2, comparative:​=:​2014:​col:​concert:​Year] <br>4. DISCARD[#1, #3] <br>5. PROJECT[col:​stadium:​Name, #4] <br> | 1. SELECT[tbl:​stadium] <br>2. PROJECT[tbl:​concert, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​2014:​col:​concert:​Year] <br>4. DISCARD[#1, #3] <br>5. PROJECT[col:​stadium:​Name, #4] <br> | + | hard | 
  | SPIDER_dev_33 | Show the name and theme for all concerts and the number of singers in each concert. | SELECT T2.concert_name ,  T2.theme ,  count(*) FROM singer_in_concert AS T1 JOIN concert AS T2 ON T1.concert_id  =  T2.concert_id GROUP BY T2.concert_id | 1. SELECT[col:​concert:​concert_ID] <br>2. PROJECT[col:​concert:​concert_Name, #1] <br>3. PROJECT[col:​concert:​Theme, #1] <br>4. PROJECT[tbl:​singer_in_concert, #1] <br>5. GROUP[count, #4, #1] <br>6. UNION[#2, #3, #5] <br> | 1. SELECT[tbl:​concert] <br>2. PROJECT[col:​concert:​concert_Name, #1] <br>3. PROJECT[col:​concert:​Theme, #1] <br>4. PROJECT[tbl:​singer_in_concert, #1] <br>5. GROUP[count, #4, #1] <br>6. UNION[#2, #3, #5] <br> | + | medium | 
  | SPIDER_dev_34 | What are the names, themes, and number of singers for every concert? | select t2.concert_name ,  t2.theme ,  count(*) from singer_in_concert as t1 join concert as t2 on t1.concert_id  =  t2.concert_id group by t2.concert_id | 1. SELECT[col:​concert:​concert_ID] <br>2. PROJECT[col:​concert:​concert_Name, #1] <br>3. PROJECT[col:​concert:​Theme, #1] <br>4. PROJECT[tbl:​singer_in_concert, #1] <br>5. GROUP[count, #4, #1] <br>6. UNION[#2, #3, #5] <br> | 1. SELECT[tbl:​concert] <br>2. PROJECT[col:​concert:​concert_Name, #1] <br>3. PROJECT[col:​concert:​Theme, #1] <br>4. PROJECT[tbl:​singer, #1] <br>5. GROUP[count, #4, #1] <br>6. UNION[#2, #3, #5] <br> | + | medium | 
  | SPIDER_dev_35 | List singer names and number of concerts for each singer. | SELECT T2.name ,  count(*) FROM singer_in_concert AS T1 JOIN singer AS T2 ON T1.singer_id  =  T2.singer_id GROUP BY T2.singer_id | 1. SELECT[col:​singer:​Singer_ID] <br>2. PROJECT[col:​singer:​Name, #1] <br>3. PROJECT[tbl:​singer_in_concert, #1] <br>4. GROUP[count, #3, #1] <br>5. UNION[#2, #4] <br> | 1. SELECT[col:​singer_in_concert:​Singer_ID] <br>2. PROJECT[col:​singer:​Name, #1] <br>3. PROJECT[tbl:​singer_in_concert, #1] <br>4. GROUP[count, #3, #1] <br>5. UNION[#2, #4] <br> | + | medium | 
  | SPIDER_dev_36 | What are the names of the singers and number of concerts for each person? | SELECT T2.name ,  count(*) FROM singer_in_concert AS T1 JOIN singer AS T2 ON T1.singer_id  =  T2.singer_id GROUP BY T2.singer_id | 1. SELECT[col:​singer:​Singer_ID] <br>2. PROJECT[col:​singer:​Name, #1] <br>3. PROJECT[tbl:​singer_in_concert, #1] <br>4. GROUP[count, #3, #1] <br>5. UNION[#2, #4] <br> | 1. SELECT[col:​singer:​Name] <br>2. PROJECT[col:​singer:​Name, #1] <br>3. PROJECT[tbl:​singer_in_concert, #1] <br>4. GROUP[count, #3, #1] <br>5. UNION[#2, #4] <br> | + | medium | 
  | SPIDER_dev_37 | List all singer names in concerts in year 2014. | SELECT T2.name FROM singer_in_concert AS T1 JOIN singer AS T2 ON T1.singer_id  =  T2.singer_id JOIN concert AS T3 ON T1.concert_id  =  T3.concert_id WHERE T3.year  =  2014 | 1. SELECT[tbl:​concert] <br>2. FILTER[#1, comparative:​=:​2014:​col:​concert:​Year] <br>3. PROJECT[col:​singer:​Name, #2] <br> | 1. SELECT[tbl:​concert] <br>2. PROJECT[col:​concert:​Year, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​2014:​col:​concert:​Year] <br>4. PROJECT[col:​singer:​Name, #3] <br> | + | hard | 
  | SPIDER_dev_38 | What are the names of the singers who performed in a concert in 2014? | SELECT T2.name FROM singer_in_concert AS T1 JOIN singer AS T2 ON T1.singer_id  =  T2.singer_id JOIN concert AS T3 ON T1.concert_id  =  T3.concert_id WHERE T3.year  =  2014 | 1. SELECT[tbl:​concert] <br>2. FILTER[#1, comparative:​=:​2014:​col:​concert:​Year] <br>3. PROJECT[tbl:​singer_in_concert, #2] <br>4. PROJECT[col:​singer:​Name, #3] <br> | 1. SELECT[tbl:​singer] <br>2. COMPARATIVE[#1, #1, comparative:​=:​2014:​col:​singer:​Song_release_year] <br>3. PROJECT[col:​singer:​Name, #2] <br> | - | hard | 
  | SPIDER_dev_39 | what is the name and nation of the singer who have a song having 'Hey' in its name? | SELECT name ,  country FROM singer WHERE song_name LIKE '%Hey%' | 1. SELECT[tbl:​singer] <br>2. FILTER[#1, comparative:​like:​%Hey%:​col:​singer:​Song_Name] <br>3. PROJECT[col:​singer:​Name, #2] <br>4. PROJECT[col:​singer:​Country, #2] <br>5. UNION[#3, #4] <br> | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Song_Name, #1] <br>3. COMPARATIVE[#1, #2, comparative:​like:​Hey:​col:​singer:​Song_Name] <br>4. PROJECT[col:​singer:​Name, #3] <br>5. PROJECT[col:​singer:​Country, #3] <br>6. UNION[#4, #5] <br> | + | medium | 
  | SPIDER_dev_40 | What is the name and country of origin of every singer who has a song with the word 'Hey' in its title? | SELECT name ,  country FROM singer WHERE song_name LIKE '%Hey%' | 1. SELECT[col:​singer:​Song_Name] <br>2. PROJECT[col:​singer:​Song_Name, #1] <br>3. COMPARATIVE[#1, #2, comparative:​like:​%Hey%:​col:​singer:​Song_Name] <br>4. PROJECT[tbl:​singer, #3] <br>5. PROJECT[col:​singer:​Name, #4] <br>6. PROJECT[col:​singer:​Country, #4] <br>7. UNION[#5, #6] <br> | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Song_Name, #1] <br>3. COMPARATIVE[#1, #2, comparative:​like:​Hey:​col:​singer:​Song_Name] <br>4. PROJECT[col:​singer:​Name, #3] <br>5. PROJECT[col:​singer:​Country, #3] <br>6. UNION[#4, #5] <br> | + | medium | 
  | SPIDER_dev_41 | Find the name and location of the stadiums which some concerts happened in the years of both 2014 and 2015. | SELECT T2.name ,  T2.location FROM concert AS T1 JOIN stadium AS T2 ON T1.stadium_id  =  T2.stadium_id WHERE T1.Year  =  2014 INTERSECT SELECT T2.name ,  T2.location FROM concert AS T1 JOIN stadium AS T2 ON T1.stadium_id  =  T2.stadium_id WHERE T1.Year  =  2015 | 1. SELECT[tbl:​stadium] <br>2. PROJECT[tbl:​concert, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​2014:​col:​concert:​Year] <br>4. COMPARATIVE[#1, #2, comparative:​=:​2015:​col:​concert:​Year] <br>5. INTERSECTION[#1, #3, #4] <br>6. PROJECT[col:​stadium:​Name, #5] <br>7. PROJECT[col:​stadium:​Location, #5] <br>8. UNION[#6, #7] <br> | 1. SELECT[tbl:​stadium] <br>2. PROJECT[tbl:​concert, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​2014:​col:​concert:​Year] <br>4. COMPARATIVE[#1, #2, comparative:​=:​2015:​col:​concert:​Year] <br>5. INTERSECTION[#1, #3, #4] <br>6. PROJECT[col:​stadium:​Name, #5] <br>7. PROJECT[col:​stadium:​Location, #5] <br>8. UNION[#6, #7] <br> | + | extra | 
  | SPIDER_dev_42 | What are the names and locations of the stadiums that had concerts that occurred in both 2014 and 2015? | SELECT T2.name ,  T2.location FROM concert AS T1 JOIN stadium AS T2 ON T1.stadium_id  =  T2.stadium_id WHERE T1.Year  =  2014 INTERSECT SELECT T2.name ,  T2.location FROM concert AS T1 JOIN stadium AS T2 ON T1.stadium_id  =  T2.stadium_id WHERE T1.Year  =  2015 | 1. SELECT[tbl:​stadium] <br>2. FILTER[#1, comparative:​=:​2014:​col:​concert:​Year] <br>3. FILTER[#1, comparative:​=:​2015:​col:​concert:​Year] <br>4. PROJECT[col:​stadium:​Name, #1] <br>5. INTERSECTION[#4, #2, #3] <br>6. PROJECT[col:​stadium:​Location, #1] <br>7. INTERSECTION[#6, #2, #3] <br>8. UNION[#5, #7] <br> | 1. SELECT[tbl:​stadium] <br>2. PROJECT[tbl:​concert, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​2014:​col:​concert:​Year] <br>4. COMPARATIVE[#1, #2, comparative:​=:​2015:​col:​concert:​Year] <br>5. INTERSECTION[#1, #3, #4] <br>6. PROJECT[col:​stadium:​Name, #5] <br>7. PROJECT[col:​stadium:​Location, #5] <br>8. UNION[#6, #7] <br> | + | extra | 
  | SPIDER_dev_43 | Find the number of concerts happened in the stadium with the highest capacity. | select count(*) from concert where stadium_id = (select stadium_id from stadium order by capacity desc limit 1) | 1. SELECT[tbl:​stadium] <br>2. PROJECT[col:​stadium:​Capacity, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. PROJECT[tbl:​concert, #3] <br>5. AGGREGATE[count, #4] <br> | 1. SELECT[tbl:​stadium] <br>2. PROJECT[col:​stadium:​Capacity, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. PROJECT[tbl:​concert, #3] <br>5. AGGREGATE[sum, #4] <br> | - | hard | 
  | SPIDER_dev_44 | What are the number of concerts that occurred in the stadium with the largest capacity? | select count(*) from concert where stadium_id = (select stadium_id from stadium order by capacity desc limit 1) | 1. SELECT[tbl:​stadium] <br>2. PROJECT[col:​stadium:​Capacity, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. PROJECT[tbl:​concert, #3] <br>5. AGGREGATE[count, #4] <br> | 1. SELECT[tbl:​stadium] <br>2. PROJECT[col:​stadium:​Capacity, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. PROJECT[tbl:​concert, #3] <br>5. AGGREGATE[count, #4] <br> | + | hard | 
 ***
 Exec acc: **0.8095**
