 | Idx | Table      | Column | Primary Key | Foreign Key | 
 | ----------- | ----------- | ----------- | ----------- | ----------- | 
  | 0 |  | * |   |   | 
 | 1 | **Addresses** | address_id | + |   | 
 | 2 |   | line_1 |   |   | 
 | 3 |   | line_2 |   |   | 
 | 4 |   | line_3 |   |   | 
 | 5 |   | city |   |   | 
 | 6 |   | zip_postcode |   |   | 
 | 7 |   | state_province_county |   |   | 
 | 8 |   | country |   |   | 
 | 9 |   | other_address_details |   |   | 
 | 10 | **Courses** | course_id | + |   | 
 | 11 |   | course_name |   |   | 
 | 12 |   | course_description |   |   | 
 | 13 |   | other_details |   |   | 
 | 14 | **Departments** | department_id | + |   | 
 | 15 |   | department_name |   |   | 
 | 16 |   | department_description |   |   | 
 | 17 |   | other_details |   |   | 
 | 18 | **Degree_Programs** | degree_program_id | + |   | 
 | 19 |   | department_id |   | --> 14 | 
 | 20 |   | degree_summary_name |   |   | 
 | 21 |   | degree_summary_description |   |   | 
 | 22 |   | other_details |   |   | 
 | 23 | **Sections** | section_id | + |   | 
 | 24 |   | course_id |   | --> 10 | 
 | 25 |   | section_name |   |   | 
 | 26 |   | section_description |   |   | 
 | 27 |   | other_details |   |   | 
 | 28 | **Semesters** | semester_id | + |   | 
 | 29 |   | semester_name |   |   | 
 | 30 |   | semester_description |   |   | 
 | 31 |   | other_details |   |   | 
 | 32 | **Students** | student_id | + |   | 
 | 33 |   | current_address_id |   | --> 1 | 
 | 34 |   | permanent_address_id |   | --> 1 | 
 | 35 |   | first_name |   |   | 
 | 36 |   | middle_name |   |   | 
 | 37 |   | last_name |   |   | 
 | 38 |   | cell_mobile_number |   |   | 
 | 39 |   | email_address |   |   | 
 | 40 |   | ssn |   |   | 
 | 41 |   | date_first_registered |   |   | 
 | 42 |   | date_left |   |   | 
 | 43 |   | other_student_details |   |   | 
 | 44 | **Student_Enrolment** | student_enrolment_id | + |   | 
 | 45 |   | degree_program_id |   | --> 18 | 
 | 46 |   | semester_id |   | --> 28 | 
 | 47 |   | student_id |   | --> 32 | 
 | 48 |   | other_details |   |   | 
 | 49 | **Student_Enrolment_Courses** | student_course_id | + |   | 
 | 50 |   | course_id |   | --> 10 | 
 | 51 |   | student_enrolment_id |   | --> 44 | 
 | 52 | **Transcripts** | transcript_id | + |   | 
 | 53 |   | transcript_date |   |   | 
 | 54 |   | other_details |   |   | 
 | 55 | **Transcript_Contents** | student_course_id |   | --> 49 | 
 | 56 |   | transcript_id |   | --> 52 | 
 
  | Index | Question  | SQL | gold QDMR | pred QDMR | Exec | SQL hardness |
  | ----------- | ----------- | ----------- |  ----------- | ----------- | ----------- | ----------- | 
 | SPIDER_dev_507 | what are all the addresses including line 1 and line 2? | SELECT line_1 ,  line_2 FROM addresses |  | 1. SELECT[col:​Addresses:​line_1] <br>2. COMPARATIVE[#1, #1, comparative:​=:​1:​col:​Addresses:​address_id] <br>3. COMPARATIVE[#1, #1, comparative:​=:​2:​col:​Addresses:​address_id] <br>4. INTERSECTION[#1, #2, #3] <br> | - | medium | 
  | SPIDER_dev_508 | What is the first and second line for all addresses? | SELECT line_1 ,  line_2 FROM addresses |  | 1. SELECT[tbl:​Addresses] <br>2. PROJECT[col:​Addresses:​line_1, #1] <br>3. PROJECT[col:​Addresses:​line_2, #1] <br>4. UNION[#2, #3] <br> | + | medium | 
  | SPIDER_dev_509 | How many courses in total are listed? | SELECT count(*) FROM Courses |  | 1. SELECT[tbl:​Courses] <br>2. AGGREGATE[count, #1] <br> | + | easy | 
  | SPIDER_dev_510 | How many courses are there? | SELECT count(*) FROM Courses |  | 1. SELECT[tbl:​Courses] <br>2. AGGREGATE[count, #1] <br> | + | easy | 
  | SPIDER_dev_511 | How is the math course described? | SELECT course_description FROM Courses WHERE course_name  =  'math' |  | 1. SELECT[tbl:​Courses] <br>2. COMPARATIVE[#1, #1, comparative:​=:​math:​col:​Courses:​course_name] <br>3. AGGREGATE[count, #2] <br> | - | easy | 
  | SPIDER_dev_512 | What are the descriptions for all the math courses? | SELECT course_description FROM Courses WHERE course_name  =  'math' |  | 1. SELECT[val:​Departments:​department_name:​math] <br>2. COMPARATIVE[#1, #1, comparative:​=:​math:​col:​Courses:​course_name] <br>3. PROJECT[col:​Courses:​course_description, #2] <br> | - | easy | 
  | SPIDER_dev_513 | What is the zip code of the address in the city Port Chelsea? | SELECT zip_postcode FROM Addresses WHERE city  =  'Port Chelsea' |  | 1. SELECT[tbl:​Addresses] <br>2. PROJECT[col:​Addresses:​city, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​Port Chelsea:​col:​Addresses:​city] <br>4. PROJECT[col:​Addresses:​zip_postcode, #3] <br> | + | easy | 
  | SPIDER_dev_514 | What is the zip code for Port Chelsea? | SELECT zip_postcode FROM Addresses WHERE city  =  'Port Chelsea' |  | 1. SELECT[val:​Addresses:​city:​Port Chelsea] <br>2. PROJECT[col:​Addresses:​zip_postcode, #1] <br> | + | easy | 
  | SPIDER_dev_515 | Which department offers the most number of degrees? List department name and id. | SELECT T2.department_name ,  T1.department_id FROM Degree_Programs AS T1 JOIN Departments AS T2 ON T1.department_id  =  T2.department_id GROUP BY T1.department_id ORDER BY count(*) DESC LIMIT 1 |  | 1. SELECT[tbl:​Departments] <br>2. PROJECT[tbl:​Degree_Programs, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​Departments:​department_name, #4] <br>6. PROJECT[col:​Departments:​department_id, #4] <br>7. UNION[#5, #6] <br> | + | extra | 
  | SPIDER_dev_516 | What is the name and id of the department with the most number of degrees? | select t2.department_name ,  t1.department_id from degree_programs as t1 join departments as t2 on t1.department_id  =  t2.department_id group by t1.department_id order by count(*) desc limit 1 |  | 1. SELECT[tbl:​Departments] <br>2. PROJECT[tbl:​Degree_Programs, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​Departments:​department_name, #4] <br>6. PROJECT[col:​Degree_Programs:​department_id, #4] <br>7. UNION[#5, #6] <br> | + | extra | 
  | SPIDER_dev_517 | How many departments offer any degree? | SELECT count(DISTINCT department_id) FROM Degree_Programs |  | 1. SELECT[tbl:​Departments] <br>2. COMPARATIVE[#1, #1, tbl:​Degree_Programs] <br>3. AGGREGATE[count, #2] <br> | + | easy | 
  | SPIDER_dev_518 | How many different departments offer degrees? | SELECT count(DISTINCT department_id) FROM Degree_Programs |  | 1. SELECT[col:​Degree_Programs:​department_id] <br>2. COMPARATIVE[#1, #1, tbl:​Degree_Programs] <br>3. PROJECT[None, #2] <br>4. AGGREGATE[count, #3] <br> | + | easy | 
  | SPIDER_dev_519 | How many different degree names are offered? | SELECT count(DISTINCT degree_summary_name) FROM Degree_Programs |  | 1. SELECT[col:​Courses:​course_name] <br>2.*(distinct)* PROJECT[None, #1] <br>3. AGGREGATE[count, #2] <br> | - | easy | 
  | SPIDER_dev_520 | How many different degrees are offered? | SELECT count(DISTINCT degree_summary_name) FROM Degree_Programs |  | 1. SELECT[col:​Degree_Programs:​degree_summary_name] <br>2.*(distinct)* PROJECT[None, #1] <br>3. AGGREGATE[count, #2] <br> | + | easy | 
  | SPIDER_dev_521 | How many degrees does the engineering department offer? | SELECT count(*) FROM Departments AS T1 JOIN Degree_Programs AS T2 ON T1.department_id  =  T2.department_id WHERE T1.department_name  =  'engineer' |  | 1. SELECT[tbl:​Departments] <br>2. PROJECT[tbl:​Degree_Programs, #1] <br>3. AGGREGATE[count, #2] <br> | - | medium | 
  | SPIDER_dev_522 | How many degrees does the engineering department have? | SELECT count(*) FROM Departments AS T1 JOIN Degree_Programs AS T2 ON T1.department_id  =  T2.department_id WHERE T1.department_name  =  'engineer' |  | 1. SELECT[col:​Degree_Programs:​department_id] <br>2. PROJECT[tbl:​Degree_Programs, #1] <br>3. AGGREGATE[count, #2] <br> | - | medium | 
  | SPIDER_dev_523 | What are the names and descriptions of all the sections? | SELECT section_name ,  section_description FROM Sections |  | 1. SELECT[tbl:​Sections] <br>2. PROJECT[col:​Sections:​section_name, #1] <br>3. PROJECT[col:​Sections:​section_description, #1] <br>4. UNION[#2, #3] <br> | + | medium | 
  | SPIDER_dev_524 | What are the names and descriptions for all the sections? | SELECT section_name ,  section_description FROM Sections |  | 1. SELECT[tbl:​Sections] <br>2. PROJECT[col:​Sections:​section_name, #1] <br>3. PROJECT[col:​Sections:​section_description, #1] <br>4. UNION[#2, #3] <br> | + | medium | 
  | SPIDER_dev_525 | What are the names and id of courses having at most 2 sections? | SELECT T1.course_name ,  T1.course_id FROM Courses AS T1 JOIN Sections AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_id HAVING count(*)  <=  2 |  | 1. SELECT[tbl:​Courses] <br>2. PROJECT[tbl:​Sections, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​<=:​2] <br>5. PROJECT[col:​Courses:​course_name, #4] <br>6. PROJECT[col:​Courses:​course_id, #4] <br>7. UNION[#5, #6] <br> | + | medium | 
  | SPIDER_dev_526 | What are the names and ids of every course with less than 2 sections? | SELECT T1.course_name ,  T1.course_id FROM Courses AS T1 JOIN Sections AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_id HAVING count(*)  <=  2 |  | 1. SELECT[tbl:​Courses] <br>2. PROJECT[tbl:​Sections, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​<:​2] <br>5. PROJECT[col:​Courses:​course_name, #4] <br>6. PROJECT[col:​Courses:​course_id, #4] <br>7. UNION[#5, #6] <br> | - | medium | 
  | SPIDER_dev_527 | List the section_name in reversed lexicographical order. | SELECT section_name FROM Sections ORDER BY section_name DESC |  | 1. SELECT[tbl:​Sections] <br>2. PROJECT[col:​Sections:​section_name, #1] <br>3. SORT[#2, #2, sortdir:​ascending] <br> | - | easy | 
  | SPIDER_dev_528 | What are the names of the sections in reverse alphabetical order? | SELECT section_name FROM Sections ORDER BY section_name DESC |  | 1. SELECT[tbl:​Sections] <br>2. PROJECT[col:​Sections:​section_name, #1] <br>3. SORT[#2, #2, sortdir:​ascending] <br> | - | easy | 
  | SPIDER_dev_529 | What is the semester which most student registered in? Show both the name and the id. | SELECT T1.semester_name ,  T1.semester_id FROM Semesters AS T1 JOIN Student_Enrolment AS T2 ON T1.semester_id  =  T2.semester_id GROUP BY T1.semester_id ORDER BY count(*) DESC LIMIT 1 |  | 1. SELECT[tbl:​Semesters] <br>2. PROJECT[tbl:​Student_Enrolment, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​Semesters:​semester_name, #4] <br>6. PROJECT[col:​Semesters:​semester_id, #4] <br>7. UNION[#5, #6] <br> | + | extra | 
  | SPIDER_dev_530 | For each semester, what is the name and id of the one with the most students registered? | SELECT T1.semester_name ,  T1.semester_id FROM Semesters AS T1 JOIN Student_Enrolment AS T2 ON T1.semester_id  =  T2.semester_id GROUP BY T1.semester_id ORDER BY count(*) DESC LIMIT 1 |  | 1. SELECT[tbl:​Semesters] <br>2. PROJECT[tbl:​Student_Enrolment, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​Semesters:​semester_name, #4] <br>6. PROJECT[col:​Semesters:​semester_id, #4] <br>7. UNION[#5, #6] <br> | + | extra | 
  | SPIDER_dev_531 | What is the description of the department whose name has the substring the computer? | SELECT department_description FROM Departments WHERE department_name LIKE '%computer%' |  | 1. SELECT[tbl:​Departments] <br>2. PROJECT[col:​Departments:​department_name, #1] <br>3. COMPARATIVE[#1, #2, comparative:​like:​computer science:​col:​Departments:​department_name] <br>4. PROJECT[col:​Departments:​department_description, #3] <br> | + | medium | 
  | SPIDER_dev_532 | What is the department description for the one whose name has the word computer? | SELECT department_description FROM Departments WHERE department_name LIKE '%computer%' |  | 1. SELECT[tbl:​Departments] <br>2. PROJECT[tbl:​Departments, #1] <br>3. COMPARATIVE[#1, #2, comparative:​like:​computer science:​col:​Departments:​department_name] <br>4. PROJECT[col:​Departments:​department_description, #3] <br> | + | medium | 
  | SPIDER_dev_533 | Who are enrolled in 2 degree programs in one semester? List the first name, middle name and last name and the id. | SELECT T1.first_name ,  T1.middle_name ,  T1.last_name ,  T1.student_id FROM Students AS T1 JOIN Student_Enrolment AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id HAVING count(*)  =  2 |  | 1. SELECT[col:​Student_Enrolment:​student_enrolment_id] <br>2. COMPARATIVE[#1, #1, comparative:​=:​2:​col:​Student_Enrolment:​semester_id] <br>3. PROJECT[col:​Students:​first_name, #2] <br>4. PROJECT[col:​Students:​middle_name, #2] <br>5. PROJECT[col:​Students:​last_name, #2] <br>6. UNION[#3, #4, #5] <br> | - | medium | 
  | SPIDER_dev_534 | What are the first, middle, and last names, along with the ids, of all students who enrolled in 2 degree programs in one semester? | SELECT T1.first_name ,  T1.middle_name ,  T1.last_name ,  T1.student_id FROM Students AS T1 JOIN Student_Enrolment AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id HAVING count(*)  =  2 |  | 1. SELECT[tbl:​Students] <br>2. PROJECT[tbl:​Student_Enrolment, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​=:​2] <br>5. PROJECT[col:​Students:​first_name, #4] <br>6. PROJECT[col:​Students:​middle_name, #4] <br>7. PROJECT[col:​Students:​last_name, #4] <br>8. UNION[#5, #6, #7] <br> | - | medium | 
  | SPIDER_dev_535 | Who is enrolled in a Bachelor degree program? List the first name, middle name, last name. | SELECT DISTINCT T1.first_name ,  T1.middle_name ,  T1.last_name FROM Students AS T1 JOIN Student_Enrolment AS T2 ON T1.student_id  =  T2.student_id JOIN Degree_Programs AS T3 ON T2.degree_program_id  =  T3.degree_program_id WHERE T3.degree_summary_name  =  'Bachelor' |  | 1. SELECT[tbl:​Degree_Programs] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Bachelor:​col:​Degree_Programs:​degree_summary_name] <br>3. PROJECT[col:​Students:​first_name, #2] <br>4. PROJECT[col:​Students:​middle_name, #2] <br>5. PROJECT[col:​Students:​last_name, #2] <br>6. UNION[#3, #4, #5] <br> | - | hard | 
  | SPIDER_dev_536 | What are the first, middle, and last names for everybody enrolled in a Bachelors program? | SELECT DISTINCT T1.first_name ,  T1.middle_name ,  T1.last_name FROM Students AS T1 JOIN Student_Enrolment AS T2 ON T1.student_id  =  T2.student_id JOIN Degree_Programs AS T3 ON T2.degree_program_id  =  T3.degree_program_id WHERE T3.degree_summary_name  =  'Bachelor' |  | 1. SELECT[tbl:​Students] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Bachelor:​col:​Degree_Programs:​degree_summary_name] <br>3. PROJECT[col:​Students:​first_name, #2] <br>4. PROJECT[col:​Students:​middle_name, #2] <br>5. PROJECT[col:​Students:​last_name, #2] <br>6. UNION[#3, #4, #5] <br> | + | hard | 
  | SPIDER_dev_537 | Find the kind of program which most number of students are enrolled in? | SELECT T1.degree_summary_name FROM Degree_Programs AS T1 JOIN Student_Enrolment AS T2 ON T1.degree_program_id  =  T2.degree_program_id GROUP BY T1.degree_summary_name ORDER BY count(*) DESC LIMIT 1 |  | 1. SELECT[col:​Student_Enrolment:​degree_program_id] <br>2. PROJECT[tbl:​Student_Enrolment, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​Student_Enrolment:​degree_program_id, #4] <br> | - | extra | 
  | SPIDER_dev_538 | What is the degree summary name that has the most number of students enrolled? | SELECT T1.degree_summary_name FROM Degree_Programs AS T1 JOIN Student_Enrolment AS T2 ON T1.degree_program_id  =  T2.degree_program_id GROUP BY T1.degree_summary_name ORDER BY count(*) DESC LIMIT 1 |  | 1. SELECT[col:​Degree_Programs:​degree_summary_name] <br>2. PROJECT[tbl:​Student_Enrolment, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br> | + | extra | 
  | SPIDER_dev_539 | Find the program which most number of students are enrolled in. List both the id and the summary. | SELECT T1.degree_program_id ,  T1.degree_summary_name FROM Degree_Programs AS T1 JOIN Student_Enrolment AS T2 ON T1.degree_program_id  =  T2.degree_program_id GROUP BY T1.degree_program_id ORDER BY count(*) DESC LIMIT 1 |  | 1. SELECT[col:​Student_Enrolment:​degree_program_id] <br>2. PROJECT[tbl:​Student_Enrolment, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​Student_Enrolment:​degree_program_id, #4] <br>6. PROJECT[col:​Degree_Programs:​degree_summary_name, #4] <br>7. UNION[#5, #6] <br> | + | extra | 
  | SPIDER_dev_540 | What is the program id and the summary of the degree that has the most students enrolled? | SELECT T1.degree_program_id ,  T1.degree_summary_name FROM Degree_Programs AS T1 JOIN Student_Enrolment AS T2 ON T1.degree_program_id  =  T2.degree_program_id GROUP BY T1.degree_program_id ORDER BY count(*) DESC LIMIT 1 |  | 1. SELECT[tbl:​Degree_Programs] <br>2. PROJECT[tbl:​Student_Enrolment, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​Degree_Programs:​degree_program_id, #4] <br>6. PROJECT[col:​Degree_Programs:​degree_summary_description, #4] <br>7. UNION[#5, #6] <br> | - | extra | 
  | SPIDER_dev_541 | Which student has enrolled for the most times in any program? List the id, first name, middle name, last name, the number of enrollments and student id. | SELECT T1.student_id ,  T1.first_name ,  T1.middle_name ,  T1.last_name ,  count(*) ,  T1.student_id FROM Students AS T1 JOIN Student_Enrolment AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id ORDER BY count(*) DESC LIMIT 1 |  | 1. SELECT[col:​Student_Enrolment_Courses:​student_enrolment_id] <br>2. PROJECT[tbl:​Student_Enrolment, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​Student_Enrolment:​student_enrolment_id, #4] <br>6. PROJECT[col:​Students:​first_name, #4] <br>7. PROJECT[col:​Students:​last_name, #4] <br>8. PROJECT[tbl:​Student_Enrolment, #4] <br>9. GROUP[count, #7, #1] <br>10. UNION[#2, #5, #9, #9] <br> | - | extra | 
  | SPIDER_dev_542 | What is the first, middle, and last name, along with the id and number of enrollments, for the student who enrolled the most in any program? | SELECT T1.student_id ,  T1.first_name ,  T1.middle_name ,  T1.last_name ,  count(*) ,  T1.student_id FROM Students AS T1 JOIN Student_Enrolment AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id ORDER BY count(*) DESC LIMIT 1 |  | 1. SELECT[tbl:​Students] <br>2. PROJECT[tbl:​Student_Enrolment, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. PROJECT[col:​Students:​first_name, #3] <br>5. PROJECT[col:​Students:​middle_name, #3] <br>6. PROJECT[col:​Students:​last_name, #3] <br>7. UNION[#4, #5, #6] <br> | - | extra | 
  | SPIDER_dev_543 | Which semesters do not have any student enrolled? List the semester name. | SELECT semester_name FROM Semesters WHERE semester_id NOT IN( SELECT semester_id FROM Student_Enrolment ) |  | 1. SELECT[tbl:​Semesters] <br>2. COMPARATIVE[#1, #1, tbl:​Student_Enrolment] <br>3. DISCARD[#1, #2] <br>4. PROJECT[col:​Semesters:​semester_name, #3] <br> | + | hard | 
  | SPIDER_dev_544 | What is the name of the semester with no students enrolled? | SELECT semester_name FROM Semesters WHERE semester_id NOT IN( SELECT semester_id FROM Student_Enrolment ) |  | 1. SELECT[tbl:​Semesters] <br>2. PROJECT[tbl:​Student_Enrolment, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, None] <br>5. DISCARD[#1, #4] <br>6. PROJECT[col:​Semesters:​semester_name, #5] <br> | + | hard | 
  | SPIDER_dev_545 | What are all the course names of the courses which ever have students enrolled in? | SELECT DISTINCT T1.course_name FROM Courses AS T1 JOIN Student_Enrolment_Courses AS T2 ON T1.course_id  =  T2.course_id |  | 1. SELECT[tbl:​Courses] <br>2. COMPARATIVE[#1, #1, tbl:​Student_Enrolment_Courses] <br>3. PROJECT[col:​Courses:​course_name, #2] <br> | + | easy | 
  | SPIDER_dev_546 | What are the names of all courses that have some students enrolled? | SELECT DISTINCT T1.course_name FROM Courses AS T1 JOIN Student_Enrolment_Courses AS T2 ON T1.course_id  =  T2.course_id |  | 1. SELECT[tbl:​Courses] <br>2. COMPARATIVE[#1, #1, tbl:​Student_Enrolment_Courses] <br>3. PROJECT[col:​Courses:​course_name, #2] <br> | + | easy | 
  | SPIDER_dev_547 | What's the name of the course with most number of enrollments? | SELECT  T1.course_name FROM Courses AS T1 JOIN Student_Enrolment_Courses AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_name ORDER BY count(*) DESC LIMIT 1 |  | 1. SELECT[tbl:​Courses] <br>2. PROJECT[tbl:​Student_Enrolment, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​Courses:​course_name, #4] <br> | + | extra | 
  | SPIDER_dev_548 | What is the name of the course with the most students enrolled? | SELECT  T1.course_name FROM Courses AS T1 JOIN Student_Enrolment_Courses AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_name ORDER BY count(*) DESC LIMIT 1 |  | 1. SELECT[tbl:​Courses] <br>2. PROJECT[tbl:​Student_Enrolment, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​Courses:​course_name, #4] <br> | + | extra | 
  | SPIDER_dev_549 | Find the last name of the students who currently live in the state of North Carolina but have not registered in any degree program. | SELECT T1.last_name FROM Students AS T1 JOIN Addresses AS T2 ON T1.current_address_id  =  T2.address_id WHERE T2.state_province_county  =  'NorthCarolina' EXCEPT SELECT DISTINCT T3.last_name FROM Students AS T3 JOIN Student_Enrolment AS T4 ON T3.student_id  =  T4.student_id |  | 1. SELECT[tbl:​Students] <br>2. COMPARATIVE[#1, #1, comparative:​=:​NorthCarolina:​col:​Addresses:​state_province_county] <br>3. COMPARATIVE[#2, #2, tbl:​Student_Enrolment] <br>4. DISCARD[#2, #3] <br>5. PROJECT[col:​Students:​last_name, #4] <br> | + | extra | 
  | SPIDER_dev_550 | What are the last name of the students who live in North Carolina but have not registered in any degree programs? | SELECT T1.last_name FROM Students AS T1 JOIN Addresses AS T2 ON T1.current_address_id  =  T2.address_id WHERE T2.state_province_county  =  'NorthCarolina' EXCEPT SELECT DISTINCT T3.last_name FROM Students AS T3 JOIN Student_Enrolment AS T4 ON T3.student_id  =  T4.student_id |  | 1. SELECT[tbl:​Students] <br>2. COMPARATIVE[#1, #1, comparative:​=:​NorthCarolina:​col:​Addresses:​state_province_county] <br>3. COMPARATIVE[#1, #1, tbl:​Student_Enrolment] <br>4. DISCARD[#1, #3] <br>5. INTERSECTION[#1, #2, #4] <br>6. PROJECT[col:​Students:​last_name, #5] <br> | + | extra | 
  | SPIDER_dev_551 | Show the date and id of the transcript with at least 2 course results. | SELECT T2.transcript_date ,  T1.transcript_id FROM Transcript_Contents AS T1 JOIN Transcripts AS T2 ON T1.transcript_id  =  T2.transcript_id GROUP BY T1.transcript_id HAVING count(*)  >=  2 |  | 1. SELECT[tbl:​Transcripts] <br>2. PROJECT[tbl:​Courses, #1] <br>3. GROUP[sum, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>=:​2:​col:​Transcript_Contents:​student_course_id] <br>5. PROJECT[col:​Transcripts:​transcript_date, #4] <br>6. PROJECT[col:​Transcripts:​transcript_id, #4] <br>7. UNION[#5, #6] <br> | - | medium | 
  | SPIDER_dev_552 | What is the date and id of the transcript with at least 2 courses listed? | SELECT T2.transcript_date ,  T1.transcript_id FROM Transcript_Contents AS T1 JOIN Transcripts AS T2 ON T1.transcript_id  =  T2.transcript_id GROUP BY T1.transcript_id HAVING count(*)  >=  2 |  | 1. SELECT[tbl:​Transcripts] <br>2. PROJECT[tbl:​Courses, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>=:​2] <br>5. PROJECT[col:​Transcripts:​transcript_date, #4] <br>6. PROJECT[col:​Transcripts:​transcript_id, #4] <br>7. UNION[#5, #6] <br> | + | medium | 
  | SPIDER_dev_553 | What is the phone number of the man with the first name Timmothy and the last name Ward? | SELECT cell_mobile_number FROM Students WHERE first_name  =  'Timmothy' AND last_name  =  'Ward' |  | 1. SELECT[col:​Students:​cell_mobile_number] <br>2. PROJECT[col:​Students:​last_name, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​Timmothy:​col:​Students:​first_name] <br>4. PROJECT[col:​Students:​last_name, #1] <br>5. COMPARATIVE[#1, #4, comparative:​=:​Ward:​col:​Students:​last_name] <br>6. INTERSECTION[#1, #3, #5] <br>7. PROJECT[col:​Students:​cell_mobile_number, #6] <br>8. AGGREGATE[sum, #7] <br>9. UNION[#8, #8] <br> | + | medium | 
  | SPIDER_dev_554 | What is the mobile phone number of the student named Timmothy Ward? | select cell_mobile_number from students where first_name  =  'timmothy' and last_name  =  'ward' |  | 1. SELECT[col:​Students:​cell_mobile_number] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Ward:​col:​Students:​last_name] <br>3. AGGREGATE[sum, #2] <br> | - | medium | 
  | SPIDER_dev_555 | Who is the first student to register? List the first name, middle name and last name. | SELECT first_name ,  middle_name ,  last_name FROM Students ORDER BY date_first_registered ASC LIMIT 1 |  | 1. SELECT[tbl:​Students] <br>2. PROJECT[col:​Students:​first_name, #1] <br>3. PROJECT[col:​Students:​middle_name, #1] <br>4. PROJECT[col:​Students:​last_name, #1] <br>5. UNION[#2, #3, #4] <br> | + | medium | 
  | SPIDER_dev_556 | What is the first, middle, and last name of the first student to register? | SELECT first_name ,  middle_name ,  last_name FROM Students ORDER BY date_first_registered ASC LIMIT 1 |  | 1. SELECT[tbl:​Students] <br>2. PROJECT[col:​Students:​first_name, #1] <br>3. PROJECT[col:​Students:​middle_name, #1] <br>4. PROJECT[col:​Students:​last_name, #1] <br>5. UNION[#2, #3, #4] <br> | + | medium | 
  | SPIDER_dev_557 | Who is the earliest graduate of the school? List the first name, middle name and last name. | SELECT first_name ,  middle_name ,  last_name FROM Students ORDER BY date_left ASC LIMIT 1 |  | 1. SELECT[tbl:​Students] <br>2. PROJECT[col:​Students:​first_name, #1] <br>3. SUPERLATIVE[comparative:​min:​None, #1, #2] <br>4. PROJECT[col:​Students:​first_name, #3] <br>5. PROJECT[col:​Students:​middle_name, #3] <br>6. PROJECT[col:​Students:​last_name, #3] <br>7. UNION[#4, #5, #6] <br> | - | medium | 
  | SPIDER_dev_558 | What is the first, middle, and last name of the earliest school graduate? | SELECT first_name ,  middle_name ,  last_name FROM Students ORDER BY date_left ASC LIMIT 1 |  | 1. SELECT[tbl:​Students] <br>2. PROJECT[col:​Students:​last_name, #1] <br>3. SUPERLATIVE[comparative:​min:​None, #1, #2] <br>4. PROJECT[col:​Students:​first_name, #3] <br>5. PROJECT[col:​Students:​middle_name, #3] <br>6. PROJECT[col:​Students:​last_name, #3] <br>7. UNION[#4, #5, #6] <br> | - | medium | 
  | SPIDER_dev_560 | What is the first name of the student whose permanent address is different from his or her current one? | SELECT first_name FROM Students WHERE current_address_id != permanent_address_id |  | 1. SELECT[tbl:​Students] <br>2. PROJECT[col:​Students:​current_address_id, #1] <br>3. COMPARATIVE[#1, #2, comparative:​<=:​#1] <br>4. PROJECT[col:​Students:​first_name, #3] <br> | - | easy | 
  | SPIDER_dev_561 | Which address holds the most number of students currently? List the address id and all lines. | SELECT T1.address_id ,  T1.line_1 ,  T1.line_2 FROM Addresses AS T1 JOIN Students AS T2 ON T1.address_id  =  T2.current_address_id GROUP BY T1.address_id ORDER BY count(*) DESC LIMIT 1 |  | 1. SELECT[tbl:​Addresses] <br>2. PROJECT[tbl:​Students, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​Addresses:​address_id, #4] <br>6. PROJECT[col:​Addresses:​line_3, #4] <br>7. UNION[#5, #6] <br> | - | extra | 
  | SPIDER_dev_562 | What is the id, line 1, and line 2 of the address with the most students? | SELECT T1.address_id ,  T1.line_1 ,  T1.line_2 FROM Addresses AS T1 JOIN Students AS T2 ON T1.address_id  =  T2.current_address_id GROUP BY T1.address_id ORDER BY count(*) DESC LIMIT 1 |  | 1. SELECT[tbl:​Addresses] <br>2. PROJECT[tbl:​Students, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​Addresses:​address_id, #4] <br>6. PROJECT[col:​Addresses:​line_1, #4] <br>7. PROJECT[col:​Addresses:​line_2, #4] <br>8. UNION[#5, #6, #7] <br> | + | extra | 
  | SPIDER_dev_563 | On average, when were the transcripts printed? | SELECT avg(transcript_date) FROM Transcripts |  | 1. SELECT[tbl:​Transcripts] <br>2. PROJECT[col:​Transcripts:​transcript_date, #1] <br>3. UNION[#1, #2] <br> | - | easy | 
  | SPIDER_dev_564 | What is the average transcript date? | SELECT avg(transcript_date) FROM Transcripts |  | 1. SELECT[tbl:​Transcripts] <br>2. PROJECT[col:​Transcripts:​transcript_date, #1] <br>3. AGGREGATE[avg, #2] <br> | - | easy | 
  | SPIDER_dev_565 | When is the first transcript released? List the date and details. | SELECT transcript_date ,  other_details FROM Transcripts ORDER BY transcript_date ASC LIMIT 1 |  | 1. SELECT[tbl:​Transcripts] <br>2. PROJECT[col:​Transcripts:​transcript_date, #1] <br>3. SUPERLATIVE[comparative:​min:​None, #1, #2] <br>4. PROJECT[col:​Transcripts:​transcript_date, #3] <br>5. PROJECT[col:​Transcripts:​transcript_date, #3] <br>6. UNION[#4, #5] <br> | - | medium | 
  | SPIDER_dev_566 | What is the earliest date of a transcript release, and what details can you tell me? | SELECT transcript_date ,  other_details FROM Transcripts ORDER BY transcript_date ASC LIMIT 1 |  | 1. SELECT[tbl:​Transcripts] <br>2. PROJECT[col:​Transcripts:​transcript_date, #1] <br>3. SUPERLATIVE[comparative:​min:​None, #1, #2] <br>4. PROJECT[col:​Transcripts:​transcript_date, #3] <br>5. PROJECT[col:​Transcripts:​transcript_date, #3] <br>6. UNION[#4, #5] <br> | - | medium | 
  | SPIDER_dev_567 | How many transcripts are released? | SELECT count(*) FROM Transcripts |  | 1. SELECT[tbl:​Transcripts] <br>2. AGGREGATE[count, #1] <br> | + | easy | 
  | SPIDER_dev_568 | How many transcripts are listed? | SELECT count(*) FROM Transcripts |  | 1. SELECT[tbl:​Transcripts] <br>2. AGGREGATE[count, #1] <br> | + | easy | 
  | SPIDER_dev_569 | What is the last transcript release date? | SELECT transcript_date FROM Transcripts ORDER BY transcript_date DESC LIMIT 1 |  | 1. SELECT[tbl:​Transcripts] <br>2. PROJECT[col:​Transcripts:​transcript_date, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br> | - | medium | 
  | SPIDER_dev_570 | When was the last transcript released? | SELECT transcript_date FROM Transcripts ORDER BY transcript_date DESC LIMIT 1 |  | 1. SELECT[tbl:​Transcripts] <br>2. PROJECT[None, #1] <br>3. SUPERLATIVE[comparative:​max:​None, #1, #2] <br>4. PROJECT[col:​Transcripts:​transcript_date, #3] <br> | - | medium | 
  | SPIDER_dev_572 | What is the maximum number of times that a course shows up in different transcripts and what is that course's enrollment id? | SELECT count(*) ,  student_course_id FROM Transcript_Contents GROUP BY student_course_id ORDER BY count(*) DESC LIMIT 1 |  | 1. SELECT[col:​Student_Enrolment_Courses:​course_id] <br>2. PROJECT[tbl:​Transcript_Contents, #1] <br>3. GROUP[count, #2, #1] <br>4. AGGREGATE[max, #3] <br>5. COMPARATIVE[#1, #3, comparative:​=:​rs:​col:​Courses:​course_name] <br>6. UNION[#4, #5] <br>7. PROJECT[col:​Student_Enrolment_Courses:​student_enrolment_id, #6] <br>8. AGGREGATE[max, #7] <br>9. UNION[#8, #8] <br> | - | hard | 
  | SPIDER_dev_573 | Show the date of the transcript which shows the least number of results, also list the id. | SELECT T2.transcript_date ,  T1.transcript_id FROM Transcript_Contents AS T1 JOIN Transcripts AS T2 ON T1.transcript_id  =  T2.transcript_id GROUP BY T1.transcript_id ORDER BY count(*) ASC LIMIT 1 |  | 1. SELECT[tbl:​Transcripts] <br>2. PROJECT[col:​Transcripts:​transcript_id, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​min:​None, #1, #3] <br>5. PROJECT[col:​Transcripts:​transcript_date, #4] <br> | - | extra | 
  | SPIDER_dev_574 | What is the date and id of the transcript with the least number of results? | SELECT T2.transcript_date ,  T1.transcript_id FROM Transcript_Contents AS T1 JOIN Transcripts AS T2 ON T1.transcript_id  =  T2.transcript_id GROUP BY T1.transcript_id ORDER BY count(*) ASC LIMIT 1 |  | 1. SELECT[tbl:​Transcripts] <br>2. PROJECT[tbl:​Transcript_Contents, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​min:​None, #1, #3] <br>5. PROJECT[col:​Transcripts:​transcript_date, #4] <br>6. PROJECT[col:​Transcripts:​transcript_id, #4] <br>7. UNION[#5, #6] <br> | + | extra | 
  | SPIDER_dev_575 | Find the semester when both Master students and Bachelor students got enrolled in. | SELECT DISTINCT T2.semester_id FROM Degree_Programs AS T1 JOIN Student_Enrolment AS T2 ON T1.degree_program_id  =  T2.degree_program_id WHERE degree_summary_name  =  'Master' INTERSECT SELECT DISTINCT T2.semester_id FROM Degree_Programs AS T1 JOIN Student_Enrolment AS T2 ON T1.degree_program_id  =  T2.degree_program_id WHERE degree_summary_name  =  'Bachelor' |  | 1. SELECT[col:​Semesters:​semester_name] <br>2. PROJECT[tbl:​Student_Enrolment, #1] <br>3. COMPARATIVE[#2, #2, comparative:​=:​Master:​col:​Degree_Programs:​degree_summary_name] <br>4. COMPARATIVE[#1, #2, comparative:​=:​Bachelor:​col:​Degree_Programs:​degree_summary_name] <br>5. INTERSECTION[#1, #3, #4] <br>6. PROJECT[col:​Semesters:​semester_name, #5] <br> | - | extra | 
  | SPIDER_dev_576 | What is the id of the semester that had both Masters and Bachelors students enrolled? | SELECT DISTINCT T2.semester_id FROM Degree_Programs AS T1 JOIN Student_Enrolment AS T2 ON T1.degree_program_id  =  T2.degree_program_id WHERE degree_summary_name  =  'Master' INTERSECT SELECT DISTINCT T2.semester_id FROM Degree_Programs AS T1 JOIN Student_Enrolment AS T2 ON T1.degree_program_id  =  T2.degree_program_id WHERE degree_summary_name  =  'Bachelor' |  | 1. SELECT[tbl:​Semesters] <br>2. PROJECT[tbl:​Student_Enrolment, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​=:​Bachelor:​col:​Degree_Programs:​degree_summary_name] <br>5. COMPARATIVE[#1, #3, comparative:​=:​Bachelor:​col:​Degree_Programs:​degree_summary_name] <br>6. INTERSECTION[#1, #4, #5] <br>7. PROJECT[col:​Student_Enrolment:​semester_id, #6] <br> | - | extra | 
  | SPIDER_dev_577 | How many different addresses do the students currently live? | SELECT count(DISTINCT current_address_id) FROM Students |  | 1. SELECT[tbl:​Students] <br>2. PROJECT[col:​Students:​current_address_id, #1] <br>3.*(distinct)* PROJECT[None, #2] <br>4. AGGREGATE[count, #3] <br> | + | easy | 
  | SPIDER_dev_578 | What are the different addresses that have students living there? | SELECT count(DISTINCT current_address_id) FROM Students |  | 1. SELECT[col:​Addresses:​city] <br>2. COMPARATIVE[#1, #1, tbl:​Students] <br> | - | easy | 
  | SPIDER_dev_579 | List all the student details in reversed lexicographical order. | SELECT other_student_details FROM Students ORDER BY other_student_details DESC |  | 1. SELECT[col:​Students:​other_student_details] <br>2. PROJECT[col:​Students:​other_student_details, #1] <br>3. SORT[#2, #2, sortdir:​ascending] <br> | - | easy | 
  | SPIDER_dev_580 | What other details can you tell me about students in reverse alphabetical order? | SELECT other_student_details FROM Students ORDER BY other_student_details DESC |  | 1. SELECT[tbl:​Students] <br>2. PROJECT[col:​Student_Enrolment:​other_details, #1] <br>3. SORT[#2, #2, sortdir:​ascending] <br> | - | easy | 
  | SPIDER_dev_581 | Describe the section h. | SELECT section_description FROM Sections WHERE section_name  =  'h' |  | 1. SELECT[tbl:​Sections] <br>2. PROJECT[col:​Sections:​section_name, #1] <br> | - | easy | 
  | SPIDER_dev_582 | What is the description for the section named h? | SELECT section_description FROM Sections WHERE section_name  =  'h' |  | 1. SELECT[tbl:​Sections] <br>2. COMPARATIVE[#1, #1, comparative:​=:​h:​col:​Sections:​section_name] <br>3. PROJECT[col:​Sections:​section_description, #2] <br> | + | easy | 
  | SPIDER_dev_583 | Find the first name of the students who permanently live in the country Haiti or have the cell phone number 09700166582. | select t1.first_name from students as t1 join addresses as t2 on t1.permanent_address_id  =  t2.address_id where t2.country  =  'haiti' or t1.cell_mobile_number  =  '09700166582' |  | 1. SELECT[tbl:​Students] <br>2. PROJECT[col:​Addresses:​country, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​Haiti:​col:​Addresses:​country] <br>4. PROJECT[col:​Students:​cell_mobile_number, #1] <br>5. COMPARATIVE[#1, #4, comparative:​=:​09700166582:​col:​Students:​cell_mobile_number] <br>6. UNION[#3, #5] <br>7. PROJECT[col:​Students:​first_name, #6] <br> | + | hard | 
  | SPIDER_dev_584 | What are the first names of the students who live in Haiti permanently or have the cell phone number 09700166582? | select t1.first_name from students as t1 join addresses as t2 on t1.permanent_address_id  =  t2.address_id where t2.country  =  'haiti' or t1.cell_mobile_number  =  '09700166582' |  | 1. SELECT[tbl:​Students] <br>2. PROJECT[col:​Addresses:​country, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​Haiti:​col:​Addresses:​country] <br>4. PROJECT[col:​Students:​cell_mobile_number, #1] <br>5. COMPARATIVE[#1, #4, comparative:​=:​09700166582:​col:​Students:​cell_mobile_number] <br>6. UNION[#3, #5] <br>7. PROJECT[col:​Students:​first_name, #6] <br> | + | hard | 
 ***
 Exec acc: **0.5263**
