Question

查询每门课成绩最高分的同学的sql语句，输出课程名，姓名，学号，分数。表的结构如下。写出完整的sql语句





Answer 1

select cname,sname,sc.sno,grade
from student,SC,course
where student.sno =SC.sno and SC.cno =course.cno and grade=(select MAX(grade) from SC where SC.cno =course.cno )

Answer 2

首先三表连接，然后select max(分数),姓名,学号, 课程 from 连接表 group by 姓名,学号, 课程
即可。

Answer 3

SELECT cname,sname,student.sno,grade
FROM student join sc on student.sno=sc.sno
join course on course.cno=sc.cno
where grade=(select max(grade)
from sc
where cno=course.cno )

Answer 4

select cname,sname,student.sno,sc1.grade

from student join SC as sc1 on student.sno=sc1.sno
join course on sc1.cno=course.cno
where not exists(
select * from SC as sc2
where sc1.cno=sc2.cno and sc1.grade<sc2.grade)and grade is not null
order by sc1.cno