create table Person ( 
Name char (20), 
ID char (9) not null, 
Address char (30), 
DOB date, 
Primary key (ID)); 

create table Instructor ( InstructorID char (9) not null references Person(ID), 
Rank char (12), 
Salary integer, 
Primary key (InstructorID));
 
create table Student ( StudentID char (9) not null references Person(ID), 
Classification char (10), 
GPA double, 
MentorID char (9) references Instructor(InstructorID), 
CreditHours integer, 
primary key (StudentID));
 
create table Course ( 
CourseCode char (6) not null, 
CourseName char (50), 
PreReq char (6)); 

create table Offering ( 
CourseCode char(6) not null, 
SectionNo integer not null, 
InstructorID char(9) not null references Instructor(InstructorID), 
Primary Key (CourseCode,SectionNo)); 

create table Enrollment ( CourseCode char(6) NOT NULL, 
SectionNo integer NOT NULL, 
StudentID char(9) NOT NULL references Student, 
Grade char(4) NOT NULL, 
primary key (CourseCode,StudentID), 
foreign key (CourseCode, SectionNo) references Offering);

select e.CourseCode as "Course Code", min(mycount) as "Number of Students"
from (select e.CourseCode, count(distinct e.StudentID) mycount 
from Enrollment e
group by e.CourseCode) eTemp, Enrollment e;

select e.CourseCode, count(e.CourseCode) as Enrollment
                from Enrollment e
                group by e.CourseCode
                having count(*) <= all( select e.StudentID from Enrollment e);

quote:

---

Originally posted by cvocvo

For the first one I've tried a couple of things to no avail. The first thing I tried gets me the correct lowest number of students enrolled, but gives the wrong course name:

select e.CourseCode as "Course Code", min(mycount) as "Number of Students"
from (select e.CourseCode, count(distinct e.StudentID) mycount 
from Enrollment e
group by e.CourseCode) eTemp, Enrollment e;

The second thing I tried returns all of the courses and the numbers enrolled for each:

select e.CourseCode, count(e.CourseCode) as Enrollment
                from Enrollment e
                group by e.CourseCode
                having count(*) <= all( select e.StudentID from Enrollment e);

Any thoughts?

---

SELECT TOP 1 e.CourseCode,count(distinct e.StudentID) mycount 
from Enrollment e
group by e.CourseCode
order by mycount ASC