human
Schema:
classroom (building, room_number, capacity)
department (dept_name, building, budget)
course (course_id, title, dept_name, credits)
instructor (ID, name, dept_name, salary)
section (course_id, sec_id, semester, year, building, room_number, time_slot_id)
teaches (ID, course_id, sec_id, semester, year)
student (ID, name, dept_name, tot_cred)
takes (ID, course_id, sec_id, semester, year, grade)
advisor (s_ID, i_ID)
time_slot (time_slot_id, day, start_hr, start_min, end_hr, end_min)
prereq (course_id, prereq_id)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the year which offers the largest number of courses.
assistant
SELECT YEAR FROM SECTION GROUP BY YEAR ORDER BY count(*) DESC LIMIT 1
human
Find the names of students who have taken any course in the fall semester of year 2003.
assistant
SELECT name FROM student WHERE id IN (SELECT id FROM takes WHERE semester = 'Fall' AND YEAR = 2003)
human
Find the year and semester when offers the largest number of courses.
assistant
SELECT semester , YEAR FROM SECTION GROUP BY semester , YEAR ORDER BY count(*) DESC LIMIT 1
human
Find the name of students who took any class in the years of 2009 and 2010.
assistant
SELECT DISTINCT T1.name FROM student AS T1 JOIN takes AS T2 ON T1.id = T2.id WHERE YEAR = 2009 OR YEAR = 2010
human
Find the number of courses provided in each semester and year.
assistant
SELECT count(*) , semester , YEAR FROM SECTION GROUP BY semester , YEAR
human
Find the semester and year which has the least number of student taking any class.
assistant
SELECT semester , YEAR FROM takes GROUP BY semester , YEAR ORDER BY COUNT( * ) LIMIT 1 ;