4db.txt

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

use 企业销售管理系统
select * from 职工 where 仓库号
in(select 仓库号 from 仓库 where 城市 is not null)
and 工资 between 1400 and 2100
and 职工号 in(select 职工号 from 订购单 where 金额 not between 8000 and 12000)


use 企业销售管理系统
select * from 职工 where 工资 between 
(select 工资 from 职工 where 职工号='zg1') and (select 工资 from 职工 where 职工号='zg15')
and 仓库号 in(select 仓库号 from 仓库 where 城市='北京')


use 企业销售管理系统
select * from 职工 where 工资<=(select 工资 from 职工 where 职工号='zg16')
and 仓库号 in('wh1','wh2')
and 姓名 not like '%平%'
order by 工资 desc


use 企业销售管理系统
select * from 职工 order by 工资 desc,职工ID asc

select max(工资) as 最大工资,min(工资) as 最小工资,sum(工资) as 工资总和 ,avg(工资) as 平均工资,count(*) as 职工人数 from 职工
where 工资>1500

select * from 职工 where 工资>(select avg(工资) from 职工)

select * from 职工 where 工资>(select avg(工资) from 职工 where 仓库号 in('wh1','wh2'))
and 姓名 not like '%亮%'
order by 工资

select 职工.*,(select avg(工资) from 职工)  as 平均工资 from 职工

select 职工.*,(select avg(工资) from 职工) as 平均工资,
(工资-(select avg(工资) from 职工)) as 与平均工资的差
from 职工
order by 工资

select 仓库号,max(工资) as 最大工资,min(工资) as 最小工资,sum(工资) as 工资总和,
avg(工资) as 平均工资,count(*) as 职工人数
from 职工
group by 仓库号

select 仓库号,max(工资) as 最大工资,min(工资) as 最小工资,sum(工资) as 工资总和,
avg(工资) as 平均工资,count(*) as 职工人数
from 职工
where 工资>(select 工资 from 职工 where 职工号='zg5')
group by 仓库号

select 仓库号,max(工资) as 最大工资,min(工资) as 最小工资,
sum(工资) as 工资总和,avg(工资) as 平均工资,count(*) as 职工人数
from 职工
group by 仓库号
having avg(工资)>1760
and max(工资)-min(工资) between 380 and 800

select * from 仓库
where 仓库号 in
(select 仓库号 from 职工 group by 仓库号
having max(工资)-min(工资) between 380 and 800
)

select a.* from 订购单 a where 金额=(
select max(金额) from 订购单 where 职工号=a.职工号
)

select a.*,
(select avg(工资) from 职工 where 仓库号=a.仓库号) as 所在仓库的平均工资
from 职工 a

select a.*,
(select avg(工资) from 职工 where 仓库号=a.仓库号) as 所在仓库的平均工资,
工资-(select avg(工资) from 职工 where 仓库号 =a.仓库号) as 与所在仓库平均工资之差
from 职工 a

select * from 仓库 where not exists (select *from 职工 where 仓库号=仓库.仓库号)