 | Idx | Table      | Column | Primary Key | Foreign Key | 
 | ----------- | ----------- | ----------- | ----------- | ----------- | 
  | 0 |  | * |   |   | 
 | 1 | **conductor** | Conductor_ID | + |   | 
 | 2 |   | Name |   |   | 
 | 3 |   | Age |   |   | 
 | 4 |   | Nationality |   |   | 
 | 5 |   | Year_of_Work |   |   | 
 | 6 | **orchestra** | Orchestra_ID | + |   | 
 | 7 |   | Orchestra |   |   | 
 | 8 |   | Conductor_ID |   | --> 1 | 
 | 9 |   | Record_Company |   |   | 
 | 10 |   | Year_of_Founded |   |   | 
 | 11 |   | Major_Record_Format |   |   | 
 | 12 | **performance** | Performance_ID | + |   | 
 | 13 |   | Orchestra_ID |   | --> 6 | 
 | 14 |   | Type |   |   | 
 | 15 |   | Date |   |   | 
 | 16 |   | Official_ratings_(millions) |   |   | 
 | 17 |   | Weekly_rank |   |   | 
 | 18 |   | Share |   |   | 
 | 19 | **show** | Show_ID |   |   | 
 | 20 |   | Performance_ID |   | --> 12 | 
 | 21 |   | If_first_show |   |   | 
 | 22 |   | Result |   |   | 
 | 23 |   | Attendance |   |   | 
 
  | Index | Question  | SQL | gold QDMR | pred QDMR | Exec | SQL hardness |
  | ----------- | ----------- | ----------- |  ----------- | ----------- | ----------- | ----------- | 
 | SPIDER_dev_822 | How many conductors are there? | SELECT count(*) FROM conductor |  | 1. SELECT[tbl:​conductor] <br>2. AGGREGATE[count, #1] <br> | + | easy | 
  | SPIDER_dev_823 | Count the number of conductors. | SELECT count(*) FROM conductor |  | 1. SELECT[tbl:​conductor] <br>2. AGGREGATE[count, #1] <br> | + | easy | 
  | SPIDER_dev_824 | List the names of conductors in ascending order of age. | SELECT Name FROM conductor ORDER BY Age ASC |  | 1. SELECT[tbl:​conductor] <br>2. PROJECT[col:​conductor:​Name, #1] <br>3. PROJECT[col:​conductor:​Age, #1] <br>4. SORT[#2, #3, sortdir:​ascending] <br> | + | easy | 
  | SPIDER_dev_825 | What are the names of conductors, ordered by age? | SELECT Name FROM conductor ORDER BY Age ASC |  | 1. SELECT[tbl:​conductor] <br>2. PROJECT[col:​conductor:​Name, #1] <br>3. PROJECT[col:​conductor:​Age, #1] <br>4. SORT[#2, #3, sortdir:​ascending] <br> | + | easy | 
  | SPIDER_dev_826 | What are the names of conductors whose nationalities are not "USA"? | SELECT Name FROM conductor WHERE Nationality != 'USA' |  | 1. SELECT[tbl:​conductor] <br>2. PROJECT[col:​conductor:​Nationality, #1] <br>3. COMPARATIVE[#1, #2, comparative:​!=:​USA:​col:​conductor:​Nationality] <br>4. PROJECT[col:​conductor:​Name, #3] <br> | + | easy | 
  | SPIDER_dev_827 | Return the names of conductors that do not have the nationality "USA". | SELECT Name FROM conductor WHERE Nationality != 'USA' |  | 1. SELECT[tbl:​conductor] <br>2. PROJECT[col:​conductor:​Nationality, #1] <br>3. COMPARATIVE[#1, #2, comparative:​!=:​USA:​col:​conductor:​Nationality] <br>4. PROJECT[col:​conductor:​Name, #3] <br> | + | easy | 
  | SPIDER_dev_828 | What are the record companies of orchestras in descending order of years in which they were founded? | SELECT Record_Company FROM orchestra ORDER BY Year_of_Founded DESC |  | 1. SELECT[tbl:​orchestra] <br>2. PROJECT[col:​orchestra:​Record_Company, #1] <br>3. PROJECT[col:​orchestra:​Year_of_Founded, #1] <br>4. SORT[#2, #3, sortdir:​descending] <br> | + | easy | 
  | SPIDER_dev_829 | Return the record companies of orchestras, sorted descending by the years in which they were founded. | SELECT Record_Company FROM orchestra ORDER BY Year_of_Founded DESC |  | 1. SELECT[tbl:​orchestra] <br>2. PROJECT[col:​orchestra:​Record_Company, #1] <br>3. PROJECT[col:​orchestra:​Year_of_Founded, #1] <br>4. SORT[#2, #3, sortdir:​descending] <br> | + | easy | 
  | SPIDER_dev_830 | What is the average attendance of shows? | SELECT avg(Attendance) FROM SHOW |  | 1. SELECT[tbl:​show] <br>2. PROJECT[col:​show:​Attendance, #1] <br>3. AGGREGATE[avg, #2] <br> | + | easy | 
  | SPIDER_dev_831 | Return the average attendance across all shows. | SELECT avg(Attendance) FROM SHOW |  | 1. SELECT[tbl:​show] <br>2. PROJECT[col:​show:​Attendance, #1] <br>3. AGGREGATE[avg, #2] <br> | + | easy | 
  | SPIDER_dev_832 | What are the maximum and minimum share of performances whose type is not "Live final". | SELECT max(SHARE) ,  min(SHARE) FROM performance WHERE TYPE != "Live final" |  | 1. SELECT[tbl:​performance] <br>2. PROJECT[col:​performance:​Type, #1] <br>3. COMPARATIVE[#1, #2, comparative:​!=:​Live final:​col:​performance:​Type] <br>4. PROJECT[col:​performance:​Share, #3] <br>5. AGGREGATE[max, #4] <br>6. AGGREGATE[min, #4] <br>7. UNION[#5, #6] <br> | - | medium | 
  | SPIDER_dev_833 | Return the maximum and minimum shares for performances that do not have the type "Live final". | SELECT max(SHARE) ,  min(SHARE) FROM performance WHERE TYPE != "Live final" |  | 1. SELECT[tbl:​performance] <br>2. PROJECT[col:​performance:​Type, #1] <br>3. COMPARATIVE[#1, #2, comparative:​!=:​Live final:​col:​performance:​Type] <br>4. PROJECT[col:​performance:​Share, #3] <br>5. AGGREGATE[max, #4] <br>6. AGGREGATE[min, #4] <br>7. UNION[#5, #6] <br> | - | medium | 
  | SPIDER_dev_834 | How many different nationalities do conductors have? | SELECT count(DISTINCT Nationality) FROM conductor |  | 1. SELECT[tbl:​conductor] <br>2. PROJECT[col:​conductor:​Nationality, #1] <br>3.*(distinct)* PROJECT[None, #2] <br>4. AGGREGATE[count, #3] <br> | + | easy | 
  | SPIDER_dev_835 | Count the number of different nationalities of conductors. | SELECT count(DISTINCT Nationality) FROM conductor |  | 1. SELECT[tbl:​conductor] <br>2.*(distinct)* PROJECT[col:​conductor:​Nationality, #1] <br>3. AGGREGATE[count, #2] <br> | + | easy | 
  | SPIDER_dev_836 | List names of conductors in descending order of years of work. | SELECT Name FROM conductor ORDER BY Year_of_Work DESC |  | 1. SELECT[tbl:​conductor] <br>2. PROJECT[col:​conductor:​Name, #1] <br>3. PROJECT[col:​conductor:​Year_of_Work, #1] <br>4. SORT[#2, #3, sortdir:​descending] <br> | + | easy | 
  | SPIDER_dev_837 | What are the names of conductors, sorted descending by the number of years they have worked? | SELECT Name FROM conductor ORDER BY Year_of_Work DESC |  | 1. SELECT[tbl:​conductor] <br>2. PROJECT[col:​conductor:​Name, #1] <br>3. PROJECT[col:​conductor:​Year_of_Work, #1] <br>4. GROUP[count, #3, #1] <br>5. SORT[#2, #4, sortdir:​descending] <br> | + | easy | 
  | SPIDER_dev_838 | List the name of the conductor with the most years of work. | SELECT Name FROM conductor ORDER BY Year_of_Work DESC LIMIT 1 |  | 1. SELECT[tbl:​conductor] <br>2. PROJECT[col:​conductor:​Year_of_Work, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. PROJECT[col:​conductor:​Name, #3] <br> | + | medium | 
  | SPIDER_dev_839 | What is the name of the conductor who has worked the greatest number of years? | SELECT Name FROM conductor ORDER BY Year_of_Work DESC LIMIT 1 |  | 1. SELECT[tbl:​conductor] <br>2. PROJECT[col:​conductor:​Year_of_Work, #1] <br>3. GROUP[sum, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​conductor:​Name, #4] <br> | + | medium | 
  | SPIDER_dev_840 | Show the names of conductors and the orchestras they have conducted. | SELECT T1.Name ,  T2.Orchestra FROM conductor AS T1 JOIN orchestra AS T2 ON T1.Conductor_ID  =  T2.Conductor_ID |  | 1. SELECT[tbl:​conductor] <br>2. PROJECT[col:​conductor:​Name, #1] <br>3. PROJECT[col:​orchestra:​Orchestra, #1] <br>4. UNION[#2, #3] <br> | + | medium | 
  | SPIDER_dev_841 | What are the names of conductors as well as the corresonding orchestras that they have conducted? | SELECT T1.Name ,  T2.Orchestra FROM conductor AS T1 JOIN orchestra AS T2 ON T1.Conductor_ID  =  T2.Conductor_ID |  | 1. SELECT[tbl:​conductor] <br>2. PROJECT[col:​conductor:​Name, #1] <br>3. PROJECT[col:​orchestra:​Orchestra, #1] <br>4. UNION[#2, #3] <br> | + | medium | 
  | SPIDER_dev_842 | Show the names of conductors that have conducted more than one orchestras. | SELECT T1.Name FROM conductor AS T1 JOIN orchestra AS T2 ON T1.Conductor_ID  =  T2.Conductor_ID GROUP BY T2.Conductor_ID HAVING COUNT(*)  >  1 |  | 1. SELECT[tbl:​conductor] <br>2. PROJECT[tbl:​orchestra, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​1] <br>5. PROJECT[col:​conductor:​Name, #4] <br> | + | medium | 
  | SPIDER_dev_843 | What are the names of conductors who have conducted at more than one orchestra? | SELECT T1.Name FROM conductor AS T1 JOIN orchestra AS T2 ON T1.Conductor_ID  =  T2.Conductor_ID GROUP BY T2.Conductor_ID HAVING COUNT(*)  >  1 |  | 1. SELECT[tbl:​conductor] <br>2. PROJECT[tbl:​orchestra, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​1] <br>5. PROJECT[col:​conductor:​Name, #4] <br> | + | medium | 
  | SPIDER_dev_844 | Show the name of the conductor that has conducted the most number of orchestras. | SELECT T1.Name FROM conductor AS T1 JOIN orchestra AS T2 ON T1.Conductor_ID  =  T2.Conductor_ID GROUP BY T2.Conductor_ID ORDER BY COUNT(*) DESC LIMIT 1 |  | 1. SELECT[tbl:​conductor] <br>2. PROJECT[tbl:​orchestra, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​conductor:​Name, #4] <br> | + | extra | 
  | SPIDER_dev_845 | What is the name of the conductor who has conducted the most orchestras? | SELECT T1.Name FROM conductor AS T1 JOIN orchestra AS T2 ON T1.Conductor_ID  =  T2.Conductor_ID GROUP BY T2.Conductor_ID ORDER BY COUNT(*) DESC LIMIT 1 |  | 1. SELECT[tbl:​conductor] <br>2. PROJECT[tbl:​orchestra, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​conductor:​Name, #4] <br> | + | extra | 
  | SPIDER_dev_846 | Please show the name of the conductor that has conducted orchestras founded after 2008. | SELECT T1.Name FROM conductor AS T1 JOIN orchestra AS T2 ON T1.Conductor_ID  =  T2.Conductor_ID WHERE Year_of_Founded  >  2008 |  | 1. SELECT[tbl:​conductor] <br>2. PROJECT[tbl:​orchestra, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​2008:​col:​orchestra:​Year_of_Founded] <br>4. PROJECT[col:​conductor:​Name, #3] <br> | + | medium | 
  | SPIDER_dev_847 | What are the names of conductors who have conducted orchestras founded after the year 2008? | SELECT T1.Name FROM conductor AS T1 JOIN orchestra AS T2 ON T1.Conductor_ID  =  T2.Conductor_ID WHERE Year_of_Founded  >  2008 |  | 1. SELECT[tbl:​orchestra] <br>2. PROJECT[col:​orchestra:​Year_of_Founded, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​2008:​col:​orchestra:​Year_of_Founded] <br>4. PROJECT[tbl:​conductor, #3] <br>5. PROJECT[col:​conductor:​Name, #4] <br> | + | medium | 
  | SPIDER_dev_848 | Please show the different record companies and the corresponding number of orchestras. | SELECT Record_Company ,  COUNT(*) FROM orchestra GROUP BY Record_Company |  | 1.*(distinct)* SELECT[col:​orchestra:​Record_Company] <br>2. PROJECT[tbl:​orchestra, #1] <br>3. GROUP[count, #2, #1] <br>4. UNION[#1, #3] <br> | + | medium | 
  | SPIDER_dev_849 | How many orchestras does each record company manage? | SELECT Record_Company ,  COUNT(*) FROM orchestra GROUP BY Record_Company |  | 1. SELECT[col:​orchestra:​Record_Company] <br>2. PROJECT[tbl:​orchestra, #1] <br>3. GROUP[count, #2, #1] <br>4. UNION[#1, #3] <br> | + | medium | 
  | SPIDER_dev_850 | Please show the record formats of orchestras in ascending order of count. | SELECT Major_Record_Format FROM orchestra GROUP BY Major_Record_Format ORDER BY COUNT(*) ASC |  | 1. SELECT[tbl:​orchestra] <br>2. PROJECT[col:​orchestra:​Major_Record_Format, #1] <br>3. SORT[#2, #2, sortdir:​ascending] <br> | - | medium | 
  | SPIDER_dev_851 | What are the major record formats of orchestras, sorted by their frequency? | SELECT Major_Record_Format FROM orchestra GROUP BY Major_Record_Format ORDER BY COUNT(*) ASC |  | 1. SELECT[tbl:​orchestra] <br>2. PROJECT[col:​orchestra:​Major_Record_Format, #1] <br>3. SORT[#2, #2, sortdir:​ascending] <br> | - | medium | 
  | SPIDER_dev_852 | List the record company shared by the most number of orchestras. | SELECT Record_Company FROM orchestra GROUP BY Record_Company ORDER BY COUNT(*) DESC LIMIT 1 |  | 1. SELECT[col:​orchestra:​Record_Company] <br>2. PROJECT[tbl:​orchestra, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br> | + | hard | 
  | SPIDER_dev_853 | What is the record company used by the greatest number of orchestras? | SELECT Record_Company FROM orchestra GROUP BY Record_Company ORDER BY COUNT(*) DESC LIMIT 1 |  | 1. SELECT[col:​orchestra:​Record_Company] <br>2. PROJECT[tbl:​orchestra, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br> | + | hard | 
  | SPIDER_dev_854 | List the names of orchestras that have no performance. | SELECT Orchestra FROM orchestra WHERE Orchestra_ID NOT IN (SELECT Orchestra_ID FROM performance) |  | 1. SELECT[tbl:​orchestra] <br>2. COMPARATIVE[#1, #1, tbl:​performance] <br>3. DISCARD[#1, #2] <br>4. PROJECT[col:​orchestra:​Orchestra, #3] <br> | + | hard | 
  | SPIDER_dev_855 | What are the orchestras that do not have any performances? | SELECT Orchestra FROM orchestra WHERE Orchestra_ID NOT IN (SELECT Orchestra_ID FROM performance) |  | 1. SELECT[col:​orchestra:​Orchestra] <br>2. COMPARATIVE[#1, #1, tbl:​performance] <br>3. DISCARD[#1, #2] <br> | + | hard | 
  | SPIDER_dev_856 | Show the record companies shared by orchestras founded before 2003 and after 2003. | SELECT Record_Company FROM orchestra WHERE Year_of_Founded  <  2003 INTERSECT SELECT Record_Company FROM orchestra WHERE Year_of_Founded  >  2003 |  | 1. SELECT[tbl:​orchestra] <br>2. COMPARATIVE[#1, #1, comparative:​<:​2003:​col:​orchestra:​Year_of_Founded] <br>3. COMPARATIVE[#1, #1, comparative:​<=:​2003:​col:​orchestra:​Year_of_Founded] <br>4. PROJECT[col:​orchestra:​Record_Company, #1] <br>5. INTERSECTION[#4, #2, #3] <br> | - | hard | 
  | SPIDER_dev_857 | What are the record companies that are used by both orchestras founded before 2003 and those founded after 2003? | SELECT Record_Company FROM orchestra WHERE Year_of_Founded  <  2003 INTERSECT SELECT Record_Company FROM orchestra WHERE Year_of_Founded  >  2003 |  | 1. SELECT[tbl:​orchestra] <br>2. COMPARATIVE[#1, #1, comparative:​<:​2003:​col:​orchestra:​Year_of_Founded] <br>3. COMPARATIVE[#1, #1, comparative:​>:​2003:​col:​orchestra:​Year_of_Founded] <br>4. PROJECT[col:​orchestra:​Record_Company, #1] <br>5. INTERSECTION[#4, #2, #3] <br> | + | hard | 
  | SPIDER_dev_858 | Find the number of orchestras whose record format is "CD" or "DVD". | SELECT COUNT(*) FROM orchestra WHERE Major_Record_Format  =  "CD" OR Major_Record_Format  =  "DVD" |  | 1. SELECT[col:​orchestra:​Orchestra] <br>2. PROJECT[col:​orchestra:​Major_Record_Format, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​CD:​col:​orchestra:​Major_Record_Format] <br>4. COMPARATIVE[#1, #2, comparative:​=:​DVD:​col:​orchestra:​Major_Record_Format] <br>5. UNION[#3, #4] <br>6. AGGREGATE[count, #5] <br> | + | medium | 
  | SPIDER_dev_859 | Count the number of orchestras that have CD or DVD as their record format. | SELECT COUNT(*) FROM orchestra WHERE Major_Record_Format  =  "CD" OR Major_Record_Format  =  "DVD" |  | 1. SELECT[col:​orchestra:​Orchestra] <br>2. PROJECT[col:​orchestra:​Major_Record_Format, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​CD:​col:​orchestra:​Major_Record_Format] <br>4. COMPARATIVE[#1, #2, comparative:​=:​DVD:​col:​orchestra:​Major_Record_Format] <br>5. UNION[#3, #4] <br>6. AGGREGATE[count, #5] <br> | + | medium | 
  | SPIDER_dev_860 | Show the years in which orchestras that have given more than one performance are founded. | SELECT Year_of_Founded FROM orchestra AS T1 JOIN performance AS T2 ON T1.Orchestra_ID  =  T2.Orchestra_ID GROUP BY T2.Orchestra_ID HAVING COUNT(*)  >  1 |  | 1. SELECT[tbl:​orchestra] <br>2. PROJECT[tbl:​performance, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​1] <br>5. PROJECT[col:​orchestra:​Year_of_Founded, #4] <br> | + | medium | 
  | SPIDER_dev_861 | What are years of founding for orchestras that have had more than a single performance? | SELECT Year_of_Founded FROM orchestra AS T1 JOIN performance AS T2 ON T1.Orchestra_ID  =  T2.Orchestra_ID GROUP BY T2.Orchestra_ID HAVING COUNT(*)  >  1 |  | 1. SELECT[tbl:​orchestra] <br>2. COMPARATIVE[#1, #1, tbl:​performance] <br>3. PROJECT[col:​orchestra:​Year_of_Founded, #2] <br> | - | medium | 
 ***
 Exec acc: **0.8500**
