数据库期末考试编程题.doc

完整word版)数据库期末考试编程题
四、编程题（每小题1分，共15分）
可能用到的表结构如下：
学生表（学号，姓名，性别，年龄，班级）
课程表（课程号，课程名，学分）
成绩表（学号，课程号，成绩）

1.创建存储过程GetStudentByID，带有参数sno，根据学生的学号查询学生情况
Create procedure GetStudendByID sno varchar(10)
As
Begin
Select * from 学生表 where 学号=sno
end

2. 编写SQL语句，查询学生表中所有学生，要求第一按照班级升序排序，第二按照年龄降序排序
select * from 学生表 order by 班级 asc,年龄 desc

3. 编写SQL语句，查询学生表中姓张的学生
select * from 学生表 where 姓名 like '张%'

4. 编写SQL语句，查询学生表中男女生的人数各多少人
select 性别,count(*) from 学生表 group by 性别

5. 编写SQL语句，查询所有比“王平”年龄大的情况
Select * from 学生表 where 年龄>(select 年龄 from 学生表 where 姓名=’王平’)
6. 编写SQL语句，在学生表和成绩表中查询学号，姓名，课程号，成绩
SELECT 学生表.学号, 学生表.姓名, 成绩表.课程号, 成绩表.成绩 FROM 学生表 INNER JOIN 成绩表 ON 学生表.学号 = 成绩表.学号

7. 编写SQL语句，将（1022，张望，男，19，信息2）的学生插入到学生表中
insert into 学生表 (学号,姓名,性别,年龄, 班级) values('1022','张望','男',19,'信息2')

8. 编写SQL语句，将学号为1022学生的姓名改为张旺
update 学生表 set 姓名='张旺' where 学号='1022'

9. 编写SQL语句，删除没有选课的学生
delete from 学生表 sno not in (select sno from sc)

10. 编写SQL语句，查询课程号9的先行课的情况
select * from 课程表 where 课程号= (select 先行课号 from 课程表 where 课程号=9)
11. 编写关系代数，查询选修3号课程的学生学号
∏学号（∫课程号=‘3’（选课表））
12. 编写关系代数，查询至少选修了一门直接先行课为5号课程的学生姓名
参看课本60页
13. 编写SQL语句，创建学生表
Create table 学生表 (学号 char（10），姓名 varchar(20)，性别 char(2),年龄 int,班级 char(30))
14. 编写SQL语句，建立计算机1班男生的视图
Create view as select * from 学生表 where 班级=‘计算机1班’ and 性别=‘男’
15. 编写SQL语句，将对学生表的修改权限赋给用户U1
Grant update on table 学生表 to U1
1.设有一个学生课程数据库，包括学生关系Student、课程关系Course、选修关系SC，图1所示：
  Student
学号
Sno
姓名
Sname
性别
Ssex
年龄
Sage
所在系
Sdept
95001
李勇
男
20
CS
95002
刘晨
女
19
IS
95003
王敏
女
18
MA
95004
张立
男
19
IS
Course
课程号
Cno
课程名
Cname
先行课
Cpno
学分
Ccredit
1
数据库
5
4
2
数学

2
3
信息系统
1
4
4
操作系统
6
3
5
数据结构
7
4
6
数据处理

2
7
PASCAL语言
6
4
  SC
学号
Sno
课程号
Cno
成绩
Grade
95001
1
92
95001
2
85
95001
3
88
95002
2
90
95002
3
80
图1  学生-课程数据库
写出完成下列要求的SQL语句：
1)查询所有年龄在20岁以下的学生姓名与年龄。
1. select Sname,Sage from Student where Sage<20;
或 select Sname,Sage from Student where not Sage>=20;
2)查询年龄不在20～23(包括20岁和23岁)之间的学生的姓名、系别和年龄。
select Sname,Sdept,Sage from Student where Sage not between 20 and 23;
3)查询每个学生及其选修课程的情况。
select Student.*,SC.* from Student,SC where Student.Sno=SC.Sno;
2.把下面用关系表示的实体、实体与实体之间的联系，用E-R图表示出来，要求在图中表示联系的类型(1∶1、 1∶n、 m∶n)。
实体1：学生(学号，姓名，性别，年龄) 关键字为：学号
实体2：课程(课程号，课程名，学分数) 关键字为：课程号
实体1与实体2的联系：学习(学号，课程号，成绩) 关键字为：学号+课程号
注：一个学生可以选多门课程，一门课程也可以被多个学生选，学生选课后有成绩。
学生选课关系E-R图

