数据库管理系统技术、应用与实例分析SQL_Server_2005(第2版)孟宪虎-电子工业出版社-课后实验参考答案[1]

实验参考答案

实验四

(1)创建表

①使用对象资源管理器创建

②使用DDL语言定义基本表.

有一个项目管理（xmgl）数据库,现有四张表，分别是部门表(部门号，部门名，部门电话，部门地址);员工表(员工号，姓名，性别，出生年月，所在部门号）；项目表(项目编号，项目名称，所在地方）以及员工参与项目(员工号，项目编号,职责）。其中一个员工属于一个部门，一个部门有多个员工；一个员工可同时参加多个项目，一个项目有多个员工一起开发。用SQL语言中的DDL语言建立这四张表,设计并定义表的主码和外码，各字段的数据类型自己设计。

部门表的创建：

create table部门表(

部门号char(4),

部门名char(10),

部门电话char(13),

部门地址char(30),

部门人数char(4),

primary key(部门号)

)

员工表的创建：

create table员工表(

员工号char(4),

姓名char(8),

性别char(2),

出生年月datetime,

所在部门号char(4),

primary key(员工号),

foreign key(所在部门号) references部门表(部门号)

)

项目表的创建：

create table项目表(

项目编号char(5),

项目名称char(20),

所在地方char(30),

项目类型char(10),

primary key(项目编号)

)

员工参与项目表的创建：

create table员工参与项目表(

员工号char(4),

项目编号char(5),

职责char(10),

primary key(员工号,项目编号),

foreign key(员工号) references员工表(员工号),

foreign key(项目编号) references项目表(项目编号)

)

所得结果为：

(2)从sysobjects、sysindexes和syscolumns，sysrefrences表中查看关于上

述创建的表的信息。

sysobjects表：

sysindexes表：

Syscolumns表：

Sysrefrences表：

（3）使用对象资源管理器定义下面的约束。

①员工号是四位数字串，其中第一位和最后一位是取1-9之间的数字，其他是0-9。

②项目编号是以字母J开始的其它是数字的4位字符串。

③约束性别的取值为‘男’、‘女’，且非空，缺省是男。

④约束电话号码的格式：以8892开始的，第5位取234中的一个，其它是数字的8位数字串。

（4）使用DDL语言增加修改部分表及字段

①在部门表中增加部门领导字段(注意和员工号同域)；在项目表中增加项目主管字段(注意和员工号同域)；

②在员工表中增加技术职称和工资字段，其中工资为数字类型；

③在项目表中增加开工日期和完工日期字段，类型为日期类型；alter table项目表

add开工日期datetime

alter table项目表

add完工日期datetime

④增加约束工程起始日期小于计划完成日期。

alter table项目表

add check(开工日期<完工日期)

⑤修改部门人数字段类型为整型。alter table部门表

alter column部门人数int

⑥删除项目表中的项目类型字段。alter table项目表

drop column项目类型

实验五

（1）调出第4章实验创建的四个表。方法：附加XMGL数据库或运行创建4个表的SQL语句。

（2）用SQL语句增加数据语句输入四个表中数据。其中部门表不得少于5个，员工表不得少于10个，项目表不得少于10个，员工参与项目的情况表不得少于20个。

（3）设计查询语句并在查询编辑器中进行查询。

①求参加'J3'项目的员工姓名

--请参加了‘J3’项目的员工姓名

select 姓名

from 员工参与项目表,员工表

where 项目编号='J3'

and 员工表.员工号=员工参与项目表.员工号

②查询'张明'这个职工所参加的项目的项目号，项目名称以及项目所在地方。select 项目表.项目编号,项目名称,所在地方

from 员工参与项目表,员工表,项目表

where 员工表.员工号=员工参与项目表.员工号

and 员工参与项目表.项目编号=项目表.项目编号

and 姓名='张明'

③查询参与了所有项目的员工姓名和员工所在的部门。

select 姓名,所在部门号

from 员工表

where not exists

(select *

from 项目表

where not exists

(select *

from 员工参与项目表

where 员工号=员工表.员工号

and 项目编号=项目表.项目编号))

④查询没有参与任何一个项目的员工姓名和所在部门。select 姓名,所在部门号

from 员工表

where not exists

(select *

from 项目表

where exists

(select *

from 员工参与项目表

where 员工号=员工表.员工号

and 项目编号=项目表.项目编号))

⑤查询所有部门都有员工参与的项目。