[
  {
    "db_id": "computer_student",
    "question": "How many courses are there for basic or medium undergraduate courses?",
    "evidence": "basic or medium undergraduate courses refers to courseLevel = 'Level_300'; courses refers to course.course_id",
    "SQL": "SELECT COUNT(course_id) FROM course WHERE courseLevel = 'Level_300'"
  },
  {
    "db_id": "computer_student",
    "question": "List the ID of all professors who are not faculty member along with the courses taught by him/her.",
    "evidence": "ID of all professors refers to person.p_id where professor = 1; not faculty member refers to hasPosition = 0; courses refers to taughtBy.course_id",
    "SQL": "SELECT T2.p_id, T2.course_id FROM person AS T1 INNER JOIN taughtBy AS T2 ON T1.p_id = T2.p_id WHERE T1.professor = 1 AND T1.hasPosition <> 0"
  },
  {
    "db_id": "computer_student",
    "question": "Provide the ID of professors who are teaching high-level or harder undergraduate course.",
    "evidence": "ID of professors refers to taughtBy.p_id; high-level or harder undergraduate course refers to courseLevel = 'Level_400'",
    "SQL": "SELECT T2.p_id FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id WHERE T1.courseLevel = 'Level_400'"
  },
  {
    "db_id": "computer_student",
    "question": "What are the courses taught by the advisors who gave advice to student with ID 376?",
    "evidence": "courses refers to course_id; advisors refers to p_id_dummy and taughtBy.p_id; student with ID 376 refers to advisedBy.p_id = 376",
    "SQL": "SELECT T3.course_id FROM advisedBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id INNER JOIN taughtBy AS T3 ON T2.p_id = T3.p_id WHERE T1.p_id = 141"
  },
  {
    "db_id": "computer_student",
    "question": "Name the advisors for students in Year 3 of the program.",
    "evidence": "advisors refers to p_id_dummy; students in Year 3 of the program refers to yearsInProgram = 'Year_3'",
    "SQL": "SELECT T1.p_id FROM advisedBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T2.yearsInProgram = 'Year_3'"
  },
  {
    "db_id": "computer_student",
    "question": "Which level of courses is taught by professor ID 297?",
    "evidence": "professor ID 297 refers to taughtBy.p_id = 297",
    "SQL": "SELECT T1.courseLevel FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id WHERE T2.p_id = 297"
  },
  {
    "db_id": "computer_student",
    "question": "What level is course 165? List the professors who teach the course.",
    "evidence": "course 165 refers to course_id = 165; professors refers to taughtBy.p_id",
    "SQL": "SELECT T1.courseLevel, T2.p_id FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id WHERE T2.course_id = 165"
  },
  {
    "db_id": "computer_student",
    "question": "List the ID and years in program for students taught by advisor with ID 5.",
    "evidence": "advisor with ID 5 refers to p_id_dummy = 5",
    "SQL": "SELECT T1.p_id, T2.yearsInProgram FROM advisedBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T1.p_id_dummy = 5"
  },
  {
    "db_id": "computer_student",
    "question": "State the courses and level of courses by professors who are faculty employees.",
    "evidence": "professors who are faculty employees refers to professor = 1; faculty employees refers to hasPosition = 'Faculty_eme'",
    "SQL": "SELECT T3.course_id, T3.courseLevel FROM taughtBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id INNER JOIN course AS T3 ON T3.course_id = T1.course_id WHERE T2.hasPosition = 'Faculty_eme'"
  },
  {
    "db_id": "computer_student",
    "question": "Find the ID of advisor of student ID 80 and state the level of courses taught by him/her.",
    "evidence": "ID of advisor refers to p_id_dummy; student ID 80 refers to advisedBy.p_id = 80; level of courses refers to courseLevel",
    "SQL": "SELECT T1.p_id_dummy, T2.courseLevel FROM advisedBy AS T1 INNER JOIN course AS T2 ON T1.p_id = T2.course_id INNER JOIN taughtBy AS T3 ON T2.course_id = T3.course_id WHERE T1.p_id = 80"
  },
  {
    "db_id": "computer_student",
    "question": "Provide the ID of professors who teach in both harder undergraduate course and master/graduate courses.",
    "evidence": "harder undergraduate course refers to courseLevel = 'Level_400'; master/graduate courses refers to courseLevel = 'Level_500'; ID of professors refers to taughtBy.p_id",
    "SQL": "SELECT DISTINCT T2.p_id FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id WHERE T1.courseLevel = 'Level_400' OR T1.courseLevel = 'Level_500'"
  },
  {
    "db_id": "computer_student",
    "question": "Who are the professors who gave advice to students in the 12th years of program?",
    "evidence": "professors refers to p_id_dummy; 12th years of program refers to yearsInProgram = 'Year_12'",
    "SQL": "SELECT T1.p_id_dummy FROM advisedBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T2.yearsInProgram = 'Year_12'"
  },
  {
    "db_id": "computer_student",
    "question": "Which are the courses with the most number of professors? State the course ID and the level of the course.",
    "evidence": "courses refers taughtBy.course_id; most number of professors\u00a0 refers to max(count(taughtBy.p_id)); level of the course refers to courseLevel",
    "SQL": "SELECT T1.course_id, T1.courseLevel FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id GROUP BY T1.course_id, T1.courseLevel ORDER BY COUNT(T1.course_id) DESC LIMIT 1"
  },
  {
    "db_id": "computer_student",
    "question": "How many basic and medium undergraduate courses are there?",
    "evidence": "basic and medium undergraduate courses refers to courseLevel = 'Level_300' and courses refers to course.course_id",
    "SQL": "SELECT COUNT(*) FROM course WHERE courseLevel = 'Level_300'"
  },
  {
    "db_id": "computer_student",
    "question": "How many people teaches course no.11?",
    "evidence": "people refers to taughtBy.p_id; course no.11 refers to course_id = 11",
    "SQL": "SELECT COUNT(*) FROM taughtBy WHERE course_id = 11"
  },
  {
    "db_id": "computer_student",
    "question": "Which course has more teachers, course no.16 or course no.18?",
    "evidence": "teachers refers to taughtBy.p_id; course no.16 refers to course_id = 16; course no.18 refers to course_id = 18",
    "SQL": "SELECT course_id FROM taughtBy WHERE course_id = 11 OR course_id = 18 GROUP BY course_id ORDER BY COUNT(course_id) DESC LIMIT 1"
  },
  {
    "db_id": "computer_student",
    "question": "How many teachers are faculty employees?",
    "evidence": "teachers refers to professor = 1; faculty employees refers to hasPosition = 'Faculty_eme'",
    "SQL": "SELECT COUNT(*) FROM person WHERE hasPosition = 'Faculty_eme'"
  },
  {
    "db_id": "computer_student",
    "question": "Please list the IDs of the teachers who have advised more than 4 others to teach.",
    "evidence": "teachers refers to p_id_dummy; have advised more than 4 others refers to count(advisedBy.p_id) > 4",
    "SQL": "SELECT p_id_dummy FROM advisedBy GROUP BY p_id_dummy HAVING COUNT(p_id_dummy) > 4"
  },
  {
    "db_id": "computer_student",
    "question": "How many basic or medium undergraduate courses are taught by a professor?",
    "evidence": "basic or medium undergraduate courses refers to courseLevel = 'Level_300'; professor refers to professor = 1",
    "SQL": "SELECT COUNT(*) FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id INNER JOIN person AS T3 ON T3.p_id = T2.p_id WHERE T1.courseLevel = 'Level_300' AND T3.professor = 1"
  },
  {
    "db_id": "computer_student",
    "question": "Please list the IDs of all the faculty employees who teaches a basic or medium undergraduate course.",
    "evidence": "faculty employees refers to hasPosition = 'Faculty_eme'; basic or medium undergraduate course refers to courseLevel = 'Level_300'",
    "SQL": "SELECT T2.p_id FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id INNER JOIN person AS T3 ON T3.p_id = T2.p_id WHERE T1.courseLevel = 'Level_300' AND T3.hasPosition = 'Faculty_eme'"
  },
  {
    "db_id": "computer_student",
    "question": "Is the teacher who teaches course no.9 a faculty member?",
    "evidence": "teacher refers to taughtBy.p_id; course no.9 refers to taughtBy.course_id = 9; faculty member refers to hasPosition ! = 0",
    "SQL": "SELECT T2.hasPosition FROM taughtBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T1.course_id = 9"
  },
  {
    "db_id": "computer_student",
    "question": "Please list the levels of the all courses taught by teacher no.79.",
    "evidence": "levels of the all courses refers to courseLevel; teacher no.79 refers to taughtBy.p_id = 79",
    "SQL": "SELECT T1.courseLevel FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id WHERE T2.p_id = 79"
  },
  {
    "db_id": "computer_student",
    "question": "Please list the IDs of the advisors of the students who are in the 5th year of their program.",
    "evidence": "IDs of the advisors refers to p_id_dummy; in the 5th year of their program refers to yearsInProgram = 'Year_5'",
    "SQL": "SELECT T1.p_id_dummy FROM advisedBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T2.yearsInProgram = 'Year_5'"
  },
  {
    "db_id": "computer_student",
    "question": "How many students are advised to teach by a professor teaching basic or medium undergraduate courses?",
    "evidence": "students refers to advisedBy.p_id; professor refers to p_id_dummy and taughtBy.p_id and professor = 1; basic or medium undergraduate courses refers to courseLevel = 'Level_300'",
    "SQL": "SELECT COUNT(DISTINCT T4.p_id) FROM person AS T1 INNER JOIN taughtBy AS T2 ON T1.p_id = T2.p_id INNER JOIN course AS T3 ON T3.course_id = T2.course_id INNER JOIN advisedBy AS T4 ON T4.p_id = T1.p_id WHERE T1.professor = 1 AND T3.courseLevel = 'Level_300'"
  },
  {
    "db_id": "computer_student",
    "question": "Among the courses that are basic or medium undergraduate courses, how many of them are taught by a faculty member?",
    "evidence": "courses that are basic or medium undergraduate courses refers to courseLevel = 'Level_300'; faculty member refers to hasPosition ! = 0",
    "SQL": "SELECT COUNT(*) FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id INNER JOIN person AS T3 ON T2.p_id = T3.p_id WHERE T3.professor = 1 AND T1.courseLevel = 'Level_300'"
  },
  {
    "db_id": "computer_student",
    "question": "For the professor who advised student no.6, please list the IDs of the courses he or she teaches.",
    "evidence": "professor refers to p_id_dummy and professor = 1; student no.6 refers to advisedBy.p_id = 6; IDs of the courses refers to taughtBy.course_id",
    "SQL": "SELECT T2.course_id FROM taughtBy AS T1 INNER JOIN course AS T2 ON T1.course_id = T2.course_id INNER JOIN advisedBy AS T3 ON T3.p_id = T1.p_id WHERE T1.p_id = 9"
  },
  {
    "db_id": "computer_student",
    "question": "What is the level of the course with the most number of teachers?",
    "evidence": "level of the course refers to courseLevel; course with most number of teachers refers to course_id = max(count(taughtBy.p_id))",
    "SQL": "SELECT T1.courseLevel FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id GROUP BY T2.course_id ORDER BY COUNT(T2.p_id) DESC LIMIT 1"
  },
  {
    "db_id": "computer_student",
    "question": "Please list the IDs of the professors that teaches more than 3 courses.",
    "evidence": "IDs of the professors refers to taughtBy.p_id and professor = 1; teaches more than 3 courses\u00a0 refers to count(course_id) > 3",
    "SQL": "SELECT T1.p_id FROM taughtBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T2.professor = 1 GROUP BY T1.p_id HAVING COUNT(DISTINCT T1.course_id) > 3"
  },
  {
    "db_id": "computer_student",
    "question": "Please list the IDs of the top 3 professors that teaches the most courses.",
    "evidence": "IDs of the professors refers to taughtBy.p_id and professor = 1; teaches the most courses refers to max(count(course_id))",
    "SQL": "SELECT T1.p_id FROM taughtBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T2.professor = 1 GROUP BY T1.p_id ORDER BY COUNT(*) DESC LIMIT 3"
  },
  {
    "db_id": "computer_student",
    "question": "In total, all the students in the 3rd year of their program are advised by how many professors?",
    "evidence": "3rd year of their program refers to yearsInProgram = 'Year_3'; professors refers to p_id_dummy",
    "SQL": "SELECT COUNT(DISTINCT T1.p_id_dummy) FROM advisedBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T2.yearsInProgram = 'Year_3'"
  },
  {
    "db_id": "computer_student",
    "question": "What is the average number of courses taught by a professor?",
    "evidence": "professor refers to professor = 1; average number of courses = divide(count(taughtBy.course_id), count(taughtBy.p_id) where professor = 1 )",
    "SQL": "SELECT CAST(COUNT(T1.course_id) AS REAL) / COUNT(DISTINCT T2.p_id) AS num FROM taughtBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T2.professor = 1"
  },
  {
    "db_id": "computer_student",
    "question": "What is the ratio of professors and students?",
    "evidence": "professors refers to professor = 1; students refers to student = 1; ratio = divide(count(person.p_id) when professor = 1, count(person.p_id) when student = 1)",
    "SQL": "SELECT CAST(SUM(CASE  WHEN professor = 1 THEN 1 ELSE 0 END) AS REAL) * 100 / SUM(CASE  WHEN student = 1 THEN 1 ELSE 0 END) AS per FROM person"
  },
  {
    "db_id": "computer_student",
    "question": "Calculate the percentage of high-level undergraduate course.",
    "evidence": "high-level undergraduate course refers to courseLevel = 'Level_400'; percentage = divide(count(course.course_id) when courseLevel = 'Level_400', count(course.course_id)) * 100%",
    "SQL": "SELECT CAST(SUM(CASE  WHEN courseLevel = 'Level_400' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(*) AS per FROM course"
  },
  {
    "db_id": "computer_student",
    "question": "List down all the person IDs who taught course ID of 18.",
    "evidence": "person IDs refers to taughtBy.p_id; course ID of 18\u00a0 refers to taughtBy.course_id = 18",
    "SQL": "SELECT p_id FROM taughtBy WHERE course_id = 18"
  },
  {
    "db_id": "computer_student",
    "question": "Provide the position status and IDs of professor who advised student ID \"303\".",
    "evidence": "position status refers to hasPosition; IDs of professor refers to p_id_dummy; student ID \"303\" refers to advisedBy.p_id = 303",
    "SQL": "SELECT T2.hasPosition, T1.p_id_dummy FROM advisedBy AS T1 INNER JOIN person AS T2 ON T1.p_id_dummy = T2.p_id WHERE T1.p_id = 303"
  },
  {
    "db_id": "computer_student",
    "question": "List the person IDs and course levels of the affiliated professors in faculty.",
    "evidence": "person IDs refers to person.p_id; affiliated professors in faculty refers to professor = 1 and hasPosition = 'Faculty_aff'",
    "SQL": "SELECT T1.p_id, T3.courseLevel FROM person AS T1 INNER JOIN taughtBy AS T2 ON T1.p_id = T2.p_id INNER JOIN course AS T3 ON T3.course_id = T2.course_id WHERE T1.hasPosition = 'Faculty_aff'"
  },
  {
    "db_id": "computer_student",
    "question": "Describe the year in program and in phase status for the student with most number in advisor.",
    "evidence": "student refers to advisedBy.p_id; most number in advisor refers to max(count(p_id_dummy))",
    "SQL": "SELECT T2.yearsInProgram, T2.inPhase FROM advisedBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id GROUP BY T1.p_id ORDER BY COUNT(*) DESC LIMIT 1"
  },
  {
    "db_id": "computer_student",
    "question": "List down the advised student IDs and IDs of employing professor in faculty.",
    "evidence": "advised student IDs refers to person.p_id; IDs of employing professor in faculty refers to p_id_dummy and hasPosition = 'Faculty_eme'",
    "SQL": "SELECT T1.p_id, T2.p_id FROM advisedBy AS T1 INNER JOIN person AS T2 ON T1.p_id_dummy = T2.p_id WHERE hasPosition = 'Faculty_eme'"
  },
  {
    "db_id": "computer_student",
    "question": "List the course IDs and levels of person IDs from 40 to 50.",
    "evidence": "course IDs and levels refers to course.course_id and courseLevel; person IDs from 40 to 50 refers to taughtBy.p_id between 40 and 50",
    "SQL": "SELECT T1.course_id, T1.courseLevel FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id WHERE T2.p_id BETWEEN 40 AND 50"
  },
  {
    "db_id": "computer_student",
    "question": "Describe the course level and list of person IDs who taught course ID of 147.",
    "evidence": "person IDs refers to taughtBy.p_id; course ID of 147 refers to course.course_id = 147",
    "SQL": "SELECT T1.courseLevel, T1.course_id FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id WHERE T2.p_id = 141"
  },
  {
    "db_id": "computer_student",
    "question": "Mention the person ID of faculty professor who taught course ID 104 and the course level.",
    "evidence": "person ID refers to person.p_id; faculty professor refers to professor = 1 and hasPosition ! = 0",
    "SQL": "SELECT T1.p_id, T3.courseLevel FROM person AS T1 INNER JOIN taughtBy AS T2 ON T1.p_id = T2.p_id INNER JOIN course AS T3 ON T3.course_id = T2.course_id WHERE T3.course_id = 104 AND T1.hasPosition <> 0"
  },
  {
    "db_id": "computer_student",
    "question": "Find the professor ID and position in faculty who taught high-level undergraduate course of less than 10 in ID.",
    "evidence": "professor ID refers to person.p_id when professor = 1; position in faculty refers to hasPosition; high-level undergraduate course refers to courseLevel = 'Level_400'; less than 10 in ID refers to course.course_id < 10",
    "SQL": "SELECT T1.p_id, T1.hasPosition FROM person AS T1 INNER JOIN taughtBy AS T2 ON T1.p_id = T2.p_id INNER JOIN course AS T3 ON T3.course_id = T2.course_id WHERE T3.courseLevel = 'Level_400' AND T2.course_id < 10"
  },
  {
    "db_id": "computer_student",
    "question": "List the professor ID who taught the course ID from 121 to 130 of basic undergraduate courses.",
    "evidence": "professor ID refers to taughtBy.p_id; course ID from 121 to 130 of basic undergraduate courses refers to courseLevel = 'Level_300' and course.course_id between 121 and 130",
    "SQL": "SELECT T2.p_id FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id WHERE T1.courseLevel = 'Level_300' AND T1.course_id > 121 AND T1.course_id < 130"
  },
  {
    "db_id": "computer_student",
    "question": "List the advisor IDs for students with eighth year of program and position status in faculty of those professors.",
    "evidence": "advisor IDs refers to p_id_dummy and person.p_id where professor = 1; eighth year of program refers to yearsInprogram = 'Year_8'; position status in faculty of those professors refers to hasPosition",
    "SQL": "SELECT T1.p_id_dummy, T2.hasPosition FROM advisedBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T2.yearsInProgram = 'Year_8'"
  },
  {
    "db_id": "computer_student",
    "question": "List any five of course IDs with professor IDs who taught master courses.",
    "evidence": "professor IDs refers to taughtBy.p_id; master course refers to courseLevel = 'Level_500'",
    "SQL": "SELECT T1.course_id, T2.p_id FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id WHERE T1.courseLevel = 'Level_500' LIMIT 5"
  },
  {
    "db_id": "computer_student",
    "question": "How many students are under advisor 415?",
    "evidence": "advisor 415 refers to p_id_dummy = 415",
    "SQL": "SELECT COUNT(*) FROM advisedBy WHERE p_id_dummy = 415"
  },
  {
    "db_id": "computer_student",
    "question": "How many professional or master/graduate courses are there?",
    "evidence": "professional or master/graduate courses refers to courseLevel = 'Level_500'",
    "SQL": "SELECT COUNT(*) FROM course WHERE courseLevel = 'Level_500'"
  },
  {
    "db_id": "computer_student",
    "question": "How many non-faculty members are not undergoing the phase of qualifications?",
    "evidence": "non-faculty members refers to hasPosition = 0; are not undergoing the phase of qualifications refers to inPhase = 0",
    "SQL": "SELECT COUNT(*) FROM person WHERE hasPosition = 0 AND inPhase = 0"
  },
  {
    "db_id": "computer_student",
    "question": "Which professor taught the least amount of courses?",
    "evidence": "professor refers to taughtBy.p_id; least amount of courses refers to min(count(course_id))",
    "SQL": "SELECT p_id FROM taughtBy GROUP BY p_id ORDER BY COUNT(course_id) ASC LIMIT 1"
  },
  {
    "db_id": "computer_student",
    "question": "Among the students being advised by Advisor 5, how many students are in the 5th year?",
    "evidence": "Advisor 5 refers to p_id_dummy = 5; are in the 5th year refers to yearsInProgram = 'Year_5'",
    "SQL": "SELECT COUNT(*) FROM advisedBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T1.p_id_dummy = 5 AND T2.student = 1 AND T2.yearsInProgram = 'Year_5'"
  },
  {
    "db_id": "computer_student",
    "question": "Which professor teaches the highest number of professional or master/graduate courses?",
    "evidence": "professor refers to taughtBy.p_id; highest number of professional or master/graduate courses refers to max(count(taughtBy.course_id)) where courseLevel = 'Level_500'",
    "SQL": "SELECT T2.p_id FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id WHERE T1.courseLevel = 'Level_500' GROUP BY T2.p_id ORDER BY COUNT(T2.course_id) DESC LIMIT 1"
  },
  {
    "db_id": "computer_student",
    "question": "Among the faculty affiliated professor, how many professors teaches professional or master/undergraduate courses?",
    "evidence": "faculty affiliated professor refers to professor = 1 and hasPosition = 'Faculty_aff'; professional or master/undergraduate courses refers to courseLevel = 'Level_500'",
    "SQL": "SELECT COUNT(*) FROM person AS T1 INNER JOIN taughtBy AS T2 ON T1.p_id = T2.p_id INNER JOIN course AS T3 ON T3.course_id = T2.course_id WHERE T1.hasPosition = 'Faculty_aff' AND T1.professor = 1 AND T3.courseLevel = 'Level_500'"
  },
  {
    "db_id": "computer_student",
    "question": "Who are the top 5 professors who teaches the highest number of professional or master/undergraduate courses?",
    "evidence": "professors refers to course.p_id; highest number of professional or master/undergraduate courses refers to max(count(course.course_id)) where courseLevel = 'Level_500'",
    "SQL": "SELECT T2.p_id FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id WHERE T1.courseLevel = 'Level_500' GROUP BY T2.p_id ORDER BY COUNT(T2.p_id) DESC LIMIT 5"
  },
  {
    "db_id": "computer_student",
    "question": "How many advisors are in charge of advising all the students in 1st year?",
    "evidence": "advisors refers to p_id_dummy; students in 1st year refers to student = 1 and yearsInProgram = 'Year_1'",
    "SQL": "SELECT COUNT(T1.p_id_dummy) FROM advisedBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T2.yearsInProgram = 'Year_1' AND T2.student = 1"
  },
  {
    "db_id": "computer_student",
    "question": "How many professors teaches no more than two high-level or harder undergraduate courses?",
    "evidence": "professors refers to taughtBy.p_id; high-level or harder undergraduate courses\u00a0 refers to courseLevel = 'Level_400' ; no more than two refers to count(taughtBy.course_id) < = 2",
    "SQL": "SELECT COUNT(*) FROM ( SELECT COUNT(T2.p_id) FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id WHERE T1.courseLevel = 'Level_400' GROUP BY T2.p_id HAVING COUNT(DISTINCT T1.course_id) <= 2 )"
  },
  {
    "db_id": "computer_student",
    "question": "Between the faculty employee professors, how many teaches high-level or harder undergraduate courses? Indicate each of the professors unique identifying number.",
    "evidence": "faculty employee professors refers to hasPosition = 'Faculty_eme' and professor = 1; high-level or harder undergraduate courses refers to courseLevel = 'Level_400'; professors unique identifying number refers to person.p_id",
    "SQL": "SELECT COUNT(*) FROM person AS T1 INNER JOIN taughtBy AS T2 ON T1.p_id = T2.p_id INNER JOIN course AS T3 ON T3.course_id = T2.course_id WHERE T1.hasPosition = 'Faculty_eme' AND T1.professor = 1 AND T3.courseLevel = 'Level_400'"
  },
  {
    "db_id": "computer_student",
    "question": "What is the position in the faculty of the professor who teaches the highest number of courses?",
    "evidence": "position in the faculty refers to hasPosition; professor refers to professor = 1; teaches the highest number of courses refers to max(count(taughtBy.course_id))",
    "SQL": "SELECT T1.hasPosition FROM person AS T1 INNER JOIN taughtBy AS T2 ON T1.p_id = T2.p_id WHERE T1.professor = 1 GROUP BY T1.p_id ORDER BY COUNT(T2.course_id) DESC LIMIT 1"
  },
  {
    "db_id": "computer_student",
    "question": "What year in the program do the students with more than 2 advisors are in?",
    "evidence": "students refers to student = 1; more than 2 advisors refers to count(p_id_dummy) > 2",
    "SQL": "SELECT T2.yearsInProgram FROM advisedBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T2.student = 1 GROUP BY T2.p_id HAVING COUNT(T2.p_id) > 2"
  },
  {
    "db_id": "computer_student",
    "question": "How many professors teaches basic or medium undergraduate courses?",
    "evidence": "professors refers to taughtBy.p_id; basic or medium undergraduate courses refers to couresLevel = 'Level_300'",
    "SQL": "SELECT COUNT(*) FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id WHERE T1.courseLevel = 'Level_300'"
  },
  {
    "db_id": "computer_student",
    "question": "Among the students being advised by advisors, which students' year in the program do the advisors advise the majority of?",
    "evidence": "students refers to student = 1; students' year in the program do the advisors advise the majority of refers to max(count(yearsInProgram))",
    "SQL": "SELECT T2.yearsInProgram FROM advisedBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T2.student = 1 GROUP BY T2.yearsInProgram ORDER BY COUNT(T1.p_id_dummy) DESC LIMIT 1"
  },
  {
    "db_id": "computer_student",
    "question": "How many students that are undergoing the pre-phase of qualification have advisors?",
    "evidence": "students refers to student = 1 and ; undergoing the phase of pre-qualification refers to inPhase = 'Pre-Quals'; have advisors refers to advisedBy.p_id",
    "SQL": "SELECT COUNT(T1.p_id_dummy) FROM advisedBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T2.inPhase = 'Pre_Quals' AND T2.student = 1"
  },
  {
    "db_id": "computer_student",
    "question": "What is the average number of professional or master/undergraduate courses being taught by each professor?",
    "evidence": "professional or master/undergraduate courses refers to courseLevel = 'Level_500'; average number = divide(count(taughtBy.course_id), count(taughtBy.p_id))",
    "SQL": "SELECT CAST(COUNT(T1.course_id) AS REAL) / COUNT(DISTINCT T2.p_id) FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id WHERE T1.courseLevel = 'Level_500'"
  },
  {
    "db_id": "computer_student",
    "question": "How many courses were taught by more than 4 people?",
    "evidence": "courses refers to taughtBy.course_id; more than 4 people refers to count(taughtBy.p_id) > 4",
    "SQL": "SELECT COUNT(*) FROM ( SELECT COUNT(course_id) FROM taughtBy GROUP BY course_id HAVING COUNT(course_id) > 4 )"
  },
  {
    "db_id": "computer_student",
    "question": "What is the total of professional courses available at the university? List out all the course id.",
    "evidence": "professional courses refers to courseLevel = 'Level_500'; course id refers to course.course_id",
    "SQL": "SELECT COUNT(course_id) FROM course WHERE courseLevel = 'Level_500'"
  },
  {
    "db_id": "computer_student",
    "question": "What is the sum of year 1 and year 2 students?",
    "evidence": "year 1 and year 2 students refers to yearsInProgram = 'Year_1' and yearsInProgram = 'Year_2' and student = 1",
    "SQL": "SELECT COUNT(*) FROM person WHERE yearsInProgram = 'Year_1' OR yearsInProgram = 'Year_2'"
  },
  {
    "db_id": "computer_student",
    "question": "How many courses were taught by a professor who is currently the member of faculty?",
    "evidence": "professor refers to professor = 1;\u00a0 member of faculty refers to hasPosition <> 0",
    "SQL": "SELECT COUNT(*) FROM person AS T1 INNER JOIN taughtBy AS T2 ON T1.p_id = T2.p_id WHERE T1.professor = 1 AND T1.hasPosition <> 0"
  },
  {
    "db_id": "computer_student",
    "question": "Which professor taught the most courses and what is the position of this person in the university?",
    "evidence": "professor refers to taughtBy.p_id; most courses refers to max(taughtBy.p_id); position refers to hasPosition",
    "SQL": "SELECT T1.p_id, T1.hasPosition FROM person AS T1 INNER JOIN taughtBy AS T2 ON T1.p_id = T2.p_id GROUP BY T1.p_id ORDER BY COUNT(T2.course_id) DESC LIMIT 1"
  },
  {
    "db_id": "computer_student",
    "question": "Which courses were taught by a professor who is not a faculty member?",
    "evidence": "courses refers to taughtBy.course_id; professor refers to professor = 1; is not a faculty member refers to hasPosition = 0",
    "SQL": "SELECT DISTINCT T2.course_id FROM person AS T1 INNER JOIN taughtBy AS T2 ON T1.p_id = T2.p_id WHERE T1.professor = 1 AND T1.hasPosition = 0"
  },
  {
    "db_id": "computer_student",
    "question": "Which member of the faculty are teaching the most courses and what is his/her general course level?",
    "evidence": "member of the faculty refers to hasPosition <> 0, most courses refers to max(count(course.course_id))",
    "SQL": "SELECT T1.p_id, T3.courseLevel FROM person AS T1 INNER JOIN taughtBy AS T2 ON T1.p_id = T2.p_id INNER JOIN course AS T3 ON T3.course_id = T2.course_id GROUP BY T1.p_id ORDER BY COUNT(T2.course_id) DESC LIMIT 1"
  }
]