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
How many courses that do not have prerequisite?
assistant
SELECT count(*) FROM course WHERE course_id NOT IN (SELECT course_id FROM prereq)
human
How many departments offer courses?
assistant
SELECT count(DISTINCT dept_name) FROM course
human
Find the names of the top 3 departments that provide the largest amount of courses?
assistant
SELECT dept_name FROM course GROUP BY dept_name ORDER BY count(*) DESC LIMIT 3
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 number of courses provided in each semester and year.
assistant
SELECT count(*) , semester , YEAR FROM SECTION GROUP BY semester , YEAR
human
What is the year and semester with the most courses?
assistant
SELECT semester , YEAR FROM SECTION GROUP BY semester , YEAR ORDER BY COUNT( * ) DESC LIMIT 1 ;