用上次作业创建的两个数据表为基础数据，创建多表查询，创建报表，完成后将数据库文件压缩后上传。

实验（第八周）（总分：18分） 高级数据查询（上） 1. 实验目的（1分） (1) 掌握联接查询 （1条及以上的得满分；否则按不完整比例扣分） 2. 实验原理（1分） （自己总结，1条以上得满分；否则按不完整比例扣分） 3. 实验内容（1分） (1) 使用联接运算符实现多表查询，包括内联接、外联接和复合条件联接查询 （1条及以上的得满分；否则按不完整比例扣分） 4. 实验作业（13分=小题3+10分） 必须用SQL语言完成作业并在截图时将sql语言和对应输出同框截图，否则不给分，截图模板如下：4.0 数据准备 利用上次作业建立的questiondb数据库和question表做以下操作。 (1) 用SQL语句在questiondb数据库中创建知识点表point，表结构如下： 表 point结构 属性 描述 类型 长度 pointid 知识点编号 varchar 18 pcontent 知识点内容 varchar 20 pchapter 知识点分布章节 varchar 4 并向表中插入如下数据： 表 point记录 pointid pcontent pchapter 33320 事务是一系列不可分割的数据库操作…… 3.3 24010 一元二次方程的主要解法如下…… 6.7 03066 尊老爱幼是中华民族的传统美德 8.9 62603 SQL语言是声明性语言…… 9.9 （表格创建正确，得1分； 数据插入正确，得1分； 说明和截图完整，得以上2分；否则按不完整比例扣分） (2) 为了之后的操作简便，使用以下语句删除原问题表question中的记录，重新插入5行数据： delete from question; insert into question values ('00089',5,'X2-2x+1=0',0.2,'24010',65,'1'), ('84979',2,'事务有哪些特性？',0.3,'33320',126,'ACID'), ('97088',3,'SQL语言包括哪些部分？',0.2,'62603',67,'数据定义、操作、保护'), ('69783',4,'秦始皇执政多少年？',0.5,'73778',89,'49'), ('94844',1,'操作系统的目标？',0.4,'93956',100,'简单高效使用计算机') （上述执行正确，得1分；需要截图修改后的question表的所有记录。 说明和截图完整，得以上1分；否则按不完整比例扣分） 4.1要求以下作业使用联接查询，否则不给分 (1) 查询question表与point表有相同知识点编号的问题，并输出此类问题及其知识点的详细信息，要求分别用交叉联接、自然联接、using条件联接、on条件联接，并简述四种方法的不同之处 （每种方法在一条SQL语句中实现且查询结果正确，各给1分，共4分；否则不给分。 不同之处叙述得当，得2分。 说明和截图完整，得以上6分；否则按不完整比例扣分） (2) 查询每个问题的详细信息，若某些问题在point表中存储有相关知识点，也一并输出，否则为空值 （在一条SQL语句中实现且输出结果正确，给2分；否则按不达标比例扣分； 说明和截图完整，得以上2分；否则按不完整比例扣分） (3) 查询question表中跟’00089’号问题的难度一样的问题ID及其难度，要求使用自联接和更名操作 （在一条SQL语句中实现，并使用了自联接、更名操作，且输出结果正确，给2分； 否则按不达标比例扣分； 说明和截图完整，得以上2分；否则按不完整比例扣分） 5. 思考与体会（1分） (1) 在联接查询中，在查询某些列时什么时候可以忽略列前的表名，什么时候又不可以忽略？ （问题回答两点及以上且内容充实，得1分；否则，酌情扣分） 6. 实验体会（1分） （自己总结一点及以上，得1分；否则，酌情扣分）

