Table Structure

Courses (id, course information)
Students (id, student information)
Semester (id Semester information)
CourseOffering (id, CourseID, SemesterID)
StudentEnrollment (id, StudentID, CourseOfferingID)
Prerequisites (id, CourseID, PCourseID)

Select a Prequisite with a known courseID

SELECT PCourseID 
FROM Prerequisites 
WHERE CourseID = @CourseID

Select Courses a student has taken given
@StudentID
returns CourseID

SELECT CourseID 
FROM CourseOffering 
WHERE id IN
(SELECT CourseOfferingID 
 FROM StudentEnrollment 
 WHERE StudentID = @StudentID)

Select Prerequisites of all courses a 
student has taken. Just finds the pre-
requisites and returns the CourseID of
 the courses.

SELECT PCourseID 
FROM Prerequisites 
WHERE CourseID IN
(SELECT CourseID 
 FROM CourseOffering 
 WHERE id IN
(SELECT CourseOfferingID 
 FROM StudentEnrollment 
 WHERE StudentID = @StudentID))

Finds the list of all pre-requisites 
for a course that a student has not taken.

SELECT PCourseID 
 FROM Prerequisites 
 WHERE CourseID = @CourseID
AND
PCourseID NOT IN
(SELECT CourseID 
 FROM CourseOffering 
 WHERE id IN
(SELECT CourseOfferingID 
 FROM StudentEnrollment 
 WHERE StudentID = @StudentID))