-- Find courses with maximum enrollment
-- Use of a derived table -- see week04 Notes 01
-- max() and min() functions return only 1 record

select *
from (select crn, count(*) as cnt
	from Schedule   
	group by crn) as DT -- Create derived table
	-- SQL complains if you do not give the derived table a name
where cnt = (
	select max(cnt) as mx
	from (select crn, count(*) as cnt
	from Schedule
	group by crn) as DT -- must declare again
	-- can not reference the previous DT, I think, because it is
	-- 'out of scope'
);

-- Alternate method without the nesting, but still
-- declares the derived table twice
select max(cnt) into @maxCnt -- declare session variable
from (select crn, count(*) as cnt 
			from Schedule
			group by crn) as DT;
-- select @maxCnt   -- show the value of the variable
select crn, cnt
from (select crn, count(*) as cnt 
			from Schedule
			group by crn
			having cnt >= @maxCnt) as DT;

-- Find crns of CS course(s) with max enrollment:
-- Modify query above to use only the CS courses
select *
from (select crn, count(*) as cnt
	from Schedule
	where crn in (
		select crn
		from Course
		where name like 'CS%')
	group by crn) as DT
where cnt = (
	select max(cnt) as mx
	from (select crn, count(*) as cnt
	from Schedule
	group by crn) as DT
);

-- Show crn, name, #Enrolled for all CS course(s)
-- Still need the derived table to calculate the counts
select DT.crn, name, cnt
from (select crn, count(*) as cnt
	from Schedule
	where crn in (
		select crn
		from Course
		where name like 'CS%')
	group by crn) as DT, Course
where DT.crn = Course.crn;


-- Show crn, name, #Enrolled for the CS course(s) with maximum enrollment
-- still need the derived table to capture the counts and max
-- but join the result with DT to get the course names
select DT.crn, name, cnt
from (select crn, count(*) as cnt
	from Schedule
	where crn in (
		select crn
		from Course
		where name like 'CS%')
	group by crn) as DT, Course
where cnt = (
	select max(cnt) as mx
	from (select crn, count(*) as cnt
	from Schedule
	group by crn) as DT
) and DT.crn = Course.crn;

-- Show crn, name, #Enrolled for all CS course(s)
