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 SnoIN
3、(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统计每门课程的
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.SNAM
5、E 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
6、、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%（用两个 UPD
7、ATE 语句实现） 。参考答案：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
8、 EXISTS(SELECT * FROM SC WHERE 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 嵌套语句
9、查询不选修课程编号为 C5的学员姓名和所属单位4. 使用标准 SQL 嵌套语句查询只选修了一门课程的学员姓名和所属单位5. 查询选修了课程的学员人数6. 查询选修课程超过 5 门的学员学号和所属单位 参考答案：1使用标准 SQL 嵌套语句查询选修课程名称为税收基础的学员学号和姓名SELECT SN,SD FROM SWHERE Sno IN(SELECT Sno FROM C,SCWHERE C.Cno=SC.CnoAND CN=N税收基础)2使用标准 SQL 嵌套语句查询选修课程编号为C2的学员姓名和所属单位SELECT S.SN,S.SD FROM S,SCWHERE S.Sno=SC.S
10、noAND SC.Cno=C23使用标准 SQL 嵌套语句查询不选修课程编号为C5的学员姓名和所属单位SELECT SN,SD FROM SWHERE Sno 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.CnoGROUP BY SnoHAVING COUNT(*)=1)5查询选修了课程的学员人数SELECT 学员人数=COUNT(DISTINC
11、T Sno) FROM SC6查询选修课程超过 5 门的学员学号和所属单位SELECT SN,SD FROM SWHERE Sno IN(SELECT Sno FROM SC GROUP BY SnoHAVING COUNT(DISTINCT Cno)5)四、问题描述：已知关系模式：S (SNO,SNAME） 学生关系。SNO 为学号，SNAME 为姓名C (CNO,CNAME,TEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，TEACHER 为任课教师SC(SNO,CNO,GRADE) 选课关系。GRADE 为成绩要求实现如下 5 个处理：1 找出没有选修过“李明”老师讲授
12、课程的所有学生姓名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 sn
13、o,sname from s where sno not in(select sno from sc,c where o=oAND c.teacher=Nliu)2列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩SELECT S.SNO,SNAME,AVG_GRADE=AVG(SC.GRADE)FROM S,SCWHERE GRADE=2或SELECT S.SNO,S.SNAME,AVG_GRADE=AVG(SC.GRADE)FROM S,SC,(SELECT SNOFROM SCWHERE GRADE=2)A WHERE S.SNO=A.SNO AND SC.SNO=A.SNOGRO
14、UP 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
15、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.sNOAND grade(select grade from s,scwhere s.SNO=04010002and SC.CNO=1and SC.sNO=S.sNO)5。列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其 “1”号课和“2”号课的成绩SELECT SC1.SNO,1 号课成绩=SC1.GRADE,2 号课成绩=SC
16、2.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
17、结尾的所有六个字母的名称（如 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 -
