数据库上机答案

数据库上机1
1、新建工厂数据库factory。

create database factory
on(
name=factory_data,
filename='d:\factory.mdf')
log on(name=factory_log,
filename='d:\factory.ldf')
2、数据库factory包括职工表worker、部门表depart和职工工资表salary。

用SQL语句建立这三个表并输入数据，其表结构及用例数据分别如下：
职工表结构为：
职工号：int;
姓名：char(8) ;
性别：char(2) ;
出生日期：datetime;
党员否：char(2) ;
参加工作：datetime;
部门号：int;
其中职工号为主码。

部门表结构为：
部门号：int;
部门名：char(10)；
其中部门号为主码。

职工工资表结构为：
职工号：int;
日期：datetime;
工资：decimal(6,1) ;
其中职工号和日期为主码。

create table worker
( 职工号int primary key, 姓名char(8) ,
性别char(2),
出生日期datetime,
党员否char(2),
参加工作datetime,
部门号int
)
create table depart
( 部门号int primary key, 部门名char(10)
)
create table salary
( 职工号int,
日期datetime,
工资decimal(6,1),
Primary key(职工号,日期)
)
3. 显示部门的详细信息。

select * from depart
4．查询3号职工的工资单。

select * from salary where职工号=3
5．显示所有职工的姓名及年龄，并按姓名升序显示。

select 姓名, 2015-year(出生日期) '年龄' from worker order by姓名6. 求出各部门的部门号及党员人数。

select 部门号,count(党员否) '党员人数' from worker
where 党员否='是'
group by 部门号
7. 在worker表的“姓名”列创建唯一索引nameindex。

create unique index nameindex on worker (姓名)
数据库上机2
1. 查询12月份过生日的名单。

答：select 姓名from worker
where month(出生日期)= 12
2. 查询所有姓刘的职工信息。

select * from worker where 姓名like ‘刘%’
3. 显示所有职工的职工号和2011年2月份的工资数。

select 职工号,工资from salary
where year(日期)=2011 and month(日期)=2
4. 查询1号部门所有职工的姓名和工资，并按工资降序排列。

select姓名,工资from worker, salary
where worker.职工号= salary.职工号and部门号=1
order by工资desc
5. 显示所有平均工资低于2500的部门名和对应的工资。

select 部门名, avg(工资) '部门平均工资'
from salary,worker,depart
where worker.部门号=depart.部门号and salary.职工号=worker.职工号group by部门名
having avg(工资) < 2500
6. 显示工资最高职工的职工号、姓名、部门名、工资发放日期和工资。

select worker.职工号, 姓名, 部门名, 日期, 工资
from worker,salary,depart
where worker.职工号=salary.职工号and worker.部门号= depart.部门号and 工资=(select max(工资) from salary)
7. 显示所有平均工资低于全部职工平均工资的职工的职工号、姓名和平均工资。

select worker.职工号, 姓名, avg(工资) '平均工资'
from worker,salary
where worker.职工号=salary.职工号
group by worker.职工号, 姓名
having avg(工资)<(select avg(工资) from salary)
数据库上机3
1. depart表中插入一条部门记录：(4,'设备处')。

insert into depart values(4,’设备处’)
2. worker表中插入一条职工记录：(20, '陈立', '女', '1980/06/08', ‘是’, '1999/11/10', 4)。

insert into worker values(20, '陈立', '女', '1980/03/08', '是', '1999/10/10', 4)
3. worker和depart表进行外连接显示职工的职工号、姓名和部门名；
select 职工号, 姓名, 部门名
from worker left outer join depart on(worker.部门号= depart.部门号)
或full outer join
4. 删除刚才新增的两条记录。

delete from worker where 职工号=20
delete from depart where 部门号=4
5. 建立视图view1，查询所有职工的工资发放情况。

create view view1 as
select * from salary
6. 建立视图view2，查询所有职工的姓名、部门名和2011年1月份的工资。

create view view2 as
select姓名,部门名,工资'2011年1月工资'
from salary,depart,worker
where worker.部门号= depart.部门号and worker.职工号= salary.职工号and year(salary.日期)=2011 and month(salary.日期)=1
7. 建立视图view3，查询各部门名和该部门的所有职工的平均工资。

create view view3 as
select 部门名, avg(工资) '部门平均工资'
from salary,worker,depart
where worker.部门号=depart.部门号and salary.职工号=worker.职工号
group by部门名
数据库上机4
1. 实施salary表的“工资”字段限定在0~9999的约束con1。

alter table salary
add constraint con2 check(工资between 0 and 9999)
2. 实施worker表的外码约束con2，为worker表建立外码“部门号”，
参考表depart的“部门号”字段。

alter table worker
add constraint con3 foreign key(部门号) references depart(部门号)
3. 创建默认对象con3，并使其值为“男”，使用sp_bindefault 绑定con3到worker表的性别列上。

create default con3 as ‘男’
exec sp_bindefault ‘con3’ ，‘worker.性别’
4. 删除题2建立的约束con2。

alter table worker
drop constraint con2
5. 在表depart上创建一个触发器depart_update，当更改部门号的同时更改worker表中对应的部门号。

将表depart中3号部门改为5号，并检查worker表中对应的部门号是否进行了修改。

create trigger depart_update on depart
after update
as
begin
update worker set 部门号= (select 部门号from inserted)
where 部门号= (select 部门号from deleted)
end
执行触发事件：update depart set 部门号=5 where 部门号=3
6. 在表worker上创建一个触发器worker_delete，当删除职工记录时同步删除salary表中对应的工资记录。

将表worker中10号职工删除，并检查salary表中10号职工的工资信息是否进行了删除。

create trigger worker_delete on worker
after delete
as
begin
delete from salary
where 职工号= (select 职工号from deleted)
end
执行触发事件：delete from worker where 职工号= 10
7. 删除触发器worker_delete。

drop trigger worker_delete on worker
数据库上机5、6
配置ODBC数据源factoryDNS，在VC++中通过ADO控件和DataGrid控件访问factory数据库。