作业题2 创建一个Excel文档，命名为“test2.xlsx”，然后完成如下操作： （操作1） 在“Sheet1”工作表中输入以下销售统计表数据：（2分） “山姆”超市第3季度洗衣机销售统计表 2015年4月9日 品牌 单价 七月 八月 九月 销售小计 平均销量 销售额 小天鹅 1500 58 86 63 爱妻 1400 64 45 47 威力 1450 97 70 46 乐声 1350 76 43 73 操作要求： ① 将该表的名称由“Sheet1”更为“洗衣机销售统计表”。 （１分） ② 将“洗衣机销售统计表”中的内容复制到“Sheet2”、“Sheet3”中。（2分） ③ 在“洗衣机销售统计表”中，运用输入公式表达式的方法，求出“小天鹅“的销售量小计、月平均销售量和销售额；再利用公式填充的方法，求出其余各品牌的销售量小计、月平均销售量和销售额。（6分） （操作2） 在“Sheet2”工作表中先利用输入公式函数的方法，求出“小天鹅“的销售量小计、月平均销售量和销售额；再利用公式填充的方法，求出其余各品牌的销售量小计、月平均销售量和销售额。（6分） （操作3） 在“Sheet3”工作表中，利用自动求和按钮，求出各品牌的销售量小计；利用多区域自动求和的方法，求出各品牌的销售量的总和，并将计算结果存放在“F8”单元格中。（４分） （操作4） 在“洗衣机销售统计表”中完成下列操作： ① 在“洗衣机销售统计表”中的“乐声”行上面插入一空行，在该空行的品牌、单价、　 七月、八月、九月的各栏中分别填入：水仙、1375、56、78、34；最后利用复制公式的方法，求出该品牌的销售量小计、月平均销售量和销售额；（5分） ② 在“洗衣机销售统计表”中的“销售额”后插入一空列，并在该列的品牌对应行填入“7-9月趋势图”；该列其他行则分别用其左侧7-9月销量为基础插入折线迷你图。（４分）

