数据库查询的实现题目.docVIP

4月11日实验要求 针对上次实验建立的数据库实现一下查询 查询选修人数超过50人的课程的名字，以及每门课的选课总人数，并将结果按照人数的升序排序 select Cname,COUNT(*) from SC,C where SC.Cno=C.Cno group by Cname,SC.Cno having COUNT (*)2 order by SC.Cno 查询每门课的成绩都比这门课的其他同学高的学生的学号 select distinct Sno from SC where Grade any (select Grade from SC,C where SC.Cno = C.Cno ) 查询每个同学的学号和姓名以及这个同学成绩为优秀的课程的门数。 select SC.Sno,S.Sname,COUNT(*)as 优秀的课程数 from (select SC.Cno,SC.Grade from SC group by SC.Grade,SC.Cno having SC.Grade90) as table1,SC,S where table1.Cno=SC.Cno and table1.Grade=SC.Grade and SC.Sno=S.Sno group by SC.Sno,S.Sname; select distinct Sno,Sname,Sdept from S,C where 2011-Sborn all (select 2011-Sborn from S where Sdept = 软件工程) and Sdept软件工程 5.查询每个系中年龄高于这个系的平均年龄的学生的学号和姓名、年龄 select Sno,Sname,2011-Sborn from S S1 where 2011-Sborn (select AVG(2011-Sborn) from S S2 where S1.Sdept =S2 .Sdept ) 6. 查询没有选课的学生的学号和姓名 select Sno,Sname from S where not exists (select * from SC where Sno=S.Sno ) 7. 查询每个同学成绩高于自己选修课程的平均分的学生的学号和选修课程的课号 select COUNT(*) 选课人数 from SC,S,C where SC.Cno =C.Cno and SC.Sno = S.Sno and S.Sdept = 软件工程 and C.Cname = 数据库 select S.Sno ,Sname,Ssex,Sdept,Cno,Grade from S left outer join SC on (S.Sno = SC.Sno ) 9. 查询选修‘数据库’的‘软件工程’系的学生的总人数 select COUNT(*) from Student,Course where Sdept=软件工程 and Cno = (select Cno from Course where Cname=数据库) select Sname,2011-Sborn from Student where 2011-Sbornany (select 2011-Sborn from S where Sdept=软件工程) and Sdept 软件工程 查询其他系中比‘软件工程’所有学生年龄都小的学生姓名和年龄 select Sname,2011-Sborn from S where 2011-Sbornall (select 2011-Sborn from Student where Sdept=软件工程) and Sdept 软件工程 update S set Sborn = Sborn -2 where 2011-Sborn (select AVG (2011-Sborn )