 | Idx | Table      | Column | Primary Key | Foreign Key | 
 | ----------- | ----------- | ----------- | ----------- | ----------- | 
  | 0 |  | * |   |   | 
 | 1 | **singer** | Singer_ID | + |   | 
 | 2 |   | Name |   |   | 
 | 3 |   | Birth_Year |   |   | 
 | 4 |   | Net_Worth_Millions |   |   | 
 | 5 |   | Citizenship |   |   | 
 | 6 | **song** | Song_ID | + |   | 
 | 7 |   | Title |   |   | 
 | 8 |   | Singer_ID |   | --> 1 | 
 | 9 |   | Sales |   |   | 
 | 10 |   | Highest_Position |   |   | 
 
  | Index | Question  | SQL | gold QDMR | pred QDMR | Exec | SQL hardness |
  | ----------- | ----------- | ----------- |  ----------- | ----------- | ----------- | ----------- | 
 | SPIDER_dev_1000 | How many singers are there? | SELECT count(*) FROM singer |  | 1. SELECT[tbl:​singer] <br>2. AGGREGATE[count, #1] <br> | + | easy | 
  | SPIDER_dev_1001 | What is the count of singers? | SELECT count(*) FROM singer |  | 1. SELECT[tbl:​singer] <br>2. AGGREGATE[count, #1] <br> | + | easy | 
  | SPIDER_dev_1002 | List the name of singers in ascending order of net worth. | SELECT Name FROM singer ORDER BY Net_Worth_Millions ASC |  | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Name, #1] <br>3. PROJECT[col:​singer:​Net_Worth_Millions, #1] <br>4. SORT[#2, #3, sortdir:​ascending] <br> | + | easy | 
  | SPIDER_dev_1003 | What are the names of singers ordered by ascending net worth? | SELECT Name FROM singer ORDER BY Net_Worth_Millions ASC |  | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Name, #1] <br>3. PROJECT[col:​singer:​Net_Worth_Millions, #1] <br>4. SORT[#2, #3, sortdir:​ascending] <br> | + | easy | 
  | SPIDER_dev_1004 | What are the birth year and citizenship of singers? | SELECT Birth_Year ,  Citizenship FROM singer |  | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Birth_Year, #1] <br>3. PROJECT[col:​singer:​Citizenship, #1] <br>4. UNION[#2, #3] <br> | + | medium | 
  | SPIDER_dev_1005 | What are the birth years and citizenships of the singers? | SELECT Birth_Year ,  Citizenship FROM singer |  | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Birth_Year, #1] <br>3. PROJECT[col:​singer:​Citizenship, #1] <br>4. UNION[#2, #3] <br> | + | medium | 
  | SPIDER_dev_1006 | List the name of singers whose citizenship is not "France". | SELECT Name FROM singer WHERE Citizenship != "France" |  | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Citizenship, #1] <br>3. COMPARATIVE[#1, #2, comparative:​!=:​France:​col:​singer:​Citizenship] <br>4. PROJECT[col:​singer:​Name, #3] <br> | + | easy | 
  | SPIDER_dev_1007 | What are the names of the singers who are not French citizens? | SELECT Name FROM singer WHERE Citizenship != "France" |  | 1. SELECT[tbl:​singer] <br>2. COMPARATIVE[#1, #1, col:​singer:​Citizenship] <br>3. DISCARD[#1, #2] <br>4. PROJECT[col:​singer:​Name, #3] <br> | - | easy | 
  | SPIDER_dev_1008 | Show the name of singers whose birth year is either 1948 or 1949? | SELECT Name FROM singer WHERE Birth_Year  =  1948 OR Birth_Year  =  1949 |  | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Birth_Year, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​1948:​col:​singer:​Birth_Year] <br>4. COMPARATIVE[#1, #2, comparative:​=:​1949:​col:​singer:​Birth_Year] <br>5. UNION[#3, #4] <br>6. PROJECT[col:​singer:​Name, #5] <br> | + | medium | 
  | SPIDER_dev_1009 | What are the names of the singers whose birth years are either 1948 or 1949? | SELECT Name FROM singer WHERE Birth_Year  =  1948 OR Birth_Year  =  1949 |  | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Birth_Year, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​1948:​col:​singer:​Birth_Year] <br>4. COMPARATIVE[#1, #2, comparative:​=:​1949:​col:​singer:​Birth_Year] <br>5. UNION[#3, #4] <br>6. PROJECT[col:​singer:​Name, #5] <br> | + | medium | 
  | SPIDER_dev_1010 | What is the name of the singer with the largest net worth? | SELECT Name FROM singer ORDER BY Net_Worth_Millions DESC LIMIT 1 |  | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Net_Worth_Millions, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. PROJECT[col:​singer:​Name, #3] <br> | + | medium | 
  | SPIDER_dev_1011 | What is the name of the singer who is worth the most? | SELECT Name FROM singer ORDER BY Net_Worth_Millions DESC LIMIT 1 |  | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Net_Worth_Millions, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. PROJECT[col:​singer:​Name, #3] <br> | + | medium | 
  | SPIDER_dev_1012 | Show different citizenship of singers and the number of singers of each citizenship. | SELECT Citizenship ,  COUNT(*) FROM singer GROUP BY Citizenship |  | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Citizenship, #1] <br>3. GROUP[count, #1, #2] <br>4. UNION[#2, #3] <br> | + | medium | 
  | SPIDER_dev_1013 | For each citizenship, how many singers are from that country? | SELECT Citizenship ,  COUNT(*) FROM singer GROUP BY Citizenship |  | 1. SELECT[col:​singer:​Citizenship] <br>2. PROJECT[tbl:​singer, #1] <br>3. GROUP[count, #2, #1] <br>4. UNION[#1, #3] <br> | + | medium | 
  | SPIDER_dev_1014 | Please show the most common citizenship of singers. | SELECT Citizenship FROM singer GROUP BY Citizenship ORDER BY COUNT(*) DESC LIMIT 1 |  | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Citizenship, #1] <br>3. GROUP[count, #1, #2] <br>4. SUPERLATIVE[comparative:​max:​None, #2, #3] <br> | + | hard | 
  | SPIDER_dev_1015 | What is the most common singer citizenship? | select citizenship from singer group by citizenship order by count(*) desc limit 1 |  | 1. SELECT[col:​singer:​Citizenship] <br>2. SELECT[col:​singer:​Citizenship] <br>3. GROUP[count, #1, #2] <br>4. SUPERLATIVE[comparative:​max:​None, #2, #3] <br> | + | hard | 
  | SPIDER_dev_1016 | Show different citizenships and the maximum net worth of singers of each citizenship. | SELECT Citizenship ,  max(Net_Worth_Millions) FROM singer GROUP BY Citizenship |  | 1. SELECT[col:​singer:​Citizenship] <br>2. PROJECT[tbl:​singer, #1] <br>3. PROJECT[col:​singer:​Net_Worth_Millions, #2] <br>4. GROUP[max, #3, #1] <br>5. UNION[#1, #4] <br> | + | medium | 
  | SPIDER_dev_1017 | For each citizenship, what is the maximum net worth? | SELECT Citizenship ,  max(Net_Worth_Millions) FROM singer GROUP BY Citizenship |  | 1. SELECT[col:​singer:​Citizenship] <br>2. PROJECT[col:​singer:​Net_Worth_Millions, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br> | - | medium | 
  | SPIDER_dev_1018 | Show titles of songs and names of singers. | SELECT T2.Title ,  T1.Name FROM singer AS T1 JOIN song AS T2 ON T1.Singer_ID  =  T2.Singer_ID |  | 1. SELECT[tbl:​song] <br>2. PROJECT[col:​song:​Title, #1] <br>3. PROJECT[col:​singer:​Name, #1] <br>4. UNION[#2, #3] <br> | + | medium | 
  | SPIDER_dev_1019 | What are the song titles and singer names? | SELECT T2.Title ,  T1.Name FROM singer AS T1 JOIN song AS T2 ON T1.Singer_ID  =  T2.Singer_ID |  | 1. SELECT[tbl:​song] <br>2. PROJECT[col:​song:​Title, #1] <br>3. PROJECT[col:​singer:​Name, #1] <br>4. UNION[#2, #3] <br> | + | medium | 
  | SPIDER_dev_1020 | Show distinct names of singers that have songs with sales more than 300000. | SELECT DISTINCT T1.Name FROM singer AS T1 JOIN song AS T2 ON T1.Singer_ID  =  T2.Singer_ID WHERE T2.Sales  >  300000 |  | 1. SELECT[tbl:​song] <br>2. PROJECT[col:​song:​Sales, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​300000:​col:​song:​Sales] <br>4. PROJECT[tbl:​singer, #3] <br>5. PROJECT[col:​singer:​Name, #4] <br> | - | medium | 
  | SPIDER_dev_1021 | what are the different names of the singers that have sales more than 300000? | SELECT DISTINCT T1.Name FROM singer AS T1 JOIN song AS T2 ON T1.Singer_ID  =  T2.Singer_ID WHERE T2.Sales  >  300000 |  | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​song:​Sales, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​300000:​col:​song:​Sales] <br>4.*(distinct)* PROJECT[col:​singer:​Name, #3] <br> | + | medium | 
  | SPIDER_dev_1022 | Show the names of singers that have more than one song. | SELECT T1.Name FROM singer AS T1 JOIN song AS T2 ON T1.Singer_ID  =  T2.Singer_ID GROUP BY T1.Name HAVING COUNT(*)  >  1 |  | 1. SELECT[tbl:​singer] <br>2. PROJECT[tbl:​song, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​1] <br>5. PROJECT[col:​singer:​Name, #4] <br> | + | medium | 
  | SPIDER_dev_1023 | What are the names of the singers that have more than one songs? | SELECT T1.Name FROM singer AS T1 JOIN song AS T2 ON T1.Singer_ID  =  T2.Singer_ID GROUP BY T1.Name HAVING COUNT(*)  >  1 |  | 1. SELECT[tbl:​singer] <br>2. PROJECT[tbl:​song, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​1] <br>5. PROJECT[col:​singer:​Name, #4] <br> | + | medium | 
  | SPIDER_dev_1024 | Show the names of singers and the total sales of their songs. | SELECT T1.Name ,  sum(T2.Sales) FROM singer AS T1 JOIN song AS T2 ON T1.Singer_ID  =  T2.Singer_ID GROUP BY T1.Name |  | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Name, #1] <br>3. PROJECT[col:​song:​Sales, #1] <br>4. GROUP[sum, #3, #1] <br>5. UNION[#2, #4] <br> | + | medium | 
  | SPIDER_dev_1025 | For each singer name, what is the total sales for their songs? | SELECT T1.Name ,  sum(T2.Sales) FROM singer AS T1 JOIN song AS T2 ON T1.Singer_ID  =  T2.Singer_ID GROUP BY T1.Name |  | 1. SELECT[tbl:​song] <br>2. PROJECT[col:​singer:​Name, #1] <br>3. PROJECT[col:​song:​Sales, #1] <br>4. GROUP[sum, #3, #1] <br>5. UNION[#2, #4] <br> | - | medium | 
  | SPIDER_dev_1026 | List the name of singers that do not have any song. | SELECT Name FROM singer WHERE Singer_ID NOT IN (SELECT Singer_ID FROM song) |  | 1. SELECT[tbl:​singer] <br>2. COMPARATIVE[#1, #1, tbl:​song] <br>3. DISCARD[#1, #2] <br>4. PROJECT[col:​singer:​Name, #3] <br> | + | hard | 
  | SPIDER_dev_1027 | What is the sname of every sing that does not have any song? | SELECT Name FROM singer WHERE Singer_ID NOT IN (SELECT Singer_ID FROM song) |  | 1. SELECT[tbl:​singer] <br>2. COMPARATIVE[#1, #1, tbl:​song] <br>3. DISCARD[#1, #2] <br>4. PROJECT[col:​singer:​Name, #3] <br> | + | hard | 
  | SPIDER_dev_1028 | Show the citizenship shared by singers with birth year before 1945 and after 1955. | SELECT Citizenship FROM singer WHERE Birth_Year  <  1945 INTERSECT SELECT Citizenship FROM singer WHERE Birth_Year  >  1955 |  | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Birth_Year, #1] <br>3. COMPARATIVE[#1, #2, comparative:​<:​1955:​col:​singer:​Birth_Year] <br>4. COMPARATIVE[#1, #2, comparative:​>:​1955:​col:​singer:​Birth_Year] <br>5. PROJECT[col:​singer:​Citizenship, #1] <br>6. INTERSECTION[#5, #3, #4] <br> | + | hard | 
  | SPIDER_dev_1029 | What are the citizenships that are shared by singers with a birth year before 1945 and after 1955? | SELECT Citizenship FROM singer WHERE Birth_Year  <  1945 INTERSECT SELECT Citizenship FROM singer WHERE Birth_Year  >  1955 |  | 1. SELECT[tbl:​singer] <br>2. PROJECT[col:​singer:​Birth_Year, #1] <br>3. COMPARATIVE[#1, #2, comparative:​<:​1955:​col:​singer:​Birth_Year] <br>4. PROJECT[col:​singer:​Birth_Year, #1] <br>5. COMPARATIVE[#1, #4, comparative:​<:​1945:​col:​singer:​Birth_Year] <br>6. PROJECT[col:​singer:​Citizenship, #1] <br>7. INTERSECTION[#6, #3, #5] <br> | - | hard | 
 ***
 Exec acc: **0.8333**
