数据库原理与应用习题集(11套)

=============================================1

第1章习题

一、选择题

1．下列四项中，不属于数据库系统优点的是（）。

A．实现数据共享B．确保数据的安全性与保密性

C．控制数据冗余D．数据依赖程序

2．数据库系统中，对用户使用的数据视图的描述称为（）。

A．概念模式 B．内模式

C．存储模式 D．外模式

3．下列四项中，不属于数据库特点的是( ) 。

A. 数据共享

B.数据完整性

C. 数据冗余很高

D. 数据独立性高

4．数据库的三级模式之间存在着两级映像，使数据库系统具有较高的数据（）。

A．相容性 B．独立性

C．共享性 D．一致性

5．数据库系统的三级模式结构中，定义索引的组织方式属于（）。

A．概念模式B．外模式

C．逻辑模式D．内模式

6. E-R模型属于（）。

A. 概念模型

B. 层次模型

C. 网状模型

D. 关系模型

7．学生社团可以接纳多名学生参加，但每个学生只能参加一个社团，从社团到学生之间的联系类型是( ) 。

A. 多对多

B. 一对一

C. 多对一

D. 一对多

8．反映现实世界中实体及实体间联系的信息模型是( ) 。

A. 关系模型

B. 层次模型

C. 网状模型

D. E-R模型

二、设计题

1．某医院病房计算机管理中需要如下信息：

科室：科名，科地址，科电话，医生姓名

病房：病房号，床位号，所属科室名

医生：姓名，职称，所属科室名，年龄，工作证号

病人：病历号，姓名，性别，诊断，主管医生，病房号

其中，一个科室有多个病房、多个医生，一个病房只能属于一个科室，一个医生只属于一个科室，但可负责多个病人的诊治，一个病人的主管医生只有一个。设计该计算机管理系统的E－R图，并注明属性和联系类型。

2．假设某公司的业务规则如下：

（1）公司下设几个部门，如技术部、财务部、市场部等。

（2）每个部门承担多个工程项目，每个工程项目属于一个部门。

（3）每个部门有多名职工，每一名职工只能属于一个部门。

（4）一个部门可能参与多个工程项目，且每个工程项目有多名职工参与施工。根据职工在工程项目中完成的情况发放酬金。

（5）工程项目有工程号、工程名两个属性；部门有部门号、部门名称两个属性；职工有职工号、姓名、性别属性。

请根据上述规则设计E-R模型。

3. 某公司的业务规则如下：

（1）每位职工可以参加几个不同的工程，且每个工程有多名职工参与；

（2）每位职工有一个职位，且多名职工可能有相同的职位；

（3）职位决定小时工资率，公司按职工在每一个工程中完成的工时，计算酬金；

（4）职工的属性有职工号、姓名、职位和小时工资率；

（5）工程的属性有工程号和工程名称。

试根据上述业务规则设计E-R模型。

答案：

一、选择题 DDCB DADD

二、设计题

1.

2.

3.

===================================================2

第3章习题

1．学校有若干个系，每个系有若干班级和教研室，每个教研室有若干教师，每名教师只教一门课，每门课可由多个教师教；每个班有若干学生，每个学生选修若干课程，每门课程可由若干学生选修。请用E-R图画出该学校的概念模型，注明联系类型，再将E-R模型转换为关系模型。

2．工厂生产的每种产品由不同的零件组成，有的零件可用于不同的产品。这些零件由不同的原材料制成，不同的零件所用的材料可以相同。一个仓库存放多种产品，一种产品存放在一个仓库中。零件按所属的不同产品分别放在仓库中，原材料按照类别放在若干仓库中（不跨仓库存放）。请用E-R图画出此关于产品，零件，材料，仓库的概念模型，注明联系类型，再将E-R模型转换为关系模型。

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

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

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

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

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

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

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

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

（3）指出转换后的每个关系模式的关系键。

答案

1.

n

1

假定：主码用____ 表示

外码用 表示

2. 该E-R 图可以转换为以下关系模式：

产品（产品名，型号，重量，仓库号，数量c ）

材料（材料编号，名称，仓库号，数量a ）