实验（第七周）简单数据查询 （总分：20分） 1. 实验目的（1分） (1) 掌握基本SQL查询语句格式 (2) 掌握单表查询方法 (3) 掌握聚集函数查询方法 （3条及以上或者自己总结2条以上的得满分；否则，按不完整比例扣分） 2. 实验原理（1分） （自己总结，2条以上得满分；否则，按不完整比例扣分） 3. 实验内容（1分） (1) 使用SQL基本查询语句进行单表查询，主要有：查询属性、查询指定记录、查询空值、多条件的查询、去重、分组查询以及对查询结果排序等。 (2) 使用集合函数对表进行查询，聚集函数主要有：AVG()，COUNT()，MAX()，MIN()，SUM()。 （2条及以上或者自己总结且1条以上的得满分；否则，按不完整比例扣分） 4. 实验作业（13分） （注意：请保存并备份好步骤（0）中的内容，后续实验会在这个数据库上进行） (0) 用SQL语句创建数据库questiondb，在库中创建表question。 表 question结构 属性 描述 类型 长度 qid 问题ID varchar 18 qtype 类型 smallint difficulty 难度 double pointid 知识点编号 varchar 18 etime 预期时间 double qtext 题干 text answer 答案 text 向表中插入如下数据。 表question中记录 qid qtype qtext difficulty pointid etime answer 500884983903900089 5 X2-2x+1=0 0.1 402126002030324010 65 1 498786998909084979 2 事务有哪些特性？ 0.3 633224023033033320 126 ACID 340883983906097088 3 SQL语言包括哪些部分？ 0.2 362632000332362603 67 数据定义、操作、保护 940907998709969783 4 秦始皇执政多少年？ 0.5 773307973739773778 89 49 980905983909894844 1 操作系统的目标？ 0.4 379579999903093956 100 简单高效使用计算机 883308983839883884 3 What does zoo mean? 0.5 367507999007357693 76 动物园 379589999903093956 2 确定准确位置的信息技术？ 0.3 383223023036301322 88 全球定位系统 368508999008358693 5 种瓜得瓜种豆得豆取决于？ 0.2 083031002130060123 65 细胞核 343223023036301322 4 验证假设的途径是？ 0.5 023036023030208288 186 进行实验 043031002130060123 3 毛泽东思想三个活的灵魂？ 0.4 383223123136311322 67 实事求是/独立自主/群众路线 023036023030204244 2 拥有范围经济的企业必存在规模经济? 0.3 183131112131161123 89 错误，并无必然联系 223332023230223224 5 老年节是哪天？ 0.1 310620000033303066 89 农历九月初九 （表格创建正确，得2分； 数据插入正确，得2分； 说明和截图完整，得以上4分；否则，按不完整比例扣分） (1) 在question表中，查询所有记录的qid、qtype、difficulty属性值。 （SQL语句及查询结果正确，得1分； 说明和截图完整，得以上1分；否则，按不完整比例扣分） (2) 在question表中，查询pointid等于083031002130060123和023036023030208288的所有记录。 （SQL语句及查询结果正确，得1分； 说明和截图完整，得以上1分；否则，按不完整比例扣分） (3) 在question表中，查询difficulty范围在0.3~0.6之间的所有记录。 （SQL语句及查询结果正确，得1分； 说明和截图完整，得以上1分；否则，按不完整比例扣分） (4) 在question表中，查询qtype为2的所有记录。 （SQL语句及查询结果正确，得1分； 说明和截图完整，得以上1分；否则，按不完整比例扣分） (5) 在question表中，查询每个类型中难度最大的记录，输出类型和最大难度。 （SQL语句及查询结果正确，得1分； 说明和截图完整，得以上1分；否则，按不完整比例扣分） (6) 在question表中，计算每个类型各有多少题目，输出类型和题目数。 （SQL语句及查询结果正确，得1分； 说明和截图完整，得以上1分；否则，按不完整比例扣分） (7) 在question表中，计算不同类型的平均难度，输出类型和平均难度。 （SQL语句及查询结果正确，得1分； 说明和截图完整，得以上1分；否则，按不完整比例扣分） (8) 在question表中，查询难度低于0.5的所有记录，并只显示前3个查询结果。 （SQL语句及查询结果正确，得1分； 说明和截图完整，得以上1分；否则，按不完整比例扣分） (9) 查询表question，将查询记录先按问题编号由高到低排列，再按题目难度由低到高排列。 （SQL语句及查询结果正确，得1分； 说明和截图完整，得以上1分；否则，按不完整比例扣分） 5. 思考与体会（2分） (1) 当使用DISTINCT去除重复行时，如果要查询表table1中所有的列，是否可以使用SELECT DISTINCT * FROM table1呢? (2) 查询时，WHERE子句中会使用条件，有的值加上单引号，而有的值未加。思考什么时候使用单引号。 （两个问题分别回答两点及以上且内容充实，得2分；否则，酌情扣分） 6. 实验体会（2分） （自己总结两点及以上，得2分；否则，酌情扣分）

