《数据库sql练习题.doc》由会员分享，可在线阅读，更多相关《数据库sql练习题.doc（6页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、已知关系模式： S (SNO,SNAME） 学生关系。SNO 为学号，SNAME 为姓名 C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师 SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩 要求实现如下5个处理： 1 找出没有选修过“李明”老师讲授课程的所有学生姓名 2 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩 3 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名 4 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号 5 列出“1”号课成绩比“2”号课成绩高的
2、所有学生的学号及其“1”号课和“2”号课的成绩 */ -create table s(sno varchar(10),sname varchar(20) -create table c(cno varchar(10),cname varchar(20),cteacher varchar(20) -create table sc(sno varchar(10),cno varchar(20),scgrade integer) insert into s select 1,ghb union all select 2,tw union all select 3,wkp insert into c
3、select 1,语文,李明 union all select 2,数学,王了 union all select 3,英语,其它 insert into sc select 1,2,50 union all select 1,3,52 union all select 2,1,80 union all select 2,2,90 union all select 2,3,59 union all select 3,1,100 union all select 3,2,59 union all select 3,3,70 -delete from sc where sno = 1 and cno
4、 = 1 select * from s select * from c select * from sc - 1 找出没有选修过“李明”老师讲授课程的所有学生姓名 select sname from s where s.sno not in (select sc.sno from sc,c where o = o and c.cteacher = 李明) SELECT SNAME FROM S WHERE NOT EXISTS( SELECT * FROM SC,C WHERE SC.CNO=C.CNO AND C.CTEACHER=李明 AND SC.SNO=S.SNO) - 2 列出有二
5、门以上（含两门）不及格课程的学生姓名及其平均成绩 select s.sname,avg(sc.scgrade) as avgsc from s,sc where s.sno = sc.sno and s.sno in (select sno from sc where scgrade = 2) group by s.sname select s.SNAME,avg(sc.SCGRADE) from S,sc where sc.SCGRADE=2 SELECT S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE) FROM S,SC,( SELECT SNO FRO
6、M SC WHERE SCGRADE=2 )A WHERE S.SNO=A.SNO AND SC.SNO=A.SNO GROUP BY S.SNO,S.SNAME - 3 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名 select tem.sname from ( select s.sname from s where s.sno in(select sno from sc where cno = 1) union all select s.sname from s where s.sno in(select sno from sc where cno = 2) )tem grou
7、p by tem.sname having count(tem.sname) 1 select s.sname from s,sc where s.sno=sc.sno and o=1 and sc.sno in (select b.sno from sc b where b.sno=sc.sno and o=2) select s.sname from s,sc where s.sno=sc.sno and o=1 and exists (select b.sno from sc b where b.sno=sc.sno and o=2) SELECT S.SNO,S.SNAME FROM
8、S,( SELECT SC.SNO FROM SC,C WHERE SC.CNO=C.CNO AND C.cno IN(1,2) GROUP BY SNO HAVING COUNT(DISTINCT c.CNO)=2 )SC WHERE S.SNO=SC.SNO - 4 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号 select sc.sno from sc where cno = 1 and scgrade (select scgrade from sc where sno = 2 and cno = 1) and sno 2 SELECT Sc.SNO FROM SC wh
9、ere o = 1 and exists (select * from sc b where b.sno = sc.sno and sc.scgrade b.scgrade and b.sno = 2) select s.SNO from s,sc where s.sno=sc.sno and o=1 and sc.SCGRADE(select b.SCGRADE from sc b where b.sno=sc.sno and o=2) SELECT S.SNO,S.SNAME FROM S,SC SC1,SC SC2 WHERE SC1.CNO=1 AND SC2.SNO=2 AND SC
10、1.CNO=S.CNO AND SC1.SCGRADESC2.SCGRADE select sc1.sno from sc sc1,sc sc2 where o = 1 and sc1.sno 2 and o = 1 and sc2.sno = 2 and sc1.scgrade sc2.scgrade and o = o - 5 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩 select sc1.sno,sc1.scgrade,sc2.scgrade from sc sc1,sc sc2 where sc1.sno = sc2.sno and o =
11、1 and o = 2 and sc1.scgrade sc2.scgrade select a.SNO,a.SCGRADE from sc a where (a.CNO=1 or a.CNO=2) and a.sno in (select s.SNO from s,sc where s.sno=sc.sno and o=1 and sc.SCGRADE(select b.SCGRADE from sc b where b.sno=sc.sno and o=2) SELECT SC1.SNO,1号课成绩=SC1.SCGRADE,2号课成绩=SC2.SCGRADE FROM SC SC1,SC
12、SC2 WHERE SC1.CNO=1 AND SC2.CNO=2 AND SC1.SNO=SC2.SNO AND SC1.SCGRADESC2.SCGRADE 为管理岗位业务培训信息，建立3个表:S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄C (C#,CN ) C#,CN 分别代表课程编号、课程名称SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩 1. 使用标准SQL嵌套语句查询选修课程名称为税收基础的学员学号和姓名 -实现代码：SELECT SN,SD FROM SWHERE S# IN( SELE
13、CT S# FROM C,SC WHERE C.C#=SC.C# AND CN=N税收基础)2. 使用标准SQL嵌套语句查询选修课程编号为C2的学员姓名和所属单位-实现代码：SELECT S.SN,S.SD FROM S,SCWHERE S.S#=SC.S# AND SC.C#=C23. 使用标准SQL嵌套语句查询不选修课程编号为C5的学员姓名和所属单位-实现代码：SELECT SN,SD FROM SWHERE S# NOT IN( SELECT S# FROM SC WHERE C#=C5)4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位-实现代码：SELECT SN,SD FROM SWHERE S# IN ( SELECT S# FROM SC RIGHT JOIN C ON SC.C#=C.C# GROUP BY S# HAVING COUNT(*)=COUNT(S#)5. 查询选修了课程的学员人数-实现代码：SELECT 学员人数=COUNT(DISTINCT S#) FROM SC6. 查询选修课程超过5门的学员学号和所属单位-实现代码：SELECT SN,SD FROM SWHERE S# IN( SELECT S# FROM SC GROUP BY S# HAVING COUNT(DISTINCT C#)5)