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

Question

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); //有问题

Answer 1

http://zhidao.baidu.com/link?url=9NjSAvoHO5bVdxm8OGbJX1QSMWCrhNT99znXymke9GRuR7Vwgf9rPmMsVkn2eUbqt4Sw7XMvTt7t76WGQKepfuTeU8qG_YJcBPJP-A8T-w7