SQL查询更新语句-课堂练习和习题.doc

SQL查询语句 课堂练习和习题一、试用SQL查询语句表达以下对教学数据库中三个根本表S、SC、C的查询：S(sno,sname,SAGE,SSEX) 各字段表示学号，年龄，性别Sc(sno,cno,grade) 各字段表示学号，课程号，成绩C(cno,cname, TEACHER) 各字段表示课程号，课程名和教师名其中SAGE， grade是数值型，其他均为字符型。要求用SQL查询语句实现如下处理：1统计有学生选修的课程门数。2求选修C4课程的学生的平均年龄。3求LIU教师所授课程的每门课程的学生平均成绩。4统计每门课程的学生选修人数超过2人的课程才统计。要求输出课程号和选修人数，查询结果按人数降序排列，假设人数一样，按课程号升序排列。5检索学号比WANG同学大，而年龄比他小的学生。6检索以WANG打头的所有学生的和年龄。7在SC中检索成绩为空值的学生学号和课程号。8求年龄大于女同学平均年龄的男学生和年龄。9求年龄大于所有女同学年龄的男学生和年龄。其中涉与单表题：.7参考答案：1统计有学生选修的课程门数。 SELECT COUNT(DISTINCT Cno) FROM SC2求选修C4课程的学生的平均年龄。SELECT AVG(SAGE )FROM S WHERE Sno IN(SELECT Sno FROM SC WHERE Cno=4)或者，SELECT AVG(SAGE )FROM S,SC WHERE S.Sno=SC.Sno AND Cno=43求LIU教师所授课程的每门课程的学生平均成绩。SELECT AVG(GRADE)FROM SC join C on SC.Cno=C.CnoWHERE TEACHER=liu GROUP BY c.Cno另：SELECT CNAME,AVG(GRADE) FROM SC ,C WHERE SC.Cno=C.Cno AND TEACHER=liu GROUP BY c.Cno,cname4统计每门课程的学生选修人数超过2人的课程才统计。要求输出课程号和选修人数，查询结果按人数降序排列，假设人数一样，按课程号升序排列。SELECT DISTINCT Cno,COUNT(Sno)FROM SC GROUP BY Cno HAVING COUNT(Sno)2ORDER BY 2 DESC, Cno ASC或：SELECT DISTINCT Cno,COUNT(Sno) as 人数FROM SC GROUP BY Cno HAVING COUNT(Sno)2ORDER BY 人数DESC, Cno ASC5检索学号比WANG同学大，而年龄比他小的学生。SELECT X.SNAME FROM S AS X, S AS Y WHERE Y.SNAME=WANG AND X.SnoY.Sno AND X.SAGE(select sno from s where SNAME=WANG) and SAGE(SELECT AVG(SAGE) FROM S WHERE SSEX=女)9求年龄大于所有女同学年龄的男学生和年龄。SELECT SNAME,SAGE FROM S AS XWHERE X.SSEX=男 AND X.SAGE ALL (SELECT SAGE FROM S AS Y WHERE Y.SSEX=女)二、试用SQL更新语句表达对教学数据库中三个根本表S、SC、C的各个更新操作：要求用SQL更新语句实现如下处理：1往根本表S中插入一个学生元组S9，WU，18。2在根本表S中检索每一门课程成绩都大于等于80分的学生学号、和性别，并把检索到的值送往另一个已存在的根本表S1Sno，SNAME，SSEX。3在根本表SC中删除尚无成绩的选课元组。4把WANG同学的学习选课和成绩全部删去。5把选修数据库原理课不与格的成绩全改为空值。6把低于总平均成绩的女同学成绩提高5%。7在根本表SC中修改4号课程的成绩，假设成绩小于等于75分时提高5%，假设成绩大于75分时提高4%用两个UPDATE语句实现。参考答案：1往根本表S中插入一个学生元组S9，WU，18。 INSERT INTO S(Sno,SNAME,SAGE) VALUES(59,WU,18) 2在根本表S中检索每一门课程成绩都大于等于80分的学生学号、和性别，并把检索到的值送往另一个已存在的根本表S1Sno，SANME，SSEX。select Sno,SNAME,SSEX into s1 from studentdelete from s1INSERT INTO S1(Sno,SNAME,SSEX) SELECT Sno,SNAME,SSEX FROM S WHERE NOT EXISTS(SELECT * FROM SC WHERE GRADE80 AND S.Sno=SC.Sno)select * from s1考虑：以上会有什么问题？INSERT INTO S1(Sno,SNAME,SSEX) SELECT Sno,SNAME,SSEX FROM S WHERE NOT EXISTS(SELECT * FROM SC WHERE GRADE80 AND S.Sno=SC.Sno or S.Sno=SC.Sno and grade is null) and sno in (select sno from sc)3在根本表SC中删除尚无成绩的选课元组。DELETE FROM SC WHERE GRADE IS NULL4把WANG同学的学习选课和成绩全部删去。DELETE FROM SC WHERE Sno IN(SELECT Sno FROM S WHERE SNAME=WANG)5把选修数据库原理课不与格的成绩全改为空值。UPDATE SC SET GRADE=NULL WHERE GRADE60 AND Cno IN(SELECT Cno FROM C WHERE CNAME=数据库原理)6把低于总平均成绩的女同学成绩提高5%。UPDATE SC SET GRADE=GRADE*1.05 WHERE GRADE(SELECT AVG(GRADE) FROM SC) AND Sno IN (SELECT Sno FROM S WHERE SSEX=女)7在根本表SC中修改4号课程的成绩，假设成绩小于等于75分时提高5%，假设成绩大于75分时提高4%用两个UPDATE语句实现。UPDATE SC SET GRADE=GRADE*1.05 WHERE Cno=4 AND GRADE75三、问题描述：为管理岗位业务培训信息，建立3个表:S(Sno,SN,SD,SA)Sno,SN,SD,SA分别代表学号、学员、所属单位、学员年龄C(Cno,CN)Cno,CN分别代表课程编号、课程名称SC(Sno,Cno,G)Sno,Cno,G分别代表学号、所选修的课程编号、学习成绩要现如下5个处理：1.使用标准SQL嵌套语句查询选修课程名称为税收根底的学员学号和2.使用标准SQL嵌套语句查询选修课程编号为C2的学员和所属单位3.使用标准SQL嵌套语句查询不选修课程编号为C5的学员和所属单位4.使用标准SQL嵌套语句查询只选修了一门课程的学员和所属单位5.查询选修了课程的学员人数6.查询选修课程超过5门的学员学号和所属单位参考答案：1使用标准SQL嵌套语句查询选修课程名称为税收根底的学员学号和SELECTSN,SDFROMSWHERESnoIN(SELECTSnoFROMC,SCWHEREC.Cno=SC.CnoANDCN=N税收根底)2使用标准SQL嵌套语句查询选修课程编号为C2的学员和所属单位SELECTS.SN,S.SDFROMS,SCWHERES.Sno=SC.SnoANDSC.Cno=C23使用标准SQL嵌套语句查询不选修课程编号为C5的学员和所属单位SELECTSN,SDFROMSWHERESnoNOTIN(SELECTSnoFROMSCWHERECno=C5)4使用标准SQL嵌套语句查询只选修了一门课程的学员和所属单位SELECT SN,SD FROM SWHERE Sno IN( SELECT Sno FROM SC inner JOIN C ON SC.Cno=C.Cno GROUP BY SnoHAVING COUNT(*)=1)5查询选修了课程的学员人数SELECT学员人数=COUNT(DISTINCTSno)FROMSC6查询选修课程超过5门的学员学号和所属单位SELECTSN,SDFROMSWHERESnoIN(SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(DISTINCTCno)5)四、问题描述：关系模式：S(SNO,SNAME学生关系。SNO为学号，SNAME为C(CNO,CNAME,TEACHER)课程关系。CNO为课程号，CNAME为课程名，TEACHER为任课教师SC(SNO,CNO,GRADE)选课关系。GRADE为成绩要现如下5个处理：1找出没有选修过“明教师讲授课程的所有学生2列出有二门以上含两门不与格课程的学生与其平均成绩3列出既学过“1”号课程，又学过“2”号课程的所有学生4列出“1”号课成绩比“04010002”号同学该门课成绩高的所有学生的学号5列出“1”号课成绩比“2”号课成绩高的所有学生的学号与其“1”号课和“2”号课的成绩参考答案：1找出没有选修过“明教师讲授课程的所有学生select sname from s where not exists (select * from c,sc where o=o AND c.teacher=N明 AND s.sno=sc.sno)参考：select sno,sname from s where sno not in(select sno from sc,c where o=o AND c.teacher=Nliu)2列出有二门以上含两门不与格课程的学生与其平均成绩SELECT S.SNO,SNAME,AVG_GRADE=AVG(SC.GRADE)FROM S,SC WHERE GRADE=2或SELECT S.SNO,S.SNAME,AVG_GRADE=AVG(SC.GRADE)FROM S,SC,( SELECT SNO FROM SC WHERE GRADE=2)A WHERE S.SNO=A.SNO AND SC.SNO=A.SNOGROUP BY S.SNO,S.SNAME3列出既学过“1”号课程，又学过“2”号课程的所有学生SELECT S.SNO,S.SNAME FROM S,scwhere S.SNO=SC.SNO ando=1 and s.sno in (select S.sno from S,scwhere S.SNO=SC.SNO ando=2)或SELECT S.SNO,S.SNAME FROM 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”号课成绩比“04010002”号同学该门课成绩高的所有学生的学号SELECT S.SNO,S.SNAME FROM S,SC WHERE SC.CNO=1and SC.sNO=S.sNO AND grade(select grade from s,sc where s.SNO=04010002and SC.CNO=1and SC.sNO=S.sNO)5。列出“1”号课成绩比“2”号课成绩高的所有学生的学号与其“1”号课和“2”号课的成绩SELECT SC1.SNO,1号课成绩=SC1.GRADE,2号课成绩=SC2.GRADE FROM SC SC1,SC SC2 WHERE SC1.CNO=1 AND SC2.CNO=2 AND SC1.SNO=SC2.SNO AND SC1.GRADESC2.GRADE四、通配符理解题请说出以下通配符的含义1LIKE Mc% 将搜索以字母 Mc 开头的所有字符串如 McBadden。2LIKE %inger 将搜索以字母 inger 结尾的所有字符串如 Ringer、Stringer。3LIKE %en% 将搜索在任何位置包含字母 en 的所有字符串如 Bennet、Green、McBadden。4LIKE _heryl 将搜索以字母 heryl 结尾的所有六个字母的名称如 Cheryl、Sheryl。5LIKE M-Zinger 将搜索以字符串 inger 结尾、以从 M 到 Z 的任何单个字母开头的所有名称如 Ringer。6LIKE Mc% 将搜索以字母 M 开头，并且第二个字母不是 c 的所有名称如 MacFeather。7LIKE 5% - 5% 8LIKE 5% - 5 后跟 0 个或更多字符的字符串 9LIKE _n - _n 10LIKE _n -an,in, on (and so on) 11LIKE a-cdf - a, b, c, d, or f 12LIKE -acdf - -, a, c, d, or f 13LIKE - 14LIKE - 8 / 8