实验3 T-SQL基础
【实验目的】
1、掌握在SQL SERVER 2005下进行数据还原的方法。 2、掌握SQL语句的查询功能和数据更改功能。 【实验内容】
1. 还原factory数据库。
2. 在实验2建立的factory数据库上，完成如下各题。 （1）显示所有职工的年龄，并按职工号递增排序。 （2）求出各部门的党员人数。
（3）显示所有职工的姓名和2004年1月份的工资。 （4）显示所有职工的职工号、姓名和平均工资。
（5）显示所有职工的职工号、姓名、部门名和2004年2月份的工资，并按部门名的顺序排列。
（6）显示各部门名和该部门的所有职工平均工资。
（7）显示所有平均工资高于1200的部门名和对应的平均工资。
（8）显示所有职工的职工号、姓名和部门类型，其中财务部和人事部属于管理部门，市场部属于市场部门。
（9）*若存在职工号为10的职工，则显示其工作部门名称，否则显示相应提示信息。
**求出男女职工的平均工资，（10）若男职工平均工资高出女职工平均工资50%，
则显示“男职工比女职工的工资高多了”的信息；若男职工平均工资与女职工平均工资比率在1.5～0.8之间，则显示“男职工跟女职工的工资差不多”的信息；否则显示“女职工比男职工的工资高多了”的信息。 【实验报告要求】
写出实现各题要求的命令/程序，并列出执行结果。 例如：
（1） 显示所有职工的年龄，并按职工号递增排序。
对应的程序如下：
USE factory GO
SELECT 姓名,YEAR(GETDATE())-YEAR(出生日期) AS '年龄' FROM worker ORDER BY 职工号 GO
执行结果如下：
姓名 年龄
---------- ----------- 孙华 56 陈涛 50 刘欣 56 李涵 43 王小燕 44 李艺 45 魏君 38 孙天奇 43 陈明 63 李华 52 余慧 28 欧阳少兵 37 程西 28 张旗 28 刘夫文 66
操作步骤：
1. 还原数据库如下图
2.（1）显示所有职工的年龄，并按职工号递增排序。
USE factory GO
SELECT 姓名,YEAR(GETDATE())-YEAR(出生日期) AS '年龄'
FROM worker ORDER BY 职工号 GO
2.
（2）求出各部门的党员人数。 SELECT depart.部门名， COUNT(*) AS ‘党员人数’ FROM worker,depart
WHERE worker.党员否=1 AND worker.部门号=depart.部门号 GROUP BY depart.部门号
（3） 显示所有职工的姓名和2004年1月份的工资。 SELECT worker.姓名,salary.工资 FROM worker,salary
WHERE worker.职工号=salary.职工号 AND
YEAR(salary.
日
期
)=2004
AND
MONTH(salary.
日
期
)=1
（4）显示所有职工的职工号、姓名和平均工资.
SELECT worker.职工号,worker.姓名,AVG(salary.工资) AS’平均工资’ FROM worker,salary
WHERE worker.职工号=salary.职工号 GROUP BY worker.职工号,worker.姓名
（5）显示所有职工的职工号、姓名、部门名和2004年2月份的工资，并按部门名的顺序排列。
SELECT worker.职工号,worker.姓名,depart.部门名, salary. 工资 AS '2004年月工资' FROM worker,depart,salary
WHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 AND YEAR(salary.日期)=2004
AND MONTH(salary.日期)=2 ORDER BY worker.部门号
（6）显示各部门名和该部门的所有职工平均工资。 SELECT depart.部门名,AVG(salary.工资) AS '平均工资' FROM worker,depart,salary
WHERE worker.部门号=depart.部门号AND worker.职工号=salary.职工号 GROUP
BY
depart.
部
门
名
（7）显示所有平均工资高于1200的部门名和对应的平均工资。 SELECT depart.部门名,AVG(salary.工资) AS '平均工资'
FROM worker,depart,salary
WHERE worker.部门号=depart.部门号AND worker.职工号=salary.职工号 GROUP BY depart.部门名 HAVING AVG(salary.工资)>1200
（8）显示所有职工的职工号、姓名和部门类型，其中财务部和人事部属于管理部门，市场部属于市场部门。
SELECT worker.职工号,worker.姓名, CASE depart.部门名
WHEN '财务部'THEN '管理部门' WHEN '人事部'THEN '管理部门' WHEN '市场部'THEN '市场部门' END AS '部门类型' FROM worker,depart
WHERE worker.部门号=depart.部门号
（9）*若存在职工号为10的职工，则显示其工作部门名称，否则显示相应提示信息。
DECLARE @no int,@dep char(10) SET @no=10
IF EXISTS(SELECT * FROM worker WHERE 职工号=@no) BEGIN
SELECT @dep=depart.部门名 FROM worker,depart
WHERE worker.职工号=@no AND worker.部门号=depart.部门号
PRINT '职工号为'+CAST(@no AS CHAR(2))+'的职工在'+ CAST(@dep AS char(6))+'工作'
END ELSE PRINT
'
不
存
在
该
职
工
号
的
记
录
'
**求出男女职工的平均工资，（10）若男职工平均工资高出女职工平均工资50%，
则显示“男职工比女职工的工资高多了”的信息；若男职工平均工资与女职工平均工资比率在1.5～0.8之间，则显示“男职工跟女职工的工资差不多”的信息；否则显示“女职工比男职工的工资高多了”的信息。DECLARE @avg1 float,@avg2 float,@ratio float
--计算男职工平均工资
SELECT @avg1=AVG(salary.工资) FROM worker,salary
WHERE worker.职工号=salary.职工号 AND worker.性别='男' --计算女职工平均工资
SELECT @avg2=AVG(salary.工资) FROM worker,salary
WHERE worker.职工号=salary.职工号 AND worker.性别='女' SET @ratio=@avg1/@avg2 IF @ratio>1.5
PRINT '男职工比女职工的工资高多了' ELSE
IF @ratio>=0.8
PRINT '男职工跟女职工的工资差不多' ELSE
PRINT '女职工比男职工的工资高多了'
因篇幅问题不能全部显示，请点此查看更多更全内容