中国石油大学华东 数据库实验四 龚安

实验四 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 '采油一矿%';

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库中国石油大学华东 数据库实验四 龚安在线全文阅读。