数据库上机答案

数据库上机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数据库。