CODEWITHSUNDEEP
sql
user1675198
user1675198

Reputation: 51

Is this sql query correct? If incorrect how can I fix it?

Schema:

Student(studentid,name,age)
Course(coursename,dept)
enroll(studentid,course,grade)

I need to find , for students in each age group find their average grade for courses they have taken for Political Science and History, and return the names of student with max average grade for each age group

My attempt so far is :

select max(grade), age, name
from (
    select name, age, grade
    from student s, (
            select avg(grade) as grade, e.studentid
            from enroll e
            where dname in ('Political Sciences', 'History')
            group by studentid
            ) as temp
    where s.studentid = temp.studentid
) temp1
group by temp1.age;

I want to know if logically it is correct, and not syntactically.

Upvotes: 0

Views: 87

Answers (2)

Philip Kelley
Philip Kelley

Reputation: 40309

The logic behind your SQL looks solid to me, so long as "Age" correlates to "Age Group", and does not refer to the individual student's age.

Upvotes: 0

Kermit
Kermit

Reputation: 34055

Here's a few tips regarding your query:

  1. Be careful with your table aliases. Make sure that you carry them over to your SELECT
  2. You can only include columns in your SELECT that are being used in your aggregate (GROUP BY). Therefore, you can't GROUP BY temp1.age and SELECT age, name

Upvotes: 1

Related Questions