Geek asks and answers

How to execute this sql query?

Scheme:

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

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

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%'
                                    )
            )
+5
source share
2 answers

You can get the upper class using subquery. Give it a 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
+2
source

You can try something in this direction.

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
0
source

More articles:


All Articles