2 oiu1010110 oiu1010110 于 2016.04.13 22:31 提问

sql 查询每门课程成绩最高的女同学

course(cname,ccredit,cno)
sc(sno,cno,grade)
student(sno,ssex,sage,sdept)
有这三张表
1、检索数据库的成绩在90分以上的学生的学号和姓名
--(有问题)
select student.sno,student.sname from student
where sno in (select sno from sc where
grade in(select grade from sc where grade >= 90 and cno in(select cno from course where sname = '数据结构'))
); //什么也没输出
2、查询数据结构课程成绩最高的学生的姓名
select sname,grade from student,course,sc where student.sno = sc.sno and sc.cno = course.cno and
cname = '数据结构' and grade>=all(select grade from sc where cname = '数据结构'); //什么也没输出

select sname from student where sno in(select sno from sc 
                                                    where grade>=all(select grade from sc where cno in(select cno from course where cname = '数据结构'))
                                                    and cno in(select cno from course where cname = '数据结构')
                                                    );  //对的

3、查询每个系的最高成绩
select student.sno,sdept,max(grade) from student,sc where student.sno = sc.sno group by student.sno,sdept having grade >=all(select grade from sc); //HAVING 子句中的列 'sc.grade' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中

4、检索数据库的成绩在90分以上的学生的学号和姓名
select student.sno,student.sname from student
where sno in (select sno from sc where
grade in(select grade from sc where grade >= 90 and cno in(select cno from course where sname = '数据结构'))
); //有问题,什么也没输出
select student.sno,student.sname from sc,student,course where sc.cno = course.cno and student.sno = sc.sno and course.cname = '数据结构' and sc.grade >=90; //对的

6、查询每个系的最高成绩
select student.sno,sdept,max(grade) from student,sc where student.sno = sc.sno group by student.sno,sdept having grade >=all(select grade from sc);//有问题,什么也没输出

7、查询每门课程成绩最高的女同学
select sc.sno,cno,max(grade) from sc,student where sc.sno = student.sno and ssex = '女' group by sc.sno,cno having grade >=all(select grade from sc group by sc.sno,cno); //有问题
select sc.sno,cno,max(grade) from sc,student where sc.sno = student.sno and ssex = '女'and grade >=all(select grade from sc group by cno); //有问题

2个回答

CSDNXIAON
CSDNXIAON   2016.04.13 22:42

sql查询每个班上成绩最高的学生信息
sql获取每门课程成绩最好的学生信息
----------------------同志你好,我是CSDN问答机器人小N,奉组织之命为你提供参考答案,编程尚未成功,同志仍需努力!

devmiao
devmiao   Ds   Rxr 2016.04.14 04:52
Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!