Geek Answers Handbook

Find students who enrolled in a course for which there is a prerequisite so that the student does not pass

Sometimes I get a syntax error, and sometimes I just get the wrong results. Therefore, I have a table called "Enrollment" and a table "Prerequisite", they look like this:

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 entered a course that he did not have a precondition, that is, the course has a precondition in the table of preconditions, and the student did not have the necessary condition. The student did not have a prerequisite for if there is a precondition in which he / she did not register, or he / she did not fulfill the precondition. A student has failed the course if the s / he course mark is less than 50.

So here is what I 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
+4
source share
3 answers

First, you probably mean that

(StudentNo, CourseNo) is a Primary Key of Enrollment and what

(CourseNo, PrereqCourseNo) is a Primary Key of Prerequistie

The table has one primary key and a little. In this case, the primary keys are not separate fields, but combinations of two fields.

Secondly, is it Prerequistie or Prerequisite ?

As for your request, I think this is what you need (updated, the first version was wrong):

The request can be changed as "Find all students who are enrolled in a course for which there is a condition that the student does not have )

 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 ) )
+2
source 
 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 who are trying to take 2202 without accepting 1101 (but passing 101); another tries to take 202 after failure 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 | +-----------+----------+------+---------+------+

In the current classes in which students participate, there is a NULL sign (for now).

Request Result:

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

The first thing to jump out on 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 register of 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

It may be wrong, but in the DB I'm using, it complains about it.

0
source

More articles:


All Articles