数据库语言程序设计复习题1005

《数据库语言程序设计》复习题

一、分析题（15分）

一个图书馆理系统中有如下信息：

图书：书号、书名、数量、位置

借书人：借书证号、姓名、单位

出版社：出版社名、邮编、地址、电话、E-mail

其中约定：任何人可以借多种书，任何一种书可以被多个人借，借书和还书时，要登记相应的借书日期和还书日期；一个出版社可以出版多种书籍，同一本书仅为一个出版社所出版，出版社名具有惟一性。

根据以上情况，完成如下设计：

（1）设计系统的E-R图；5分

（2）将E-R图转换为关系模式；5分

（3）指出转换后的每个关系模式的关系键（主键，外键）。5分

解答：

（1）

图书关系模式：图书（书号，书名，数量，位置，出版社名）

主键：书号

外键：出版社名

借书人关系模式：借书人（借书证号，姓名，单位）

主键：借书证号

出版社关系模式：出版社（出版社名，邮编，地址，电话，E-mail）

主键：出版社名

借阅关系模式：借阅（借书证号，书号，借书日期，还书日期）

主键：（借书证号，书号）

外键1：借书证号

外键2：书号

二、设计题1

有一个[学生课程]数据库，数据库中包括三个表：

学生表(学号，姓名，性别，年龄，所在系)

课程表(课程号，课程名，先修课号，学分)

成绩表(学号，课程号，成绩)

用SQL语言编写实现下列功能的代码：

1、建立一个[学生表]，要求设置学号属性为主键，规定年龄大于16。

2、查询年龄在20至23岁之间的学生的姓名、系别、和年龄。

3、查询各系的人数及平均年龄。

4、计算“数据库原理”课程的学生平均成绩。

5、将计算机科学系全体学生的成绩置零。

6、创建一个“经济系”全体学生的视图V_JJX。

7、创建一个自定义函数，可以求解任意数n的阶乘累加和，即计算S = 1！+2！+3！+…… + n！，并用n=10调用该函数。

8、创建一个触发器，要求当更新课程表的课程号时，能更新成绩表中相应的课程号。

答案：

1、建立一个学生表。

CREATE TABLE 学生表

(学号 CHAR(5) PRIMARY KEY,

姓名CHAR(20),

性别 CHAR(2),

年龄 INT CHECK(年龄>16),

所在系 CHAR(15))

2、查询年龄在20至23岁之间的学生的姓名、系别、和年龄

SELECT 姓名, 所在系, 年龄

FROM 学生表

WHERE 年龄 BETWEEN 20 AND 23

3、查询各系的人数及平均年龄。

SELECT 所在系,COUNT(*),AVG(年龄)

FROM 学生表

GROUP BY 所在系

4、计算数据库应用课程的学生平均成绩

SELECT AVG(成绩)

FROM 成绩表

WHERE 课程号in

( SELECT 课程号 FROM 课程表

WHERE 课程名 ='数据库应用')

5、将计算机科学系全体学生的成绩置零

UPDATE 成绩表

SET 成绩=0

WHERE 学号 in

( SELECT 学号 FROM 学生表

WHERE 所在系 ='计算机科学系')

6、create view V_JJX

as

select * from 学生表

WHERE 所在系 ='经济系'

7、

CREATE FUNCTION FC(@n INT)

RETURNS BIGINT AS

BEGIN

DECLARE @i int , @p bigint, @s bigint

SELECT @i=1, @p=1, @s=0

WHILE @i<=@n

BEGIN

SET @p=@p*@i

SET @s=@s+@p

SET @i=@i+1

END

Return @S

END

Go

select dbo.FC(10) --调用

8、

Create trigger trupkc on kc

For update

As

Declare @khold char(3), @khnew char(3)

Select @khold=deleted.课程号, @khnew=inserted.课程号From deleted d , inserted i

Where d.课程名=i.课程名

Update xs_kc set 课程号=@khnew

Where 课程号=@khold

二、设计题2

现有关系数据库如下：

数据库名：医院管理

医生表(医生编号，姓名，性别，出生日期，职称)

病人表(病人编号，姓名，性别，年龄，身份证号)

医疗表(ID，病人编号,医生编号，入院日期，病历描述)

用SQL语言写出实现下列功能的语句代码：

1. 创建数据库，库名“医院管理”,指定所有文件存放在e:\data\,其他参数均使用默认值。

2. 创建上述三表的建表代码；

要求使用：主键(师医生表.编号，病人表.课号)、外键(病历表.医生编号，病历表.病人课号)、非空(职称，姓名)、检查(性别),自动编号(ID)

3. 将下列医生信息插入到医生表

编号姓名性别出生日期职称

100001 王医生男 1963-5-18 副主任医师

100002 郭医生女 1950-7-26 副主任医师

100003 刘医生男 1973-9-18 医师

4. 修改数据：将编号为100002的医生职称改为‘主任医师’

5. 删除数据：删除职称为空且没有病人的医生信息

6. 修改数据表，为病人表增加一列“联系电话”，约束为11位数字；为“入院日期”设置默认值约束为

系统当前日期，用该默认值填充表中已有行的新列。

ALTER TABLE 病人表

ADD CONSTRAINT date_dflt DEFAULT getdate() FOR 入院日期WITH V ALUES

7. 查询：检索管有出生年份（从身份证号中截取）在1950到1960之间的病人的医生信息。

7. 查询：检索管有病人年龄在50到60之间的的医生信息。

8. 查询：查询所有姓王的病人姓名、病历描述、以及病人所对应的医生编号；

9. 创建视图：医生患者视图(医生姓名，病人姓名，入院日期，病历描述)；

10.创建存储过程并执行该存储过程：输出某医生的看病人数

（要求输入参数为：医生姓名，输出参数为：病人数）。

解答：

1.

CREATE DATABASE [医院管理]

ON

(NAME = 'yygl_Data',

FILENAME ='E:\data\yygl_Data.MDF'

)

LOG ON

(NAME = 'yygl_Log',

FILENAME ='E:\data\yygl_log.LDF' )

go

use [医院数据库]

go