一、试用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 )

froms where sno

in(select sno from sc where cno=’4′)

或者，

select avg(sage)

from s,sc wheres.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

另：

selectcname,avg(grade) from sc ,c where sc.cno=c.cno and teacher=’liu’

group by c.cno,cname

4 ．统计每门课程的学生选修人数 （超过 2 人的课程才统计） 。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。

selectdistinct cno,count(sno) from sc

groupby cno having count(sno)>2

order by 2 desc, cno asc

或：

select distinct cno,count(sno) as 人数

fromsc group by cno

having count(sno)>2

order by 人 数 desc, cno asc

5 ．检索学号比 wang 同学大，而年龄比他小的学生姓名。

selectx.sname from s as x, s as y

where y .sname=’wang’ and x.sno>y.sno and x.sage<>< p=””><>

或：

select sname

from s

where sno>(select sno from s where sname=’wang’) andsage<(select sage from s where sname=’wang’)

6 ．检索姓名以 wang 打头的所有学生的姓名和年龄。

select sname,sage from s

where sname like ‘wang%’

7．在 sc 中检索成绩为空值的学生学号和课程号。

select sno,cnofrom sc where grade is null

8 ．求年龄大于女同学平均年龄的男学生姓名和年龄。 select sname,sage

from s

where ssex=’ 男’

and sage>(selectavg(sage) from s where ssex=’女’)

9 ．求年龄大于所有女同学年龄的男学生姓名和年龄。

selectsname,sage from s as x

where x.ssex=’ 男’and x.sage >all (select sage froms 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 intos1 from student delete from s1

insert into s1(sno,sname,ssex) select sno,sname,ssex

froms where not exists(select * from sc where grade<80 and s.sno=sc.sno)

select * from s1

考虑：以上会有什么问题？

insertinto 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 gradeis null) and sno in (select sno from sc)

3 ．在基本表 sc 中删除尚无成绩的选课元组。

delete from scwhere grade is null

4 ．把wang 同学的学习选课和成绩全部删去。

deletefrom 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

setgrade=grade*1.05

where grade<(select avg(grade) from sc)

and sno in (select sno from swhere ssex=’ 女’)

7 ．在基本表 sc 中修改 4 号课程的成绩，若成绩小于等于 75 分时提高 5% ， 若成绩大于 75 分时提高 4% （用两个 update 语句实现）。

update sc

setgrade=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 ’的学员姓名和所属单位

selects.sn,s.sd from s,sc where s.[sno]=sc.[sno]

andsc.[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]

havingcount(*)=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 swhere not exists (select * from c,sc where c.cno=sc.cno and c.teacher=n’ 李明 ‘ and s.sno=sc.sno)

参考： select sno,sname from s where sno not in

(select sno from sc,c where c.cno=sc.cno andc.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

havingcount(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

havingcount(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 and cno=’1′ and s.sno in (select s.snofrom s,sc

where 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

havingcount(distinct c.cno)=2

)sc wheres.sno=sc.sno

4 。列出“ 1 ”号课成绩比“ 04010002 ”号同学该门课成绩高的所有学生的学号

select s.sno,s.sname from s,sc

wheresc.cno=’1’and sc.sno=s.sno

andgrade>(select grade from s,sc

wheres.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 fromsc sc1,sc sc2

where sc1.cno=’1′ and sc2.cno=’2′

andsc1.sno=sc2.sno

andsc1.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 ‘]’ — ]