This is a homework question. I am not asking for the correct answer, I am just looking for help whether I am going in the right direction.
The prompt is that I am to return the first and last name of students who either (OR): (a) Is not currently enrolled (not in Enrollments table) or (b) has the lowest score in any class within their own department.
The database schema is as follows:
My SQL query that I came up with is:
SELECT FIRSTNAME, LASTNAME
FROM STUDENTS
JOIN ENROLLMENTS ON STUDENTS.NETID = ENROLLMENTS.NETID
JOIN COURSES ON COURSES.CRN = ENROLLMENTS.CRN
WHERE STUDENTS.NETID NOT IN (
SELECT NETID
FROM ENROLLMENTS
) OR
ENROLLMENTS.SCORE IN (
SELECT MIN(SCORE)
FROM ENROLLMENTS
WHERE COURSES.DEPARTMENT = STUDENTS.DEPARTMENT
GROUP BY ENROLLMENTS.CRN
);
I tried to use JOIN clauses to combine the three tables where they intersect. The NetId is distinct to each student, identifying them in the Enrollments table. Each class has a unique CRN so I connected them there between Courses table and Enrollments table.
I do not know where the problem lies but the expected outcome is:
Wbixik Yjepuriluwe
Wtoyi Avamijosu
Jheyiresoxo Bsexedoh
Ulerusota Mzuzu
But my outcome is:
Wbixik Yjepuriluwe
Jropop Vduyumi
Jheyiresoxo Bsexedoh
Ulerusota Mzuzu
Looking for any guidance to get me back on track.