仓库信息查询练习

use cangku 
create table cangkubiao
(
cno varchar(50) primary key not null,
city varchar(50)not null,
mianji int not null
)
insert into cangkubiao values('wh1','北京',370)
insert into cangkubiao values('wh2','上海',500)
insert into cangkubiao values('wh3','广州',200)
insert into cangkubiao values('wh4','武汉',400)
select *from cangkubiao 
drop table cangkubiao
go
create table zhigongbiao
(
cno varchar(50)references cangkubiao(cno) not null,
zno varchar(50) primary key not null,
gongzi decimal(6,2) not null
)
insert into zhigongbiao values ('wh2','e1',1220)
insert into zhigongbiao values ('wh1','e3',1210)
insert into zhigongbiao values ('wh2','e4',1250)
insert into zhigongbiao values ('wh3','e6',1230)
insert into zhigongbiao values ('wh1','e7',1250)

select*from zhigongbiao
drop table zhigongbiao
go
create table dingdanbiao
(
zno varchar(50)references zhigongbiao(zno) not null,
gys varchar(50) references  gongyingshangbiao (gys),
dingdanhao varchar(50)not null,
dingdandate date ,
dingdanzongjia decimal(8,2)
)
insert into dingdanbiao values('e3','s7','or67','2001-6-23',8870)
insert into dingdanbiao values('e1','s4','or67','2001-7-28',8860)
insert into dingdanbiao values('e7','s4','or67','2001-5-25',5600)
insert into dingdanbiao values('e6',null,'or67',null,4800)
insert into dingdanbiao values('e3','s4','or67','2001-6-13',6400)
insert into dingdanbiao values('e1',null,'or67',null,3890)
insert into dingdanbiao values('e3',null,'or67',null,8730)
insert into dingdanbiao values('e3','s7','or67','2001-7-23',7900)
select*from dingdanbiao
drop table dingdanbiao
go
create table gongyingshangbiao
(
gys varchar(50)primary key not null,
gysname varchar(50)not null,
dizhi varchar(50)not null
)
insert into gongyingshangbiao values('s3','振华电子厂','西安')
insert into gongyingshangbiao values('s4','华通电子','北京')
insert into gongyingshangbiao values('s6','607厂','郑州')
insert into gongyingshangbiao values('s7','爱华电子厂','北京')
select*from gongyingshangbiao 
drop table gongyingshangbiao
1从职工关系中检索所有工资值
select gongzi from  zhigongbiao 

2检索仓库关系中的所有记录
select*from cangkubiao
3检索工资多于1230元的职工号
select*from zhigongbiao 
select*from cangkubiao
select zno from  zhigongbiao where gongzi>1230
4.检索哪些仓库有工资多于1210元的职工
select cno from zhigongbiao where gongzi>1210
5给出在仓库“wh1”或“wh2”工作，并且工资少于1250元的职工号
select zno from  zhigongbiao where gongzi <1250 and cno in('wh1','wh2')
6找出工资多于1230元的职工号和他们所在的城市
select zno,city from cangkubiao join zhigongbiao on cangkubiao.cno=zhigongbiao .cno where gongzi>1230
7找出工作在面积大于400的仓库的职工号以及这些职工工作所在的城市
select zno ,city from cangkubiao join zhigongbiao on cangkubiao.cno=zhigongbiao .cno where mianji>400
8 .哪些城市至少有一个仓库的职工工资为1250元
select city from cangkubiao where cno in(select cno from zhigongbiao where gongzi=1250 )
9.查询所有职工的工资都多于1210元的仓库的信息
select*from cangkubiao where cno in(select cno from zhigongbiao where gongzi>1210)
select*from cangkubiao 
select*from zhigongbiao 
10.找出和职工e4挣同样工资的所有职工
select zno from zhigongbiao  where gongzi in(select gongzi from zhigongbiao where zno='e4')
11.检索出工资在1220元到1240元范围内的职工信息。
select *from zhigongbiao where gongzi between 1220 and 1240
12.从供应商关系中检索出全部公司的信息，不要工厂或其他供应商的信息
select *from gongyingshangbiao 
select*from  dingdanbiao 
select*from gongyingshangbiao where gysname not like '%厂'
13.找出不在北京的全部供应商信息
select*from gongyingshangbiao  where dizhi not like '北京'
14.按职工的工资值升序检索出全部职工信息
select*from zhigongbiao order by gongzi asc
15.先按仓库号排序，再按工资排序并输出全部职工信息
select*from zhigongbiao order by cno asc,gongzi desc
16.找出供应商所在地的数目
select COUNT(dizhi) from gongyingshangbiao  
17.求支付的工资总数
select SUM(gongzi) 工资 from zhigongbiao 
8.求北京和上海的仓库职工的工资总和
select SUM(gongzi) 工资  from zhigongbiao where cno in (select cno from cangkubiao where city in('北京','上海'))
19.求所有职工的工资都多于1210元的仓库的平均面积
select AVG (mianji) from cangkubiao where cno in (select cno from zhigongbiao where gongzi >1210)
20.求在wh2仓库工作的职工的最高工资值
select MAX(gongzi) from zhigongbiao where cno in('wh2')
21.求每个仓库的职工的平均工资   --重点题目
select*from zhigongbiao 
select cno, AVG(gongzi ) 平均工资 from zhigongbiao  group by cno 
22.求至少有两个职工的每个仓库的平均工资
select cno,AVG (gongzi) 平均工资 from zhigongbiao group by cno having COUNT(*)>=1

25.查询供应商名
select*from gongyingshangbiao 
select*from dingdanbiao 
select gysname from gongyingshangbiao 
26.在订购单表中加入一个新字段总金额，说明完成该订购单所应付出的总金额数。
27.列出每个职工经手的具有最高总金额的订购单信息   --重点题目，相关子查询
select* from dingdanbiao 

select*from dingdanbiao a where dingdanzongjia  not in (select MAX(dingdanzongjia) from dingdanbiao b where a.zno=b.zno) 


28.检索哪些仓库中还没有职工的仓库的信息
select *from cangkubiao where cno not in(select cno from zhigongbiao )
select*from zhigongbiao 
select*from cangkubiao 
29.检索哪些仓库中至少已经有一个职工的仓库的信息
select*from cangkubiao where cno in (select cno from zhigongbiao group by cno having COUNT(*)>=1)
30.检索有职工的工资大于或等于wh1仓库中任何一名职工工资的仓库号
select cno from zhigongbiao where gongzi >= (select Min(gongzi) from zhigongbiao where cno in ('wh1'))

31.检索有职工的工资大于或等于wh1仓库中所有职工工资的仓库号。
select cno from zhigongbiao where gongzi >=(select Max(gongzi) from zhigongbiao where cno in ('wh1'))