 | Idx | Table      | Column | Primary Key | Foreign Key | 
 | ----------- | ----------- | ----------- | ----------- | ----------- | 
  | 0 |  | * |   |   | 
 | 1 | **Student** | StuID | + |   | 
 | 2 |   | LName |   |   | 
 | 3 |   | Fname |   |   | 
 | 4 |   | Age |   |   | 
 | 5 |   | Sex |   |   | 
 | 6 |   | Major |   |   | 
 | 7 |   | Advisor |   |   | 
 | 8 |   | city_code |   |   | 
 | 9 | **Has_Pet** | StuID |   | --> 1 | 
 | 10 |   | PetID |   | --> 11 | 
 | 11 | **Pets** | PetID | + |   | 
 | 12 |   | PetType |   |   | 
 | 13 |   | pet_age |   |   | 
 | 14 |   | weight |   |   | 
 
  | Index | Question  | SQL | gold QDMR | pred QDMR | Exec | SQL hardness |
  | ----------- | ----------- | ----------- |  ----------- | ----------- | ----------- | ----------- | 
 | SPIDER_dev_45 | Find the number of pets whose weight is heavier than 10. | SELECT count(*) FROM pets WHERE weight  >  10 | 1. SELECT[tbl:​Pets] <br>2. PROJECT[col:​Pets:​weight, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​10:​col:​Pets:​weight] <br>4. AGGREGATE[count, #3] <br> | 1. SELECT[tbl:​Pets] <br>2. PROJECT[col:​Pets:​weight, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​10:​col:​Pets:​weight] <br>4. AGGREGATE[count, #3] <br> | + | easy | 
  | SPIDER_dev_46 | How many pets have a greater weight than 10? | SELECT count(*) FROM pets WHERE weight  >  10 | 1. SELECT[tbl:​Pets] <br>2. PROJECT[col:​Pets:​weight, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​10:​col:​Pets:​weight] <br>4. AGGREGATE[count, #3] <br> | 1. SELECT[tbl:​Pets] <br>2. PROJECT[col:​Pets:​weight, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​10:​col:​Pets:​weight] <br>4. AGGREGATE[count, #3] <br> | + | easy | 
  | SPIDER_dev_47 | Find the weight of the youngest dog. | SELECT weight FROM pets WHERE pettype  =  'dog' ORDER BY pet_age LIMIT 1 | 1. SELECT[val:​Pets:​PetType:​dog] <br>2. PROJECT[col:​Pets:​pet_age, #1] <br>3. COMPARATIVE[#1, #2, comparative:​min:​None] <br>4. PROJECT[col:​Pets:​weight, #3] <br> | 1. SELECT[val:​Pets:​PetType:​dog] <br>2. PROJECT[col:​Pets:​weight, #1] <br>3. SUPERLATIVE[comparative:​min:​None, #1, #2] <br>4. PROJECT[col:​Pets:​weight, #3] <br> | + | hard | 
  | SPIDER_dev_48 | How much does the youngest dog weigh? | SELECT weight FROM pets WHERE pettype  =  'dog' ORDER BY pet_age LIMIT 1 | 1. SELECT[val:​Pets:​PetType:​dog] <br>2. PROJECT[col:​Pets:​pet_age, #1] <br>3. SUPERLATIVE[comparative:​min:​None, #1, #2] <br>4. PROJECT[col:​Pets:​weight, #3] <br> | 1. SELECT[val:​Pets:​PetType:​dog] <br>2. PROJECT[col:​Pets:​weight, #1] <br>3. SUPERLATIVE[comparative:​min:​None, #1, #2] <br>4. AGGREGATE[max, #3] <br> | - | hard | 
  | SPIDER_dev_49 | Find the maximum weight for each type of pet. List the maximum weight and pet type. | SELECT max(weight) ,  petType FROM pets GROUP BY petType | 1. SELECT[tbl:​Pets] <br>2. SELECT[col:​Pets:​PetType] <br>3. PROJECT[col:​Pets:​weight, #2] <br>4. GROUP[max, #3, #2] <br>5. UNION[#4, #2] <br> | 1. SELECT[tbl:​Pets] <br>2. PROJECT[col:​Pets:​weight, #1] <br>3. GROUP[max, #2, #1] <br>4. PROJECT[col:​Pets:​PetType, #1] <br>5. UNION[#3, #4] <br> | - | medium | 
  | SPIDER_dev_50 | List the maximum weight and type for each type of pet. | SELECT max(weight) ,  petType FROM pets GROUP BY petType | 1. SELECT[tbl:​Pets] <br>2. PROJECT[col:​Pets:​PetType, #1] <br>3. PROJECT[col:​Pets:​weight, #2] <br>4. GROUP[max, #3, #2] <br>5. UNION[#4, #2] <br> | 1. SELECT[tbl:​Pets] <br>2. PROJECT[col:​Pets:​weight, #1] <br>3. PROJECT[col:​Pets:​PetType, #1] <br>4. GROUP[max, #3, #1] <br>5. UNION[#2, #4] <br> | - | medium | 
  | SPIDER_dev_51 | Find number of pets owned by students who are older than 20. | SELECT count(*) FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid WHERE T1.age  >  20 | 1. SELECT[tbl:​Student] <br>2. FILTER[#1, comparative:​>:​20:​col:​Student:​Age] <br>3. PROJECT[tbl:​Has_Pet, #2] <br>4. AGGREGATE[count, #3] <br> | 1. SELECT[tbl:​Student] <br>2. COMPARATIVE[#1, #1, comparative:​>:​20:​col:​Student:​Age] <br>3. PROJECT[tbl:​Pets, #2] <br>4. PROJECT[col:​Pets:​PetType, #3] <br> | - | medium | 
  | SPIDER_dev_52 | How many pets are owned by students that have an age greater than 20? | SELECT count(*) FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid WHERE T1.age  >  20 | 1. SELECT[tbl:​Student] <br>2. PROJECT[col:​Student:​Age, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​20:​col:​Student:​Age] <br>4. PROJECT[tbl:​Has_Pet, #3] <br>5. AGGREGATE[count, #4] <br> | 1. SELECT[tbl:​Student] <br>2. PROJECT[col:​Student:​Age, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​20:​col:​Student:​Age] <br>4. PROJECT[tbl:​Pets, #3] <br>5. AGGREGATE[count, #4] <br> | + | medium | 
  | SPIDER_dev_53 | Find the number of dog pets that are raised by female students (with sex F). | SELECT count(*) FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T2.petid  =  T3.petid WHERE T1.sex  =  'F' AND T3.pettype  =  'dog' | 1. SELECT[tbl:​Student] <br>2. FILTER[#1, comparative:​=:​F:​col:​Student:​Sex] <br>3. PROJECT[val:​Pets:​PetType:​dog, #2] <br>4. GROUP[count, #3, #2] <br>5. AGGREGATE[sum, #4] <br> | 1. SELECT[tbl:​Student] <br>2. PROJECT[col:​Student:​Sex, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​F:​col:​Student:​Sex] <br>4. PROJECT[tbl:​Pets, #3] <br>5. AGGREGATE[count, #4] <br> | - | hard | 
  | SPIDER_dev_54 | How many dog pets are raised by female students? | SELECT count(*) FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T2.petid  =  T3.petid WHERE T1.sex  =  'F' AND T3.pettype  =  'dog' | 1. SELECT[tbl:​Student] <br>2. FILTER[#1, comparative:​=:​F:​col:​Student:​Sex] <br>3. PROJECT[val:​Pets:​PetType:​dog, #2] <br>4. GROUP[count, #3, #2] <br>5. AGGREGATE[sum, #4] <br> | 1. SELECT[val:​Pets:​PetType:​dog] <br>2. PROJECT[val:​Pets:​PetType:​dog, #1] <br>3. AGGREGATE[count, #2] <br> | + | hard | 
  | SPIDER_dev_55 | Find the number of distinct type of pets. | SELECT count(DISTINCT pettype) FROM pets | 1. SELECT[tbl:​Pets] <br>2.*(distinct)* PROJECT[col:​Pets:​PetType, #1] <br>3. AGGREGATE[count, #2] <br> | 1. SELECT[tbl:​Pets] <br>2.*(distinct)* PROJECT[col:​Pets:​PetType, #1] <br>3. AGGREGATE[count, #2] <br> | + | easy | 
  | SPIDER_dev_56 | How many different types of pet are there? | SELECT count(DISTINCT pettype) FROM pets | 1. SELECT[tbl:​Pets] <br>2.*(distinct)* PROJECT[col:​Pets:​PetType, #1] <br>3. AGGREGATE[count, #2] <br> | 1. SELECT[tbl:​Pets] <br>2. PROJECT[col:​Pets:​PetType, #1] <br>3.*(distinct)* PROJECT[None, #2] <br>4. AGGREGATE[count, #3] <br> | + | easy | 
  | SPIDER_dev_57 | Find the first name of students who have cat or dog pet. | SELECT DISTINCT T1.Fname FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pettype  =  'cat' OR T3.pettype  =  'dog' | 1. SELECT[tbl:​Student] <br>2. PROJECT[tbl:​Pets, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​dog:​col:​Pets:​PetType] <br>4. COMPARATIVE[#1, #2, comparative:​=:​cat:​col:​Pets:​PetType] <br>5. UNION[#3, #4] <br>6. PROJECT[col:​Student:​Fname, #5] <br> | 1. SELECT[tbl:​Student] <br>2. PROJECT[tbl:​Pets, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​cat:​col:​Pets:​PetType] <br>4. COMPARATIVE[#1, #2, comparative:​=:​dog:​col:​Pets:​PetType] <br>5. UNION[#3, #4] <br>6. PROJECT[col:​Student:​Fname, #5] <br> | + | extra | 
  | SPIDER_dev_58 | What are the first names of every student who has a cat or dog as a pet? | SELECT DISTINCT T1.Fname FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pettype  =  'cat' OR T3.pettype  =  'dog' | 1. SELECT[tbl:​Student] <br>2. PROJECT[tbl:​Pets, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​cat:​col:​Pets:​PetType] <br>4. COMPARATIVE[#1, #2, comparative:​=:​dog:​col:​Pets:​PetType] <br>5. UNION[#3, #4] <br>6. PROJECT[col:​Student:​Fname, #5] <br> | 1. SELECT[tbl:​Student] <br>2. PROJECT[tbl:​Pets, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​cat:​col:​Pets:​PetType] <br>4. COMPARATIVE[#1, #2, comparative:​=:​dog:​col:​Pets:​PetType] <br>5. UNION[#3, #4] <br>6. PROJECT[col:​Student:​Fname, #5] <br> | + | extra | 
  | SPIDER_dev_59 | Find the first name of students who have both cat and dog pets. | select t1.fname from student as t1 join has_pet as t2 on t1.stuid  =  t2.stuid join pets as t3 on t3.petid  =  t2.petid where t3.pettype  =  'cat' intersect select t1.fname from student as t1 join has_pet as t2 on t1.stuid  =  t2.stuid join pets as t3 on t3.petid  =  t2.petid where t3.pettype  =  'dog' | 1. SELECT[tbl:​Student] <br>2. FILTER[#1, comparative:​=:​cat:​col:​Pets:​PetType] <br>3. FILTER[#1, comparative:​=:​dog:​col:​Pets:​PetType] <br>4. INTERSECTION[#1, #2, #3] <br>5. PROJECT[col:​Student:​Fname, #4] <br> | 1. SELECT[tbl:​Student] <br>2. PROJECT[tbl:​Pets, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​cat:​col:​Pets:​PetType] <br>4. COMPARATIVE[#1, #2, comparative:​=:​dog:​col:​Pets:​PetType] <br>5. INTERSECTION[#1, #3, #4] <br>6. PROJECT[col:​Student:​Fname, #5] <br> | + | extra | 
  | SPIDER_dev_60 | What are the students' first names who have both cats and dogs as pets? | SELECT T1.Fname FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pettype  =  'cat' INTERSECT SELECT T1.Fname FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pettype  =  'dog' | 1. SELECT[tbl:​Student] <br>2. PROJECT[tbl:​Pets, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​cat:​col:​Pets:​PetType] <br>4. COMPARATIVE[#1, #2, comparative:​=:​dog:​col:​Pets:​PetType] <br>5. PROJECT[col:​Student:​Fname, #1] <br>6. INTERSECTION[#5, #3, #4] <br> | 1. SELECT[tbl:​Student] <br>2. PROJECT[tbl:​Pets, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​cat:​col:​Pets:​PetType] <br>4. COMPARATIVE[#1, #2, comparative:​=:​dog:​col:​Pets:​PetType] <br>5. INTERSECTION[#1, #3, #4] <br>6. PROJECT[col:​Student:​Fname, #5] <br> | + | extra | 
  | SPIDER_dev_64 | What are the ids of the students who do not own cats as pets? | SELECT stuid FROM student EXCEPT SELECT T1.stuid FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pettype  =  'cat' | 1. SELECT[tbl:​Student] <br>2. FILTER[#1, comparative:​=:​cat:​col:​Pets:​PetType] <br>3. DISCARD[#1, #2] <br>4. PROJECT[col:​Student:​StuID, #3] <br> | 1. SELECT[tbl:​Student] <br>2. PROJECT[tbl:​Pets, #1] <br>3. COMPARATIVE[#1, #2, comparative:​!=:​cat:​col:​Pets:​PetType] <br>4. PROJECT[col:​Student:​StuID, #3] <br> | - | hard | 
  | SPIDER_dev_66 | What is the first name of every student who has a dog but does not have a cat? | SELECT distinct T1.fname FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pettype  =  'dog' AND T1.stuid NOT IN (SELECT T1.stuid FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pettype  =  'cat') | 1. SELECT[tbl:​Student] <br>2. FILTER[#1, comparative:​=:​dog:​col:​Pets:​PetType] <br>3. FILTER[#2, comparative:​=:​cat:​col:​Pets:​PetType] <br>4. DISCARD[#2, #3] <br>5. PROJECT[col:​Student:​Fname, #4] <br> | 1. SELECT[tbl:​Student] <br>2. COMPARATIVE[#1, #1, comparative:​=:​dog:​col:​Pets:​PetType] <br>3. COMPARATIVE[#2, #2, comparative:​!=:​cat:​col:​Pets:​PetType] <br>4. PROJECT[col:​Student:​Fname, #3] <br> | + | extra | 
  | SPIDER_dev_67 | Find the type and weight of the youngest pet. | SELECT pettype ,  weight FROM pets ORDER BY pet_age LIMIT 1 | 1. SELECT[tbl:​Pets] <br>2. PROJECT[col:​Pets:​pet_age, #1] <br>3. SUPERLATIVE[comparative:​min:​None, #1, #2] <br>4. PROJECT[col:​Pets:​PetType, #3] <br>5. PROJECT[col:​Pets:​weight, #3] <br>6. UNION[#4, #5] <br> | 1. SELECT[tbl:​Pets] <br>2. PROJECT[col:​Pets:​weight, #1] <br>3. SUPERLATIVE[comparative:​min:​None, #1, #2] <br>4. PROJECT[col:​Pets:​PetType, #3] <br>5. PROJECT[col:​Pets:​weight, #3] <br>6. UNION[#4, #5] <br> | + | medium | 
  | SPIDER_dev_68 | What type of pet is the youngest animal, and how much does it weigh? | SELECT pettype ,  weight FROM pets ORDER BY pet_age LIMIT 1 | 1. SELECT[col:​Pets:​pet_age] <br>2. FILTER[#1, comparative:​min:​None] <br>3. PROJECT[col:​Pets:​PetType, #2] <br>4. PROJECT[col:​Pets:​weight, #2] <br>5. UNION[#3, #4] <br> | 1. SELECT[tbl:​Pets] <br>2. PROJECT[col:​Pets:​weight, #1] <br>3. SUPERLATIVE[comparative:​min:​None, #1, #2] <br>4. PROJECT[col:​Pets:​pet_age, #3] <br>5. PROJECT[col:​Pets:​weight, #3] <br>6. UNION[#4, #5] <br> | - | medium | 
  | SPIDER_dev_69 | Find the id and weight of all pets whose age is older than 1. | SELECT petid ,  weight FROM pets WHERE pet_age  >  1 | 1. SELECT[tbl:​Pets] <br>2. PROJECT[col:​Pets:​pet_age, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​1:​col:​Pets:​pet_age] <br>4. PROJECT[col:​Pets:​PetID, #3] <br>5. PROJECT[col:​Pets:​weight, #3] <br>6. UNION[#4, #5] <br> | 1. SELECT[tbl:​Pets] <br>2. PROJECT[col:​Pets:​pet_age, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​1:​col:​Pets:​pet_age] <br>4. PROJECT[col:​Pets:​PetID, #3] <br>5. PROJECT[col:​Pets:​weight, #3] <br>6. UNION[#4, #5] <br> | + | medium | 
  | SPIDER_dev_70 | What is the id and weight of every pet who is older than 1? | SELECT petid ,  weight FROM pets WHERE pet_age  >  1 | 1. SELECT[tbl:​Pets] <br>2. FILTER[#1, comparative:​>:​1:​col:​Pets:​pet_age] <br>3. PROJECT[col:​Pets:​PetID, #2] <br>4. PROJECT[col:​Pets:​weight, #2] <br>5. UNION[#3, #4] <br> | 1. SELECT[tbl:​Pets] <br>2. COMPARATIVE[#1, #1, comparative:​>:​1:​col:​Pets:​pet_age] <br>3. PROJECT[col:​Pets:​PetID, #2] <br>4. PROJECT[col:​Pets:​weight, #2] <br>5. UNION[#3, #4] <br> | + | medium | 
  | SPIDER_dev_71 | Find the average and maximum age for each type of pet. | SELECT pettype ,  avg(pet_age) ,  max(pet_age) FROM pets GROUP BY pettype | 1. SELECT[tbl:​Pets] <br>2. PROJECT[col:​Pets:​PetType, #1] <br>3. PROJECT[col:​Pets:​pet_age, #2] <br>4. GROUP[avg, #3, #2] <br>5. GROUP[max, #3, #2] <br>6. UNION[#2, #4, #5] <br> | 1. SELECT[col:​Pets:​PetType] <br>2. PROJECT[tbl:​Pets, #1] <br>3. PROJECT[col:​Pets:​pet_age, #2] <br>4. GROUP[avg, #3, #1] <br>5. UNION[#1, #4] <br> | - | medium | 
  | SPIDER_dev_72 | What is the average and maximum age for each pet type? | SELECT pettype ,  avg(pet_age) ,  max(pet_age) FROM pets GROUP BY pettype | 1. SELECT[col:​Pets:​PetType] <br>2. PROJECT[col:​Pets:​pet_age, #1] <br>3. GROUP[avg, #2, #1] <br>4. GROUP[max, #2, #1] <br>5. UNION[#1, #3, #4] <br> | 1. SELECT[col:​Pets:​PetType] <br>2. PROJECT[col:​Pets:​pet_age, #1] <br>3. GROUP[avg, #2, #1] <br>4. UNION[#1, #3] <br> | - | medium | 
  | SPIDER_dev_73 | Find the average weight for each pet type. | SELECT pettype ,  avg(weight) FROM pets GROUP BY pettype | 1. SELECT[col:​Pets:​PetType] <br>2. PROJECT[col:​Pets:​weight, #1] <br>3. GROUP[avg, #2, #1] <br>4. UNION[#1, #3] <br> | 1. SELECT[col:​Pets:​PetType] <br>2. PROJECT[col:​Pets:​weight, #1] <br>3. GROUP[avg, #2, #1] <br>4. UNION[#1, #3] <br> | + | medium | 
  | SPIDER_dev_74 | What is the average weight for each type of pet? | SELECT pettype ,  avg(weight) FROM pets GROUP BY pettype | 1. SELECT[tbl:​Pets] <br>2. PROJECT[col:​Pets:​PetType, #1] <br>3. PROJECT[col:​Pets:​weight, #2] <br>4. GROUP[avg, #3, #2] <br>5. UNION[#2, #4] <br> | 1. SELECT[col:​Pets:​PetType] <br>2. PROJECT[tbl:​Pets, #1] <br>3. PROJECT[col:​Pets:​weight, #2] <br>4. GROUP[avg, #3, #1] <br>5. UNION[#1, #4] <br> | + | medium | 
  | SPIDER_dev_75 | Find the first name and age of students who have a pet. | SELECT DISTINCT T1.fname ,  T1.age FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid | 1. SELECT[tbl:​Student] <br>2. FILTER[#1, tbl:​Has_Pet] <br>3. PROJECT[col:​Student:​Fname, #2] <br>4. PROJECT[col:​Student:​Age, #2] <br>5. UNION[#3, #4] <br> | 1. SELECT[tbl:​Student] <br>2. COMPARATIVE[#1, #1, tbl:​Has_Pet] <br>3. PROJECT[col:​Student:​Fname, #2] <br>4. PROJECT[col:​Student:​Age, #2] <br>5. UNION[#3, #4] <br> | + | medium | 
  | SPIDER_dev_76 | What are the different first names and ages of the students who do have pets? | SELECT DISTINCT T1.fname ,  T1.age FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid | 1. SELECT[tbl:​Student] <br>2. FILTER[#1, tbl:​Has_Pet] <br>3.*(distinct)* PROJECT[col:​Student:​Fname, #2] <br>4. PROJECT[col:​Student:​Age, #2] <br>5. UNION[#3, #4] <br> | 1. SELECT[tbl:​Student] <br>2. COMPARATIVE[#1, #1, tbl:​Has_Pet] <br>3.*(distinct)* PROJECT[col:​Student:​Fname, #2] <br>4. PROJECT[col:​Student:​Age, #2] <br>5. UNION[#3, #4] <br> | + | medium | 
  | SPIDER_dev_77 | Find the id of the pet owned by student whose last name is ‘Smith’. | SELECT T2.petid FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid WHERE T1.Lname  =  'Smith' | 1. SELECT[tbl:​Student] <br>2. PROJECT[tbl:​Has_Pet, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​Smith:​col:​Student:​LName] <br>4. PROJECT[tbl:​Has_Pet, #3] <br>5. PROJECT[col:​Has_Pet:​PetID, #4] <br> | 1. SELECT[tbl:​Student] <br>2. PROJECT[col:​Student:​LName, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​Smith:​col:​Student:​LName] <br>4. PROJECT[tbl:​Pets, #3] <br>5. PROJECT[col:​Has_Pet:​PetID, #4] <br> | + | medium | 
  | SPIDER_dev_78 | What is the id of the pet owned by the student whose last name is 'Smith'? | SELECT T2.petid FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid WHERE T1.Lname  =  'Smith' | 1. SELECT[tbl:​Student] <br>2. PROJECT[tbl:​Has_Pet, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​Smith:​col:​Student:​LName] <br>4. PROJECT[tbl:​Has_Pet, #3] <br>5. PROJECT[col:​Has_Pet:​PetID, #4] <br> | 1. SELECT[tbl:​Student] <br>2. PROJECT[col:​Student:​LName, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​Smith:​col:​Student:​LName] <br>4. PROJECT[tbl:​Pets, #3] <br>5. PROJECT[col:​Has_Pet:​PetID, #4] <br> | + | medium | 
  | SPIDER_dev_79 | Find the number of pets for each student who has any pet and student id. | SELECT count(*) ,  T1.stuid FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid GROUP BY T1.stuid | 1. SELECT[tbl:​Student] <br>2. FILTER[#1, tbl:​Has_Pet] <br>3. PROJECT[tbl:​Has_Pet, #2] <br>4. GROUP[count, #3, #2] <br>5. PROJECT[col:​Student:​StuID, #2] <br>6. UNION[#4, #5] <br> | 1. SELECT[col:​Has_Pet:​StuID] <br>2. PROJECT[tbl:​Has_Pet, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, None] <br>5. PROJECT[tbl:​Pets, #4] <br>6. GROUP[count, #5, #4] <br>7. PROJECT[col:​Has_Pet:​StuID, #4] <br>8. UNION[#6, #7] <br> | + | medium | 
  | SPIDER_dev_80 | For students who have pets, how many pets does each student have? list their ids instead of names. | select t1.stuid,  count(*) from student as t1 join has_pet as t2 on t1.stuid  =  t2.stuid group by t1.stuid | 1. SELECT[tbl:​Student] <br>2. FILTER[#1, tbl:​Has_Pet] <br>3. PROJECT[tbl:​Has_Pet, #2] <br>4. GROUP[count, #3, #2] <br>5. UNION[#2, #4] <br> | 1. SELECT[col:​Has_Pet:​StuID] <br>2. COMPARATIVE[#1, #1, tbl:​Has_Pet] <br>3. PROJECT[col:​Has_Pet:​StuID, #2] <br>4. GROUP[count, #3, #2] <br>5. UNION[#2, #4] <br> | - | medium | 
  | SPIDER_dev_81 | Find the first name and gender of student who have more than one pet. | SELECT T1.fname ,  T1.sex FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid GROUP BY T1.stuid HAVING count(*)  >  1 | 1. SELECT[tbl:​Student] <br>2. PROJECT[tbl:​Has_Pet, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​1] <br>5. PROJECT[col:​Student:​Fname, #4] <br>6. PROJECT[col:​Student:​Sex, #4] <br>7. UNION[#5, #6] <br> | 1. SELECT[tbl:​Student] <br>2. PROJECT[tbl:​Has_Pet, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​1] <br>5. PROJECT[col:​Student:​Fname, #4] <br>6. PROJECT[col:​Student:​Sex, #4] <br>7. UNION[#5, #6] <br> | + | medium | 
  | SPIDER_dev_82 | What is the first name and gender of the all the students who have more than one pet? | SELECT T1.fname ,  T1.sex FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid GROUP BY T1.stuid HAVING count(*)  >  1 | 1. SELECT[tbl:​Student] <br>2. PROJECT[tbl:​Has_Pet, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​1] <br>5. PROJECT[col:​Student:​Fname, #4] <br>6. PROJECT[col:​Student:​Sex, #4] <br>7. UNION[#5, #6] <br> | 1. SELECT[tbl:​Student] <br>2. PROJECT[tbl:​Has_Pet, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​1] <br>5. PROJECT[col:​Student:​Fname, #4] <br>6. PROJECT[col:​Student:​Sex, #4] <br>7. UNION[#5, #6] <br> | + | medium | 
  | SPIDER_dev_83 | Find the last name of the student who has a cat that is age 3. | SELECT T1.lname FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pet_age  =  3 AND T3.pettype  =  'cat' | 1. SELECT[tbl:​Student] <br>2. PROJECT[val:​Pets:​PetType:​cat, #1] <br>3. PROJECT[col:​Pets:​pet_age, #2] <br>4. COMPARATIVE[#1, #3, comparative:​=:​3:​col:​Pets:​pet_age] <br>5. PROJECT[col:​Student:​LName, #4] <br> | 1. SELECT[tbl:​Student] <br>2. COMPARATIVE[#1, #1, comparative:​=:​3:​col:​Pets:​pet_age] <br>3. PROJECT[col:​Student:​LName, #2] <br> | + | hard | 
  | SPIDER_dev_84 | What is the last name of the student who has a cat that is 3 years old? | SELECT T1.lname FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pet_age  =  3 AND T3.pettype  =  'cat' | 1. SELECT[tbl:​Student] <br>2. PROJECT[val:​Pets:​PetType:​cat, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​3:​col:​Pets:​pet_age] <br>4. PROJECT[col:​Student:​LName, #3] <br> | 1. SELECT[tbl:​Student] <br>2. COMPARATIVE[#1, #1, comparative:​=:​cat:​col:​Pets:​PetType] <br>3. PROJECT[col:​Student:​LName, #2] <br> | + | hard | 
  | SPIDER_dev_86 | What is the average age for all students who do not own any pets? | select avg(age) from student where stuid not in (select stuid from has_pet) | 1. SELECT[tbl:​Student] <br>2. FILTER[#1, tbl:​Has_Pet] <br>3. DISCARD[#1, #2] <br>4. PROJECT[col:​Student:​Age, #3] <br>5. AGGREGATE[avg, #4] <br> | 1. SELECT[tbl:​Student] <br>2. COMPARATIVE[#1, #1, tbl:​Has_Pet] <br>3. DISCARD[#1, #2] <br>4. PROJECT[col:​Student:​Age, #3] <br>5. AGGREGATE[avg, #4] <br> | + | extra | 
 ***
 Exec acc: **0.7297**
