SQL实训
join depart
on worker.部门号=depart.部门号 where 工资='2004-02'
-------------------------------------------------------------------------------------------------------
(5).查询所有市场部和人事处职工的职工号、职工姓名。脚本文件名：0405.sql。
------------------------------------脚本文件0405.sql-------------- ------------------------------
select 职工号,姓名 from worker join depart
on worker.部门号=depart.部门号
where worker.部门号='2' or worker.部门号='3'
-------------------------------------------------------------------------------------------------------
(6).查询所有职工的总工资、职工号、职工姓名。脚本文件名：0406.sql。 ------------------------------------脚本文件0406.sql-------------- ------------------------------
select worker.职工号,worker.姓名,sum(工资) 总工资 from worker join salary
on worker.职工号=salary.职工号 group by worker.职工号,worker.姓名 order by sum(工资)
-------------------------------------------------------------------------------------------------------
(7).查询所有职工的年龄，显示字段包括职工号、职工姓名、出生年月、年龄。脚本文件名：0407.sql。
------------------------------------脚本文件0407.sql-------------- ------------------------------
select 职工号,姓名,出生日期,year(getdate())-year(出生日期) 年龄 from worker
-------------------------------------------------------------------------------------------------------
(8).查询各部门的党员人数，显示字段包括部门号、部门名称、党员人数。脚本文件名：0408.sql。
------------------------------------脚本文件0408.sql-------------- ------------------------------
Select worker.部门号,depart.部门名,count(党员否)党员人数 from worker join depart
on worker.部门号=depart.部门号 group by worker.部门号,depart.部门名 order by count(党员否)
-------------------------------------------------------------------------------------------------------
(9).查询所有职工的平均工资，显示字段包括职工号、职工姓名、平均工资。脚本文件名：0409.sql。
------------------------------------脚本文件0409.sql-------------- ------------------------------
Select worker.职工号,worker.姓名,avg(工资)平均工资 from worker join salary
- 5 -
SQL实训
on worker.职工号=salary.职工号 group by worker.职工号,worker.姓名 order by avg(工资)
-------------------------------------------------------------------------------------------------------
(10).查询所有平均工资高于1200的职工信息（职工号、职工姓名、性别）和对应的平均工资。脚本文件名：0410.sql。
------------------------------------脚本文件0410.sql-------------- ------------------------------
Select worker.职工号,worker.姓名,worker.性别,avg(工资)平均工资 from worker join salary
on worker.职工号=salary.职工号
group by worker.职工号,worker.姓名,worker.性别 having avg(工资)>'1200' order by avg(工资) desc
-------------------------------------------------------------------------------------------------------
(11).显示最高工资的职工的职工号、姓名、部门名、工资发放日期和工资。脚本文件名：0411.sql。
------------------------------------脚本文件0411.sql-------------- ------------------------------
Select worker.职工号,worker.姓名,depart.部门名,salary.日期,工资 from salary join worker
on worker.职工号=salary.职工号 join depart
on depart.部门号=worker.部门号
where salary.职工号=(select top 1 职工号 from salary group by 职工号
order by avg(工资) desc)
-------------------------------------------------------------------------------------------------------
(12)显示最高工资的职工所在的部门名。脚本文件名：0412.sql。
------------------------------------脚本文件0412.sql-------------- ------------------------------
Select depart.部门名 from salary join worker
on worker.职工号=salary.职工号 join depart
on depart.部门号=worker.部门号
where salary.职工号=(select top 1 职工号 from salary group by 职工号
order by avg(工资) desc)
-------------------------------------------------------------------------------------------------------
(13)显示所有平均工资低于全部职工平均工资的职工的职工号和姓名。脚本文件名：0413.sql。
- 6 -
SQL实训
------------------------------------脚本文件0413.sql-------------- ------------------------------
Select worker.职工号,worker.姓名 from worker left join salary
on worker.职工号=salary.职工号 group by worker.职工号,worker.姓名
having avg(工资)<(select avg(工资) from salary
------------------------------------------------------------------------------------------------------- 5.更新数据
(1).在depart表中插入设备处记录，部门编号为4。脚本文件名：0501.sql。 ------------------------------------脚本文件0501.sql-------------- ------------------------------
insert into depart values('4','设备处')
-------------------------------------------------------------------------------------------------------
(2).在depart表中插入资产处记录，部门编号为当前表中的最大编号+1。脚本文件名：0502.sql。
------------------------------------脚本文件0502.sql-------------- ------------------------------
insert into depart values('5','资产处')
-------------------------------------------------------------------------------------------------------
(3).在worker表中插入一条记录，记录内容为职工号20，姓名陈立，性别女，出生日期1955年3月8日，参加工作时间为1975年10月10日，部门号为4。脚本文件名：0503.sql。
------------------------------------脚本文件0503.sql-------------- ------------------------------
insert into worker (职工号,姓名,性别,出生日期,参加工作,部门号) values ('20','陈立','女','1955-3-8','1975-10-10','4')
-------------------------------------------------------------------------------------------------------
(4).在worker表中插入一条记录，记录内容为职工号为最大职工号+1，姓名张三，性别男，出生日期1965年9月9日，参加工作时间为1980年10月1日，部门号为3。脚本文件名：0504.sql。
------------------------------------脚本文件0504.sql-------------- ------------------------------
insert into worker(职工号,姓名,性别,出生日期,参加工作,部门号) values ('21','张三','男','1965-9-9','1980-10-1','3')
-------------------------------------------------------------------------------------------------------
(5).修改职工1月份工资，将每个人的工资上涨10%。脚本文件名：0505.sql。 ------------------------------------脚本文件0505.sql-------------- ------------------------------
update salary set 工资=工资*1.1
where 日期='2004-1-4 0:00:00'
-------------------------------------------------------------------------------------------------------
(6).修改所有女性职工2月份工资，将每人的工资加上100元的医疗补贴。脚本文件名：0506.sql。
------------------------------------脚本文件0506.sql-------------- ------------------------------
update salary set 工资=工资+100 where 性别in (select 性别from worker where 性别='女' and 日期='2004-2-3 0:00:00')
-------------------------------------------------------------------------------------------------------
- 7 -
SQL实训
(7).将所有财务处女性职工的参加工作时间向前推一年。脚本文件名：0507.sql。
------------------------------------脚本文件0507.sql-------------- ------------------------------
update worker set 参加工作=year(参加工作)-1 where 部门号='1' and 性别='女'
-------------------------------------------------------------------------------------------------------
(8).删除工资表中工资最高的记录。脚本文件名：0508.sql。
------------------------------------脚本文件0508.sql-------------- ------------------------------
delete from salary where 职工号='9'and 日期='2004-1-4 0:00:00'
-------------------------------------------------------------------------------------------------------
(9).删除工资表中平均工资最高的职工的工资记录。脚本文件名：0509.sql。 ------------------------------------脚本文件0509.sql-------------- ------------------------------
delete from salary where 职工号='9'
-------------------------------------------------------------------------------------------------------
(10).删除职工为1号的职工的所有信息。脚本文件名：0510.sql。
------------------------------------脚本文件0510.sql-------------- ------------------------------
delete from salary where 职工号='1' delete from worker where 职工号='1'
------------------------------------------------------------------------------------------------------- 6.视图
(1)建立视图职工年龄查询，显示所有职工的年龄。脚本文件名：0601.sql。 ------------------------------------脚本文件0601.sql-------------- ------------------------------
Create view vw_ageworker as
select 姓名,year(getdate())-year(出生日期) 年龄 from worker
-------------------------------------------------------------------------------------------------------
(2)建立视图“党员人数统计”，求出各部门的党员人数。脚本文件名：0602.sql。 ------------------------------------脚本文件0602.sql-------------- ------------------------------
create view vw_dangworker as
select 部门号,count(党员否) 党员人数 from worker where 党员否='是'
-------------------------------------------------------------------------------------------------------
(3)建立视图2004年1月份工资查询，显示所有职工的姓名和2004年1月份工资数。脚本文件名：0603.sql。
------------------------------------脚本文件0603.sql-------------- ------------------------------
create view vw_gongsalary as
select 姓名, 工资 from salary
where 日期='2004-01'
-------------------------------------------------------------------------------------------------------
(4)建立视图职工平均工资查询，显示所有职工的职工号、姓名和平均工资。
- 8 -
SQL实训
脚本文件名：0604.sql。
------------------------------------脚本文件0604.sql-------------- ------------------------------
CREATE VIEW vw_View1 AS
SELECT 职工号, 姓名, avg(工资)平均工资 FROM salary
-------------------------------------------------------------------------------------------------------
(5)建立视图2004年2月份工资查询，显示所有职工的职工号、姓名、部门名和2004年2月份工资，并按部门名顺序排列。脚本文件名：0605.sql。
------------------------------------脚本文件0605.sql-------------- ------------------------------
CREATE VIEW vw_View2 AS
SELECT TOP (100) PERCENT dbo.salary.职工号, dbo.salary.姓名, dbo.salary. 工资, dbo.depart.部门名, dbo.salary.日期 FROM dbo.salary CROSS JOIN dbo.depart
WHERE (dbo.salary.日期= CONVERT(DATETIME, '2004-02 ', 102))
-------------------------------------------------------------------------------------------------------
(6)建立视图部门平均工资查询，显示各部门名和该部门的所有职工平均工资。脚本文件名：0606.sql。
------------------------------------脚本文件0606.sql-------------- ------------------------------
CREATE VIEW vw_View3 AS
SELECT depart.部门名, avg(salary. 工资)平均工资 FROM salary CROSS JOIN depart
------------------------------------------------------------------------------------------------------- 7.索引
(1).在worker表的姓名列上创建名为workername的惟一性索引。脚本文件名：0701.sql。
------------------------------------脚本文件0701.sql-------------- ------------------------------
USE [factory] GO
CREATE UNIQUE NONCLUSTERED INDEX [workername] ON [dbo].[worker] (
[姓名] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
-------------------------------------------------------------------------------------------------------
(2).在worker表的出生日期列上创建名为workerborn的非聚集索引，并按从大到小的次序排列。脚本文件名：0702.sql。
------------------------------------脚本文件0702.sql-------------- ------------------------------
USE [factory] GO
CREATE NONCLUSTERED INDEX [workerborn] ON [dbo].[worker]
- 9 -