How to do this sql query?

Question

Schema:

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

I need to write sql to find student names for each age group with the maximum grade for the courses taken from the history and political science department.

My attempt so far has been

SELECT 
    name 
FROM 
    student 
GROUP BY age 
HAVING sid IN 
            (
                SELECT 
                    max(grade) 
                FROM 
                    enroll e,enroll e1 
                WHERE 
                    e.studentid = e1.studentid 
                AND e.coursename = (
                                    SELECT coursename FROM course 
                                    WHERE 
                                        dname like '%History%'
                                  ) 
                AND e1.coursename = (
                                    SELECT coursename FROM course 
                                    WHERE 
                                        dname like '%PoliticalScience%'
                                    )
            )

Answer 1

You can get the top grade using subquery. Try,

SELECT  d.*,
        f.dept,
        e.grade
FROM    student d
        INNER JOIN enroll e
            on d.studentID = e.studentID
        INNER JOIN course f
            ON e.courseName = f.courseName
        INNER JOIN
            (
                SELECT  a.age, c.dept, Max(b.grade) maxGrade
                FROM    student a
                        INNER JOIN enroll b
                            on a.studentID = b.studentID
                        INNER JOIN course c
                            ON b.courseName = c.courseName
                WHERE    c.dept IN ('history','political science')
                GROUP BY a.age, c.dept
            ) topScore
            ON  topscore.age = d.age AND
                topscore.dept = f.dept AND
                topscore.maxGrade = e.grade