CODEWITHSUNDEEP
sqloracle11g
IntoTheDeep
IntoTheDeep

Reputation: 4118

SQL query needs improvement

I am trying to get FIRSTNAME and LASTNAME of STUDENT, which have started after course after year 2010 and it's average grade from all disciplines is above 5.50. Year is integer(4) Grades props are - null (not finished yet), 0 (not being on exam) and 2,3,4,5,6.

Database: Oracle

What am I trying is: for each student with YEARSATART greater than 2010, get average of his grades, except those which are nulls or zeros.

Code I have tried:

SELECT DISTINCT FIRSTNAME, LASTNAME 
FROM PERSON  
JOIN STUDENT ON PERSON.PERSONID = STUDENT.STUDENTID 
JOIN CLASSSTUDENT ON STUDENT.STUDENTID = CLASSSTUDENT.STUDENTID
WHERE STUDENT.YEARSTART > 2010
  AND (SELECT AVG(FINALGRADE) 
       FROM CLASSSTUDENT
       WHERE FINALGRADE IS NOT NULL
         AND FINALGRADE > 1) >= 4.50;

I guess my query is wrong because I see not proper results. Do you have any ideas of improvements / fixes? Where could be my mistake? I got feeling that I get average from all grades, not for each student separately

Upvotes: 0

Views: 52

Answers (2)

dani herrera
dani herrera

Reputation: 51745

You need to join nested query to external one, is preferable to set alias to each table to allow disambiguation. Also you don't need CLASSSTUDENT on main query:

SELECT DISTINCT FIRSTNAME, LASTNAME 
FROM PERSON  
JOIN STUDENT S ON PERSON.PERSONID = S.STUDENTID 
WHERE STUDENT.YEARSTART > 2010
AND (SELECT AVG(FINALGRADE) 
        FROM CLASSSTUDENT C
        WHERE C.STUDENTID = S.STUDENTID
        AND FINALGRADE > 1) >= 4.50;

Also not null, on nested query, is not needed because aggregation function don't compute null values.

Upvotes: 0

Obie
Obie

Reputation: 477

I think you are just missing a where on your subselect. You need to filter on classstudent where the StudentID=Student.StudentID:

SELECT DISTINCT FIRSTNAME, LASTNAME FROM PERSON  
JOIN STUDENT ON PERSON.PERSONID = STUDENT.STUDENTID 
JOIN CLASSSTUDENT ON STUDENT.STUDENTID = CLASSSTUDENT.STUDENTID
WHERE STUDENT.YEARSTART > 2010
AND (SELECT AVG(FINALGRADE) 
    FROM CLASSSTUDENT
    WHERE FINALGRADE IS NOT NULL 
    AND FINALGRADE > 1
    AND STUDENTID=STUDENT.STUDENTID) >= 4.50;

Upvotes: 2

Related Questions