产品-零件（产品名，零件名，组装日期b ）

零件-材料（材料编号，零件名，生产日期a ） 零件-仓库（零件名，仓库号，数量b ）

3.

3个M:N 的联系必须成为一个独立的关系模4个实体各自为一个关系模式，2个1:N 的联系没有成为一个独立的关系模式，而是合并到多方实体，合并时包括联系得属性

该E-R图可以转换为以下关系模式：

图书（书号，书名，数量，位置，出版社名）

借书人（借书证号，姓名，单位）

出版社（出版社名，邮编，地址，电话，E-mail）

借阅（借书证号，书号，借书日期，还书日期）

===================================================3

第5章习题

1．试用T-SQL语言创建ZYGL(职员管理)数据库，要求数据文件的初始大小为1MB，最大大小为50MB，增长方式按10%增长；日志文件的初始大小为3MB，按1MB增长。

2．试用T-SQL语言修改ZYGL数据库的主数据文件，将增长方式修改为按5MB增长。

3．试用T-SQL语言修改ZYGL数据库，为ZYGL增加一个数据文件ZYGLBAK。

4．试用T-SQL语言删除ZYGL数据库。

--1.

CREATE DATABASE zygl

ON

(NAME = 'zygl_Data',

FILENAME ='E:\sql\zygl_Data.MDF' ,

SIZE = 1, MAXSIZE = 30, FILEGROWTH = 10%

)

LOG ON

(NAME = 'zygl_Log',

FILENAME ='E:\sql\zygl_Log.LDF' ,

SIZE = 3, FILEGROWTH = 1

)

GO

--2

ATLER DATABASE ZYGL

MODIFY FILE

( NAME =ZYGL_DA TA, FILEGROWTH =5 )

--3

ATLER DATABASE ZYGL

ADD FILE

(NAME =ZYGLBAK ,

FILENAME ='E:\sql\zygl_Data1 .NDF' ,

SIZE = 1, MAXSIZE = 50, FILEGROWTH = 10% )

--4

DROP DATABASE ZYGL

===================================================4

第6章习题

设计题

现有一个数据库scd，库中包含以下系、学生、班级各表：

student（学号，姓名，年龄，班号）

class（班号，专业名，系名，入学年份）

department（系号，系名）

假设表中将有如下数据：

student （

2008101，张山，18，101；

2008102，李斯，16，102；

2008103，王玲，17，111；

2008105，李飞，19，112

）

class（

101，软件，计算机，2005；

102，微电子，计算机，2006；

111，无机化学，化学，2004；

112，高分子化学，化学，2006

）

department （

001，数学；

002，计算机；

003，化学

）

1. 用SQL语言创建以上各数据表，在定义中要求为每一字段（列）选择合适的数据类型和长度，并设置适当的约束，即声明：

（1）每个表的主键和所有可能的外键。

（2）学生姓名不能为空。

（3）系部系名不能为空，且唯一。

（4）学生的年龄介于15到40岁之间，默认为18。

2．试用SQL语言完成下列功能：

（1）学校又新增加了两个系部：一个物理系，编号为006，一个经济系，编号为008。

（2）将入学年份在2004年以前的班级删除。

（3）学生张山转到111班，请更新相关的表。

（4）为每个学生的年龄增加1岁，请更新相关的表。

3．试用SQL语言修改表结构：

由于年龄可以通过出生日期计算而来，对student表删除“年龄”字段，增加一个“出生日期”字段。（提示：必须先删除对年龄的检查约束，才能删除年龄列）

答案

--1.

create database scd

go

Use scd

Go

CREATE TABLE department

(系号 char(3) PRIMARY KEY,

系名 char(20) not null unique

)

Go

CREATE TABLE class

(班号 char(3) PRIMARY KEY,

专业名 char(20),

系名 char(20) references department(系名) ,

入学年份 int

)

Go

CREATE TABLE student

(学号 char(7) PRIMARY KEY,

姓名 char(8) NOT NULL,

年龄 int constraint ck_nl CHECK(年龄 >=15 and 年龄<=40)

constraint df_nl DEFAULT 18, --指定约束名ck_nl 和df_nl,以便以后好删除

班号 char(3) references class(班号)

)

Insert into department values ('001', '数学');

Insert into department values ('002', '计算机');

Insert into department values ('003', '化学')

Insert into class values ( '101','软件','计算机',2005);

Insert into class values ( '102','微电子','计算机',2006);

Insert into class values ('111','无机化学','化学',2004);

Insert into class values ('112','高分子化学','化学',2006 )

Insert into student values('2008101','张山',18,'101');

Insert into student values('2008102','李斯',16,'102');

Insert into student values( '2008103','王玲',17,'111')

Insert into student values( '2008104','李飞',19,'112')

--2.

--1)

Insert into department values(‘006’,’物理系‘)

Insert into department values(‘008’,’经济系‘)

--2)

Delete class where 入学年份<2004

--3)

Update student set班号='111'where姓名='张山'

--4）

Update student set 年龄=年龄+1

--3.

--1)

--要删除年龄列，应先删除该列上的约束，再删除

Alter table student drop constraint ck_nl, df_nl

--删除年龄列

Alter table student drop column 年龄

Alter table student add 出生日期 datetime

--2)

Alter table class

Add 班长学号 char(7) references student(学号)

===================================================5

第7章习题

设计题

以下各题在CJGL数据库中实现：

1. 查询“数据库原理”这门课程的学分和开课学期。

2. 查询身高在175cm以上的男生信息。

3. 查询所有姓“李”同学的基本情况。

4. 查询至少选修了两门课程的学生学号。

5. 将所有学生信息按身高逆序输出。

6. 查询平均成绩在75分以上的学生姓名、性别和专业。

7. 查询“李林”同学没学过的课程号。

8. 查询有两门课以上不及格的学生姓名。

9. 检索选修课程包含“程明”同学所选课程之一的学生学号。

10. 检索同时选修了课程号为101和102这两门课程的学生学号。

11. 检索选修课程名为“数据库原理”的学生学号和姓名。

12*. 查询每门课得最高分的学生姓名。

13*. 检索选修课程包含学号为081103的学生所修全部课程的学生学号。

14. 创建一个名为avg75的视图，包含所有平均成绩在75分以上的学生信息。答案

1. 查询“数据库原理”这门课程的学分和开课学期。

Select 课程号，开课学期

From kc

Where 课程名 =’数据库原理’

2. 查询身高在175cm以上的男生信息。

Select *

From xs

Where 性别=’男’ and 身高>175

3. 查询所有姓“李”同学的基本情况。

Select *

From xs

Where 姓名 like ‘李%’

4.查询至少选修了两门课程的学生学号。

Select 学号

From xs_kc

Group by 学号

Having count(课程号)>=2

5. 将所有学生信息按身高逆序输出。

Select *

From xs

Order by 身高 desc

6 检索平均成绩在75分以上的学生姓名、性别和专业。方法一：

Select 姓名，性别，专业名

From xs

Where 学号 in (

Select 学号

From xs_kc

Group by 学号

Having avg(成绩)>75)

方法二：

Select 姓名，性别，专业名

From xs_kc JOIN xs ON xs.学号=xs_kc.学号

Group by xs.学号, 姓名，性别，专业名

Having avg(成绩)>75

7. 检索“李林”同学不学的课程号。

Select 课程号

From kc

Where 课程号 not in (

Select 课程号 from xs, xs_kc

Where xs.学号=xs_kc.学号 and xs.姓名=’李林’)

8. 查询只有两门课不及格的学生姓名。

Select 姓名

From xs

Where 学号 in (Select 学号

From xs_kc

Where 成绩<60

Group by 学号

Having count(课程号)=2)

9. 检索选修课程包含“程明”同学所选课程之一的学生学号。

Select distinct 学号

From xs_kc

Where 课程号 in (

Select 课程号 from xs, xs_kc

Where xs.学号=xs_kc.学号 and xs.姓名=’程明’)

10. 检索同时选修了课程号为101和102这两门课程的学生学号。

select*from xs_kc

------------------

错误写法：

Select学号

From xs_kc

Where课程号='101'or课程号='102'

Select学号

From xs_kc

Where课程号='101'and课程号='102'

-------------------------------------------方法一，使用自连接：方法一，使用自连接：

Select a.学号

From xs_kc a , xs_kc b

Where a.学号=b.学号and a.课程号='101'and b.课程号='102'

方法二，使用子查询：

Select学号

From xs

Where学号in(select学号From xs_kc where课程号='101') And学号in(select学号From xs_kc where课程号='102')

方法三、

Select学号

From xs_kc

Where课程号='101'or课程号='102'

group by学号having count(学号)=2

11. 检索选修课程名为“数据库原理”的学生学号和姓名。

Select a.学号,姓名

From xs a , xs_kc b , kc c

Where a.学号=b.学号 and b.课程号=c. 课程号 and 课程名=’数据库原理’

12. 查询每门课的最高分的学生姓名。

相关查询法

SELECT A.学号, 姓名, B.课程号, 成绩

FROM XS A, XS_KC B

WHERE A.学号=B.学号 AND 成绩=

(SELECT MAX(成绩) FROM XS_KC C

WHERE B.课程号=C.课程号)

或派生表法

SELECT A.姓名, B.课程号, B.成绩, C.最高成绩

FROM XS A , XS_KC B , (SELECT 课程号, MAX(成绩) 最高成绩 FROM XS_KC group by 课程号) C

WHERE A.学号=B.学号 AND B.课程号= C.课程号 and B.成绩=C.最高成绩

13. 检索选修课程包含学号为081101的学生所修课程的学生学号。

方法一，

select distinct 学号

from xs_kc

where 课程号 in ( select 课程号

from xs_kc

where 学号='081101')

group by 学号

having count(课程号)=

(select count (课程号)

from xs_kc

where 学号='081101')

方法二，

SELECT 学号，姓名

FROM xs

WHERE NOT EXISTS(

SELECT *

FROM kc , xs_kc a

WHERE kc.课程号=a. 课程号 and 学号=’ 081103’

and NOT EXISTS(

SELECT *

FROM xs_kc b

WHERE b.学号=xs.学号 AND 课程号=kc.课程号 ) )

14. 创建一个名为avg75的视图，包含所有平均成绩在75分以上的学生信息。

Create view avg75

As

Select *

From xs

Where 学号 in (

Select 学号

From xs_kc

Group by 学号

Having avg(成绩)>75)

Create view avg75

As

Select 姓名，性别，专业名，avg(成绩)

From xs_kc JOIN xs ON xs.学号=xs_kc.学号

Group by 姓名，性别，专业名

Having avg(成绩)>75

===================================================6

第8章习题

对于数据库scd，库中包含以下系、学生、班级各表：

student （学号，姓名，年龄，班号）

class（班号，专业名，系名，入学年份）

department （系号，系名）

请使用T-SQL语言完成以下各题。

1．将student表的班号与姓名这两列组合创建一个升序的非聚集索引。

2．为department表的系名建立一个唯一索引，如果输入了重复的键，将忽略该INSERT或UPDATE语句，并使用填充因子FILLFACTOR 为50%。

3．为数据库scd建立一个默认对象，使其对应于年龄为18，将其绑定到student表的年龄列上。

4．为数据库scd建立一个规则对象，并将其绑定到学生表的专业名列上，规定专业名的取值只能为‘护理学’、‘地质勘探’和‘考古学’之一。

5．修改class表为其建立一个CHECK约束，检查入学年份是否小于2009。

答案

1．将student表的班号与姓名这两列组合创建一个升序的非聚集索引。

Create nonclustered index ix_bj_xm on student (班号 asc , 姓名 asc)

2．为department表的系名建立一个唯一索引，如果输入了重复的键，将忽略该INSERT或UPDATE语句，并使用填充因子FILLFACTOR 为50%。

Create unique index uqix_xm on department (系名 asc)

WITH IGNORE_DUP_KEY， FILLFACTOR = 50

3．为数据库scd建立一个默认对象，使其对应于年龄为18，将其绑定到student表的年龄列上。

Create default d_age as 18

Go

Exec sp_bindefault ‘d_age’ , ‘student.年龄’

4．为数据库scd建立一个规则对象，并将其绑定到学生表的专业名列上，规定专业名的取值只能为‘护理学’、‘地质勘探’和‘考古学’之一。

Create rule r_zym as @zym in (‘护理学’, ‘地质勘探’,’考古学’)

Go

Exec sp_bindrule ‘r_zym’, ‘student.专业名’

5．修改class表为其建立一个CHECK约束，检查入学年份是否小于2008。

Alter table class

Add constraint ck_rxnf check (入学年份<2008 )

==================================================7

第9章习题1

习题九

三、设计题：

1．使用流程控制语句编写程序：

（1）在CJGL数据库中，使用case函数处理：如果课程的学时在80以上显示学习时间长, 学时在54~80 显示学习时间一般, 否则显示学习时间短。

（2）在CJGL数据库中，使用case函数处理：如果学生的专业是“计算机”，显示热门，是“通信工程”显示一般，否则显示冷门。

（3）计算18！=18*17*16…*2*1。

（4）计算S = 1！+2！+3！+ (10)

2．以下各题在CJGL数据库中实现：

（1）创建一个自定义函数，可以求解任意数n的阶乘，即n!。并用n=10调用该函数。

（2）分别用函数或存储过程实现以下功能：给定输入参数课程名，统计该课程成绩介于58~59分的学生人数。使用输入参数“数据库原理”调用该函数或该存储过程。

（3）创建存储过程来完成求解给定某个学生的学号返回该生的姓名和平均分的功能。

（4）分别使用约束和触发器实现以下功能：检查在课程表中插入或修改的课程号是否在101~108之间。

3．以下各题在第六章习题（设计题）给出的数据库SCD中实现：

（1）创建一个自定义函数用于统计各班级的人数，将class表增加一个人数字段，然后调用该函数将人数定义为计算列。

（2）创建一个可以按给定学号输出学生的姓名、系名信息的存储过程。

（3）创建一个触发器，当插入或更新某个学生的记录时，检查该班级的学生是否超过50人，如果超过，就发出警告信息同时撤消所作的插入或更新操作。

答案：

三、设计题

1．使用流程控制语句编写程序：

（1）在CJGL数据库中，使用case函数处理：如果课程的学时在80以上显示学习时间长, 学时在54~80 显示学习时间一般, 否则显示学习时间短。

SELECT 课程号，课程名，学习时间=

CASE

WHEN 学时>=80 THEN '学习时间长'

WHEN 学时>=54 THEN '学习时间一般'

ELSE ‘学习时间短’

END

FROM KC

（2）在CJGL数据库中，使用case函数处理：如果学生的专业是“计算机”，显示热门，是“通信工程”显示一般，否则显示冷门。

SELECT 学号, 姓名, 专业热度=

CASE 专业名

WHEN ‘计算机’ THEN '热门'

WHEN ‘通信工程’ THEN '一般'

ELSE '冷门 '

END

FROM xs

（3）计算18！=18*17*16…*2*1。

DECLARE @i int , @p bigint

SELECT @i=1, @p=1

WHILE @i<=18

BEGIN

SET @p=@p*@i

SET @i=@i+1

END

PRINT '18*17*16…*2*1='+CAST(@p AS char(20))

（4）计算S = 1！+2！+3！+ (10)

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

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

WHILE @i<=10

BEGIN

SET @p=@p*@i

SET @s=@s+@p

SET @i=@i+1

END

PRINT '1！+2！+3！+……+10！='+CAST(@s AS char(20))

2．以下各题在CJGL数据库中实现：

（1）创建一个自定义函数，可以求解任意数n的阶乘，即n!。并用n=10调用该函数。

CREATE FUNCTION F_C(@n INT)

RETURNS INT AS

BEGIN

DECLARE @i int , @p bigint

SELECT @i=1, @p=1

WHILE @i<=@n

BEGIN

SET @p=@p*@i

SET @i=@i+1

END

Return @ p

END

Go

--调用

select dbo. F_C (10)

（2）分别用函数或存储过程实现以下功能：给定输入参数课程名，统计该课程成绩介于58~59分的学生人数。使用输入参数“数据库原理”调用该函数或该存储过程。

----函数：

Create function f_cj58 (@cna char(20)) returns int

As

BEGIN

DECLARE @c int

SELECT @c=count(*) from CJGL.dbo.xs_kc a , CJGL.dbo. kc b

where a.课程号= b.课程号 and 课程名=@cna and (成绩 between 58 and 59)

Return @c

END

Go

以下函数调用：

Select dbo.f_cj58 (‘数据库原理’) as [58~59分人数]

Go

-----存储过程：

create procedure p_cj58

@cna char(20), @c int output

as

SELECT @c=count(*) from CJGL.dbo.xs_kc a , CJGL.dbo. kc b

where a.课程号= b.课程号 and 课程名=@cna and (成绩 between 58 and 59)

return

Go

--存储过程调用

DECLARE @c1 int

Exec p_cj58 ‘数据库原理’, @c1 output

（3）创建存储过程来完成求解给定某个学生的学号返回该生的姓名和平均分的功能。

Create procedure p_pj

@sno char(8) , @sna char(8) output , @savg int output

as

SELECT @sna=姓名,@ savg=avg(成绩) from CJGL.dbo.xs a , CJGL.dbo.xs_kc b

where a.学号= b.学号 and a.学号=@sno GROUP BY 姓名

return

Go

--调用

DECLARE @sna char(8) , @savg int

Exec p_pj ‘061101’ , @sna output , @savg output

Select @sna, @savg

（4）分别使用约束和触发器实现以下功能：检查在课程表中插入或修改的课程号是否在101~108之间。

---------------------------使用触发器

Create trigger T_KC on kc

For INSERT, UPDATE

As

Declare @cno char(4)

Select @cno=课程号 from inserted

If @cno not like ’10[1-8]’

rollback transaction

go

----------------------------使用约束

Alter talbe kc

Add constraint ck_kh check( 课程号 like ’10[1-8]’ )

3．以下各题在第六章习题（设计题）给出的数据库SCD中实现：

（1）创建一个自定义函数用于统计各班级的人数，将class表增加一个人数字段，然后调用该函数将人数定义为计算列。

USE SCD

GO

CREATE FUNCTION F_CNT (@CNUM CHAR(8)) RETURNS INT

AS

BEGIN

DECLARE @CNT INT

SELECT @CNT= COUNT(*)

FROM student

WHERE 班号=@CNUM

RETURN @CNT

END

GO

USE SCD /*假定用户函数F_CNT在此数据库中已定义*/

CREATE TABLE CLASS

(班号 CHAR(3) PRIMARY KEY,

专业名 CHAR(20),

系名 CHAR(20) REFERENCES DEPARTMENT(系名) ,

入学年份 INT

人数 AS DBO. F_CNT (班号)

)

（2）创建一个可以按给定学号输出学生的姓名、系名信息的存储过程。

方法一：

CREATE PROCEDURE STU_INFO

@SNO CHAR (7)

AS

SELECT A.学号, 姓名, 系名

FROM STUDENT A INNER JOIN CLASS B

ON A.班号= B.班号

WHERE 学号=@SNO

GO

调用：

EXEC STU_INFO '2008101'

方法二：

CREATE PROCEDURE STU_INFO_1

@SNO CHAR (6), @SNA CHAR(8) OUTPUT,@DNA CHAR(20) OUTPUT

AS

SELECT @SNA =姓名, @DNA =系名

FROM STUDENT A INNER JOIN CLASS B

ON A.班号= B.班号

WHERE 学号=@SNO

GO

调用：

DECLARE @SNA CHAR(8),@DNA CHAR(20)

EXEC STU_INFO '2008101' ,@SNA OUTPUT,@DNA OUTPUT

SELECT @SNA ,@DNA

（3）创建一个触发器，当插入或更新某个学生的记录时，检查该班级的学生是否超过50人，如果超过，就发出警告信息同时撤消所作的插入或更新操作。

Create trigger T_stu on student

For INSERT, UPDATE

As

Declare @cno char(4)

Select @cno=班号 from inserted

If (select count(*) from student

where 班号=@cno ) >50

begin

Raiserror ('该班人数超过人，插入或修改已撤销.', 16, 1)

Rollback transaction

end

GO

===================================================8

第9章习题2

在职员管理数据库ZYGL中完成以下操作：

1．创建一个自定义数据类型ID_type，用于描述员工号，执行如下程序：

USE ZYGL

EXEC sp_addtype 'ID_type', 'char(3) ', 'not null '

GO

2．在SQL Server管理平台中通过表设计器修改职员表和工资表，对它们的“员工号”

的数据类型使用自定义数据类型ID_type。

3．创建一个自定义函数用于统计某部门的人数，部门名为函数的输入参数，如果没有该部门，则返回-1值。

USE ZYGL

CREATE FUNCTION total_rs(@name_bm char(10))

RETURNS int AS

BEGIN

DECLARE @num int

IF not EXISTS( SELECT * FROM 部门表 WHERE 部门名=@name_bm)

SELECT @num= -1

ELSE

SELECT @num=count(*) FROM 职员表,部门表

WHERE 职员表.部门号=部门表.部门号 and 部门名=@name_bm

RETURN @num

END

4．分别以部门名“办公室”和“销售科”调用函数total_rs：

USE ZYGL

SELECT dbo.total_rs('销售科') AS 部门人数

记录结果。

SELECT dbo.total_rs('办公室') AS 部门人数

记录结果。

5．创建一个可以按给定员工姓名，输出其姓名、部门名及其工资信息的存储过程。USE ZYGL

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'yg_info' AND type = 'P')

DROP PROCEDURE yg_info

GO

/*创建存储过程*/

CREATE PROCEDURE yg_info @name char(8)

AS

SELECT a.员工号, 姓名, 部门名, 应发工资, 实发工资

FROM 职员表 a INNER JOIN 部门表 b ON a.部门号 = b.部门号

INNER JOIN 工资表 t ON a.员工号= t.员工号

WHERE 姓名=@name

GO

6．调用yg_info存储过程查询员工 '岳亮'的信息。

USE ZYGL

EXEC yg_info '岳亮'

7．创建一个触发器，当插入或更新某个员工的工资记录时，检查该员工的基本工资是否超过平均基本工资的三倍，如果超过，就发出警告信息同时撤消插入或更新。执行如下程序：

USE ZYGL

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'reminder_gz' AND type = 'TR')

DROP TRIGGER reminder_gz

GO

CREATE TRIGGER reminder_gz ON 工资表

FOR INSERT, UPDA TE

AS

DECLARE @jbgz decimal(7,2),@pjgz decimal(7,2)

SELECT @jbgz =基本工资 FROM inserted

SELECT @pjgz=avg(基本工资) FROM 工资表

IF @jbgz>@pjgz*3

BEGIN

ROLLBACK TRANSACTION --撤消插入操作

RAISERROR ('该工资数据有误，超过平均值三倍，操作已经撤消！', 16, 10) /*返回一个错误信息*/ END

GO

8．在SQL Server管理平台中打开ZYGL库的工资表，将001号员工的基本工资分别修改为2600与8600，看触发器的触发反应。

===================================================9

第10章习题1

在职员管理数据库ZYGL中完成以下操作：

1．采用游标的方式将每位员工的津贴提高20％。注意津贴的变化对实发工资和应发工资的影响。

方法一：游标的非CURRENT形式的UPDATE语句

USE ZYGL

GO

DECLARE @id char(3)

DECLARE salary_cursor CURSOR

FOR SELECT 员工号 FROM 工资表

OPEN salary_cursor

FETCH NEXT FROM salary_cursor

INTO @id

WHILE @@FETCH_STATUS=0

BEGIN

UPDA TE 工资表 SET 津贴=津贴*1.2 WHERE 员工号=@id

FETCH NEXT FROM salary_cursor

INTO @id

END

CLOSE salary_cursor

DEALLOCATE salary_cursor

SELECT * FROM 工资表

GO