实验参考答案
实验四
(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 项目编号=项目表.项目编号))
⑤查询所有部门都有员工参与的项目。
select 项目编号
from 项目表
where not exists
(select *
from 员工表
where not exists
(select *
from 员工参与项目表
where 员工号=员工表.员工号
and 项目编号=项目表.项目编号))
⑥查询参加了在上海的项目的所有职工的编号、姓名和所在部门。
select 员工表.员工号,姓名,所在部门号
from 员工参与项目表,员工表,项目表
where 员工表.员工号=员工参与项目表.员工号
and 员工参与项目表.项目编号=项目表.项目编号
and 所在地方 like '%杭州%'
⑦列出每个部门职工的工资，部门的平均工资、最高工资、最低工资，工资合计，以及整个单位职工的平均工资总计。
select 所在部门号,avg(工资) as 平均工资,max(工资) as 最高工资,min(工资) as 最低工资,sum(工资) as 工资合计
from 员工表
group by 所在部门号
select avg(工资) as 平均工资总计
from 员工表
⑧对所有项目主管的工资增加10%。
update 员工表
set 工资=工资*1.1
where exists
(select *
from 项目表
where 项目表.项目主管=员工表.员工号)
查询工资改变情况
改变前：foreign key references用法
改变后：
实验六
先删除员工表和员工参与项目表上面的所有索引.
1）使用对象资源管理器创建、管理索引
①为员工表创建一个索引名为“emp_id”的唯一性非聚集索引，索引关键字是“员工号”，填充因子80 % 。
⑵ 重命名索引，将索引emp_id重命名为员工表_员工号。
⑶ 删除索引员工表_员工号。
二、使用T-SQL语句创建、管理索引
①为员工表创建一个索引名为emp_id的唯一性非聚集索引，索引关键字是员工号，填充因子80 % 。
create unique nonclustered index emp_id
on 员工表(员工号 asc)
with
fillfactor=80
回到企业管理器查看：
② 重命名索引，将索引emp_id重命名为员工表_员工号。
sp_rename '员工表.emp_id','员工表_员工号'
回到企业管理器中查看：
③为员工参与项目表创建一个索引名为“员工_项目_index”的非聚集复合索引，索引关键字为“员工号”，升序，项目编号，降序，填充因子50%。
create nonclustered index 员工_项目_index
on 员工参与项目表(员工号 asc ,项目编号 desc)
with
fillfactor=50
④删除索引“员工表_员工号”和“员工_项目_index”。
drop index 员工参与项目表.员工_项目_index
回到企业管理器查看
3）索引前后的执行计划
①删除员工表中员工号上的主键。按员工姓名和项目名称查询对应的职责，然后观察执行计划信息，计算总的I/O和CPU开销。（员工表和员工参与项目表中的员工号都没有索引）
select 姓名,项目名称,职责
from 员工表,项目表,员工参与项目表
where 员工表.员工号=员工参与项目表.员工号
and 项目表.项目编号=员工参与项目表.项目编号
观察执行计划
I/O=0.00625+0.0375+0.00632+0.00632=0.05639
Cpu开销=0.000105+0.000028+0.000105+0.000106+0.00008+0.00008
=0.001944
②为员工参与项目表创建一个索引名为“员工参与项目_员工号”的非聚集索引，索引关键字为“员工号”，升序；按员工姓名和项目名称查询对应的职责，然后观察执行计划信息，计算总的I/O和CPU开销。（员工表中员工号没索引，员工参与项目表中的员工号有非聚集索引）
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论