Class Notes

max/min duration

select s.subjectName, min(c.duration) as mindur, max(c.duration) as  maxdur
from Classes c, Subjects s
where c.subjectid = s.subjectid
group by c.subjectid, c.credits
;

Number of Student for each Course

select ss.classid, count(*) as "number of students enrolled in class"
from Student_Schedules ss
where ss.classstatus = 1
group by ss.classid
order by "number of students enrolled in class"
;

Courses w/ More than 2 students

Number of Student for each Course

select ss.classid, count(*) as "number of students enrolled in class"
from Student_Schedules ss
where ss.classstatus = 1
group by ss.classid
having count(*) > 2
order by "number of students enrolled in class"
;

select avg(*) 
from Classes c
group by classroomid

Average Number of Classes in the Classrooms (over all classes)

select avg(aggTable.NumClasses)
from (select c.classroomid, count(*) as NumClasses
from Classes c
group by c.classroomid) aggTable

My tags:

 

Popular tags: