数据库语言程序设计复习题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 233、查询各系的人数及平均年龄。

SELECT 所在系,COUNT(*),AVG(年龄)FROM 学生表GROUP BY 所在系4、计算数据库应用课程的学生平均成绩SELECT AVG(成绩)FROM 成绩表WHERE 课程号in( SELECT 课程号 FROM 课程表WHERE 课程名 ='数据库应用')5、将计算机科学系全体学生的成绩置零UPDATE 成绩表SET 成绩=0WHERE 学号 in( SELECT 学号 FROM 学生表WHERE 所在系 ='计算机科学系')6、create view V_JJXasselect * from 学生表WHERE 所在系 ='经济系'7、CREATE FUNCTION FC(@n INT)RETURNS BIGINT ASBEGINDECLARE @i int , @p bigint, @s bigintSELECT @i=1, @p=1, @s=0WHILE @i<=@nBEGINSET @p=@p*@iSET @s=@s+@pSET @i=@i+1ENDReturn @SENDGoselect dbo.FC(10) --调用8、Create trigger trupkc on kcFor updateAsDeclare @khold char(3), @khnew char(3)Select @khold=deleted.课程号, @khnew=inserted.课程号From deleted d , inserted iWhere d.课程名=i.课程名Update xs_kc set 课程号=@khnewWhere 课程号=@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 ALUES7. 查询：检索管有出生年份（从身份证号中截取）在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' )gouse [医院数据库]go2.create table 医生表([医生编号] char(6) primary key，[姓名] nchar(4) not null，[性别] nchar(1) check([性别] in ('男', '女'))，[出生日期]datetime ，[职称] char(18) not null)create table病人表([病人编号] char(6) primary key,[姓名] nchar(4) not null,[性别] nchar(1) check([性别] in ('男', '女'))，[民族] nchar(10),[身份证号] char(18) not null)create table医疗表(ID IDENTITY(1, 1),[病人编号] char(6) references 病人表(病人编号),[医生编号] char(6) references 医生表(医生编号),[入院日期] datetime ,[病历描述] text)3.insert 医生表 values('100001', '王医生', '男', '1963-5-18', '副主任医师')insert 医生表 values('100002', '郭医生', '女', '1950-7-26', '副主任医师')insert 医生表 values('100003', '刘医生', '男', '1973-9-18', '医师')4.update医生表 set 职称='主任医师' where 医生编号='100002'5.Delete 医生表 where 职称 is null and 医生编号 not in (select 医生编号 from 医疗表) 6．Alter table 病人表Add 联系电话 char(11) CHECK (联系电话 LIKE ' [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9]] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9]'ALTER TABLE 病人表ADD CONSTRAINT date_dflt DEFAULT getdate() FOR 入院日期 WITH VALUES7.select 医生编号, 姓名, 性别, 出生日期, 职称from 医生表where 医生编号 in (select 医生编号 from 医疗表 and病人编号 in（select 病人编号 from 病人表 where 年龄 between 30 and 50）)8.Select br.姓名, br.病历描述, yl.医生编号from 病人表 br left join 医疗表 ylon br.病人编号 = yl.病人编号where br.姓名like ‘王%’9.create view [医生患者视图] (医生姓名，病人姓名，入院日期，病历描述)asselect ys.姓名,br.姓名, yl.入院日期，yl.病历描述from 病人表 as br join 医疗表 as ylon br.病人编号=yl.病人编号 join 医生表 as yson yl.医生编号=ys.医生编号10.create procedure [医生看病人数]@医生姓名 nchar(4), @人数 int outputasselect @人数=count(病人编号)from 医疗表where 医生编号 in（select 医生编号 from 医生表 where 姓名=@医生姓名）godeclare @TOTAL intexecute [医生看病人数] '王医生', @TOTAL二、设计题3（填空题）1、计算 1+2*3+4*5+6*7……+96*97+98*99的和，并使用PRINT显示计算结果。