Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2011
    Posts
    2

    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
    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
    Last edited by vannuge; 02-17-11 at 02:39. Reason: typo

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2011
    Posts
    2
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •