《大型数据库管理系统技术.应用与实例分析SQLServer2005第2版孟宪虎课后实验 参考 答案资料》由会员分享，可在线阅读，更多相关《大型数据库管理系统技术.应用与实例分析SQLServer2005第2版孟宪虎课后实验 参考 答案资料（76页珍藏版）》请在金锄头文库上搜索。
1、实验参考答案实验四(1)创建表使用对象资源管理器创建使用DDL语言定义基本表. 有一个项目管理（xmgl）数据库,现有四张表，分别是部门表(部门号，部门名，部门电话，部门地址);员工表(员工号，姓名，性别，出生年月，所在部门号）；项目表(项目编号，项目名称，所在地方）以及员工参与项目(员工号，项目编号,职责）。其中一个员工属于一个部门，一个部门有多个员工；一个员工可同时参加多个项目，一个项目有多个员工一起开发。用SQL语言中的DDL语言建立这四张表,设计并定义表的主码和外码，各字段的数据类型自己设计。部门表的创建：create table 部门表( 部门号 char(4), 部门名 char(
2、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), prima
3、ry 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表：Sysrefre
4、nces表：（3）使用对象资源管理器定义下面的约束。员工号是四位数字串，其中第一位和最后一位是取1-9之间的数字，其他是0-9。 项目编号是以字母J开始的其它是数字的4位字符串。 约束性别的取值为男、女，且非空，缺省是男。 约束电话号码的格式：以8892开始的，第5位取234中的一个，其它是数字的8位数字串。（4）使用DDL语言增加修改部分表及字段 在部门表中增加部门领导字段(注意和员工号同域)；在项目表中增加项目主管字段(注意和员工号同域)；在员工表中增加技术职称和工资字段，其中工资为数字类型；在项目表中增加开工日期和完工日期字段，类型为日期类型；alter table 项目表 add 开工
5、日期 datetimealter 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个，
6、员工参与项目的情况表不得少于20个。（3）设计查询语句并在查询编辑器中进行查询。求参加J3项目的员工姓名-请参加了J3项目的员工姓名select 姓名from 员工参与项目表,员工表where 项目编号=J3 and 员工表.员工号=员工参与项目表.员工号查询张明这个职工所参加的项目的项目号，项目名称以及项目所在地方。select 项目表.项目编号,项目名称,所在地方from 员工参与项目表,员工表,项目表where 员工表.员工号=员工参与项目表.员工号 and 员工参与项目表.项目编号=项目表.项目编号 and 姓名=张明查询参与了所有项目的员工姓名和员工所在的部门。select 姓名,所
7、在部门号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 项目编号=项目表.项目编号)查询所有部门都有员工参与的项目。select
8、 项目编号from 项目表where not exists (select * from 员工表 where not exists (select * from 员工参与项目表 where 员工号=员工表.员工号 and 项目编号=项目表.项目编号)查询参加了在上海的项目的所有职工的编号、姓名和所在部门。select 员工表.员工号,姓名,所在部门号from 员工参与项目表,员工表,项目表where 员工表.员工号=员工参与项目表.员工号 and 员工参与项目表.项目编号=项目表.项目编号 and 所在地方 like %杭州%列出每个部门职工的工资，部门的平均工资、最高工资、最低工资，工资合计
9、，以及整个单位职工的平均工资总计。select 所在部门号,avg(工资) as 平均工资,max(工资) as 最高工资,min(工资) as 最低工资,sum(工资) as 工资合计from 员工表group by 所在部门号 select avg(工资) as 平均工资总计from 员工表对所有项目主管的工资增加10%。update 员工表set 工资=工资*1.1where exists (select * from 项目表 where 项目表.项目主管=员工表.员工号)查询工资改变情况改变前：改变后：实验六先删除员工表和员工参与项目表上面的所有索引.1）使用对象资源管理器创建、管理索
10、引为员工表创建一个索引名为“emp_id”的唯一性非聚集索引，索引关键字是“员工号”，填充因子80 % 。 重命名索引，将索引emp_id重命名为员工表_员工号。 删除索引员工表_员工号。二、使用T-SQL语句创建、管理索引 为员工表创建一个索引名为emp_id的唯一性非聚集索引，索引关键字是员工号，填充因子80 % 。create unique nonclustered index emp_id on 员工表(员工号 asc)withfillfactor=80回到企业管理器查看： 重命名索引，将索引emp_id重命名为员工表_员工号。sp_rename 员工表.emp_id,员工表_员工号回
11、到企业管理器中查看：为员工参与项目表创建一个索引名为“员工_项目_index”的非聚集复合索引，索引关键字为“员工号”，升序，项目编号，降序，填充因子50%。create nonclustered index 员工_项目_index on 员工参与项目表(员工号 asc ,项目编号 desc)with fillfactor=50删除索引“员工表_员工号”和“员工_项目_index”。drop index 员工参与项目表.员工_项目_index回到企业管理器查看3）索引前后的执行计划删除员工表中员工号上的主键。按员工姓名和项目名称查询对应的职责，然后观察执行计划信息，计算总的I/O和CPU开销。
12、（员工表和员工参与项目表中的员工号都没有索引）select 姓名,项目名称,职责from 员工表,项目表,员工参与项目表where 员工表.员工号=员工参与项目表.员工号 and 项目表.项目编号=员工参与项目表.项目编号观察执行计划I/O=0.00625+0.0375+0.00632+0.00632=0.05639Cpu开销=0.000105+0.000028+0.000105+0.000106+0.00008+0.00008=0.001944为员工参与项目表创建一个索引名为“员工参与项目_员工号”的非聚集索引，索引关键字为“员工号”，升序；按员工姓名和项目名称查询对应的职责，然后观察执行计
13、划信息，计算总的I/O和CPU开销。（员工表中员工号没索引，员工参与项目表中的员工号有非聚集索引）create nonclustered index 员工参与项目表_员工号 on 员工参与项目表(员工号 asc)withfillfactor=80select 姓名,项目名称,职责from 员工表, 员工参与项目表,项目表where 员工表.员工号=员工参与项目表.员工号and 项目表.项目编号=员工参与项目表.项目编号观察执行计划：I/O=0.00625+0.0375+0.000081+0.00632=0.050151Cpu开销=0.000105+0.000028+0.000105+0.000089+0.000081+0.00008=0.000737重建员工表中员工号上的主键，删除“员工参与项目_员工号”的非聚集
