搜索
实验四SQL练习2一、实验目的1．掌握索引的建立、删除及使用；2．掌握单表查询、连接查询、嵌套查询和集合查询；3．掌握插入数据、修改数据和删除数据语句的非常用形式。二、实验学时2学时三、实验内容1．利用QueryAnalyzer完成以下操作：⑴在预算日期、结算日期和入账日期上分别建立索引，并在查询操作中体会索引的作用。⑵在完成第2题的查询操作后，删除预算日期、结算日期和入账日期上的索引。2．利用QueryAnalyzer完成以下操作：⑴采油一矿二队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．利用QueryAnalyzer完成以下任务：⑴对实验三中所定义的6个数据表增加主码约束条件，并观察在数据表中存在数据的情况下主码约束是否创建成功，然后执行以下2个操作，观察并记录实体完整性的检查和违约处理。①insertinto材料费表values('zy2016001','wm004',100,10)②insertinto材料费表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进行如下事务处理练习（把下列五条语句作为一个事务处理，只有五条语句全部成功执行才做提交，并给出成功的提示信息；否则就做回退处理，并给出具体的错误提示信息）：insertinto作业项目表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)insertinto材料费表values('zy2016006','wm002',200,10)insertinto材料费表values('zy2016006','wm003',200,10)insertinto材料费表values('zy2016006','wm004',100,10)注：“作业项目表”、“材料费表”根据自己所命名的表名进行相应的替换。2．利用QueryAnalyzer进行如下游标练习：定义一个游标，用于存放作业项目表的全部行数据，并打印以下表头和各行数据。表头：单据号预算单位井号预算金额预算人预算日期开工日期完工日期施工单位施工内容材料费人工费设备费其它费用结算金额结算人结算日期入账金额入账人入账日期执行以上所定义的游标，查看是否能正确输出结果。3．利用QueryAnalyzer定义一个存储过程，要求完成以下功能：生成某单位(单位可以是采油厂或采油矿或采油队)某段时间内的成本运行情况（输入参数：单位代码起始日期结束日期）。输出格式***单位**时间---**时间成本运行情况预算金额结算金额入账金额未结算金额未入账金额****.******.******.******.******.**其中：未结算金额=预算金额-结算金额未入账金额=结算金额-入账金额分三种情况（单位分别为：采油厂、采油矿、采油队）执行以上定义的存储过程，查看执行输出结果。4．利用QueryAnalyzer针对作业项目表定义三个触发器，分别完成以下功能：⑴对作业项目表插入一行数据时，自动计算并插入结算金额字段（结算金额=材料费+人工费+设备费+其它费用）。⑵当修改作业表的某行数据时自动修改结算金额字段。⑶当删除作业表中一行数据时，自动删除材料费表中相应明细数据。⑷对上述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));CREATETABLE物码表(物码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','采油厂');insertinto单位代码表values('112201','采油一矿');insertinto单位代码表values('112202','采油二矿');insertinto单位代码表values('112201001','采油一矿一队');insertinto单位代码表values('112201002','采油一矿二队');insertinto单位代码表values('112201003','采油一矿三队');insertinto单位代码表values('112202001','采油二矿一队');insertinto油水井表values('y001','油井','112201001');insertinto油水井表values('y002','油井','112201001');insertinto油水井表values('y003','油井','112201002');insertinto油水井表values('s001','水井','112201002');insertinto油水井表values('y004','油井','112201003');insertinto油水井表values('s002','水井','112202001');insertinto油水井表values('s003','水井','112202001');in