《sql查询更新语句-课堂练习和习题》由会员分享，可在线阅读，更多相关《sql查询更新语句-课堂练习和习题（8页珍藏版）》请在金锄头文库上搜索。
1、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人的课程才统计）。要求输出课程号和选修人
2、数， 查询结果按人数降序排列，若人数相同，按课程号升序排列。 5检索学号比WANG同学大，而年龄比他小的学生姓名。 6检索姓名以WANG打头的所有学生的姓名和年龄。 7在SC中检索成绩为空值的学生学号和课程号。 8求年龄大于女同学平均年龄的男学生姓名和年龄。 9求年龄大于所有女同学年龄的男学生姓名和年龄。 其中涉及单表题：1.4.6.7参考答案：1统计有学生选修的课程门数。 SELECT COUNT(DISTINCT Cno) FROM SC 2求选修C4课程的学生的平均年龄。 SELECT AVG(SAGE )FROM S WHERE Sno IN(SELECT Sno FROM SC WH
3、ERE 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人的课程才统计）。要求输出课程
4、号和选修人数， 查询结果按人数降序排列，若人数相同，按课程号升序排列。 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.
5、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
6、往基本表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,SNA
7、ME,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考
8、虑：以上会有什么问题？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
9、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号课程的成
10、绩，若成绩小于等于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嵌
11、套语句查询选修课程编号为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,SCWHER
12、ES.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列出有二门以上（含两门
