factory数据库考核资料Word下载.docx
《factory数据库考核资料Word下载.docx》由会员分享，可在线阅读，更多相关《factory数据库考核资料Word下载.docx（32页珍藏版）》请在冰豆网上搜索。
主管登录：
可插入、修改、查询、删除信息；
2）修改密码：
凡是该系统的用户都可以修改自己的密码；
3）退出系统
信息管理：
1）部门信息管理：
主管可以插入，删除，更改部门信息；
2）员工信息管理：
主管可以插入，删除，更改员工的基本信息和薪资信息；
信息查询:
普通员工可以查询自己的基本信息和薪资信息；
2、E-R图
3、关系模型：
1.部门信息（部门编号，部门名称）
2.员工信息（员工编号，姓名，性别，学历，出生日期，职务，部门编号,密码）
3.薪资信息（账号，员工编号，基本工资，福利，缺勤时间及原因,惩罚金额,应缴税费，总薪资）
2、如创建职工工资管理系统中，一个名称为factory的数据库，要求：
（1）将主数据库文件factory.mdf放置在H:
\DBF文件夹中，其文件大小自动增长为按5MB增长。
（2）将事务日志文件factory_log.ldf放置在H:
\DBF文件夹中，其文件大小自动增长为按1MB增长。
createdatabasefactory
on
（
name=factory,
FILENAME='
H:
\DBF\factory.mdf'
size=3mb,
filegrowth=5mb
）
logon
name=factory_log,
\DBF\factory_log.ldf'
size=1mb,
filegrowth=1mb
3、在上机面所创建的数据库factory中，完成如下各题操作。
（1）用create语句建立职工表worker，其结构为：
职工号，int；
姓名，char（8）；
性别，char
（2）；
出生日期，datetime；
党员否char
（2）；
参加工作，datetime；
部门号，int。
其中“职工号”为主键。
并用insert语句在worker表中输入如下记录：
职工号
姓名
性别
出生日期
党员否
参加工作
部门号
1
孙华
男
01/03/52
是
10/10/70
101
2
陈明
05/08/45
否
01/01/65
102
3
程西
女
06/10/80
07/10/02
4
孙天奇
03/10/65
07/10/87
5
刘夫文
01/11/42
08/10/60
6
刘欣
10/08/52
01/07/70
7
余慧
12/04/80
103
8
张旗
11/10/80
usefactory
go
createtableworker
（职工号intprimarykey,
姓名char（8）,
性别char
（2）,
出生日期datetime,
党员否char
（2）,
参加工作datetime,
部门号int,
）;
insertintoworker
values（1,'
孙华'
'
男'
01/03/52,'
是'
10/10/70,101）,
（2,'
陈明'
05/08/45,'
否'
01/01/65,102）,
（3,'
程西'
女'
06/10/80,'
07/10/02,101）,
（4,'
孙天奇'
03/10/65,'
07/10/87,102）,
（5,'
刘夫文'
01/11/42,'
08/10/60,102）,
（6,'
刘欣'
10/08/52,'
01/07/70,101）,
（7,'
余慧'
12/04/80,'
07/10/02,103）,
（8,'
张旗'
11/10/80,'
07/10/02,102）
（2）用create语句建立部门表depart，其结构为：
部门号，int；
部门名，char（10）。
其中，“部门号”为主键。
并用insert语句在depart表中输入如下记录：
部门名
财务处
人事处
市场部
createtabledepart
（部门号intprimarykey,
部门名char（10）
insertintodepart
values（101,'
财务处'
）,
（102,'
人事处'
（103,'
市场部'
（3）用create建立职工工资表salary，其结构为：
日期，datetime；
工资，decimal（6,1）。
其中，“职工号”和“日期”为主键。
并用insert语句在salary表中输入如下记录：
日期
工资
01/04/04
1201.5
1350.6
750.8
900.0
2006.8
1250.0
725.0
728.0
go
createtablesalary
姓名char（8）,
日期datetime,
工资decimal（6,1）
insertintosalary
01/04/04,1201.5）,
01/04/04,1350.6）,
01/04/04,750.8）,
01/04/04,900.0）,
01/04/04,2006.8）,
01/04/04,1250.0）,
01/04/04,725.0）,
（8,'
01/04/04,728.0）
（4）建立worker、depart和salary3个表之间的关系。
4、在建立的factory数据库上，完成如下各题的程序，要求以文本格式显示结果。
（1）显示所有职工的年龄，并按职工号递增排序。
USEfactory
GO
SELECT职工号,YEAR（参加工作）-YEAR（出生日期）AS年龄FROMworker
ORDERBY职工号ASC
（2）求出各部门的党员人数。
SELECT部门号,COUNT（党员否）AS党员人数FROMworker
GROUPBY部门号
（3）显示所有职工的姓名和2004年1月份的工资。
SELECTworker.姓名,工资FROMworker,salary
WHEREworker.职工号=salary.职工号
（4）显示所有职工的职工号、姓名和平均工资。
SELECTworker.职工号,worker.姓名,AVG（工资）AS平均工资FROMworker,salary
GROUPBYworker.职工号,worker.姓名
（5）显示所有职工的职工号、姓名、部门名和2004年2月份的工资，并按部门名的顺序排列。
SELECTworker.职工号,worker.姓名,部门名,工资FROMworker,depart,salary
WHEREworker.部门号=depart.部门号ANDworker.职工号=salary.职工号
ORDERBY部门名
（6）显示各部门名和该部门的所有职工平均工资。
SELECT部门名,AVG（工资）AS平均工资FROMworker,depart,salary
GROUPBY部门名
（7）显示所有平均工资高于1200的部门名和对应的平均工资。
SELECT部门名,AVG（工资）AS平均工资FROMworker,depart,salary
HAVINGAVG（工资）>
1200
（8）显示所有职工的职工号、姓名和部门类型，其中财务部和人事部属于管理部门，市场部属于市场部门。
SELECT职工号,姓名,部门类型=
CASE
WHEN部门名='
THEN'
管理部门'
市场部门'
END
FROMworker,depart
WHEREworker.部门号=depart.部门号
（9）若存在职工号为10的职工，则显示其工作部门名称，否则显示相应提示信息。
IFEXISTS
（SELECT*FROMworker
WHERE职工号=10）
BEGIN
SELECT部门名FROMworker,depart
WHEREworker.部门号=depart.部门号AND职工号=10
END
ELSE
PRINT'
不存在职工号为‘’的员工。
'
GO
（10）求出男女职工的平均工资，若男职工平均工资高出女职工平均工资50%，则显示“男职工比女职工的工资高多了”的信息；
若男职工平均工资与女职工平均工资比率在1.5～0.8之间，则显示“男职工跟女职工的工资差不多”的信息；
否则显示“女职工比男职工的工资高多了”的信息。
SELECT性别,AVG（工资）AS平均工资FROMworker,salary
GROUPBY性别
DECLARE@mdecimal
DECLARE@fdecimal
SELECT@m=AVG（工资）FROMworker,salary
WHEREworker.职工号=salary.职工号AND性别='
SELECT@f=AVG（工资）FROMworker,salary
IF（（@m-@f）/@f>
=0.5）
PRINT'
男职工比女职工的工资高多了。
ELSEIF（@m/@f>
=0.8AND@m/@f<
=1.5）
男职工跟女职工的工资差不多。
女职工比男职工的工资高多了。
5、在上机实验题4建立的factory数据库上，完成如下各题（所有SELECT语句的查询结果以文本格式显示）。
（1）删除factory数据库上各个表之间建立的关系。
ALTERTABLEsalary
DROPCONSTRAINTFK_salary_worker
ALTERTABLEworker
DROPCONSTRAINTFK_worker_depart
（2）显示各职工的工资记录和相应的工资小计。
select职工号,姓名,工资fromsalary
orderby职工号
computesum（工资）by职工号
（3）按性别和部门名的所有组合方式列出相应的平均工资。
SELECT性别,部门名,AVG（工资）AS平均工资FROMworker,depart,salary
GROUPBY性别,部门名
（4）在worker表中使用以下语句插入一个职工记录：
INSERTINTOworkerVALUES（20,'
陈立'
55/03/08'
1,'
75/10/10'
4）
在depart表中使用以下语句插入一个部门记录：
INSERTINTOdepartVALUES（5,'
设备处'
对worker和depart表进行全外连接显示职工的职工号、姓名和部门名，然后删除这两个插入的记录。
INSERTINTOworkerVALUES（'
20'
INSERTINTOdepartVALUES（'
5'
SELECT职工号,姓名,部门名FROMworker
FULLOUTERJOINdepartONworker.部门号=depart.部门号
DELETEFROMworker
WHERE职工号=20
DELETEFROMdepart
WHERE部门号=5
（5）显示最高工资的职工的职工号、姓名、部门名、工资发放日期和工资。
SELECTworker.职工号,worker.姓名,部门名,salary.日期,工资FROMworker,depart,salary
WHEREworker.部门号=depart.部门号ANDworker.职工号=salary.职工号AND工资=
（SELECTMAX（工资）FROMsalary）
（6）显示最高工资的职工所在的部门名。
SELECT部门名FROMworker,depart,salary
（7）显示所有平均工资低于全部职工平均工资的职工的职工号和姓名。
SELECTworker.职工号,worker.姓名FROMworker,salary
WHEREworker.职工号=salary.职工号AND工资<
（SELECTAVG（工资）FROMsalary）
（8）采用游标方式实现（6）小题的功能。
DECLARE@s_maxCHAR（10）
DECLAREst_cursorCURSOR
FORSELECT部门名FROMworker,depart,salary
OPENst_cursor
FETCHNEXTFROMst_cursorINTO@s_max
PRINT'
部门名'
WHILE@@FETCH_STATUS=0
PRINTCAST（@s_maxASchar（10））
FETCHNEXTFROMst_cursorINTO@s_max
CLOSEst_cursor
DEALLOCATEst_cursor
（9）采用游标方式实现（7）小题的功能。
DECLARE@c_noint,@s_namevarchar（10）
FORSELECTworker.职工号,worker.姓名FROMworker,salary
FETCHNEXTFROMst_cursorINTO@c_no,@s_name
职工号'
+'
'
姓名'
PRINTCAST（@c_noASvarchar（10））+'
'
+@s_name
FETCHNEXTFROMst_cursorINTO@c_no,@s_name
（10）先显示worker表中的职工人数，开始一个事务，插入一个职工记录，再显示worker表中的职工人数，回滚该事务，最后显示worker表中的职工人数。
SELECTCOUNT（*）AS职工人数FROMworker
BEGINTRANSACTION
INSERTINTOworker
VALUES（11,'
小明'
1955/03/08'
1'
1975/10/10'
4'
SAVETRANSACTIONTRAN1
ROLLBACKTRANSACTION
6、在factory数据库上，使用T-SQL语句完成如下各题：
（1）在worker表中的“部门号”列上创建一个非聚集索引；
若该索引已存在，则删除后重建。
CREATENONCLUSTEREDINDEXbmh_indexONworker（部门号）
（2）在salary表的“职工号”和“日期”列创建聚集索引，并且强制唯一性。
CREATEUNIQUECLUSTEREDINDEXzgh_rq_indexONsalary（职工号,日期）
7、在factory数据库上，使用T-SQL语句完成如下各题：
（1）建立视图view1，查询所有职工的职工号、姓名、部门名和2004年2月份工资，并按部门名顺序排列。
CREATEVIEWview1
AS
（2）建立视图view2，查询所有职工的职工号、姓名和平均工资。
CREATEVIEWview2
（3）建立视图view3，查询各部门名和该部门的所有职工平均工资。
CREATEVIEWview3
（4）显示视图view3的定义。
EXECsp_helpview3
8、在factory数据库上，使用T-SQL语句完成如下各题：
（1）修改worker表的结构，添加“E_mailvarchar（20）”字段。
ADDE_mailvarchar（20）
（2）将上例中worker表中“E_mail”字段的数据类型改为varchar（30）
ALTERCOLUMNE_mailvarchar（30）
（3）删除worker表中的“E_mail”字段
DROPCOLUMNE_mail
（4）将worker表中姓名为“陈明”的部门号改为“101”。
UPDATEworker
SET部门号='
101'
WHERE姓名='
（5）删除工资表salary中姓名为“陈明”的记录。
（6）实施worker表的“性别”列默认值为“男”的约束。
ADD
升级会员 