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 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='4'

3．求LIU教师所授课程的每门课程的学生平均成绩。
SELECT AVG(GRADE)
FROM SC join C on SC.Cno=C.Cno
WHERE 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)>2
ORDER BY 2 DESC, Cno ASC
或：
SELECT DISTINCT Cno,COUNT(Sno) as 人数
FROM SC
GROUP BY Cno
HAVING COUNT(Sno)>2
ORDER BY 人数DESC, Cno ASC
5．检索学号比WANG同学大，而年龄比他小的学生。
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 SNAME
from s
where sno>(select sno from s where SNAME='WANG')
and SAGE<(select sAGE from s where SNAME='WANG')
6．检索以WANG打头的所有学生的和年龄。
　　 　SELECT SNAME,SAGE
　FROM S
WHERE SNAME LIKE 'WANG%'

7．在SC中检索成绩为空值的学生学号和课程号。
SELECT Sno,Cno FROM SC WHERE GRADE IS NULL

8．求年龄大于女同学平均年龄的男学生和年龄。
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='男' 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 student
delete 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)
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.Sno=SC.Sno and grade is null)
and sno in (select sno from sc)

3．在根本表SC中删除尚无成绩的选课元组。
DELETE FROM SC WHERE GRADE IS NULL

4．把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 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 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       分别代表课程编号、课程名称
SC ( Sno,Cno,G )    Sno,Cno,G     分别代表学号、所选修的课程编号、学习成绩

要现如下5个处理：
  1. 使用标准SQL嵌套语句查询选修课程名称为’税收根底’的学员学号和
  2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员和所属单位
  3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员和所属单位
  4. 使用标准SQL嵌套语句查询只选修了一门课程的学员和所属单位
  5. 查询选修了课程的学员人数
6. 查询选修课程超过5门的学员学号和所属单位

参考答案：
 1．使用标准SQL嵌套语句查询选修课程名称为’税收根底’的学员学号和
SELECT SN,SD FROM S
WHERE [Sno] IN(
    SELECT [Sno] FROM C,SC
    WHERE C.[Cno]=SC.[Cno]
        AND CN=N'税收根底')
2．使用标准SQL嵌套语句查询选修课程编号为’C2’的学员和所属单位

SELECT S.SN,S.SD FROM S,SC
WHERE S.[Sno]=SC.[Sno]
AND SC.[Cno]='C2'
3．使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员和所属单位
SELECT SN,SD FROM S
WHERE [Sno] NOT IN(
    SELECT [Sno] FROM SC 
    WHERE [Cno]='C5')
4．使用标准SQL嵌套语句查询只选修了一门课程的学员和所属单位
SELECT SN,SD FROM S
WHERE [Sno] IN(
SELECT [Sno] FROM SC inner JOIN C ON SC.[Cno]=C.[Cno]
GROUP BY [Sno]
HAVING COUNT(*)=1)
5．查询选修了课程的学员人数
SELECT 学员人数=COUNT(DISTINCT [Sno]) FROM SC
6．查询选修课程超过5门的学员学号和所属单位
SELECT SN,SD FROM S
WHERE [Sno] IN(
    SELECT [Sno] FROM SC 
    GROUP BY [Sno]
    HAVING COUNT(DISTINCT [Cno])>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=N'liu')
2．列出有二门以上〔含两门〕不与格课程的学生与其平均成绩
SELECT 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 AND SC.SNO=A.SNO
GROUP BY S.SNO,S.SNAME
3．列出既学过“1”号课程，又学过“2”号课程的所有学生
SELECT S.SNO,S.SNAME
FROM S,sc
where S.SNO=SC.SNO ando='1' and s.sno in
(select S.sno from S,sc
where 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='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 SC SC1,SC SC2
WHERE SC1.CNO='1'
AND SC2.CNO='2'
AND SC1.SNO=SC2.SNO
AND SC1.GRADE>SC2.GRADE

四、通配符理解题〔请说出以下通配符的含义〕
1．LIKE 'Mc%' 将搜索以字母 Mc 开头的所有字符串〔如 McBadden〕。
2．LIKE '%inger' 将搜索以字母 inger 结尾的所有字符串〔如 Ringer、Stringer〕。
3．LIKE '%en%' 将搜索在任何位置包含字母 en 的所有字符串〔如 Bennet、Green、McBadden〕。
4．LIKE '_heryl' 将搜索以字母 heryl 结尾的所有六个字母的名称〔如 Cheryl、Sheryl〕。
5．LIKE '[M-Z]inger' 将搜索以字符串 inger 结尾、以从 M 到 Z 的任何单个字母开头的所有名称〔如 Ringer〕。
6．LIKE 'M[^c]%' 将搜索以字母 M 开头，并且第二个字母不是 c 的所有名称〔如 MacFeather〕。
7．LIKE '5[%]' -- 5%
8．LIKE '5%' -- 5 后跟 0 个或更多字符的字符串
9．LIKE '[_]n' -- _n
10．LIKE '_n' --an,in, on (and so on)
11．LIKE '[a-cdf]' -- a, b, c, d, or f
12．LIKE '[-acdf]' -- -, a, c, d, or f
13．LIKE '[ [ ]' -- [
14．LIKE ']' -- ]

8 / 8