title: Find students who enrolled in a course for which there exists a prerequisite that the student has not passed
body: <p>Sometimes I get a syntax error and sometimes I just simply get wrong results.
  So I have a table called Enrollment and a table called Prerequisite, they look like:</p>
  
  <pre><code>Enrollment(StudentNo, CourseNo, Term, Section, Mark)
      StudentNo and CourseNo are primary keys
  Prerequistie(CourseNo, PrereqCourseNo)
       CourseNo and PrereqCourseNo are primary keys
  </code></pre>
  
  <p>So now I want to find students who enrolled in a course which s/he did not have 
  the prerequisite, i.e. the course has a prerequisite in the prerequisite table and the student didn't have the prerequisite. A student  did not have a prerequisite for an 
  advanced course if there is a prerequisite in which s/he did not enroll, or 
  s/he failed a prerequisite. A student failed a course if the course mark s/he 
  obtained is less than 50.</p>
  
  <p>So here's what i've got: </p>
  
  <pre><code>SELECT distinct Enrollment.StudentNo, enrollment.CourseNo, prereqNeeded.prereq 
  from Enrollment, (SELECT ENROLLMENT.CourseNo, PrereqCourseNo, StudentNo
              from enrollment, prerequisite 
                  where ENROLLMENT.CourseNo = PREREQUISITE.CourseNo AND 
                  NOT EXISTS 
                  (SELECT StudentNo, MARK 
                  FROM ENROLLMENT 
                  WHERE enrollment.CourseNo = PrereqCourseNo 
                  AND MARK &gt;= 50)) prereqNeeded 
  WHERE Enrollment.StudentNo = prereqNeeded.StudentNo 
  and prereqNeeded.CourseNo = ENROLLMENT.CourseNo
  </code></pre>