数据库测试1答案answer.doc

数据库过程测试1、 以下图的数据库为例，用SQL完成以下检索： （3分/ 题）仓库 职工 订购单供应商1)检索在北京的供应商的名称。SELECT * FROM 供应商 WHERE 地址=北京2)检索发给供应商S6的订购单号。SELECT 订购单号 FROM 订购单 WHERE 供应商号=S63)检索出职工E6发给供应商S6的订购单信息。SELECT * FROM 订购单 WHERE 供应商号=S6 AND 职工号=E64)检索出向供应商S3发过订购单的职工的职工号和仓库号。SELECT 职工号,仓库号 FROM 职工 WHERE 职工号 IN( SELECT 职工号 FROM 订购单 WHERE 供应商号=S3 )5)检索出目前与S3供应商没有联系的职工信息。SELECT * FROM 职工 WHERE 职工号 NOT IN( SELECT 职工号 FROM 订购单 WHERE 供应商号=S3 )6)检索出目前没有任何订购单的供应商信息。SELECT * FROM 供应商 WHERE NOT EXISTS( SELECT * FROM 订购单 WHERE 供应商号=供应商.供应商号 )7)检索出和职工E1、E3都有联系的北京的供应商信息。SELECT * FROM 供应商 WHERE 供应商号 IN( SELECT 供应商号 FROM 订购单 WHERE 职工号=E1 )AND 供应商号 IN( SELECT 供应商号 FROM 订购单 WHERE 职工号=E3 )8)检索出目前和华通电子公司有业务联系的每个职工的工资。SELECT 职工号,工资 FROM 职工 WHERE 职工号 IN(SELECT 职工号 FROM 订购单 WHERE 供应商号 IN(SELECT 供应商号 FROM 供应商 WHERE 供应商名=华通电子公司)9)检索出与工资在1220元以下的职工没有联系的供应商的名称。SELECT 供应商名 FROM 供应商 WHERE 供应商号 IN(SELECT 供应商号 FROM 订购单 WHERE 职工号 NOT IN(SELECT 职工号 FROM 职工 WHERE 工资 1230) AND 供应商号 IN(SELECT 供应商号 FROM 供应商 WHERE 地址=北京)14)检索出仓库的个数。SELECT COUNT(*) FROM 仓库15)检索出有最大面积的仓库信息。SELECT * FROM 仓库 WHERE 面积=(SELECT MAX(面积) FROM 仓库)16)检索出所有仓库的平均面积。SELECT AVG(面积) FROM 仓库17)检索出向S4供应商发出订购单的那些仓库的平均面积。SELECT AVG(面积) FROM 仓库 WHERE 仓库号 IN(SELECT 仓库号 FROM 职工 WHERE 职工号 IN(SELECT 职工号 FROM 订购单 WHERE 供应商号=S4)18)检索出每个仓库中工资多于1220元的职工个数。SELECT 仓库号,COUNT(*) FROM 职工 WHERE 工资1220 GROUP BY 仓库号19)检索出和面积最小的仓库有联系的供应商的个数。SELECT COUNT(*) FROM 供应商 WHERE 供应商号 IN(SELECT 供应商号 FROM 订购单 WHERE 职工号 IN(SELECT 职工号 FROM 职工 WHERE 仓库号 IN(SELECT 仓库号 FROM 仓库 WHERE 面积=(SELECT MIN(面积) FROM 仓库)20)检索出工资低于本仓库平均工资的职工信息。SELECT * FROM 职工 out WHERE 工资(SELECT AVG(工资) FROM 职工 inne WHERE 仓库号=out.仓库号)2. 以上图的数据库为例，用SQL语句完成以下更新操作，并讨论数据完整性约束对这些操作的影响：（4分/ 题）1) 插入一个新的订购单元组，其中职工号为E6，订购单号为OR99，其他列为空。INSERT INTO 订购单(职工号, 订购单号) VALUES(E6, OR99)2) 删除目前没有任何订购单的供应商。DELETE FROM 供应商 WHERE 供应商号 NOT IN(SELECT 供应商号 FROM 订购单 WHERE 供应商号 IS NOT NULL)3) 删除由在上海仓库工作的职工发出的所有订购单。DELETE FROM 订购单 WHERE 职工号 IN(SELECT 职工号 FROM 职工 WHERE 仓库号 IN(SELECT 仓库号 FROM 仓库 WHERE 城市=上海)4) 北京的所有仓库增加100m2的面积。UPDATE 仓库 SET 面积=面积+100 WHERE 城市=北京5) 给低于所有职工平均工资的职工提高5%的工资。UPDATE 职工 SET 工资=工资*1.05 WHERE 工资salary and仓库号 IN(SELECT 仓库号 FROM 仓库 WHERE 城市=city)EXECUTE getemp salary =8000, city=北京4. 在第1题的数据库中建立一个触发器，当删除仓库记录时同时删除该仓库的职工信息。（5分）CREATE TRIGGER wdel_triON 仓库 FOR DELETEASDELETE 职工 WHERE 仓库号 IN(select 仓库号 from DELETED);5. 在第1题的数据库中建立一个触发器，当插入一个职工记录时，检查该职工所在的仓库是否存在，若不存在则撤消所做的插入操作。（5分）CREATE TRIGGER empinsert_triON职工