五、编码题
1、下面给出三个关系模式：
（1）学生关系S（SNO，SName，SAge，SSex）
（2）课程关系C（CNO，CName，CTeacher）
（3）学习关系SC（SNO，CNO，Grade）
根据要求用T-SQL完成下列各题。
（1） 检索学习课程号为“C01001”的学生学号与姓名；
（2） 检索至少选修课程号为“C00101”和“C02341”的学生学号。
答：（1）SELECT S.S#,SNAME
FROM S,SC
WHERE S.S#=SC.S# AND C#=’C01001’
（2）SELECT X.S#
FROM SC AS X,SC AS Y
WHERE X.S#=Y.S# AND X.C#=’C00101’ AND Y.C#=’C02341’
2、图书管理数据库BookManager由以下四个关系模式组成，根据该结构用T-SQL完成以下各题。
（1）读者（借书证号，姓名，性别，出生日期）
（2）图书（ISBN，图书名称，作者，出版社，价格，数量）
（3）借阅（借书证号，ISBN，借出时间）
根据要求完成下列各题。
（1）创建一个名称为BookManager的数据库，其初始大小为10MB，最大为20MB，允许数据库自动增长；日志文件初始大小为2MB，最大为5MB。文件存储在目录D:\TEST下。
（2）编写一个存储过程，查询某读者当前的借书情况。
（3）创建一个触发器，如果在图书表中添加或更改数据，则向客户端显示一条信息。
解：（1）CREATE DATABASE BookManager
ON( NAME=’BookManager_DATA’,
FILENAME=’D:\TEST\BookManager.MDF’,
SIZE=10MB,
MAXSIZE=20MB,
FILEGROWTH=10%)
LOG ON(
FILENAME=’D:\TEST\BookManager.LDF’,
SIZE=2MB,
MAXSIZE=5MB,
FILEGROWTH=10%)

（2）CREATE PROCEDURE proc_reader
@ lib_num char(8)
AS
SELECT A.借书证号,姓名,B.ISBN,书名
FROM Reader A, JY B , BOOK C
WHERE A. 借书证号=B. 借书证号 AND B.ISBN=C.ISBN AND A. 借书证号 @ lib_num
（3）CREATE TRIGGER trig_reminder ON Reader
FOR INSERT ,UPDATE
AS
RAISEERROR(4008,16,10)

3、设有学生表S（SNO，SN）（SNO为学生号，SN为姓名）和学生选修课程表SC（SNO，CNO，CN，G）（CNO为课程号，CN为课程名，G为成绩），试用SQL语言完成以下各题：
（1）建立一个视图V_SSC(SNO,SN,CNO,CN,G)，并按CNO升序排序；
（2）从视图V_SSC上查询平均成线在90分以上的SN、CN和G。
答：（1）CREATE VIEW V_SSC(SNO,SN,CNO,CN,G)
AS SELECT S.SNO,S.SN,CNO,SC.CN,SC.G
FROM S,SC
WHERE S.SNO=SC.SNO
ORDER BY CNO
（2） SELECT SN,CN,G
FROM V_SSC
GROUP BY SNO
HAVING AVG(G)>90
4、图书管理数据库BookManager由以下四个关系模式组成，根据该结构用T-SQL完成以下各题。
（1）读者（借书证号，姓名，性别，出生日期）
（2）图书（ISBN，图书名称，作者，出版社，价格，数量）
（3）借阅（借书证号，ISBN，借出时间）
根据要求完成下列各题。
（1）创建一个名称为BookManager的数据库，其初始大小为10MB，最大为20MB，允许数据库自动增长；日志文件初始大小为2MB，最大为5MB。文件存储在目录D:\TEST下。
（2）编写一个存储过程，查询某读者当前的借书情况。
（3）创建一个触发器，如果在图书表中添加或更改数据，则向客户端显示一条信息。
字段名
类型与
宽度
是否
主码
是否允许
空值
借书证号
Char(8)
是
NOT NULL
姓名
Char8)

