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