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求年龄大于所有女同学年龄的男学生姓名和年龄。 其中涉及单表题：参考答案：1统计有学生选修的课程门数。 SELECT COUNT(DISTINCT Cno) FROM SC 2求选修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,cname 4统计每门课程的学生选修人数（超过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 X WHERE 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分的学生学号、姓名和性别， 并把检索到的值送往另一个已存在的基本表S1（Sno，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分的学生学号、姓名和性别， 并把检索到的值送往另一个已存在的基本表S1（Sno，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 and cno=1 and s.sno in (select S.sno from S,scwhere S.SNO=SC.SNO and cno=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