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
FROM
Transcripts
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
LIMIT 5
;

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.

here is the schema.
http://pic80.picturetrail.com/VOL966.../395200168.jpg

sorry, "with no luck" is not an error message that i recognize

you will have to explain what the problem is

also, why do you have two departments tables in the query?

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.

do me a favour, please re-write your query without using the USING keyword, instead, use an ON clause where columns are matched explicitly

also, please qualify every column properly with its table prefix