CODEWITHSUNDEEP
mysqlsql
user1675198
user1675198

Reputation: 51

How to do this sql query?

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%'
                                    )
            )

Upvotes: 5

Views: 170

Answers (2)

John Woo
John Woo

Reputation: 263683

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

Upvotes: 2

Alpha01
Alpha01

Reputation: 856

You can try something along these lines

select 
    s.name 
from 
    student s, enroll e,
    (
        select 
            s.age as age, e.coursename as coursename, max(e.grade) as grade 
        from 
            student s, course c, enroll e
        where 
            s.studentid = e.studentid 
        and c.coursename = e.coursename 
        and (c.dept = 'history' or c.dept = 'political science') 
        group by s.age, e.coursename
    ) t
where 
    s.studentid = e.studentid 
and s.age = t.age 
and e.grade = t.grade 
and e.coursename = t.coursename

Upvotes: 0

Related Questions