数据库实验上机答案整理中国石油大学龚安.docx
《数据库实验上机答案整理中国石油大学龚安.docx》由会员分享,可在线阅读,更多相关《数据库实验上机答案整理中国石油大学龚安.docx(44页珍藏版)》请在冰豆网上搜索。
数据库实验上机答案整理中国石油大学龚安
实验四SQL练习2
一、实验目的
1.掌握索引的建立、删除及使用;
2.掌握单表查询、连接查询、嵌套查询和集合查询;
3.掌握插入数据、修改数据和删除数据语句的非常用形式。
二、实验学时
2学时
三、实验内容
1.利用QueryAnalyzer完成以下操作:
⑴在预算日期、结算日期和入账日期上分别建立索引,并在查询操作中体会索引的作用。
在完成第2题的查询操作后,删除预算日期、结算日期和入账日期上的索引.
2.利用Query Analyzer完成以下操作:
⑴采油一矿二队2016—5—1到2016-5—28有哪些项目完成了预算,列出相应明细.
⑵采油一矿二队2016—5-1到2016—5—28有哪些项目完成了结算,列出相应明细。
采油一矿二队2016-5—1到2016-5—28有哪些项目完成了结算,列出相应的材料费消耗明细。
采油一矿二队2016—5-1到2016—5-28有哪些项目完成了入账,列出相应明细.
列出采油一矿二队2016-5—1到2016-5—28总的预算金额。
列出采油一矿二队2016-5—1到2016—5-28总的结算金额。
列出采油一矿二队2016-5—1到2016-5-28总的入账金额。
列出采油一矿2016—5—1到2016-5-28总的入账金额。
⑼ 有哪些人员参与了入账操作.
列出2016-5-1到2016—5—28进行了结算但未入账的项目。
⑾列出采油一矿二队的所有项目,按入账金额从高到低排列。
列出有哪些施工单位实施了项目,并计算各单位所有项目结算金额总和。
找出消耗了材料三且消耗超过了2000元的项目,列出相应消耗明细(利用子查询)。
作业公司二队参与了哪些项目。
⒂作业公司一队和二队参与了哪些项目(利用union)。
⒃ 采油一矿的油井是哪些作业队参与施工的.
3.利用QueryAnalyzer完成以下操作:
建立数据表(包含3个属性列:
★施工单位、★年月、◆结算金额)保存各个施工单位每月的结算金额总和。
用子查询将各个施工单位每月的结算金额总和插入到所建立的数据表中。
用带子查询的修改语句将采油一矿油井作业项目的结算人改为“李兵”。
用带子查询的删除语句删除采油一矿油井作业项目。
撤消上述两个操作。
四、实验报告
提交实验内容中用SQL语句完成的题目的SQL语句文档及相应的执行结果。
ﻬ实验五 SQL练习3
一、实验目的
1.掌握基本表的删除与修改;
2.掌握实体完整性、参照完整性和用户定义的完整性的定义、检查和违约处理;
3.掌握视图的定义、查询和更新,了解视图的作用.
二、实验学时
2学时
三、实验内容
1.利用QueryAnalyzer完成以下操作:
向在实验四中所定义的数据表增加“备注”列,其数据类型为字符型,并查看新增列的值。
对上述数据表增加主码约束条件,并观察在数据表中存在数据的情况下主码约束是否创建成功,然后再次执行实验四中实验内容3.2的操作,并观察记录执行结果.
⑶ 删除上述数据表中的数据,然后再删除该数据表,对这两个操作进行比较。
2.利用Query Analyzer完成以下任务:
对实验三中所定义的6个数据表增加主码约束条件,并观察在数据表中存在数据的情况下主码约束是否创建成功,然后执行以下2个操作,观察并记录实体完整性的检查和违约处理。
insert into材料费表 values(’zy2016001',’wm004',100,10)
insert into材料费表values('zy2016002',NULL,200,10)
注:
“材料费表"根据自己所命名的表名进行相应的替换。
对实验三中所定义的6个数据表增加相应的参照完整性约束,并观察在数据表中存在数据的情况下参照完整性约束是否创建成功,然后执行以下操作,观察并记录参照完整性的检查和违约处理。
将(y007油井 112203002)插入到油水井表。
②insertinto 材料费表 values(’zy2016007’,'wm006',100,10)
将作业项目编号zy2016001的施工单位修改为“作业公司作业五队”。
④将单位代码表中的(112202002 采油二矿二队)删除,查看油水井表和作业项目表中的数据有何变化.
将物码表中的(wm004材料四袋)修改为(wm04材料四 袋)。
撤销上述成功的更新操作。
注:
“材料费表"根据自己所命名的表名进行相应的替换。
对实验三中所定义的6个数据表按以下要求增加相应的完整性约束条件,并观察在数据表中存在数据的情况下完整性约束是否创建成功。
单位代码表的单位名称不能取空值、且取值唯一。
油水井表的井别只允许取“油井”或“水井”,单位代码不能取空值。
物码表的名称规格不能取空值、且取值唯一,计量单位不能取空值。
④材料费表的消耗数量不能取空值,单价不能取空值.
对作业项目表根据实际应用的要求定义适当的用户定义的完整性约束条件。
3.利用QueryAnalyzer完成以下操作:
⑴定义一个视图,用于保存作业项目表和材料费表的全部列.
⑵查询上面定义的视图,可任意组合查询条件,构造出2个查询。
⑶定义一个反映作业项目预算状态的视图,并向该视图插入(’zy2016008',’112202002’,'y005’,10000,’张三', '2016—07-02'),查看作业项目表的数据有何变化。
撤销上述成功的更新操作。
四、实验报告
提交实验内容中用SQL语句完成的题目的SQL语句文档及相应的执行结果。
实验六其它数据库对象的管理
一、实验目的
1.掌握事务的概念、性质、定义及使用;
2.掌握游标的概念、组成、创建及使用;
3.掌握存储过程的概念、类型、特点、创建、执行及管理.
4.掌握触发器的概念、创建、管理及使用。
二、实验学时
2学时
三、实验内容
1.利用QueryAnalyzer进行如下事务处理练习(把下列五条语句作为一个事务处理,只有五条语句全部成功执行才做提交,并给出成功的提示信息;否则就做回退处理,并给出具体的错误提示信息):
insert into作业项目表values('zy2016006','112202002’,'y005’,
10000,'张三’,'07—01—2016’,’07—04-2016’,'07—25-2016',
’作业公司作业一队’,'堵漏',7000,2500,1000,1400,11900,
'李四’,’07-26—2016',11900,'王五','07-28-2016’)
insertinto材料费表values(’zy2016006’,’wm001’,200,10)
insert into材料费表values('zy2016006',’wm002’,200,10)
insertinto材料费表values(’zy2016006','wm003’,200,10)
insertinto材料费表values('zy2016006','wm004’,100,10)
注:
“作业项目表”、“材料费表”根据自己所命名的表名进行相应的替换.
2.利用Query Analyzer进行如下游标练习:
定义一个游标,用于存放作业项目表的全部行数据,并打印以下表头和各行数据。
表头:
单据号预算单位井号预算金额预算人预算日期开工日期完工日期施工单位 施工内容材料费人工费 设备费 其它费用 结算金额 结算人结算日期 入账金额 入账人入账日期
执行以上所定义的游标,查看是否能正确输出结果。
3.利用Query Analyzer定义一个存储过程,要求完成以下功能:
生成某单位(单位可以是采油厂或采油矿或采油队)某段时间内的成本运行情况(输入参数:
单位代码起始日期 结束日期)。
输出格式 ***单位**时间—--**时间成本运行情况
预算金额 结算金额入账金额未结算金额未入账金额
****。
******。
** ****。
******.******。
**
其中:
未结算金额=预算金额-结算金额
未入账金额=结算金额—入账金额
分三种情况(单位分别为:
采油厂、采油矿、采油队)执行以上定义的存储过程,查看执行输出结果。
4.利用Query Analyzer针对作业项目表定义三个触发器,分别完成以下功能:
对作业项目表插入一行数据时,自动计算并插入结算金额字段(结算金额=材料费+人工费+设备费+其它费用)。
当修改作业表的某行数据时自动修改结算金额字段。
⑶当删除作业表中一行数据时,自动删除材料费表中相应明细数据.
对上述3个触发器用适当的更新语句进行验证,并查看结果是否达到预期结果。
四、实验报告
提交实验内容中用SQL语句完成的题目的SQL语句文档及相应的执行结果.
实验七安全机制
2.利用QueryAnalyzer完成以下操作:
建立采油一矿的作业项目的视图,把该视图的查询权限授予给采油一矿的用户user11,以user11的身份查询该视图,观察执行情况;再以其他用户的身份查询该视图,观察执行情况.
创建一个用户user12,以user12的身份执行实验六中所定义的存储过程,观察记录是否成功执行;然后把该存储过程的执行权限授予给user12,再次以user12的身份执行该存储过程,观察记录是否成功执行。
⑶定义触发器,实现只能在工作时间内更新“作业项目表”的数据,然后通过选择不同的时间进行适当的更新操作来验证。
—-实验3-—---——----——-—--—---——---——--—-—--—--————------——-—-—-——--—--—-—
CREATETABLE 单位代码表
(单位代码 CHAR(20),
单位名称 CHAR(20) );
CREATETABLE油水井表
(井号CHAR(20),
井别CHAR(20),
单位代码 CHAR(20) );
CREATETABLE施工单位表
(施工单位名称CHAR(20));
CREATE TABLE物码表
(物码CHAR(20),
名称规格CHAR(20),
计量单位 CHAR(20));
createtable材料费表(
单据号CHAR(20),
物码CHAR(20),
消耗数量INT,
单价MONEY,
);
CREATETABLE 作业项目表
(单据号CHAR(20),
预算单位 CHAR(20),
井号 CHAR(20),
预算金额MONEY,
预算人CHAR(20),
预算日期DATETIME,
开工日期DATETIME,
完工日期 DATETIME,
施工单位CHAR(20),
施工内容CHAR(20),
材料费money,
人工费MONEY,
设备费MONEY,
其他费用money,
结算金额 MONEY,
结算人CHAR(20),
结算日期DATETIME,
入账金额MONEY,
入账人CHAR(10),
入账日期DATETIME)
-———-—-————-—-——————--—-——-——--——-—--—-——------——-——----——-———-————----—----——-
insertinto单位代码表
values('1122’,’采油厂’);
insert into单位代码表
values ('112201’,'采油一矿’);
insert into 单位代码表
values(’112202’,’采油二矿');
insertinto单位代码表
values('112201001’,'采油一矿一队’);
insertinto单位代码表
values('112201002’,’采油一矿二队');
insertinto单位代码表
values(’112201003',’采油一矿三队’);
insertinto 单位代码表
values('112202001’,’采油二矿一队’);
insertinto 油水井表
values(’y001','油井',’112201001’);
insertinto油水井表
values('y002', '油井’,’112201001’);
insert into油水井表
values (’y003', '油井' ,’112201002');
insertinto 油水井表
values('s001’, '水井’ ,’112201002’);
insertinto油水井表
values (’y004', ’油井', '112201003');
insertinto 油水井表
values('s002’, ’水井’,'112202001');
insertinto油水井表
values('s003','水井',’112202001’);
insert into油水井表
values(’y005’,’油井',’112202002’);
insert into施工单位表
values ('作业公司作业一队');
insert into施工单位表
values(’作业公司作业二队');
insertinto 施工单位表
values('作业公司作业三队');
insert into 物码表
values('wm001’,'材料一’,’吨');
insert into物码表
values(’wm002’,'材料二','米');
insertinto物码表
values('wm003’,'材料三',’桶’);
insertinto物码表
values (’wm004’,’材料四','袋');
insert into材料费表(单据号,物码,消耗数量,单价)
values('zy2016001','wm001',200,¥10);
insert into材料费表(单据号,物码,消耗数量,单价)
values('zy2016001’,’wm002',200,¥10);
insert into材料费表(单据号,物码,消耗数量,单价)
values('zy2016001',’wm003’,200,¥10);
----—-—-—-——-------—--—---—-—————-—-——-—-——--———--———---—--—————
insertinto 材料费表(单据号,物码,消耗数量,单价)
values('zy2016003','wm001',200,¥10);
insertinto 材料费表(单据号,物码,消耗数量,单价)
values ('zy2016003','wm002',200,¥10);
insertinto材料费表(单据号,物码,消耗数量,单价)
values(’zy2016003’,’wm003',250,¥10);
———-—--—---——----—-———--—-——-———————-—————-————-—-—-—————-——-———---—
insert into材料费表(单据号,物码,消耗数量,单价)
values(’zy2016004',’wm001’,200,¥10);
insertinto材料费表(单据号,物码,消耗数量,单价)
values (’zy2016004',’wm002',200,¥10);
insertinto材料费表(单据号,物码,消耗数量,单价)
values(’zy2016004',’wm004’,200,¥10);
-——-——-—-—-————--—-—-—-——-——-——————-———-———--—-—--——--
insertinto材料费表(单据号,物码,消耗数量,单价)
values(’zy2016005’,’wm001’,200,¥10);
insertinto材料费表(单据号,物码,消耗数量,单价)
values('zy2016005','wm002',200,¥10);
insert into材料费表(单据号,物码,消耗数量,单价)
values ('zy2016005’,’wm004',300,¥10);
-——--—-——-——-—--——---——---——--——————-—-—--——-——-———--—--————-—-——--—
insertinto作业项目表
values(’zy2016001',’112201001','y001',¥10000,’张三’,'2016/5/1’,
'2016/5/4',’2016/5/23’,’作业公司作业一队',’堵漏’,¥7000,¥2500,¥1000,¥1400,¥10900,'王五’,'2016/5/26',
¥10900,'王五’,'2016/5/28');
-----—--—--—----——————--——----—————---—--—-—--—---——-—----
insertinto作业项目表
values(’zy2016003','112201002','s001’,¥10500,’张三’,'2016/5/1’,
’2016/5/6','2016/5/23’,’作业公司作业二队’,'调剖',¥6500,¥2000,¥500,¥1400,¥10400,’李四',’2016/5/26’,
¥10400,’王五’,’2016/5/28’
);
—-------—--———-——-—-----—————----—---————-———
insertinto作业项目表
values(’zy2016004’,’112202001’,’s002’,¥12000,'张三’,'2016/5/1’,
'2016/5/4',’2016/5/24','作业公司作业三队’,'解堵',¥6000,¥2000,¥1000,¥1600,¥10600,'李四',’2016/5/26',¥10600,’赵六',’2016/5/28’);
—---———---———-—----—-—--—————-——-——-——-———-—
insertinto作业项目表
values('zy2016005',’112202002','y005’,¥12000,'张三',’2016/5/1’,
’2016/5/4’,'2016/5/28’,’作业公司作业三队','防砂’,¥7000,¥1000,¥2000,¥1300,¥11300,’李四','2016/6/1',
NULL,NULL,NULL);
---—--———-—-—---———---—-—-——-—--——————-
begintran
update作业项目表
set 人工费 =人工费+200
where 单据号='zy2016005’;
update作业项目表
set结算金额= 结算金额+200
where单据号=’zy2016005’;
delete
from 作业项目表
where入账金额 isNULL;
rollback tran
--实验四—-——-—--—---———--—---——---—---——--————————-————
-—1
createindex 预算日期索引on 作业项目表(预算日期);
create index结算日期索引on作业项目表(结算日期);
createindex入账日期索引on作业项目表(入账日期);
--
(1)
select*
from 作业项目表
where预算日期 between ’2016/5/1’and'2016/5/28’ and预算单位=
(select单位代码
from单位代码表
where单位名称='采油一矿二队');
—-another solution
select*
from作业项目表,单位代码表
where 单位代码= 预算单位 and单位名称= ’采油一矿二队'and 预算日期between'2016/5/1’and ’2016/5/28’;
--
(2)
select*
from 作业项目表
where结算日期between’2016/5/1'and’2016/5/28’and预算单位=
(select单位代码
from 单位代码表
where 单位名称=’采油一矿二队');
-—anothersolution
——select *
-—from作业项目表,单位代码表
——where单位名称=’采油一矿二队’and预算单位=单位代码and结算日期〉=’2016-5—1’and结算日期<='2016-5-28';
-—(3)———--——---—-———-—--——--——-—-—---—-——--——-—-————
select *
from材料费表
where单据号 =
(select单据号
from作业项目表
where 预算日期between’2016/5/1'and'2016/5/28’and预算单位 =
(select单位代码
from单位代码表
where单位名称=’采油一矿二队’));
—-(4)——-——--———-———-—-----—----—-————--—--—-———
select *
from作业项目表
where 入账日期between’2016/5/1'and'2016/5/28’and预算单位=
(select单位代码
from单位代码表
where单位名称=’采油一矿二队’);
——(5)————-—-———-—-———--—-———————————-———--——--
select,sum(预算金额)预算金额总和
from作业项目表
where 预算日期between’2016/5/1'and'2016/5/28’and预算单位=
(select单位代码
from单位代码表
where单位名称=’采油一矿二队’);
--(6)—-—-—-——--—------—--———------—--
select,sum(结算金额)结算金额总和
from 作业项目表
where结算日期between’2016/5/1'and '2016/5/28' and预算单位 =
(select单位代码
from单位代码表
where单位名称 ='采油一矿二队');
--(7)-—-————-——————-———--———---———-—
select sum(入账金额) 入账金额总和
from作业项目表
where入账日期between'2016/5/1’and ’2016/5/28’and预算单位=
(