实验（第四周） 基于SQL的数据定义及修改 1. 实验目的 　　　(1) 掌握用SQL 语句创建数据库、修改数据库属性、删除数据库的方法。 　　　(2) 掌握用SQL 对数据进行增、删、改等操作。 2. 实验环境（写清硬件配置和软件版本） 　　　(1) 硬件： 　　　(2) 操作系统： 　　　(3) 数据库管理系统： 3. 实验内容 (1) 使用SQL 语句创建数据库、修改数据库属性、删除数据库。 (2) 使用SQL 语句插入数据、更新数据、删除数据。 4. 实验数据 　　　应急预案指面对突发事件如自然灾害、重特大事故、环境公害及人为破坏的应急管理、指挥、救援计划等，是一种公文。通常一个应急预案由多个不同的编制单位协同编写，才能编制完成。应急预案包含预案编号（plan_id）,预案名（plan_name），针对的灾害类型（plan_disatype），针对的区域（plan_area），针对的灾害等级（plan_level），发布时间（plan_date）。应急预案编制的参与单位包含单位编号（depart_id），单位名(depart_name)，单位联系方式（depart_tel）。一个参与单位可能参与多个预案的编制，一个预案需要多个参与单位协作完成。当参与单位完成编写应急预案时，会记录该单位在应急预案编制中的职责（depart_respon）和工作量（workload）。 5. 实验作业 (1) 在数据库Emgyplan中，使用SQL语句创建数据表department(depart_id, depart_name, depart_tel)，设置 depart_id 为主键。 (2) 在数据库Emgyplan中,使用SQL语句创建数据plan(plan_id, plan_name, plan_disatype, plan_area, plan_level, plan_date)，设置 plan_id 为主键。 (3) 在数据库Emgyplan中,使用SQL语句创建数据record(depart_id, plan_id, plan_respon, workload)，设置主键、外键。 (4) 使用SQL修改数据表 plan 的名称为 plan1，并添加字段 plan_admin（预案联系人），plan_tel（联系方式）。 (5) 使用SQL修改数据表 plan1中的plan_date字段的类型为文本类型。 (6) 使用SQL把数据表 department中的depart_name改为departmentname。 (7) 使用SQL语句删除字段 plan_tel。 (8) 使用SQL语句删除数据表 department，这时会发生什么情况，截图说明。 (9) 使用SQL语句删除数据表 plan1，department。 (10) 创建department表，使用SQL语句向department表中插入数据。 （11）创建plan1表，使用SQL语句向plan表中插入数据 （12）使用SQL语句向record表中插入指定字段的数据 （13）使用SQL语句将plan表中plan_id=1的负责人改成“王云”。 （14）使用SQL语句将record表中所有数据删除。 6. 问题与思考 (1) 每个表中都要有一个主键吗？ (2) 插入记录时可以不指定字段名称吗？ 7. 实验体会

实验（第八周）（总分：25分） 高级数据查询（下） 1. 实验目的（1分） (1) 掌握嵌套查询 (2) 掌握集合操作 (3) 掌握使用正则表达式查询 （3条及以上或者自己总结且2条以上的得满分；否则按不完整比例扣分） 2. 实验原理（1分） （自己总结，2条以上得满分；否则按不完整比例扣分） 3. 实验内容（1分） (1) 在语句中嵌套子查询，并为属性和表创建别名，常使用的操作符有：ANY（SOME），ALL，IN，EXISTS (2) 使用集合操作实现多个查询结果的合并 (3) 在检索或替换某个符合要求的文本内容的语句中使用正则表达式 （3条及以上或者自己总结且2条以上的得满分；否则按不完整比例扣分） 4. 实验作业（19分=小题8+4+2+5分） 要求使用《实验（第八周）实验要求（上）》中的question表和point表进行以下实验。且同样要求必须用SQL语言完成作业并在截图时将sql语言和对应输出同框截图，否则不给分。 4.1要求以下作业使用嵌套查询，否则不给分 (1) 查询question表中跟’00089’号问题的难度一样的问题ID及其难度 （在一条SQL语句中实现，并使用了嵌套查询，且输出结果正确，给2分； 否则按不达标比例扣分； 说明和截图完整，得以上2分；否则按不完整比例扣分） (2) 查询question表中问题类型大于任一难度为0.2的问题的问题编号和类型 （在一条SQL语句中实现，并使用了嵌套查询，且输出结果正确，给2分； 否则按不达标比例扣分； 说明和截图完整，得以上2分；否则按不完整比例扣分） (3) 查询question表中在point表中存储有相关知识点且分布章节大于’8’的问题ID和知识点编号 （在一条SQL语句中实现，并使用了嵌套查询，且输出结果正确，给2分； 否则按不达标比例扣分； 说明和截图完整，得以上2分；否则按不完整比例扣分） (1) 查询question表中问题类型大于’3’的问题，输出在这些问题下不同难度的平均预期时间 （在一条SQL语句中实现，并使用了嵌套查询，且输出结果正确，给2分； 否则按不达标比例扣分； 说明和截图完整，得以上2分；否则按不完整比例扣分） 4.2要求以下作业使用集合操作，否则不给分 (1) 查询point表中有被question表使用的知识点编号 （在一条SQL语句中实现，并使用了集合操作，且输出结果正确，给2分； 否则按不达标比例扣分； 说明和截图完整，得以上2分；否则按不完整比例扣分） (2) 查询question表中所有难度大于0.2、小于0.5的题目信息，查询question表中知识点编号大于’60000’、小于’90000’的题目信息，然后使用UNION合并两个查询结果。 （在一条SQL语句中实现，并使用了集合操作，且输出结果正确，给2分； 否则按不达标比例扣分； 说明和截图完整，得以上2分；否则按不完整比例扣分） 4.3要求以下作业使用正则表达式，否则不给分 (1) 查询question表中题干中包含文字“哪些”的记录 （在一条SQL语句中实现，并使用了正则表达式，且输出结果正确，给2分； 否则按不达标比例扣分； 说明和截图完整，得以上2分；否则按不完整比例扣分） 4.4 探究实验 要求根据question表和point表自己提出一个复杂查询任务，并完成查询，获得结果。 （按照查询的“有趣”程度和完成情况，同学们给出自己的评分0~5分） 5. 思考与体会（2分） (1) 请举例一个相关子查询，并分析其执行过程 （举例正确且分析正确，得2分；否则，酌情扣分） 6. 实验体会（1分） （自己总结一点及以上，得1分；否则，酌情扣分）

