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 title, credit, and department name of courses that have more than one prerequisites?
assistant
SELECT T1.title , T1.credits , T1.dept_name FROM course AS T1 JOIN prereq AS T2 ON T1.course_id = T2.course_id GROUP BY T2.course_id HAVING count(*) > 1
human
Find the title of the course that is offered by more than one department.
assistant
SELECT title FROM course GROUP BY title HAVING count(*) > 1
human
Find the name of students who took some course offered by Statistics department.
assistant
SELECT T3.name FROM course AS T1 JOIN takes AS T2 ON T1.course_id = T2.course_id JOIN student AS T3 ON T2.id = T3.id WHERE T1.dept_name = 'Statistics'
human
Find the building, room number, semester and year of all courses offered by Psychology department sorted by course titles.
assistant
SELECT T2.building , T2.room_number , T2.semester , T2.year FROM course AS T1 JOIN SECTION AS T2 ON T1.course_id = T2.course_id WHERE T1.dept_name = 'Psychology' ORDER BY T1.title
human
Find the title of course that is provided by both Statistics and Psychology departments.
assistant
SELECT title FROM course WHERE dept_name = 'Statistics' INTERSECT SELECT title FROM course WHERE dept_name = 'Psychology'
human
Find the title of course that is provided by Statistics but not Psychology departments.
assistant
SELECT title FROM course WHERE dept_name = "Statistics" EXCEPT SELECT title FROM course WHERE dept_name = "Psychology" ;