关系数据库与SQL语言 - 习题答案

关系数据库与SQL语言_习题（黄旭明）参考答案

本章习题均基于以下假设：在某校，有关学生有学号、姓名、系别、出生日期等信息，有关课程有课程号、课程

名、选修课程等信息，每个学生只有一个学号、只在一个系、可以选修多门课程；每门课程只有一个课程号、可以

由多个学生选修，学生选修课程产生成绩。

这些数据在名为Student的数据库中进行管理，其中各表逻辑结构设计如下： 表名：学生

属性：学号(定长字符串，12)

如：200210515001，2002年，105系别，15专业代码，001学生编号 姓名(变长字符串，20，非空) 出生日期(日期时间，非空) 是否党员(逻辑型) 班委(定长字符串，8) 主键：学号 表名：课程

属性：课程号(定长字符串，3) 课程名称(变长字符串，20) 先修课号(定长字符串，3) 主键：课程号 外键：先修课号

其值取自本表主键“课程号”的值 表名：成绩表

属性：学号(定长字符串，12) 课程号(定长字符串，3)

成绩(整数，3，取值0～100) ． 主键：(学号，课程号)

外键：学号

其值取自表“学生”主键“学号”的值 ． 课程号

其值取自表“课程”主键“课程号”的值

1．用SQL语言创建一个数据库，命名为Student。 Create dababase student

2．在Student数据库中，用SQL语言建立以上各表。

1）Create table 学生(学号 char(12) primary key,姓名 varchar(20) not null,出生日期 datetime not null,是否党员 bit,班委 char(8))

2）Create table 课程(课程号 char(3) primary key,课程名称 varchar(20) ,先修课号 char(3) foreign key references 课程(课程号))

3）Create table 成绩表(学号 char(12) foreign key references 学生(学号),课程号 char(3) foreign key references 课程(课程号),成绩 smallint check(成绩 between 0 and 100),primary key (学号,课程号))

3．在表“成绩表”中创建关于“课程号”的索引。 Create index kch_cjb on 成绩表(课程号)

4．在各表中输入一些记录数据，并进行一些修改操作。

5．在表“学生”中增加数值类型的“德育分”、“专业分”与“总积分”属性，总积分=德育分+专业分。

1） Alter table 学生 add 德育分 int, 专业分 int,总积分 int 2） Update 学生 set 总积分=德育分+专业分

6．从“课程”表中检索所有课程的“课程号”和“课程名称”。 Select 课程号,课程名称 from 课程

7．从“学生”表中检索记录的所有属性。 Select * from 学生

8．从“学生”表中检索每一记录“总积分”，说明总积分是德育分与专业分之和，并在属性列的清单中用字符串加以说明。

Select 总积分 ,’总积分=德育分+专业分’ as 说明 from 学生

9．从“成绩表”表中检索某个学号的所有选修课程的成绩。 Select * from 成绩表 where 学号=’200110101003’

10．从“成绩表”表中检索所有不及格学生的记录。 Select * from 成绩表 where 成绩<60

11．用两种不同的比较形式，从“课程”表中检索课程名称不是“数据库”的所有记录。 Select * from 课程 where 课程名称<>'数据库' 或

Select * from 课程 where not 课程名称='数据库'

12．从“学生”表中检索出所有年龄大于20岁的学生的“学号”、“系别”。 Select 学号,系别 from 学生 where year(date())-year(出生日期)>20

13．检索所有尚未填写成绩的学生学号。

Select distinct 学号 from 成绩表 where 成绩 is null

14．从“课程”中检索出所有有“先修课程”的课程号与课程名称。 Select 课程号,课程名称 from 课程 where 先修课程 is not null

15．按学生年龄大小，从“学生”表中检索“学号”，“出生日期”。 Select学号,出生日期 from 学生 order by 出生日期

16．将“成绩表”中的成绩记录，先按“课程号”从小到大排序，然后再按“成绩”从高到低排序。 四个下划线 Select * from 成绩表 order by 课程号 ,成绩 desc

17．从“学生”表中检索“系别”为105，且“积分”大于200的“学号”。 Select 学号 from 学生 where 学号 like '____101%' and 总积分>200

18．从“成绩表”中检索选修了“课程号”为201或301课程的学生“学号”。 Select 学号 from 成绩表 where 课程号='201' or 课程号='301'

19．从“成绩表”表中检索出哪些人有不及格记录。 Select distinct 学号 from 成绩表 where 成绩<60

20．从“成绩表”表中检索选修“课程号”不为201或301，且“成绩”大于95分的学生“学号”。

Select 学号 from 成绩表 where 课程号 not in('201', '301') and 成绩>95

21．查询哪些既是班委又是党员的学生。

Select * from 学生 where 是否党员=1 and 班委 is not null

22．从“学生”表中检索“专业分”介于100到120之间的学生“学号”。