实验（第六周）（总分：30分） 数据表基本操作&备份还原（高） 1. 实验目的（1分） (1) 掌握如何创建数据表、查看数据表的属性、修改数据表、删除数据表的方法 (2) 掌握向表中插入数据、更新数据、删除数据的方法 (3) 了解数据备份和还原的概念 (4) 掌握数据备份和还原的方法 （4条及以上或者自己总结且3条以上的得满分；否则，按不完整比例扣分）2. 实验原理（1分） （自己总结，2条以上得满分；否则，按不完整比例扣分）3. 实验内容（1分） (1) 创建数据表，包括添加主键、外键、非空约束 (2) 修改已存在数据表的结构，包括添加列和主外键约束，修改属性数据类型、属性名和表名，删除属性、表的外键约束和表 (3) 向数据表中插入数据记录，包括插入完整的记录，插入不完整记录，插入多条记录 (4) 对表中已有数据进行修改和删除 (5) 使用pg_dump和pg_dumpall工具备份数据库 (6) 使用psql还原数据库 （6条及以上或者自己总结且5条以上的得满分；否则，按不完整比例扣分）4. 实验作业（23分=小题8+5+5+5分） 要求1： 创建数据库business，在business中创建数据库表clients，clients表结构如下表4-1所示，按要求进行操作。 表4-1 client表结构 属性名 数据类型 主键 外键 非空 唯一 cid INT 是 否 是 是 cname varchar(20) 否 否 否 否 cphone varchar(15) 否 否 是 否 caddress varchar(50) 否 否 否 否 (1) 创建数据库business。 （说明和截图完整，得1分；否则，按不完整比例扣上步计算得分） (2) 创建数据表clients，在cid属性上添加主键约束，在cphone属性上添加非空约束。 （ 若属性名和数据类型正确，得1分； 若外键和非空设置正确，得1分； 说明和截图完整，得以上的2分；否则，按不完整比例扣上步计算得分） (3) 增加属性cgender，数据类型为char(1)。 （说明和截图完整，得1分；否则，按不完整比例扣上步计算得分） (4) 将cname属性数据类型改为varchar(70)。 （说明和截图完整，得1分；否则，按不完整比例扣上步计算得分） (5) 将clphone属性改名为ctele。 （说明和截图完整，得1分；否则，按不完整比例扣上步计算得分） (6) 将表名修改为clients_info。 （说明和截图完整，得1分；否则，按不完整比例扣上步计算得分） (7) 删除属性caddress。 （说明和截图完整，得1分；否则，按不完整比例扣上步计算得分） 要求2： 在business数据库中创建数据表orders，orders表结构如下表4-2所示，按要求进行操作。 表4-2 order表结构 属性名 数据类型 主键 外键 非空 唯一 orderid varchar(30) 是 否 是 是 orderdate date 否 否 否 否 machineprice float 否 否 否 否 clientid INT 否 是 否 否 (1) 创建数据表orders，在orderid属性上添加主键约束 （ 若属性名和数据类型正确，得1分； 若主键设置正确，得1分； 说明和截图完整，得以上的2分；否则，按不完整比例扣上步计算得分） (2) 在clientid属性上添加外键约束，关联clients_info表中的主键cid。 （ 若是在创建表的时候设置外键约束，得0分； 否则若是在创建表之后添加的外键约束，得1分； 说明和截图完整，得以上的1分；否则，按不完整比例扣上步计算得分） (3) 删除orders表的外键约束 （说明和截图完整，得1分；否则，按不完整比例扣上步计算得分） (4) 删除表clients。 （说明和截图完整，得1分；否则，按不完整比例扣上步计算得分） 要求3： 创建数据表machines，并对表进行插入、更新和删除操作，machines表结构如下表4-3所示，machines表中的记录如下表4-4所示。 表4-3 machines表结构 属性名 数据类型 主键 外键 非空 唯一 属性说明 mcode varchar(18) 是 否 是 是 机器码 mtype varchar(50) 否 否 是 否 型号 mmaker varchar(8) 否 否 是 否 制造商 mprice Float 否 否 是 否 销售价格 表4-4 machines表中的记录 mcode mtype mmaker mprice 500224923 Dell 9600 Dell 6570 412726918 T805C Apple 7600 (1) 创建数据表machines，使用insert语句同时插入一条或多条记录的方法将表4-4中的记录插入到machines表中。 （ 若创建表正确，得1分； 若插入正确，得1分 说明和截图完整，得以上的2分；否则，按不完整比例扣上步计算得分） (2) 使用update语句将名称为Dell 9600的销售价格改为6600。 （说明和截图完整，得1分；否则，按不完整比例扣上步计算得分） (3) 删除mmaker为Dell的记录。 （说明和截图完整，得1分；否则，按不完整比例扣上步计算得分） (4) 删除表中的所有记录。 （说明和截图完整，得1分；否则，按不完整比例扣上步计算得分） 要求4： 进行备份与还原 (1) 使用pg_dump备份business数据库中的cliens_info表 （说明和截图完整，得1分；否则，按不完整比例扣上步计算得分） (2) 使用creatdb创建新的数据库newbusiness，并用psql将cliens_info表还原到该表中。 （ 若使用creatdb创建，得1分； 若还原正确，得1分； 说明和截图完整，得以上的2分；否则，按不完整比例扣上步计算得分） (3) 使用pg_dumpall备份所有数据库（即business和newbusiness），然后在本机删除原有数据库（除postgres以外），用psgl还原所有数据库。 （ 要求截图删除前后的界面！ 若pg_dumpall备份正确，得1分； 若还原正确，得1分 说明和截图完整，得以上的2分；否则，按不完整比例扣上步计算得分） 5. 思考与体会（2分） （1）当两表建立了外键约束时，若删除被引用表，会发生什么？如何解决？ （2）pd_dumpall要求多次输入口令，如何能跳过输入口令？ （两个问题分别回答两点及以上且内容充实，得2分；否则，酌情扣分） 6. 实验体会（2分） （自己总结两点及以上，得2分；否则，酌情扣分）