实验四 SQL练习2
1.
(1)创建索引
create index yusuanriqisuoyin on xiangmubiao(yusuanriqi); create index jiesuanriqisuoyin on xiangmubiao(jiesuanriqi); create index ruzhangriqisuoyin on xiangmubiao(ruzhangriqi);
(2)撤销索引
drop index yusuanriqisuoyin on xiangmubiao; drop index jiesuanriqisuoyin on xiangmubiao; drop index ruzhangriqisuoyin on xiangmubiao;
2.
⑴ 采油一矿二队2011-5-1到2011-5-28有哪些项目完成了预算，列出相应明细。
select *from danweidaimabiao, xiangmubiao where yusuandanwei=danweidaima and danweimingcheng='采油一矿二队'
and yusuanriqi>='2011-5-1' and yusuanriqi<='2011-5-28';
⑵ 采油一矿二队2011-5-1到2011-5-28有哪些项目完成了结算，列出相应明细。
select *from danweidaimabiao, xiangmubiao where yusuandanwei=danweidaima and danweimingcheng='采油一矿二队'
and jiesuanriqi between '2011-5-1' and '2011-5-28';
⑶ 采油一矿二队2011-5-1到2011-5-28有哪些项目完成了结算，列出相应的材料费消耗明细。
Select cailiaofeibiao.zuoyexiangmuhao,wuma,xiaohaoshuliang,danjia from danweidaimabiao, xiangmubiao,cailiaofeibiao where yusuandanwei=danweidaima and danweimingcheng='采油一矿二队'
and jiesuanriqi between '2011-5-1' and '2011-5-28'
and cailiaofeibiao.zuoyexiangmuhao=xiangmubiao.zuoyexiangmuhao;
⑷ 采油一矿二队2011-5-1到2011-5-28有哪些项目完成了入账，列出相应明细。
select * from danweidaimabiao, xiangmubiao where yusuandanwei=danweidaima and danweimingcheng='采油一矿二队'
and ruzhangriqi between '2011-5-1' and '2011-5-28';
⑸ 列出采油一矿二队2011-5-1到2011-5-28总的预算金额。
select yusuanzonge=sum(yusuanjine) from danweidaimabiao, xiangmubiao
where yusuandanwei=danweidaima and danweimingcheng='采油一矿二队'
and ruzhangriqi between '2011-5-1' and '2011-5-28';
⑹ 列出采油一矿二队2011-5-1到2011-5-28总的结算金额。
select jiesuanzonge=sum(jiesuanjine) from danweidaimabiao, xiangmubiao where yusuandanwei=danweidaima and danweimingcheng='采油一矿二队'
and ruzhangriqi between '2011-5-1' and '2011-5-28';
⑺ 列出采油一矿二队2011-5-1到2011-5-28总的入账金额。
select ruzhangzonge=sum(ruzhangjine) from danweidaimabiao, xiangmubiao where yusuandanwei=danweidaima and danweimingcheng='采油一矿二队'
and ruzhangriqi between '2011-5-1' and '2011-5-28';
⑻ 列出采油一矿2011-5-1到2011-5-28总的入账金额。
select ruzhangzonge=sum(ruzhangjine) from danweidaimabiao, xiangmubiao where yusuandanwei=danweidaima and danweimingcheng like '采油一矿%'
and ruzhangriqi between '2011-5-1' and '2011-5-28';
⑼ 有哪些人员参与了入账操作。
select distinct ruzhangren from xiangmubiao where ruzhangren is not null
⑽ 列出2011-5-1到2011-5-28进行了结算但未入帐的项目。
select zuoyexiangmuhao from xiangmubiao where ruzhangren is null and jiesuanren is not null
⑾ 列出采油一矿二队的所有项目，按入账金额从高到低排列。
select zuoyexiangmuhao,ruzhangjine from xiangmubiao,danweidaimabiao where danweimingcheng='采油一矿二队' and yusuandanwei=danweidaima order by ruzhangjine desc;
⑿ 列出有哪些施工单位实施了项目，并计算各单位所有项目结算金额总和。
Select danweimingcheng,xiangmushuliang=count(jiesuanjine), jiesuanzonge=sum(jiesuanjine)
from xiangmubiao,danweidaimabiao where yusuandanwei=danweidaima group by danweimingcheng;
⒀ 找出消耗了材料三且消耗超过了2000元的项目，列出相应消耗明细(利用子查询)。
Select zuoyexiangmuhao,wuma,xiaohaoshuliang,danjia from cailiaofeibiao
where wuma in (select wuma from wumabiao where mingchengguige='材料三') and danjia*xiaohaoshuliang>=2000;
⒁ 作业公司二队参与了哪些项目。
select zuoyexiangmuhao from xiangmubiao where shigongdanwei='作业公司作业二队';
⒂ 作业公司一队和二队参与了哪些项目（利用union）。
select zuoyexiangmuhao,shigongdanwei from xiangmubiao where shigongdanwei='作业公司作业二队'
union select zuoyexiangmuhao,shigongdanwei from xiangmubiao where shigongdanwei='作业公司作业一队';
⒃ 采油一矿的油井是哪些作业队参与施工的。
select shigongdanwei from xiangmubiao,youshuijingbiao,danweidaimabiao where yusuandanwei=danweidaimabiao.danweidaima
and danweidaimabiao.danweidaima=youshuijingbiao.danweidaima and danweimingcheng like '采油一矿%';