| 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] 2. AGGREGATE[count, #1] |
+ | easy | |
| SPIDER_dev_1001 | What is the count of singers? | SELECT count(*) FROM singer | 1. SELECT[tbl:singer] 2. AGGREGATE[count, #1] |
+ | 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] 2. PROJECT[col:singer:Name, #1] 3. PROJECT[col:singer:Net_Worth_Millions, #1] 4. SORT[#2, #3, sortdir:ascending] |
+ | 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] 2. PROJECT[col:singer:Name, #1] 3. PROJECT[col:singer:Net_Worth_Millions, #1] 4. SORT[#2, #3, sortdir:ascending] |
+ | easy | |
| SPIDER_dev_1004 | What are the birth year and citizenship of singers? | SELECT Birth_Year , Citizenship FROM singer | 1. SELECT[tbl:singer] 2. PROJECT[col:singer:Birth_Year, #1] 3. PROJECT[col:singer:Citizenship, #1] 4. UNION[#2, #3] |
+ | medium | |
| SPIDER_dev_1005 | What are the birth years and citizenships of the singers? | SELECT Birth_Year , Citizenship FROM singer | 1. SELECT[tbl:singer] 2. PROJECT[col:singer:Birth_Year, #1] 3. PROJECT[col:singer:Citizenship, #1] 4. UNION[#2, #3] |
+ | 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] 2. PROJECT[col:singer:Citizenship, #1] 3. COMPARATIVE[#1, #2, comparative:!=:France:col:singer:Citizenship] 4. PROJECT[col:singer:Name, #3] |
+ | 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] 2. COMPARATIVE[#1, #1, col:singer:Citizenship] 3. PROJECT[col:singer:Name, #2] |
- | 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] 2. PROJECT[col:singer:Birth_Year, #1] 3. COMPARATIVE[#1, #2, comparative:=:1949:col:singer:Birth_Year] 4. COMPARATIVE[#1, #2, comparative:=:1948:col:singer:Birth_Year] 5. UNION[#3, #4] 6. PROJECT[col:singer:Name, #5] |
+ | 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] 2. PROJECT[col:singer:Birth_Year, #1] 3. COMPARATIVE[#1, #2, comparative:=:1948:col:singer:Birth_Year] 4. COMPARATIVE[#1, #2, comparative:=:1949:col:singer:Birth_Year] 5. UNION[#3, #4] 6. PROJECT[col:singer:Name, #5] |
+ | 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] 2. PROJECT[col:singer:Net_Worth_Millions, #1] 3. SUPERLATIVE[comparative:max:None, #1, #2] 4. PROJECT[col:singer:Name, #3] |
+ | 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] 2. PROJECT[col:singer:Net_Worth_Millions, #1] 3. SUPERLATIVE[comparative:max:None, #1, #2] 4. PROJECT[col:singer:Name, #3] |
+ | 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] 2. PROJECT[col:singer:Citizenship, #1] 3. GROUP[count, #1, #2] 4. UNION[#2, #3] |
+ | 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] 2. PROJECT[tbl:singer, #1] 3. GROUP[count, #2, #1] 4. UNION[#1, #3] |
+ | 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[col:singer:Citizenship] 2. PROJECT[tbl:singer, #1] 3. GROUP[count, #2, #1] 4. SUPERLATIVE[comparative:max:None, #1, #3] |
+ | 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] 2. PROJECT[col:singer:Citizenship, #1] 3. GROUP[count, #2, #1] 4. SUPERLATIVE[comparative:max:None, #1, #3] |
+ | 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[tbl:singer] 2. PROJECT[tbl:singer, #1] 3. PROJECT[col:singer:Net_Worth_Millions, #2] 4. GROUP[max, #3, #1] 5. UNION[#1, #4] |
- | 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] 2. PROJECT[col:singer:Net_Worth_Millions, #1] 3. GROUP[max, #2, #1] 4. UNION[#1, #3] |
+ | 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:singer] 2. PROJECT[tbl:song, #1] 3. PROJECT[col:singer:Name, #1] 4. UNION[#2, #3] |
- | 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] 2. PROJECT[col:song:Title, #1] 3. PROJECT[tbl:singer, #1] 4. PROJECT[col:singer:Name, #3] 5. UNION[#2, #4] |
+ | 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:singer] 2. PROJECT[tbl:song, #1] 3. PROJECT[col:song:Sales, #2] 4. COMPARATIVE[#1, #3, comparative:>:300000:col:song:Sales] 5. PROJECT[col:singer:Name, #4] |
+ | 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] 2. PROJECT[col:song:Sales, #1] 3. COMPARATIVE[#1, #2, comparative:>:300000:col:song:Sales] 4.(distinct) PROJECT[col:singer:Name, #3] |
+ | 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] 2. PROJECT[tbl:song, #1] 3. GROUP[count, #2, #1] 4. COMPARATIVE[#1, #3, comparative:>:1] 5. PROJECT[col:singer:Name, #4] |
+ | 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] 2. PROJECT[tbl:song, #1] 3. GROUP[count, #2, #1] 4. COMPARATIVE[#1, #3, comparative:>:1] 5. PROJECT[col:singer:Name, #4] |
+ | 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] 2. PROJECT[col:singer:Name, #1] 3. PROJECT[tbl:song, #1] 4. PROJECT[col:song:Sales, #3] 5. GROUP[sum, #4, #1] 6. UNION[#2, #5] |
+ | 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[col:singer:Name] 2. PROJECT[tbl:song, #1] 3. PROJECT[col:song:Sales, #2] 4. GROUP[sum, #3, #1] 5. UNION[#1, #4] |
+ | 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] 2. COMPARATIVE[#1, #1, tbl:song] 3. DISCARD[#1, #2] 4. PROJECT[col:singer:Name, #3] |
+ | 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] 2. COMPARATIVE[#1, #1, tbl:song] 3. DISCARD[#1, #2] 4. PROJECT[col:singer:Name, #3] |
+ | 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] 2. PROJECT[col:singer:Birth_Year, #1] 3. COMPARATIVE[#1, #2, comparative:<:1945:col:singer:Birth_Year] 4. COMPARATIVE[#1, #2, comparative:>:1955:col:singer:Birth_Year] 5. PROJECT[col:singer:Citizenship, #3] 6. PROJECT[col:singer:Citizenship, #4] 7. PROJECT[col:singer:Citizenship, #1] 8. INTERSECTION[#7, #5, #6] |
+ | 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] 2. PROJECT[col:singer:Birth_Year, #1] 3. COMPARATIVE[#1, #2, comparative:<:1945:col:singer:Birth_Year] 4. COMPARATIVE[#1, #2, comparative:>:1955:col:singer:Birth_Year] 5. PROJECT[col:singer:Citizenship, #1] 6. INTERSECTION[#5, #3, #4] |
+ | hard |
Exec acc: 0.9000