7db.txt

the original code of the book the principle and practice in sql server 2005

create view ckview1 as
select 仓库号,城市,面积
from 仓库 
where 面积>800

--alter view ckview1 as
--select 仓库号,城市,面积,创建时间
--from 仓库
--where 面积>600
--select *
--from ckview1
drop view ckview1


--create view zgview1 as
--select 仓库号,职工号,姓名,工资,(select avg(工资) from 职工) as 所有职工的平均工资
--from 职工
--select *
--from zgview1

create view zgview2 as
select 仓库号,姓名,职工号,工资,所有职工的平均工资,
(select avg(工资) from zgview1 where 仓库号=a.仓库号) as 不同仓库的平均工资,
工资-(select avg(工资) from zgview1 where 仓库号=a.仓库号) as 职工工资与所在仓库的平均工资之差
from zgview1 a

select 仓库号,姓名,职工号,工资
from 职工 a
where 工资-(select avg(工资) from 职工 where 仓库号=a.仓库号)>0
and (select avg(工资) from 职工 where 仓库号=a.仓库号)>1600


--select 仓库号,姓名,职工号,工资
--from 职工 a
--where 工资-(select avg(工资) from 职工 where 仓库号=a.仓库号)>0
--and (select avg(工资) from 职工 where 仓库号=a.仓库号)>1600


select 仓库号,姓名,职工号,工资
from zgview2
where 不同仓库的平均工资>1600 and 职工工资与所在仓库的平均工资之差>0

--create view view3 as
--select a.仓库号,面积,b.职工号,姓名,工资,订购日期,金额
--from 仓库 a,职工 b,订购单 c
--where a.仓库号=b.仓库号 and b.职工号=c.职工号
--select *
--from view3

--select distinct 职工号,姓名,面积,工资
--from view3
--where 姓名 not like '%刘%' and 金额>10000

--select distinct 仓库号,avg(金额) as 平均金额,count(*) as 有订购单的职工人数
--from view3
--group by 仓库号

--create view view4 as
--select top 100 * 
--from 职工
--order by 工资 desc

select *
from view4