Select 学号 from 学生 where 专业分 between 100 and 120

23．从“学生”中检索在1982年1月1日至9月1日出生的学生的“学号”和“姓名”。 Select 学号,姓名from 学生 where 出生日期 between '1982/01/01' and '1982/09/01'

24．从“学生”表中检索姓“陈”的学生的“身份证号码”和“姓名”。 Select 身份证号码,姓名 from 学生 where 姓名 like '陈%'

25．查询已被学生选修的课程号，课程号不重复显示。 Select distinct 课程号 from 成绩表

26．计算选修课程号为201的学生人数。

Select count(*) as 选修人数 from 成绩表 where 课程号='201'

27．计算学生总人数。

Select count(*) as 学生总数 from 学生

28．计算系别为105的学生的德育积分总和。

Select sum(德育分) as 105系学生德育积分总和 from 学生 where 学号 like '____105%'

29．求所有学生总积分的平均值。

Select avg(总积分) as 总积分平均值 from 学生

30．找出“成绩表”的最低与最高成绩。

Select max(成绩) 最高成绩, min(成绩) 最低成绩 from 成绩表

31．计算某个学生的各课程平均成绩。

Select avg(成绩) 平均成绩 from 成绩表 where 学号='200210515001'

32．计算各个学生选修的课程门数。

Select 学号,count(课程号) as 课程门数 from 成绩表 group by 学号

33．分组计算各门课的最高成绩与最低成绩。

Select 课程号,max(成绩) 最高成绩, min(成绩) 最低成绩 from 成绩表 group by 课程号

34．按课程名称、学号二重排序输出：课程名称、学号、姓名、成绩。 Select课程名称,学号,姓名,成绩 from 成绩表 order by 课程名称,学号

35．查询与“刘晨”在同一个年出生的学生。

Select s2.* from 学生 s1 join 学生 s2 on year(s1.出生日期)=year(s2.出生日期) where s1.姓名='刘晨' and s2.姓名<>'刘晨'

或

Select * from 学生 where 姓名<>'刘晨' and year(出生日期)=(select year(出生日期) from 学生 where 姓名='刘晨')

36．查询选修了课程名为“信息系统”的学生学号和姓名。

Select 学生.学号,姓名 from 学生,成绩表,课程 where 学生.学号=成绩表.学号 and成绩表.课程号=课程.课程号 and 课程名='信息系统'

37．查询其他系中比105任一学生年龄都小的学生名单。

Select 姓名 from 学生 where学号 not like '____105%' and 出生日期>any(select max(出生日期) from 学生 where 学号 like '____105%')

38．查询所有选修了课程号以“l”打头的学生姓名。

Select 姓名 from 学生,成绩表 where学生.学号=成绩表.学号 and 课程号 like '1%'

39．查询选修了全部课程的学生姓名。

Select 姓名 from 学生 where (not exists(select * from 课程 where not exists(select * from 成绩表 where 学号=学生.学号 and 课程号=课程.课程号)))

40．查询105系的学生中年龄不大于19岁的学生。

Select * from 学生 where year(出生日期)<=19 and 学号like '____105%'

41．根据“成绩表”创建一个视图，其结果返回所有60分以上的成绩记录。 Creat view st1 as select * from 成绩表 where 成绩>=60

42．创建一个视图，其结果返回3门90分以上的学生的学号、姓名、系别。 Create view st2 as select 学号,姓名,系别 from 学生 where 学号 in

(select 学号 from 成绩 where 成绩>=90 group by 学号 having count(课程号)>=3)

43．向题4l的视图中添加一条记录，其值为，学号“200210501001”，课程号“C011”，成绩“85”。

Insert into st1 values('200210501001', 'C011', 85)

44．将题41的视图中的学号为“200110101003”、课程号为“C001”的成绩改为94，然后用SELECT语句在”成绩表中”查看修改的结果。

Update st1 set 成绩=94 where 学号='2002110101003' and 课程号='C001'

45．建立105系学生的视图。

Create view st3 as select * from 学生 where 学号 like ____105'

、 46．建立105系学生的视图，并要求进行修改和插入操作时仍须保证该视图只有信息系的学生。

47．建立105系选修了101号课程的学生的视图。

48．建立105系选修了101号课程且成绩在90分以上的学生的视图。 49．定义一个反映学生岁数的视图。

50．将学生的学号及他的平均成绩定义为一个视图。 51．将Student表中所有党员记录定义为一个视图。 52．在105系学生的视图中找出年龄小于20岁的学生。 53．查询105系选修了101号课程的学生。

54．将105系学生视图中学号为95002的学生姓名改为“刘辰”。

55．向105系学生视图中插入一条新的学生记录，其学号为200210515029，姓名为赵新，年龄为20岁。

56．删除105系学生视图中学号为200210515029的记录。