NOT NULL
专业名
Char(12)

NOT NULL
性别
Char(2)

NOT NULL
出生日期
Datetime

NOT NULL
借书量
Integer

NOT NULL
照片
Image

NOT NULL

字段名
类型与
宽度
是否主码
是否允许
空值
ISBN
Char(16)
是
NOT NULL
书名
Char(26)

NOT NULL
作者
Char(8)

NOT NULL
出版社
Char(20)

NOT NULL
价格
Float

NOT NULL
副本量
Integer

NOT NULL
库存量
Integer

NOT NULL

字段名
类型与
宽度
是否
主码
是否允许
空值
借书证号
Char(8)

NOT NULL
ISBN
Char(16)

NOT NULL
索书号
Char(10)
是
NOT NULL
借书时间
Datetime
是
NOT NULL
还书时间
Datetime

NOT NULL

字段名
类型与
宽度
是否
主码
是否允许
空值
借书证号
Char(8)

NOT NULL
ISBN

Char(16)

NOT NULL
索书号
Char(10)
是
NOT NULL
借书时间
Datetime

NOT NULL

表4.1 读者表(表名为XS)
表4.2 图书表(表名为BOOK)
表4.3 借阅表(表名为JY)
)
表4.4 借阅历史表(表名为JYLS)

解：（1）CREATE DATABASE BookManager
ON( NAME=’BookManager_DATA’,
FILENAME=’D:\TEST\BookManager.MDF’,

SIZE=10MB,
MAXSIZE=20MB,
FILEGROWTH=10%)
LOG ON(
FILENAME=’D:\TEST\BookManager.LDF’,
SIZE=2MB,
MAXSIZE=5MB,
FILEGROWTH=10%)

（2）CREATE PROCEDURE proc_reader
@ lib_num char(8)
AS
SELECT A.借书证号,姓名,B.ISBN,书名
FROM Reader A, JY B , BOOK C
WHERE A. 借书证号=B. 借书证号 AND B.ISBN=C.ISBN AND A. 借书证号= @ lib_num
（3）CREATE TRIGGER trig_reminder ON Reader
FOR INSERT ,UPDATE
AS
RAISEERROR(4008,16,10)

5、下面给出一个商品销售数据库中包含的三个基本表：
（1）客户表：table_Client（CNO，Cname，Csex，Cbirthday）
（2）商品表：table_Goods（GNO，Gname，Gunit，Gprice）
（3）购物表：table_CG（CNO，GNO，CGquantity）

用T-SQL语句完成下列各题。
（1）查询购买了商品的客户号及姓名。
（2）查询1978年出生的客户号及姓名。
（3）购买了三种以上商品的客户号及客户姓名。
答：（1）select CNO,Cname
from table_Client
where CNO in
(select distinct CNO
from table_CG)
（2）select CNO,Cname
from table_Client
where Cbirthday=’1978’
（3）select CNO,Cname
from table_Client
where CNO in
(select CNO
from table_CG
group by CNO
having count(*)>3)

