Skip to content

Latest commit

 

History

History
71 lines (70 loc) · 28.1 KB

concert_singer_results.md

File metadata and controls

71 lines (70 loc) · 28.1 KB
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]
2. AGGREGATE[count, #1]
1. SELECT[tbl:​singer]
2. AGGREGATE[count, #1]
+ easy
SPIDER_dev_1 What is the total number of singers? SELECT count(*) FROM singer 1. SELECT[tbl:​singer]
2. AGGREGATE[count, #1]
1. SELECT[tbl:​singer]
2. AGGREGATE[count, #1]
+ 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]
2. PROJECT[col:​singer:​Name, #1]
3. PROJECT[col:​singer:​Country, #1]
4. PROJECT[col:​singer:​Age, #1]
5. UNION[#2, #3, #4]
6. SORT[#5, #4, sortdir:​descending]
1. SELECT[tbl:​singer]
2. PROJECT[col:​singer:​Name, #1]
3. PROJECT[col:​singer:​Country, #1]
4. PROJECT[col:​singer:​Age, #1]
5. UNION[#2, #3, #4]
6. SORT[#5, #4, sortdir:​ascending]
- 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]
2. PROJECT[col:​singer:​Name, #1]
3. PROJECT[col:​singer:​Country, #1]
4. PROJECT[col:​singer:​Age, #1]
5. UNION[#2, #3, #4]
6. SORT[#5, #4, sortdir:​descending]
1. SELECT[tbl:​singer]
2. PROJECT[col:​singer:​Name, #1]
3. PROJECT[col:​singer:​Country, #1]
4. PROJECT[col:​singer:​Age, #1]
5. UNION[#2, #3, #4]
6. SORT[#5, #4, sortdir:​descending]
+ 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]
2. FILTER[#1, comparative:​=:​France:​col:​singer:​Country]
3. PROJECT[col:​singer:​Age, #2]
4. AGGREGATE[avg, #3]
5. AGGREGATE[min, #3]
6. AGGREGATE[max, #3]
7. UNION[#4, #5, #6]
1. SELECT[tbl:​singer]
2. COMPARATIVE[#1, #1, comparative:​=:​France:​col:​singer:​Country]
3. PROJECT[col:​singer:​Age, #2]
4. AGGREGATE[avg, #3]
5. AGGREGATE[min, #3]
6. AGGREGATE[max, #3]
7. UNION[#4, #5, #6]
+ 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]
2. FILTER[#1, val:​singer:​Country:​France]
3. PROJECT[col:​singer:​Age, #2]
4. AGGREGATE[avg, #3]
5. AGGREGATE[min, #3]
6. AGGREGATE[max, #3]
7. UNION[#4, #5, #6]
1. SELECT[tbl:​singer]
2. PROJECT[col:​singer:​Age, #1]
3. AGGREGATE[avg, #2]
4. AGGREGATE[min, #2]
5. AGGREGATE[max, #2]
6. UNION[#3, #4, #5]
- 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]
2. PROJECT[tbl:​singer, #1]
3. PROJECT[col:​singer:​Age, #2]
4. SUPERLATIVE[comparative:​min:​None, #1, #3]
5. PROJECT[col:​singer:​Song_Name, #4]
6. PROJECT[col:​singer:​Song_release_year, #4]
7. UNION[#5, #6]
1. SELECT[tbl:​singer]
2. PROJECT[tbl:​singer, #1]
3. GROUP[count, #2, #1]
4. SUPERLATIVE[comparative:​min:​None, #1, #3]
5. PROJECT[col:​singer:​Song_Name, #4]
6. PROJECT[col:​singer:​Song_release_year, #4]
7. UNION[#5, #6]
+ 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]
2. PROJECT[col:​singer:​Age, #1]
3. SUPERLATIVE[comparative:​min:​None, #1, #2]
4. PROJECT[col:​singer:​Song_Name, #3]
5. PROJECT[col:​singer:​Song_Name, #4]
6. PROJECT[col:​singer:​Song_release_year, #4]
7. UNION[#5, #6]
1. SELECT[tbl:​singer]
2. PROJECT[col:​singer:​Age, #1]
3. SUPERLATIVE[comparative:​min:​None, #1, #2]
4. PROJECT[col:​singer:​Song_Name, #3]
5. PROJECT[col:​singer:​Song_Name, #4]
6. PROJECT[col:​singer:​Song_release_year, #4]
7. UNION[#5, #6]
+ 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]
2. PROJECT[col:​singer:​Age, #1]
3. COMPARATIVE[#1, #2, comparative:​>:​20:​col:​singer:​Age]
4.(distinct) PROJECT[col:​singer:​Country, #3]
1. SELECT[tbl:​singer]
2. COMPARATIVE[#1, #1, comparative:​>:​20:​col:​singer:​Age]
3. PROJECT[col:​singer:​Country, #2]
4.(distinct) PROJECT[None, #3]
+ 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]
2. PROJECT[tbl:​singer, #1]
3. PROJECT[col:​singer:​Age, #2]
4. COMPARATIVE[#1, #3, comparative:​>:​20:​col:​singer:​Age]
5.(distinct) PROJECT[different #REF, #4]
1. SELECT[tbl:​singer]
2. PROJECT[col:​singer:​Age, #1]
3. COMPARATIVE[#1, #2, comparative:​>:​20:​col:​singer:​Age]
4.(distinct) PROJECT[col:​singer:​Country, #3]
+ 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]
2. PROJECT[tbl:​singer, #1]
3. GROUP[count, #2, #1]
4. UNION[#1, #3]
1. SELECT[col:​singer:​Country]
2. PROJECT[tbl:​singer, #1]
3. GROUP[count, #2, #1]
4. UNION[#1, #3]
+ medium
SPIDER_dev_11 How many singers are from each country? SELECT country , count(*) FROM singer GROUP BY country 1. SELECT[col:​singer:​Country]
2. PROJECT[tbl:​singer, #1]
3. GROUP[count, #2, #1]
4. UNION[#1, #3]
1. SELECT[col:​singer:​Country]
2. PROJECT[tbl:​singer, #1]
3. GROUP[count, #2, #1]
4. UNION[#1, #3]
+ 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]
2. PROJECT[col:​singer:​Age, #1]
3. AGGREGATE[avg, #2]
4. COMPARATIVE[#1, #2, comparative:​>:​#3]
5. PROJECT[col:​singer:​Song_Name, #4]
6. PROJECT[col:​singer:​Song_Name, #5]
1. SELECT[tbl:​singer]
2. PROJECT[col:​singer:​Age, #1]
3. AGGREGATE[avg, #2]
4. COMPARATIVE[#1, #2, comparative:​>:​#3]
5. PROJECT[col:​singer:​Song_Name, #4]
+ 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]
2. PROJECT[col:​singer:​Age, #1]
3. AGGREGATE[avg, #2]
4. COMPARATIVE[#1, #2, comparative:​>:​#3]
5. PROJECT[col:​singer:​Song_Name, #4]
6. PROJECT[col:​singer:​Song_Name, #5]
1. SELECT[tbl:​singer]
2. PROJECT[col:​stadium:​Average, #1]
3. AGGREGATE[avg, #2]
4. COMPARATIVE[#1, #2, comparative:​>:​#3]
5. PROJECT[col:​singer:​Song_Name, #4]
- 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]
2. PROJECT[col:​stadium:​Capacity, #1]
3. COMPARATIVE[#1, #2, comparative:​<=:​10000:​col:​stadium:​Capacity]
4. COMPARATIVE[#1, #2, comparative:​>=:​5000:​col:​stadium:​Capacity]
5. INTERSECTION[#1, #3, #4]
6. PROJECT[col:​stadium:​Location, #5]
7. PROJECT[col:​stadium:​Name, #5]
8. UNION[#6, #7]
1. SELECT[tbl:​stadium]
2. PROJECT[col:​stadium:​Capacity, #1]
3. COMPARATIVE[#1, #2, comparative:​<=:​10000:​col:​stadium:​Capacity]
4. COMPARATIVE[#1, #2, comparative:​>=:​5000:​col:​stadium:​Capacity]
5. INTERSECTION[#1, #3, #4]
6. PROJECT[col:​stadium:​Location, #5]
7. PROJECT[col:​stadium:​Name, #5]
8. UNION[#6, #7]
+ 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]
2. PROJECT[col:​stadium:​Capacity, #1]
3. COMPARATIVE[#1, #2, comparative:​<=:​10000:​col:​stadium:​Capacity]
4. COMPARATIVE[#1, #2, comparative:​>=:​5000:​col:​stadium:​Capacity]
5. INTERSECTION[#1, #3, #4]
6. PROJECT[col:​stadium:​Location, #5]
7. PROJECT[col:​stadium:​Name, #5]
8. UNION[#6, #7]
1. SELECT[tbl:​stadium]
2. PROJECT[col:​stadium:​Capacity, #1]
3. COMPARATIVE[#1, #2, comparative:​<=:​10000:​col:​stadium:​Capacity]
4. COMPARATIVE[#1, #2, comparative:​>=:​5000:​col:​stadium:​Capacity]
5. INTERSECTION[#1, #3, #4]
6. PROJECT[col:​stadium:​Location, #5]
7. PROJECT[col:​stadium:​Name, #5]
8. UNION[#6, #7]
+ 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]
2. PROJECT[col:​stadium:​Capacity, #1]
3. AGGREGATE[avg, #2]
4. AGGREGATE[max, #2]
5. UNION[#3, #4]
1. SELECT[tbl:​stadium]
2. PROJECT[col:​stadium:​Average, #1]
3. AGGREGATE[avg, #2]
4. AGGREGATE[max, #2]
5. UNION[#3, #4]
- 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]
2. PROJECT[attendances of #REF, #1]
3. GROUP[col:​stadium:​Average, #2, #1]
4. SUPERLATIVE[comparative:​max:​None, #1, #3]
5. PROJECT[col:​stadium:​Name, #4]
6. PROJECT[col:​stadium:​Capacity, #4]
7. UNION[#5, #6]
1. SELECT[tbl:​stadium]
2. PROJECT[col:​stadium:​Average, #1]
3. GROUP[avg, #2, #1]
4. SUPERLATIVE[comparative:​max:​None, #1, #3]
5. PROJECT[col:​stadium:​Name, #4]
6. PROJECT[col:​stadium:​Capacity, #4]
7. UNION[#5, #6]
+ 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]
2. PROJECT[attendances of #REF, #1]
3. GROUP[col:​stadium:​Average, #2, #1]
4. SUPERLATIVE[comparative:​max:​None, #1, #3]
5. PROJECT[col:​stadium:​Name, #4]
6. PROJECT[col:​stadium:​Capacity, #4]
7. UNION[#5, #6]
1. SELECT[tbl:​stadium]
2. PROJECT[col:​stadium:​Average, #1]
3. GROUP[avg, #2, #1]
4. SUPERLATIVE[comparative:​max:​None, #1, #3]
5. PROJECT[col:​stadium:​Name, #4]
6. PROJECT[col:​stadium:​Capacity, #4]
7. UNION[#5, #6]
+ 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]
2. FILTER[#1, comparative:​=:​2014:​col:​concert:​Year]
3. FILTER[#1, comparative:​=:​2015:​col:​concert:​Year]
4. UNION[#2, #3]
5. AGGREGATE[count, #4]
1. SELECT[tbl:​concert]
2. PROJECT[col:​concert:​Year, #1]
3. COMPARATIVE[#1, #2, comparative:​=:​2014:​col:​concert:​Year]
4. COMPARATIVE[#1, #2, comparative:​=:​2015:​col:​concert:​Year]
5. UNION[#3, #4]
6. AGGREGATE[count, #5]
+ 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]
2. FILTER[#1, comparative:​=:​2014:​col:​concert:​Year]
3. FILTER[#1, comparative:​=:​2015:​col:​concert:​Year]
4. UNION[#2, #3]
5. AGGREGATE[count, #4]
1. SELECT[tbl:​concert]
2. COMPARATIVE[#1, #1, comparative:​=:​2014:​col:​concert:​Year]
3. COMPARATIVE[#1, #1, comparative:​=:​2015:​col:​concert:​Year]
4. UNION[#2, #3]
5. AGGREGATE[count, #4]
+ 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]
2. PROJECT[col:​stadium:​Name, #1]
3. PROJECT[tbl:​concert, #1]
4. GROUP[count, #3, #1]
5. UNION[#2, #4]
1. SELECT[col:​concert:​Stadium_ID]
2. PROJECT[col:​stadium:​Name, #1]
3. PROJECT[tbl:​concert, #1]
4. GROUP[count, #3, #1]
5. UNION[#2, #4]
+ 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]
2. PROJECT[tbl:​concert, #1]
3. GROUP[count, #2, #1]
4. UNION[#1, #3]
1. SELECT[col:​stadium:​Name]
2. PROJECT[tbl:​concert, #1]
3. GROUP[count, #2, #1]
4. UNION[#1, #3]
+ 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]
2. PROJECT[tbl:​concert, #1]
3. FILTER[#2, comparative:​>=:​2014:​col:​concert:​Year]
4. GROUP[count, #3, #1]
5. SUPERLATIVE[comparative:​max:​None, #1, #4]
6. PROJECT[col:​stadium:​Name, #5]
7. PROJECT[col:​stadium:​Capacity, #5]
8. UNION[#6, #7]
1. SELECT[tbl:​stadium]
2. PROJECT[tbl:​concert, #1]
3. PROJECT[col:​concert:​Year, #2]
4. COMPARATIVE[#2, #3, comparative:​>=:​2014:​col:​concert:​Year]
5. GROUP[count, #4, #1]
6. SUPERLATIVE[comparative:​max:​None, #1, #5]
7. PROJECT[col:​stadium:​Name, #6]
8. PROJECT[col:​stadium:​Capacity, #6]
9. UNION[#7, #8]
+ 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]
2. PROJECT[tbl:​concert, #1]
3. FILTER[#2, comparative:​>:​2013:​col:​concert:​Year]
4. GROUP[count, #3, #1]
5. COMPARATIVE[#1, #4, comparative:​max:​None]
6. PROJECT[col:​stadium:​Name, #5]
7. PROJECT[col:​stadium:​Capacity, #5]
8. UNION[#6, #7]
1. SELECT[tbl:​stadium]
2. PROJECT[tbl:​concert, #1]
3. COMPARATIVE[#2, #2, comparative:​>:​2013:​col:​singer:​Song_release_year]
4. GROUP[count, #3, #1]
5. SUPERLATIVE[comparative:​max:​None, #1, #4]
6. PROJECT[col:​stadium:​Name, #5]
7. PROJECT[col:​stadium:​Capacity, #5]
8. UNION[#6, #7]
+ 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]
2. PROJECT[tbl:​concert, #1]
3. GROUP[count, #2, #1]
4. SUPERLATIVE[comparative:​max:​None, #1, #3]
1. SELECT[col:​concert:​Year]
2. PROJECT[tbl:​concert, #1]
3. GROUP[count, #2, #1]
4. SUPERLATIVE[comparative:​max:​None, #1, #3]
+ 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]
2. PROJECT[col:​concert:​Year, #1]
3. GROUP[count, #1, #2]
4. SUPERLATIVE[comparative:​max:​None, #2, #3]
1. SELECT[col:​concert:​Year]
2. PROJECT[tbl:​concert, #1]
3. GROUP[count, #2, #1]
4. SUPERLATIVE[comparative:​max:​None, #1, #3]
+ 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]
2. FILTER[#1, tbl:​concert]
3. DISCARD[#1, #2]
4. PROJECT[col:​stadium:​Name, #3]
1. SELECT[tbl:​stadium]
2. COMPARATIVE[#1, #1, tbl:​concert]
3. DISCARD[#1, #2]
4. PROJECT[col:​stadium:​Name, #3]
+ 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]
2. PROJECT[tbl:​singer, #1]
3. PROJECT[col:​singer:​Age, #2]
4. COMPARATIVE[#1, #3, comparative:​>:​40:​col:​singer:​Age]
5. COMPARATIVE[#1, #3, comparative:​<:​30:​col:​singer:​Age]
6. INTERSECTION[#1, #4, #5]
1. SELECT[tbl:​singer]
2. PROJECT[col:​singer:​Age, #1]
3. COMPARATIVE[#1, #2, comparative:​>:​40:​col:​singer:​Age]
4. COMPARATIVE[#1, #1, comparative:​<:​30:​col:​singer:​Age]
5. PROJECT[col:​singer:​Country, #1]
6. INTERSECTION[#5, #3, #4]
+ 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]
2. FILTER[#1, tbl:​concert]
3. FILTER[#2, comparative:​=:​2014:​col:​concert:​Year]
4. DISCARD[#1, #3]
5. PROJECT[col:​stadium:​Name, #4]
1. SELECT[tbl:​stadium]
2. COMPARATIVE[#1, #1, tbl:​concert]
3. COMPARATIVE[#2, #2, comparative:​=:​2014:​col:​concert:​Year]
4. DISCARD[#2, #3]
5. PROJECT[col:​stadium:​Name, #4]
- 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]
2. PROJECT[col:​concert:​concert_Name, #1]
3. PROJECT[col:​concert:​Theme, #1]
4. PROJECT[tbl:​singer_in_concert, #1]
5. GROUP[count, #4, #1]
6. UNION[#2, #3, #5]
1. SELECT[tbl:​concert]
2. PROJECT[col:​concert:​concert_Name, #1]
3. PROJECT[col:​concert:​Theme, #1]
4. PROJECT[tbl:​singer_in_concert, #1]
5. GROUP[count, #4, #1]
6. UNION[#2, #3, #5]
+ 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]
2. PROJECT[col:​concert:​concert_Name, #1]
3. PROJECT[col:​concert:​Theme, #1]
4. PROJECT[tbl:​singer_in_concert, #1]
5. GROUP[count, #4, #1]
6. UNION[#2, #3, #5]
1. SELECT[tbl:​concert]
2. PROJECT[col:​concert:​concert_Name, #1]
3. PROJECT[col:​concert:​Theme, #1]
4. PROJECT[tbl:​singer_in_concert, #1]
5. GROUP[count, #4, #1]
6. UNION[#2, #3, #5]
+ 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]
2. PROJECT[col:​singer:​Name, #1]
3. PROJECT[tbl:​singer_in_concert, #1]
4. GROUP[count, #3, #1]
5. UNION[#2, #4]
1. SELECT[col:​singer:​Name]
2. PROJECT[col:​singer:​Name, #1]
3. PROJECT[tbl:​singer_in_concert, #1]
4. GROUP[count, #3, #1]
5. UNION[#2, #4]
+ 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]
2. PROJECT[col:​singer:​Name, #1]
3. PROJECT[tbl:​singer_in_concert, #1]
4. GROUP[count, #3, #1]
5. UNION[#2, #4]
1. SELECT[tbl:​singer]
2. PROJECT[col:​singer:​Name, #1]
3. PROJECT[tbl:​singer_in_concert, #1]
4. GROUP[count, #3, #1]
5. UNION[#2, #4]
+ 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]
2. FILTER[#1, comparative:​=:​2014:​col:​concert:​Year]
3. PROJECT[col:​singer:​Name, #2]
1. SELECT[tbl:​concert]
2. PROJECT[col:​concert:​Year, #1]
3. COMPARATIVE[#1, #2, comparative:​=:​2014:​col:​concert:​Year]
4. PROJECT[col:​singer:​Name, #3]
+ 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]
2. FILTER[#1, comparative:​=:​2014:​col:​concert:​Year]
3. PROJECT[tbl:​singer_in_concert, #2]
4. PROJECT[col:​singer:​Name, #3]
1. SELECT[tbl:​singer]
2. COMPARATIVE[#1, #1, comparative:​=:​2014:​col:​concert:​Year]
3. PROJECT[col:​singer:​Name, #2]
- 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]
2. FILTER[#1, comparative:​like:​%Hey%:​col:​singer:​Song_Name]
3. PROJECT[col:​singer:​Name, #2]
4. PROJECT[col:​singer:​Country, #2]
5. UNION[#3, #4]
1. SELECT[tbl:​singer]
2. PROJECT[col:​singer:​Song_Name, #1]
3. PROJECT[col:​singer:​Song_Name, #2]
4. COMPARATIVE[#1, #3, comparative:​like:​Hey:​col:​singer:​Song_Name]
5. PROJECT[col:​singer:​Name, #4]
6. PROJECT[col:​singer:​Country, #4]
7. UNION[#5, #6]
+ 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]
2. PROJECT[col:​singer:​Song_Name, #1]
3. COMPARATIVE[#1, #2, comparative:​like:​%Hey%:​col:​singer:​Song_Name]
4. PROJECT[tbl:​singer, #3]
5. PROJECT[col:​singer:​Name, #4]
6. PROJECT[col:​singer:​Country, #4]
7. UNION[#5, #6]
1. SELECT[tbl:​singer]
2. PROJECT[col:​singer:​Song_Name, #1]
3. PROJECT[col:​singer:​Song_Name, #2]
4. COMPARATIVE[#1, #3, comparative:​like:​Hey:​col:​singer:​Song_Name]
5. PROJECT[col:​singer:​Name, #4]
6. PROJECT[col:​singer:​Country, #4]
7. UNION[#5, #6]
+ 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]
2. PROJECT[tbl:​concert, #1]
3. COMPARATIVE[#1, #2, comparative:​=:​2014:​col:​concert:​Year]
4. COMPARATIVE[#1, #2, comparative:​=:​2015:​col:​concert:​Year]
5. INTERSECTION[#1, #3, #4]
6. PROJECT[col:​stadium:​Name, #5]
7. PROJECT[col:​stadium:​Location, #5]
8. UNION[#6, #7]
1. SELECT[tbl:​stadium]
2. PROJECT[tbl:​concert, #1]
3. COMPARATIVE[#2, #2, comparative:​=:​2014:​col:​concert:​Year]
4. COMPARATIVE[#2, #2, comparative:​=:​2015:​col:​concert:​Year]
5. INTERSECTION[#1, #3, #4]
6. PROJECT[col:​stadium:​Name, #5]
7. PROJECT[col:​stadium:​Location, #5]
8. UNION[#6, #7]
+ 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]
2. FILTER[#1, comparative:​=:​2014:​col:​concert:​Year]
3. FILTER[#1, comparative:​=:​2015:​col:​concert:​Year]
4. PROJECT[col:​stadium:​Name, #1]
5. INTERSECTION[#4, #2, #3]
6. PROJECT[col:​stadium:​Location, #1]
7. INTERSECTION[#6, #2, #3]
8. UNION[#5, #7]
1. SELECT[tbl:​stadium]
2. PROJECT[tbl:​concert, #1]
3. COMPARATIVE[#1, #2, comparative:​=:​2014:​col:​concert:​Year]
4. COMPARATIVE[#1, #2, comparative:​=:​2015:​col:​concert:​Year]
5. INTERSECTION[#1, #3, #4]
6. PROJECT[col:​stadium:​Name, #5]
7. PROJECT[col:​stadium:​Location, #5]
8. UNION[#6, #7]
+ 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]
2. PROJECT[col:​stadium:​Capacity, #1]
3. SUPERLATIVE[comparative:​max:​None, #1, #2]
4. PROJECT[tbl:​concert, #3]
5. AGGREGATE[count, #4]
1. SELECT[tbl:​stadium]
2. PROJECT[col:​stadium:​Capacity, #1]
3. SUPERLATIVE[comparative:​max:​None, #1, #2]
4. PROJECT[tbl:​concert, #3]
5. AGGREGATE[count, #4]
+ 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]
2. PROJECT[col:​stadium:​Capacity, #1]
3. SUPERLATIVE[comparative:​max:​None, #1, #2]
4. PROJECT[tbl:​concert, #3]
5. AGGREGATE[count, #4]
1. SELECT[tbl:​stadium]
2. PROJECT[col:​stadium:​Capacity, #1]
3. SUPERLATIVE[comparative:​max:​None, #1, #2]
4. PROJECT[tbl:​concert, #3]
5. AGGREGATE[sum, #4]
- hard

Exec acc: 0.8333