CODEWITHSUNDEEP
sql
user511792
user511792

Reputation: 519

Find students who enrolled in a course for which there exists a prerequisite that the student has not passed

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:

Enrollment(StudentNo, CourseNo, Term, Section, Mark)
    StudentNo and CourseNo are primary keys
Prerequistie(CourseNo, PrereqCourseNo)
     CourseNo and PrereqCourseNo are primary keys

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.

So here's what i've got: 

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 >= 50)) prereqNeeded 
WHERE Enrollment.StudentNo = prereqNeeded.StudentNo 
and prereqNeeded.CourseNo = ENROLLMENT.CourseNo

Upvotes: 4

Views: 4501

Answers (3)

Ronen Botzer
Ronen Botzer

Reputation: 7117

SELECT CurrentPrereqs.StudentNo, CurrentPrereqs.CourseNo, PrereqCourseNo, Mark
FROM
  (SELECT StudentNo, Current.CourseNo, PrereqCourseNo
     FROM
       (SELECT * from Enrollment where Mark IS NULL) AS Current
     LEFT JOIN Prerequisite p ON Current.CourseNo=p.CourseNo
  ) AS CurrentPrereqs
LEFT JOIN Enrollment PreviousMarks
ON (CurrentPrereqs.StudentNo=PreviousMarks.StudentNo AND
    CurrentPrereqs.PrereqCourseNo=PreviousMarks.CourseNo)
WHERE PreviousMarks.Mark IS NULL OR PreviousMarks.Mark <50;

Some test data: Course 101 is a prerequisite for courses 202 and 2202; Course 202 is a prerequisite for course 303, course 1101 is a prerequisite for course 2202:

select * from Prerequisite;
+----------+----------------+
| CourseNo | PrereqCourseNo |
+----------+----------------+
|      202 |            101 |
|      303 |            202 |
|     2202 |            101 |
|     2202 |           1101 |
+----------+----------------+

Two students, one which is trying to take 2202 without taking 1101 (but having passed 101); another trying to take 202 after failing 101:

select * from Enrollment order by StudentNo;
+-----------+----------+------+---------+------+
| StudentNo | CourseNo | Term | Section | Mark |
+-----------+----------+------+---------+------+
|         1 |      101 | F01  |       1 |   92 |
|         1 |      202 | S01  |       1 |   88 |
|         1 |      303 | F02  |       1 | NULL |
|         1 |     2202 | F02  |       1 | NULL |
|         2 |      101 | F01  |       2 |   48 |
|         2 |      202 | F02  |       2 | NULL |
+-----------+----------+------+---------+------+

The current classes the students are enrolled in have a NULL Mark (for now).

The result of the query is:

+-----------+----------+----------------+------+
| StudentNo | CourseNo | PrereqCourseNo | Mark |
+-----------+----------+----------------+------+
|         1 |     2202 |           1101 | NULL |
|         2 |      202 |            101 |   48 |
+-----------+----------+----------------+------+

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

First, you probably mean that

(StudentNo, CourseNo) is the Primary Key of Enrollment and that

(CourseNo, PrereqCourseNo) is the Primary Key of Prerequistie

A table has one Primary Key and not many. In this case, the primary keys are not single fields but combinations of two fields.

Second, is it Prerequistie or Prerequisite ?

Regarding your query, I think this is what you need (updated, first version was wrong):

The query can be reworded like "Find all student who enrolled in a course for which there exists a prerequisite that the student has not passed"

SELECT e.StudentNo, e.CourseNo
FROM Enrollment e
WHERE EXISTS
  ( SELECT *
    FROM Prerequisite p
    WHERE p.CourseNo = e.CourseNo
      AND NOT EXISTS
            ( SELECT *
              FROM Enrollment ep
              WHERE ep.CourseNo = p.PrereqCourseNo
                AND ep.StudentNo = e.StudentNo
                AND ep.Mark >= 50
            )
  )

Upvotes: 2

scrappedcola
scrappedcola

Reputation: 10572

The first thing to jump out at me is 

 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 >= 50)) prereqNeeded 
WHERE Enrollment.StudentNo = prereqNeeded.StudentNo 
and prereqNeeded.CourseNo = ENROLLMENT.CourseNo

I think you need to keep the case of the table names. 

 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 >= 50)) prereqNeeded 
WHERE Enrollment.StudentNo = prereqNeeded.StudentNo 
and prereqNeeded.CourseNo = Enrollment.CourseNo

Could be wrong but on the DB I use it complains about this.

Upvotes: 0

Related Questions