SQL

--1、显示所有职工的年龄
select 姓名 ,2009 -year( 出生日期)
from worker

--2、求出各部门的党员人数
select count(党员否)
from worker
where 党员否='1';

--3、显示所有职工的姓名和2004年一月份工资数
select 姓名,工资
from salary
where year(日期)= 2004 and month(日期)=1

--4、显示所有职工的职工号、姓名和平均工资
select 职工号,姓名,AVG(工资)
from salary
group by 姓名,职工号

--5、显示所有职工的职工号，姓名、部门名和2004年2月份工资，并按部门名顺序排列
select worker.职工号,worker.姓名,depart.部门名,salary.工资
from salary,depart,worker
where
worker.职工号=salary.职工号 and
worker.姓名=salary.姓名 and
depart.部门号=worker.部门号 and
year(日期)= 2004 and month(日期)=2
order by 部门名

--6、显示各部门名和该部门的所有职工平均工资
select worker.职工号,worker.姓名,depart.部门名,AVG(工资)
from salary,depart,worker
where
worker.职工号=salary.职工号 and
worker.姓名=salary.姓名 and
depart.部门号=worker.部门号
group by worker.职工号,worker.姓名,depart.部门名

--7、显示所有平均工资高于1200的部门名和对应的平均工资
select depart.部门名,AVG(工资)
from salary,depart,worker
where
worker.职工号=salary.职工号 and
depart.部门号=worker.部门号
group by 部门名
having AVG(工资)>'1200'
--8、显示所有职工的职工号、姓名和部门类型，其中财务处和人事处属管理部门，市场部属于市场部门
select worker.职工号,worker.姓名,case depart.部门名
when '财务处' then '管理部门'
when '人事处'then'管理部门'
when '市场部'then'市场部门'
end as '部门类型'
from worker,depart
where worker.部门号=depart.部门号

--9、若存在职工号为10的职工，则显示其工作部门名称，否则显示相应的提示信息
if ( select count(*) from worker where 职工号=10)>0
select depart.部门名,worker.职工号
from worker,depart
where
worker.职工号=10 and
worker.部门号=depart.部门号
else
print '不存在'

--12.显示各职工的工资记录和相应的工资小计。
select 工资,count(*)as '工资小计'
from worker,salary,depart
where worker.职工号=salary.职工号
and worker.部门号=depart.部门号
group by 工资
--13.按性别和部门名的所有组合方式列出相应的平均工资。
select depart.部门名,worker.性别 ,avg(工资) as 平均工资
from worker,salary,depart
where worker.职工号=salary.职工号
and worker.部门号=depart.部门号
group by 性别,部门

--14、显示最高工资的职工的职工

号、姓名、部门名、工资发放日期和工资
select a.worker-id,https://www.360docs.net/doc/cd1808502.html,,a.depart,c.depart,b.salary
from worker a ,salary b,depart c
where a. worker-id =b. worker-id and a.depart-id= c.depart-id
order by salary desc
--15.显示最高工资的职工所在的部门名。
Use factory
go
Select b.depart
From salary a，depart b ,worker c
Where salary in (select max（a.salary）from a) and
a.worker-id=c.worker=id and a.depart-id= b. depart-id
--16显示所有平均工资低于全部职工平均工资的职工的职工号和姓名。
Use factory
go
select a.worker-id,https://www.360docs.net/doc/cd1808502.html,
from salary a
where a.salary--19、先显示worker表中的职工人数，开始一个事务，插入一个职工记录，再显示worker表中的职工人数，回滚该事务，最后显示worker表中的职工人数。显示worker表中的职工人数
Select count（*）
From worker
begin transaction my- transaction
go
INSERT INTO worker VALUES(20,’陈立’,’女’,’55/03/08’,1,’75/10/10’,4)
Go
commIT transaction
Select count（*）
From worker
rollback transaction
Select count（*）
From worker




--22、建立视图View11、查询所有职工的职工号、姓名、部门名和2004年2月份工资，并按部门名顺序排列

CREATE view View1
AS SELECT worker.职工号,worker.姓名,depart.部门名,salary.工资
from worker,depart,salary
where worker.职工号=depart.职工号
and
worker.wno=salary.职工号
and
year(日期)=2004 and month(日期)=2
order by depart.部门名

--23、建立视图View2，查询所有职工的职工号姓名和平均工资

CREATE view View2
AS SELECT worker.职工号,worker.姓名,avg(salary.工资) as '平均工资'
From salary,worker
WHERE worker.职工号=salary.职工号
group by worker.职工号,worker.姓名

--24、建立视图View3，查询各部门名和该部门的所有职工的平均工资

CREATE view View3
AS SELECT depart.部门名,avg(工资) as '平均工资'
From worker,depart,salary
where worker.部门号=depart.部门号 and worker.职工号=salary.职工号
group by depart.部门名


--25.实施worker表的“性别”字段默认值为“男”的约束。

ALTER TABLE worker

ADD constraint 性别 default '男' for 性别


查看表属性性别默认值变成‘男’

--26.实施salary表的“工资”字段值限定在0~9999的约束。

ALTER TABLE salary

ADD CONSTRAINT check_工资 CHECK (工资 between 0 and 9999)


--27.实施depart表的“部门号”字段值唯一的非聚集索引的约束。

ALTER TABLE depart

ADD CONSTRAINT UNIQUE_部门号 UNIQUE (部门号)

--28.为worker表建立外键“部门号”，参考表depart的“部门号”列

ALTER TABLE worker

ADD CONSTRAINT 外键

FOREIGN KEY(部门号) REFERENCES depart(部门号)

--29.建立一个规则sex1：@性别=‘男’OR@性别=‘女’，将其绑定到worker

表的“性别”列上。

CREATE RULE sex as @性别='男'or @性别='女'

GO

SP_BINDRULE 'sex','worker.性别'

--30.删除（25-28）小题所建立的约束。

ALTER TABLE worker

DROP CONSTRAINT 性别

ALTER TABLE salary

DROP CONSTRAINT check_工资

ALTER TABLE depart

DROP CONSTRAINT UNIQUE_部门号


ALTER TABLE worker

DROP CONSTRAINT 外键

--31.解除（29）小题所建立的绑定并删除规则sex1。

--建立绑定：

EXEC SP_UNBINDRULE 'worker.性别'

--删除规则：

drop rule sex1

--32 创建一个为worker表添加职工记录的存储过程Addworker
-- '王非','女','55/03/08','女','75/10/10',2

INSERT INTO worker VALUES(16,'王非','女','55/03/08',1,'75/10/10',4)
--33 创建一个存储过程Delworker删除worker表中指定职工号=20的记录。
create procedure dakwoeker
as
select 职工记录
from worker
where
Delete from worker
Where worker-id=20 and name='王非'
--34 显示存储过程 Delworker

exec dakworker

--35删除存储过程Addworker和Delworker

drop procedure addwoeker
drop procedure dakwoeker