Unanswered: Please help with creating this SQL query.
So I'm trying to use the right SQL commands to solve this question..
"return the top 5 students with the highest major GPA (Grade Point Average). Major GPA is determined by the courses taught in a student’s home department and has the attribute Core=1." as a single query.
I tried the following with no luck.
SELECT s1.name,s1.deptID,avg(grade) as GPA
JOIN Students s1 using (studentID)
JOIN Departments D1 USING (deptID)
JOIN Sections USING (sectionID)
JOIN Courses USING (deptID)
JOIN Departments D2 ON (Courses.deptID=D2.deptID)
WHERE D1.deptID=D2.deptID AND core=1
GROUP BY s1.studentid
ORDER by GPA DESC
What am I doing wrong? The WHERE course.core=1 seems to not be doing anything for me. To clarify, I'm trying to only average the grades that are core=1. Any help is greatly appreciated. Thank you.
I was under the impression that's how I can compare the department of the student with the department of the courses. That way, I can filter it to only consider courses within the student's department. I'm willing to try out other methods though.
My real question is how can I do a conditional AVG, so that it'll only average in the courses that have core=1. The error is that regardless of whether core=0 or core=1, my answer remains the same, proving to me that it's not affecting the query at all.
Please let me know if I need to clarify anything else. I'm still stuck. Thanks for replying.