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