倉庫信息查詢練習

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(*)>=125.查詢供應商名
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'))

?