4.25 数据库 仓库例题

--仓库表：
--仓库号    城市    面积
--wh1    北京    370
--wh2    上海    500
--wh3    广州    200
--wh4    武汉    400
create table cangku
(
cno varchar(50) primary key not null,--将仓库号设为主键
city varchar(50) not null,
mianji int not null
 
)
go
insert into cangku values('wh1','北京',370);
insert into cangku values('wh2','上海',500);
insert into cangku values('wh3','广州',200);
insert into cangku values('wh4','武汉',400);
select *from cangku
--职工表：
--仓库号    职工号    工资
--wh2    e1    1220
--wh1    e3    1210
--wh2    e4    1250
--wh3    e6    1230
--wh1    e7    1250
create table zhigong
(
cno varchar(50) references cangku(cno),--仓库号为外键
zhigonghao varchar(50) primary key not null,--职工号设为主键
gongzi int not null
)
insert into zhigong values('wh2','e1',1220);
insert into zhigong values('wh1','e3',1210);
insert into zhigong values('wh2','e4',1250);
insert into zhigong values('wh3','e6',1230);
insert into zhigong values('wh1','e7',1250);
select *from zhigong

--订购单表：
--职工号    供应商号    订购单号    订购日期
--e3    s7    or67    2001-6-23
--e1    s4    or73    2001-7-28
--e7    s4    or76    2001-5-25
--e6    null    or77      -   -
--e3    s4    or79    2001-6-13
--e1    null    or80      -   -
--e3    null    or90      -   -
--e3    s3    or91    2001-7-13
create table dinggou--订购单表
(zhigonghao varchar(50) references zhigong(zhigonghao),--职工号设为外键
 gongyingshanghao varchar(50) references gongyingshang(gongyingshanghao) ,
 goudanhao varchar(50) not null,
 goudanri varchar(50),
)
insert into dinggou values('e3','s7','or67','2017-6-23')
insert into dinggou values('e1','s4','or73','2017-7-28')
insert into dinggou values('e7','s4','or76','2017-5-25')
insert into dinggou values('e6',null,'or77','--')
insert into dinggou values('e3','s4','or79','2017-6-13')
insert into dinggou values('e1',null,'or80','--')
insert into dinggou values('e3',null,'or90','--')
insert into dinggou values('e3','s3','or91','2017-7-13')
select *from dinggou
drop table dinggou
--供应商表：
--供应商号    供应商名    地址
--s3    振华电子厂    西安
--s4    华通电子公司    北京
--s6    607厂    郑州
--s7    爱华电子厂    北京
create table gongyingshang--供应商表
(
gongyingshanghao varchar(50) primary key,
gongyingshangming varchar(50) not null,
dizhi varchar(50) not null,
)
insert into gongyingshang values('s3','振华电子厂','西安')
insert into gongyingshang values('s4','华通电子公司','北京')
insert into gongyingshang values('s6','607厂','郑州')
insert into gongyingshang values('s7','爱华电子厂','北京')
select *from gongyingshang
--1,从职工关系中检索所有工资值.
select *from zhigong
select gongzi from zhigong
--2,检索仓库关系中的所有记录,
select *from cangku
--3,检索工资多于1230元的职工号.
select *from zhigong
select zhigonghao from zhigong where gongzi>1230
--4.检索哪些仓库有工资多于1210元的职工

select cno from zhigong where gongzi>1230
--5，给出在仓库“wh1”或“wh2”工作，并且工资少于1250元的职工号
select *from zhigong
select zhigonghao from zhigong where cno in ('wh1','wh2')and gongzi<1250
--找到了职工号来自于职工表 从仓库里找寻wh1或者wh2,并且工资高于1250
--6,找出工资多于1230元的职工号和他们所在的城市。
select *from cangku
select *from zhigong
select city,zhigonghao from zhigong
join cangku on cangku.cno=zhigong.cno and gongzi>1230

--7,找出工作在面积大于400的仓库的职工号以及这些职工工作所在的城市。
select *from cangku

select cno,city from cangku where mianji>400
--8,.哪些城市至少有一个仓库的职工工资为1250元。
select *from cangku
select *from zhigong
--select city from  cangku where cno in(select cno from zhigong where gongzi=1250)
select city from  cangku where cno in(select cno from zhigong where gongzi=1250 group by cno having COUNT(*)>=1)
--9.查询所有职工的工资都多于1210元的仓库的信息
select *from zhigong
select *from cangku where cno in(select cno from zhigong group by cno having MIN(gongzi)>1210)
--10.找出和职工e4挣同样工资的所有职工。
select gongzi from zhigong where zhigonghao='e4'
select zhigonghao from zhigong where gongzi in (select gongzi from zhigong where zhigonghao='e4')

--11.检索出工资在1220元到1240元范围内的职工信息

select *from zhigong where gongzi between 1220 and 1240
--12.从供应商关系中检索出全部公司的信息，不要工厂或其他供应商的信息
select *from gongyingshang
select *from cangku
select *from gongyingshang where gongyingshangming like('%公司')
--13.找出不在北京的全部供应商信息。
select *from gongyingshang where dizhi!='北京'
--14.按职工的工资值升序检索出全部职工信息。
select *from zhigong order by gongzi --order by 排序 默认从小到大
--15.先按仓库号排序，再按工资排序并输出全部职工信息。
select *from zhigong order by cno,gongzi 
--16.找出供应商所在地的数目。
select *from gongyingshang
select count(*),dizhi from gongyingshang group by dizhi 
--17.求支付的工资总数
select *from zhigong
select sum(gongzi)as 工资总和 from zhigong 
--18.求北京和上海的仓库职工的工资总和
select *from zhigong
select *from cangku
select cno from cangku where city in('北京','上海')
select sum(gongzi) 工资总和 from zhigong where cno in (select cno from cangku where city in('北京','上海'))

--19.求所有职工的工资都多于1210元的仓库的平均面积
select *from zhigong
select *from cangku

select avg(mianji) as 平均面积 from cangku  where cno in(select cno from zhigong group by cno having MIN(gongzi)>1210)
--20.求在wh2仓库工作的职工的最高工资值
select *from zhigong
select *from zhigong where cno='wh2'
select  MAX(gongzi)as 最高工资值 from zhigong where cno='wh2'

--