实验四 SQL练习2

一、实验目的

1．掌握索引的建立、删除及使用；
2．掌握单表查询、连接查询、嵌套查询和集合查询；
3．掌握插入数据、修改数据和删除数据语句的非常用形式。

二、实验学时

2学时

三、实验内容

1．完成以下操作：
⑴ 在预算日期、结算日期和入账日期上分别建立索引，并在查询操作中体会索引的作用。
⑵ 在完成第2题的查询操作后，删除预算日期、结算日期和入账日期上的索引。

create clustered index 预算索引 on 成本表(预算日期);
select * from 成本表;

drop index 预算索引 on 成本表
select * from 成本表;

create clustered index 结算索引 on 成本表(结算日期);
select * from 成本表;

drop index 结算索引 on 成本表
select * from 成本表;

create clustered index 入账索引 on 成本表(入账日期);
select * from 成本表;

drop index 入账索引 on 成本表
select * from 成本表;

2．完成以下操作：
⑴ 采油一矿二队2021-5-1到2021-5-28有哪些项目完成了预算，列出相应明细。

select *
from 成本表
where 预算日期 between '2021-5-1' and '2021-5-28'
	and 预算单位 = (select 单位代码
					from 单位表
					where 单位名称 = '采油一矿二队'); 

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

select *
from 成本表
where 结算日期 between '2021-5-1' and '2021-5-28'
	and 预算单位 = (select 单位代码
					from 单位表
					where 单位名称 = '采油一矿二队'); 

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

select 单据号, 材料费, 材料一, 材料二, 材料三, 材料四
from 成本表
where 结算日期 between '2021-5-1' and '2021-5-28'
	and 预算单位 = (select 单位代码
					from 单位表
					where 单位名称 = '采油一矿二队'); 

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

select *
from 成本表
where 入账日期 between '2021-5-1' and '2021-5-28'
	and 预算单位 = (select 单位代码
					from 单位表
					where 单位名称 = '采油一矿二队'); 

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

select sum(预算金额)
from 成本表
where 入账日期 between '2021-5-1' and '2021-5-28'
	and 预算单位 = (select 单位代码
					from 单位表
					where 单位名称 = '采油一矿二队'); 

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

select sum(结算金额)
from 成本表
where 入账日期 between '2021-5-1' and '2021-5-28'
	and 预算单位 = (select 单位代码
					from 单位表
					where 单位名称 = '采油一矿二队'); 

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

select sum(入账金额)
from 成本表
where 入账日期 between '2021-5-1' and '2021-5-28'
	and 预算单位 = (select 单位代码
					from 单位表
					where 单位名称 = '采油一矿二队'); 

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

select sum(入账金额)
from 成本表
where 入账日期 between '2021-5-1' and '2021-5-28'
	and 预算单位 in (select 单位代码
					from 单位表
					where 单位名称 like '采油一矿%'); 

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

select distinct 入账人
from 成本表
where 入账人 is not null;

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

select 单据号
from 成本表
where 结算日期 between '2021-5-1' and '2021-5-28'
	and 入账日期 is null;

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

select *
from 成本表
where 预算单位 = (select 单位代码
				from 单位表
				where 单位名称 = '采油一矿二队')
order by 入账金额 DESC;

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

select distinct 施工单位, sum(结算金额)
from 成本表
group by 施工单位;

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

select *
from 材料消耗表
where 单据号 = (select 单据号
				from 成本表
				where 材料三 is not null
					and 材料三 > 2000);

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

select 单据号
from 成本表
where 施工单位 = '作业公司作业二队';

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

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

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

select 施工单位
from 成本表
where 井号 in (select 井号
			from 油水井表
			where 井别 = '油井'
				and 单位代码 in (select 单位代码
					from 单位表
					where 单位名称 like '采油一矿%'));

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

create table 汇总表
	(施工单位 varchar(16),
	年月 varchar(10),
	结算金额 int
	);

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

insert into 汇总表
select 施工单位, convert(varchar(7), 结算日期, 120), sum(结算金额)
from 成本表
group by 施工单位, convert(varchar(7), 结算日期, 120);

select * from 汇总表;

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

begin transaction;
update 成本表
	set 结算人 = '李兵'
	where 预算单位 in
			(select 单位代码 
			from 单位表
			where 单位名称 like '采油一矿%');
select * from 成本表;

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

delete from 成本表
	where 预算单位 in
			(select 单位代码 
			from 单位表
			where 单位名称 like '采油一矿%');
select * from 成本表;

⑸ 撤消上述两个操作。

rollback transaction;

四、实验报告

提交实验内容中用SQL语句完成的题目的SQL语句文档及相应的执行结果。