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”号课程平均分高的其它课程信息;