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

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.4.6.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

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