中国石油大学华东 数据库实验四 龚安由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“中国石油大学华东”。
实验四 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-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 '采油一矿%';
3．利用Query Analyzer完成以下操作： ⑴
CREATE TABLE shujubiao(shigongdanwei varchar(50), nianyue varchar(50), jiesuanjine money)
GO ⑵ 用子查询将各个施工单位每月的结算金额总和插入到所建立的数据表中。
insert into shujubiao(shigongdanwei,nianyue,jiesuanjine)(select shigongdanwei,year(jiesuanriqi)*100+month(jiesuanriqi),sum(jiesuanjine)from xiangmubiao group by shigongdanwei,year(jiesuanriqi)*100+month(jiesuanriqi));
⑶ 用带子查询的修改语句将采油一矿油井作业项目的结算人改为“李兵”。
update xiangmubiao set jiesuanren = '李兵' where yusuandanwei in
(select danweidaima from
danweidaimabiao where danweidaima=yusuandanwei and danweimingcheng like '采油一矿%');
⑷ 用带子查询的删除语句删除采油一矿油井作业项目。
delete
from xiangmubiao where yusuandanwei in
(select danweidaima from danweidaimabiao where danweidaima=yusuandanwei and danweimingcheng like '采油一矿%')
⑸ 撤消上述两个操作。
begin transaction
update xiangmubiao set jiesuanren = '李兵' where yusuandanwei in
(select danweidaima from
danweidaimabiao where danweidaima=yusuandanwei and danweimingcheng like '采油一矿%');select yusuandanwei,jiesuanren from xiangmubiao;delete
from xiangmubiao where yusuandanwei in
(select danweidaima from danweidaimabiao where danweidaima=yusuandanwei and danweimingcheng like '采油一矿%')select zuoyexiangmuhao,yusuandanwei from xiangmubiao;rollback select yusuandanwei,jiesuanren from xiangmubiao;select zuoyexiangmuhao,yusuandanwei from xiangmubiao;
