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

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统计有学生选修的课程门数。 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='4'3求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

4、,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同学大，而年龄比他小的学

5、生姓名。 SELECT X.SNAME FROM S AS X, S AS Y WHERE Y.SNAME='WANG' AND X.Sno>Y.Sno AND X.SAGE<Y.SAGE或：SELECT SNAMEfrom swhere sno>(select sno from s where SNAME='WANG') and SAGE<(select sAGE from s where SNAME='WANG')6检索姓名以WANG打头的所有学生的姓名和年龄。 SELECT SNAME,SAGE FROM S WHE

6、RE SNAME LIKE 'WANG%' 7在SC中检索成绩为空值的学生学号和课程号。 SELECT Sno,Cno FROM SC WHERE GRADE IS NULL8求年龄大于女同学平均年龄的男学生姓名和年龄。 SELECT SNAME,SAGE FROM S WHERE SSEX='男' AND SAGE>(SELECT AVG(SAGE) FROM S WHERE SSEX='女')9求年龄大于所有女同学年龄的男学生姓名和年龄。 SELECT SNAME,SAGE FROM S AS X WHERE X.SSEX='男

7、' 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把低于总

8、平均成绩的女同学成绩提高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 studentdele

9、te from s1INSERT INTO S1(Sno,SNAME,SSEX) SELECT Sno,SNAME,SSEX FROM S WHERE NOT EXISTS(SELECT * FROM SC WHERE GRADE<80 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 GRADE<80 AND S.Sno=SC.Sno or S

10、.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 GRADE<60 AND Cno IN(SELECT Cno

11、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' A

12、ND GRADE<=75 UPDATE SC SET GRADE=GRADE*1.04 WHERE Cno='4' AND GRADE>75三、问题描述：为管理岗位业务培训信息，建立3个表:S (Sno,SN,SD,SA)   Sno,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄C (Cno,CN )        Cno,CN      &#

13、160;分别代表课程编号、课程名称SC ( Sno,Cno,G )    Sno,Cno,G     分别代表学号、所选修的课程编号、学习成绩要求实现如下5个处理：  1. 使用标准SQL嵌套语句查询选修课程名称为税收基础的学员学号和姓名  2. 使用标准SQL嵌套语句查询选修课程编号为C2的学员姓名和所属单位  3. 使用标准SQL嵌套语句查询不选修课程编号为C5的学员姓名和所属单位&

14、#160; 4. 使用标准SQL嵌套语句查询只选修了一门课程的学员姓名和所属单位  5. 查询选修了课程的学员人数6. 查询选修课程超过5门的学员学号和所属单位 参考答案： 1使用标准SQL嵌套语句查询选修课程名称为税收基础的学员学号和姓名SELECT SN,SD FROM SWHERE Sno IN(    SELECT Sno FROM C,SC    WHERE

15、 C.Cno=SC.Cno        AND CN=N'税收基础')2使用标准SQL嵌套语句查询选修课程编号为C2的学员姓名和所属单位SELECT S.SN,S.SD FROM S,SCWHERE S.Sno=SC.SnoAND SC.Cno='C2'3使用标准SQL嵌套语句查询不选修课程编号为C5的学员姓名和所属单位SELECT SN,SD FROM SWHERE Sn

16、o NOT IN(    SELECT Sno FROM SC     WHERE Cno='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

17、0;学员人数=COUNT(DISTINCT Sno) FROM SC6查询选修课程超过5门的学员学号和所属单位SELECT SN,SD FROM SWHERE Sno IN(    SELECT Sno FROM SC     GROUP BY Sno    HAVING COUNT(DISTINCT Cno)>

18、5)四、问题描述：已知关系模式：S (SNO,SNAME）                       学生关系。SNO 为学号，SNAME 为姓名C (CNO,CNAME,TEACHER)   课程关系。CNO 为课程号，CNAME 为课程名，TEACHER 为任课教师SC(SNO,CN

19、O,GRADE)        选课关系。GRADE 为成绩要求实现如下5个处理：  1 找出没有选修过“李明”老师讲授课程的所有学生姓名  2 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩  3 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名  4 列出“1”号课成绩比“04010002”号同学该门课成绩高的所有学生的学号5 列出“1”号课成绩比“2”号课成绩高的

20、所有学生的学号及其“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=N'liu')2列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩SE

21、LECT S.SNO,SNAME,AVG_GRADE=AVG(SC.GRADE)FROM S,SC WHERE GRADE<60 and s.sno=sc.sno GROUP BY s.SNO,s.sname HAVING COUNT(DISTINCT CNO)>=2或SELECT S.SNO,S.SNAME,AVG_GRADE=AVG(SC.GRADE)FROM S,SC,( SELECT SNO FROM SC WHERE GRADE<60 GROUP BY SNO HAVING COUNT(DISTINCT CNO)>=2)A WHERE S.SNO=A.SNO

22、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(&#

23、39;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='1'and SC.sNO=S.sNO AND grade>(select grade from s,sc where s.SNO='04010002'and SC.CNO='1'and SC.sNO=S.sNO)5。列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩SELECT SC1.SNO,1号课成绩=SC1.GRADE,2号课成绩=SC2.GRADE FROM