SQL 相关子查询与group by的问题，谢谢指导

Question

student(sno,sname,sdept)

course(cno,cname,ccredit)

sc(sno,cno,grade)

1、查询每个系的最高成绩

select x.sno,sdept,grade from student x,sc
where x.sno = sc.sno
and grade =(select max(grade) from sc,student
where sc.sno=student.sno and sdept=x.sdept)

select x.sno,sdept,grade from student x,sc
where x.sno = sc.sno
and grade in(select max(grade) from sc,student
where sc.sno=student.sno and sdept=x.sdept group by sdept)


select sdept,max(grade) from sc,student where sc.sno=student.sno group by sdept

//上面这三种写法结果是一样的，所以我想问第二种的写法的group by sdept是不是多余的呢？

//另外，如果输出要加学号,为什么直接这样是输出了所有，又错了呢？
    select sdept,sc.sno,max(grade) from sc,student where sc.sno=student.sno group by sdept,sc.sno

Answer 1

 select sdept,sc.sno,max(grade) from sc,student where sc.sno=student.sno group by sdept,sc.sno
 这里group by后面有两个字段sdept和sno，表示按系，按学生分组，也就是每个系中每个学生的课程中的最高分，不是每个系所有学生的最高分