user3121518 user3121518 - 7 months ago 20
SQL Question

SQL - Where not exists < 70

select STUDENT.S_LNAME, STUDENT.S_FNAME, ENROLLMENT.MARK, COURSE_SECTION.LOC_ID, S_Sex

from (STUDENT

inner join ENROLLMENT on STUDENT.S_ID = ENROLLMENT.S_ID

inner join COURSE_SECTION on ENROLLMENT.C_SE_ID = COURSE_SECTION.C_SE_ID
)

where MARK > 70 and exists (select *
from ENROLLMENT
where LOC_ID='Bundoora' and S_Sex='Female');


I want to find all female student names who are in Bundoora and have never received marks < 70 in their enrolled courses.

Answer
  with sub as(select <required_fields> from student_table a where student_id not in(select student_id from <enrollment> where mark< 70)) --Filters out all records with score <70 at any point
    select S_LNAME, S_FNAME, MARK, LOC_ID, S_Sex 
 from
    sub inner join ENROLLMENT_A1 
       on (sub.S_ID = ENROLLMENT_A1.S_ID)
    inner join COURSE_SECTION_A1 
        on (ENROLLMENT_A1.C_SE_ID = COURSE_SECTION_A1.C_SE_ID ) 
    where LOC_ID='Bundoora' and S_Sex='Female';

Appropriate modifications based on your input.

Basically, you could filter out all student ids who have ever scored less than 70 using a temporary table, or even in your subselect, and then proceed with your operations.