数据库测试答案answer

数据库过程测试
1、以下图的数据库为例，用SQL完成以下检索：（3分/ 题）
仓库职工
订购单
供应商
1)检索在北京的供应商的名称。

SELECT * FROM 供应商WHERE 地址=’北京’
2)检索发给供应商S6的订购单号。

SELECT 订购单号FROM 订购单WHERE 供应商号=’S6’
3)检索出职工E6发给供应商S6的订购单信息。

SELECT * FROM 订购单WHERE 供应商号=’S6’ AND 职工号=’E6’
4)检索出向供应商S3发过订购单的职工的职工号和仓库号。

SELECT 职工号,仓库号FROM 职工WHERE 职工号IN ( SELECT 职工号FROM 订购单WHERE 供应商号=’S3’ )
5)检索出目前与S3供应商没有联系的职工信息。

SELECT * FROM 职工WHERE 职工号NOT IN
( SELECT 职工号FROM 订购单WHERE 供应商号=’S3’ )
6)检索出目前没有任何订购单的供应商信息。

1
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 工资< 1220))
10)检索出向S4供应商发出订购单的仓库所在的城市。

SELECT 城市FROM 仓库WHERE 仓库号IN
(SELECT 仓库号FROM 职工WHERE 职工号IN
(SELECT 职工号FROM 订购单WHERE 供应商号=’S4’))
11)检索出在上海工作并且向S6供应商发出了订购单的职工号。

SELECT 职工号FROM 职工WHERE 仓库号IN
(SELECT 仓库号FROM 仓库WHERE 城市=’上海’) AND 职工号IN
(SELECT 职工号FROM 订购单WHERE 供应商号=’S6’))
12)检索出在广州工作并且只向S6供应商发出了订购单的职工号。

SELECT 职工号FROM 职工WHERE 仓库号IN
(SELECT 仓库号FROM 仓库WHERE 城市=’广州’) AND 职工号IN
(SELECT 职工号FROM 订购单WHERE 供应商号=’S6’) AND 职工号NOT IN (SELECT 职工号FROM 订购单WHERE 供应商号!=’S6’)
13)检索出由工资多于1230元的职工向北京的供应商发出的订购单号。

SELECT 订购单号FROM 订购单WHERE 职工号IN
(SELECT 职工号FROM 职工WHERE 工资>1230) AND 供应商号IN
(SELECT 供应商号FROM 供应商WHERE 地址=’北京’)
14)检索出仓库的个数。

SELECT COUNT(*) FROM 仓库
15)检索出有最大面积的仓库信息。

SELECT * FROM 仓库WHERE 面积=(SELECT MAX(面积) FROM 仓库)
16)检索出所有仓库的平均面积。

SELECT A VG(面积) FROM 仓库
17)检索出向S4供应商发出订购单的那些仓库的平均面积。

SELECT A VG(面积) 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 订购单(职工号, 订购单号) V ALUES(‘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的面积。

UPDA TE 仓库SET 面积=面积+100 WHERE 城市=’北京’
5)给低于所有职工平均工资的职工提高5%的工资。

UPDA TE 职工SET 工资=工资*1.05 WHERE 工资<
(SELECT AVG(工资) FROM 职工)
3.在第1题的数据库中建立一个存储过程，查询工资大于某个值且在某个城市工作的职工信息；并
以工资大于8000元且在北京工作的职工为例执行该存储过程。

（10分）
3
CREATE PROC getemp(@salary int,@city char(8))
AS
SELECT * FROM 职工 WHERE 工资>@salary and仓库号IN
(SELECT 仓库号FROM 仓库WHERE 城市=@city)
EXECUTE getemp @salary =8000, @city=’北京’
4.在第1题的数据库中建立一个触发器，当删除仓库记录时同时删除该仓库的职工信息。

（5分）
CREATE TRIGGER wdel_tri
ON 仓库FOR DELETE
AS
DELETE 职工WHERE 仓库号IN
(select 仓库号from DELETED);
5.在第1题的数据库中建立一个触发器，当插入一个职工记录时，检查该职工所在的仓库是否存在，
若不存在则撤消所做的插入操作。

（5分）
CREATE TRIGGERempinsert_tri
ON职工FOR INSERT
AS
IF NOT EXISTS(SELECT * FROM 仓库WHERE 仓库号IN
(SELECT 仓库号FROM INSERTED)
BEGIN
PRINT ‘非法仓库号’;
ROLLBACK TRANSACTION;
END;。