6、学生-选课-课程数据库由以下四个关系模式构成：
STUDENT（SNO，SName，SAge，SSex，SAddress）
TEACHER（TNO，TTeacher，TPos，TAddress）
COURSE（CNO，CName，CTerm，CCredit，TNO）
SELECTCOURSE（SNO，CNO，Grade）
按要求完成下列各题。
（1）用SQL语句建立STUDENT表。
（2）为COURSE表建立插入触发器，保证教师编号的正确性。
（1）
解：create table student
(
sno nvarchar(8),
sname nvarchar(10),
sage int,
ssex char(4),
saddress nvarchar(50),
primary key(sno)
)
（2）
create trigger course_insert_tno on course
for insert
as if(not exists
(select tno from teacher where tno in
(select inserted.tno from inserted)))
begin
print '无此教师'
rollback transaction
end
1、设学生课程数据库中有三个关系：
学生关系S（S#，SNAME，AGE，SEX）
学习关系SC（S#，C#，GRADE）
课程关系C（C#，CNAME）
其中S#、C#、SNAME、AGE、SEX、GRADE、CNAME分别表示学号、课程号、姓名、年龄、性别、成绩和课程名。
用SQL语句表达下列操作
（1）检索选修课程名称为“MATHS”的学生的学号与姓名
（2）检索至少学习了课程号为“C1和“C2”的学生的学号
（3）检索年龄在18到20之间（含18和20）的女生的学号、姓名和年龄
（4）检索平均成绩超过80分的学生学号和平均成绩
（5）检索选修了全部课程的学生姓名
（6）检索选修了三门课以上的学生的姓名

还认真开展了6次全国统一行动，遏制了因"三超一疲劳"而引发的交通事故;在三、四、五月份，我队还按照州政府、州安委会、州支队要求，开展了道路交通集中整治专项行动、治理酒后驾驶、摩托车、电动车专项治理行动，均取得了一定的成效。
答案：（1）SELECT SNAME,AGE
FROM S,SC,C
WHERE S.S#=SC.S#
AND C.C#=SC.C#
AND CNAME=’ MATHS’
(2) SELECT S#
FROM SC
WHERE CNO=’C1’ AND S# IN( SELECT S#
FROM SC
WHERE CNO=’C2’)
(3)SELECT S#,SNAME,AGE
FROM S
WHERE AGE BETWEEN 18 AND 20
(4) SELECT S# ,AVG(GRADE) ‘平均成绩’
FROM SC
GROUP BY S#
HAVING AVG(GRADE)>80
(5) SELECT SNAME
FROM S
WHERE NOT EXISTS
(SELECT *
FROM C
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE S#=S.S# AND C#=C.C#
)
)
(6) SELECT SNAME
FROM S,SC
WHERE S.S#=SC.S#
GROUP BY SNAME
HAVING COUNT(*)>3

2、设学生-课程数据库中包括三个表：
学生表：Student （Sno，Sname，Sex，Sage，Sdept）
课程表：Course（Cno，Cname，Ccredit）
学生选课表：SC（Sno，Cno，Grade）
其中Sno、Sname、Sex、Sage、Sdept、 Cno、Cname、Ccredit 、Grade分别表示学号、姓名、性别、年龄、所在系名、课程号、课程名、学分和成绩。
试用SQL语言完成下列项操作：
（1）查询选修课程包括“1042”号学生所学的课程的学生学号
（2）创建一个计科系学生信息视图S_CS_VIEW，包括Sno学号、Sname姓名、Sex性别;
（3）通过上面第2题创建的视图修改数据，把王平的名字改为王慧平
（4）创建一选修数据库课程信息的视图，视图名称为datascore_view，包含学号、姓名、成绩。

答案：(1) SELECT DISTINCT SNO
FROM SC SCX
WHERE NOT EXISTS
(SELECT *
FROM SC SCY
WHERE SCY.SNO = ' 1042' AND
NOT EXISTS
(SELECT *
FROM SC SCZ
WHERE SCZ.SNO=SCX.SNO AND
SCZ.CNO=SCY.CNO))；
(2) CREATE VIEW S_CS_VIEW
AS
SELECT SNO,SNAME,SEX
FROM STUDENT
WHERE Sdept=’CS’
(3)UPDATE S_CS_VIEW
SET SNAME=’ 王慧平’
WHERE SNAME=’ 王平’
(4) CREATE VIEW datascore_view
AS
SELECT SNO 学号、SNAME 姓名、GRADE 成绩
FROM STUDENT,SC,COURSE
WHERE STUDENT.SNO=SC.SNO
AND COURSE.CNO=SC.CNO
AND CNAME=’ 数据库’