four_wip.sql

SELECT *
FROM (SELECT s.cno, s.dname
	FROM section s, prof p, enroll e
	WHERE s.cno = e.cno AND s.dname = p.dname AND s.dname = e.dname AND s.dname IN(
		SELECT m.dname
		FROM major m
		GROUP BY m.dname
		HAVING COUNT(*) > 8)
	GROUP BY s.cno, s.dname

	) majors_eight,

	(SELECT MAX(enrollment) as max_enroll, dname
	FROM(
		SELECT s.dname, s.cno, s.sectno, COUNT(*) as enrollment
		FROM section s, enroll e
		WHERE s.dname = e.dname AND s.cno = e.cno AND s.sectno = e.sectno
		GROUP BY s.dname, s.cno, s.sectno)
	GROUP BY dname
	) max_enroll

WHERE majors_eight.dname = max_enroll.dname AND majors_eight.enroll = max_enroll.max_enroll