数据库实验2

一、实验内容

1．完成以下操作

⑴ 在预算日期、结算日期和入账日期上分别建立索引，并在查询操作中体会索引的作用

create index 预算日期索引 on 作业项目表 (预算日期);
create index 结算日期索引 on 作业项目表 (结算日期);
create index 入账日期索引 on 作业项目表 (入账日期);

⑵ 在完成第2题的查询操作后，删除预算日期、结算日期和入账日期上的索引

drop index 预算日期索引 on 作业项目表;
drop index 结算日期索引 on 作业项目表;
drop index 入账日期索引 on 作业项目表;

2．完成以下操作

⑴ 采油一矿二队2020-5-1到2020-5-28有哪些项目完成了预算，列出相应明细

select *
from 作业项目表
where 预算日期 between '2020/5/1' and ' 2020/5/28'
  and 预算单位 = (select 单位代码
              from 单位表
              where 单位名称 = '采油一矿二队');

select *
from 作业项目表,
     单位表
where 单位代码 = 预算单位
  and 单位名称 = '采油一矿二队'
  and 预算日期 between '2020/5/1' and '2020/5/28';

⑵ 采油一矿二队2020-5-1到2020-5-28有哪些项目完成了结算，列出相应明细

select *
from 作业项目表
where 结算日期 between '2020/5/1' and '2020/5/28'
  and 预算单位 = (select 单位代码
              from 单位表
              where 单位名称 = '采油一矿二队');

select *
from 作业项目表,
     单位表
where 单位名称 = '采油一矿二队'
  and 预算单位 = 单位代码
  and 结算日期 >= '2020-5-1'
  and 结算日期 <= '2020-5-28';

⑶ 采油一矿二队2020-5-1到2020-5-28有哪些项目完成了结算，列出相应的材料费消耗明细

select *
from 材料费表
where 单据号 = any (select 单据号
                 from 作业项目表
                 where 预算日期 between '2020/5/1' and '2020/5/28'
                   and 预算单位 =
                       (select 单位代码
                        from 单位表
                        where 单位名称 = '采油一矿二队'));

⑷ 采油一矿二队2020-5-1到2020-5-28有哪些项目完成了入账，列出相应明细

select *
from 作业项目表
where 入账日期 between ' 2020/5/1' and '2020/5/28'
  and 预算单位 =
      (select 单位代码
       from 单位表
       where 单位名称 = '采油一矿二队')
  and 入账金额 is not NULL

⑸ 列出采油一矿二队2020-5-1到2020-5-28总的预算金额

select sum(预算金额) 预算金额总和
from 作业项目表
where 预算日期 between '2020/5/1' and '2020/5/28'
  and 预算单位 =
      (select 单位代码
       from 单位表
       where 单位名称 = '采油一矿二队');

⑹ 列出采油一矿二队2020-5-1到2020-5-28总的结算金额

select sum(结算金额) 结算金额总和
from 作业项目表
where 结算日期 between '2020/5/1' and '2020/5/28'
  and 预算单位 =
      (select 单位代码
       from 单位表
       where 单位名称 = '采油一矿二队');

⑺ 列出采油一矿二队2020-5-1到2020-5-28总的入账金额

select sum(入账金额) 入账金额总和
from 作业项目表
where 入账日期 between ' 2020/5/1' and '2020/5/28'
  and 预算单位 =
      (select 单位代码
       from 单位表
       where 单位名称 = '采油一矿二队');

⑻ 列出采油一矿2020-5-1到2020-5-28总的入账金额

select Sum(入账金额) 采油一矿入账金额总和
from 作业项目表,
     单位表
where 入账日期 between ' 2020/5/1' and '2020/5/28'
  and 预算单位 = 单位代码
  and 单位名称 like '采油一矿%';

select sum(入账金额) 入账金额总和
from 作业项目表
where 入账日期 between ' 2020/5/1' and '2020/5/28'
  and 预算单位 in
      (select 单位代码
       from 单位表
       where 单位名称 like '采油一矿%');

⑼ 有哪些人员参与了入账操作

select distinct 入账人
from 作业项目表
where 入账人 is not null;

⑽ 列出2020-5-1到2020-5-28进行了结算但未入账的项目

select 单据号
from 作业项目表
where 结算日期 between ' 2020/5/1' and '2020/5/28'
  and 结算金额 is not null
  and 入账金额 is null;

⑾ 列出采油一矿二队的所有项目，按入账金额从高到低排列

select 单据号
from 作业项目表
where 预算单位 =
      (select 单位代码
       from 单位表
       where 单位名称 = '采油一矿二队')
order by 入账金额 desc;

⑿ 列出有哪些施工单位实施了项目，并计算各单位所有项目结算金额总和

这个不对

select 单位表.单位名称, count(结算金额) 项目个数, sum(结算金额) 结算金额总和
from 单位表
         left join 作业项目表 on (单位表.单位名称 = 作业项目表.施工单位)
group by 单位表.单位名称;

⒀ 找出消耗了材料三且消耗超过了2000元的项目，列出相应消耗明细(利用子查询)

select *
from 材料费表
where 单据号 = (select 单据号
             from 材料费表
             where 物码 = (select 物码
                         from 材料表
                         where 名称 = '材料三')
               and 消耗数量 * 单价 > 2000);

⒁ 作业公司二队参与了哪些项目

select distinct 单据号
from 作业项目表
where 施工单位 = '作业公司作业二队';

⒂ 作业公司一队和二队参与了哪些项目（利用union）

select distinct 单据号
from 作业项目表
where 施工单位 = '作业公司作业一队'
union
select distinct 单据号
from 作业项目表
where 施工单位 = '作业公司作业二队';

⒃ 采油一矿的油井是哪些作业队参与施工的

select distinct 施工单位
from 作业项目表,
     油水井表,
     单位表
where 预算单位 = 单位表.单位代码
  and 单位表.单位代码 = 油水井表.单位代码
  and 单位名称 like '采油一矿%';

select distinct 施工单位
from 作业项目表
where 井号 in (select 井号
             from 油水井表
             where 单位代码 in (select 单位代码
                            from 单位表
                            where 单位名称 like '采油一矿%'));

3．完成以下操作

⑴ 建立汇总表(包含3个属性列：★施工单位、★年月、◆结算金额)保存各个施工单位每月的结算金额总和

create table 汇总表
(
    施工单位 char(20),
    年月   char(20),
    结算金额 smallint
);

⑵ 用子查询将各个施工单位每月的结算金额总和插入到所建立的数据表中。

insert into 汇总表
select 施工单位,
       convert(结算日期, char),
       sum(结算金额)
from 作业项目表
group by 施工单位, convert(结算日期, char);

⑶ 用带子查询的修改语句将采油一矿油井作业项目的结算人改为“李兵”

update 作业项目表
set 结算人= '李兵'
where 预算单位 in (select 单位代码
               from 单位表
               where 单位名称 like '采油一矿%'
);

⑷ 用带子查询的删除语句删除采油一矿油井作业项目

delete
from 作业项目表
where 预算单位 in (select 单位代码
               from 单位表
               where 单位名称 like '采油一矿%'
);

⑸ 撤消上述两个操作