查询选修c语言课程的学生学号和姓名,数据库嵌套循环查询联系——1.查询选修了课程号为“3”的学生姓名和系别、4.查询其它系中‘2‘号课程比信息系所有学生分数高的学生学号和姓名、6.查询每门课程中低于该...

1.查询选修了课程号为“3”的学生姓名和系别

Select sname,sdept from student where sno in (

select sno from sc where cno=‘3’);

2.查询与“刘一平”来自同一个系的学生姓名

Select sname from student where sdept in (

select sdept from student where sname=‘刘晨’) and sname <> ‘刘晨’;

3.查询Sc表中的最高分的学生学号和课程号

select sno,cno from sc where grade in(

select max(grade) from sc);

另一种：

select sno,cno from sc where grade >=all(

select grade from sc);

4.查询其它系中‘2‘号课程比信息系所有学生分数高的学生学号和姓名；

select distinct student.sno,sname from student,sc where student.sno=sc.sno and cno=‘2’ and grade >all (

select grade from sc where sdept=‘is’ ) and sdept <> ‘is’;

改进：

select distinct student.sno,sname from student,sc where student.sno=sc.sno and cno=‘2’ and grade >all ( select grade from sc where sno in(select sno from student where sdept = ‘is’) ) and sdept <> ‘is’;

老师：

select sno,sname from student where sno in (select sno from sc where cno=‘2’ and sno not in(select sno from student where sdept=‘cs’) and grade>(select max(grade) from sc where cno=‘2’ and sno in(select sno from student where sdept=‘cs’)));

5.查询其它系中比信息系所有学生年龄大的学生姓名和性别；

Select distinct sname,ssex from student where sage >all (

select sage from student where sdept = ‘is’ ) and sdept <> ‘is’;

6.查询每门课程中低于该课程平均成绩的学生学号和姓名；

Select student.sno,sname from student,sc x where student.sno = x.sno and grade < (

select avg(grade) from sc y where y.cno=x.cno ) ;

7.查询“信息系”中选课最多的学生学号；

select student.sno from student,sc where sc.sno=student.sno and student.sdept=‘is’ group by sc.sno having count() >= all(

select count() from sc, student where sc.sno= student.sno and sdept=‘is’ group by sc.sno);

select sno from student where sdept=‘cs’ and sno in(select sno from sc group by sno having count()>=all(select count() from sc where sno in(select sno from student where sdept=‘cs’)group by sno));

8.查询所有选修“计算机导论”课程的“男”同学的成绩表

select student.sno,sname,course.cno,cname,grade from student,sc,course where student.sno=sc.sno and course.cno=sc.cno and ssex=‘男’ and student.sno in(select sno from sc,course where sc.cno=course.cno and cname = ‘计算机导论’);

9.查询有两门及以上课程不及格的学生学号和姓名；

10.查询比“2”号课程平均分高的其它课程信息；