[
  {
    "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": "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": "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": "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": "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": "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 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 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": "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 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": "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": "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": "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": "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": "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": "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": "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": "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": "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": "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": "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 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": "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": "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": "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": "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": "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": "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": "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'"
  }
]