ORACLE数据库过程测试11、用SQL完成以下表格的创建：（5分*4=20分）1）仓库（仓库号，城市，面积），其中仓库号为主键；create table 仓库(仓库号char(4) primary key,城市char(6),面积number(8,2));2）职工（仓库号，职工号，工资），其中职工号为主键，仓库号为外键；create table 职工(仓库号char(4) foreign key references 仓库,职工号char(4) primary key,工资number(8,2));3）供应商（供应商号，供应商名，地址），其中供应商号为主键；create table 供应商(供应商号char(3) primary key,供应商名char(4),地址char(6));4）订购单（职工号，供应商号，订购单号，订购日期），其中订购单号为主键，供应商号和职工号为外键。

create table 订购单(订购单号char(4) primary key,经手人char(4) foreign key references 职工(职工号),供应商号char(3) foreign key references 供应商,订购日期date);2、以前面表为例，用SQL完成以下检索：（2分*20=40分）1)检索在北京的供应商的名称。

select 供应商名from 供应商where 地址=’北京’;2)检索发给供应商S6的订购单号。

select 订购单号from 订购单where 供应商号=’S6’;3)检索出职工E6发给供应商S6的订购单信息。

select * from 订购单where 经手人=’E6’ and 供应商=’S6’;4)检索出向供应商S3发过订购单的职工的职工号和仓库号。

①select 职工号,仓库号from 职工where 职工号in(select 经手人from 订购单where 供应商号=’S3’ );②select 职工号,仓库号from 职工where exists(select 经手人from 订购单where 供应商号=’S3’ );③自然连接select 职工号,仓库号from 职工as E,订购单as O whereE.职工号=O.经手人and 供应商号=’S3’;④join内连接select 职工号,仓库号from 职工as E join 订购单as Oon E.职工号=O.经手人where 供应商号=’S3’;5)检索出目前与S3供应商没有联系的职工信息。

select * from 职工where 职工号not in(select 经手人from 订购单where 订购单.经手人=职工.职工号and 供应商号=’S3’);6)检索出目前没有任何订购单的供应商信息。

①select * from 供应商where 供应商号not in( select 供应商号from 订购单where 供应商号is not null);②select * from 供应商where not exists(select * from 订购单where 供应商号=供应商.供应商号);7)检索出和职工E1、E3都有联系的北京的供应商信息。

①select * from 供应商where 地址=’北京’and 供应商号in(select 供应商号from 订购单where 经手人=’E1’)and 供应商号in(select 供应商号from 订购单where 经手人=’E3’);②select * from 供应商where 地址=’北京’and 供应商号in(select A.供应商号from 订购单as A,订购单as Bwhere A.供应商号=B.供应商号and A.供应商号=’E1’and B.供应商号=’E3’);8)检索出目前和华通电子公司有业务联系的每个职工的工资。

select * from 职工where 职工号in(select 经手人from 订购单where 供应商号in(select 供应商号from 供应商where 供应商名=’华通电子公司’));9)检索出与工资在1220元以下的职工没有联系的供应商的名称。

select 供应商名from 供应商where 供应商号not in(select 供应商号from 订购单where 供应商号is not nulland 经手人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 地址=’北京’)and 职工号in( select 职工号from 职工where 工资>1230);14)检索出仓库的个数。

select count(*) as 仓库个数from 仓库;15)检索出有最大面积的仓库信息。

select * from 仓库where 面积=(select MAX(面积) from 仓库);16)检索出所有仓库的平均面积。

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

select A VG(面积) as 平均面积from 仓库where 仓库号in (select 仓库号from 职工where 职工号in(select 经手人from 订购单where 供应商号=’S4’));18)检索出每个仓库中工资多于1220元的职工个数。

select 仓库号,count(*) as 职工个数from 职工where 工资>1220group by 仓库号;19)检索出和面积最小的仓库有联系的供应商的个数。

select count(*) as 供应商个数from 供应商where 供应商号in(select 供应商号from订购单where 经手人in(select 职工号from 职工where 仓库号in(select 仓库号from 仓库where 面积=(select MIN(面积) from 仓库))))20)检索出工资低于本仓库平均工资的职工信息。

select * from 职工as A where 工资<(select A VG(工资) from 职工as B where B.职工号=A.职工号);3、以上图的数据库为例，用SQL语句完成操作：（2分*5=10分）1)插入一个新的订购单元组，其中职工号为E6，订购单号为OR99，其他列为空。

2)删除目前没有任何订购单的供应商。

3)删除由在上海仓库工作的职工发出的所有订购单。

4)北京的所有仓库增加100m2的面积。

5)给低于所有职工平均工资的职工提高5%的工资。

解：1）insert into 订购单values (‘OR99’,’E6’,null,null);2) delect from 供应商where 供应商号not in(select 供应商号from 订购单where 供应商号is not null);3）①delect from 订购单where 职工号in(select 职工号from 职工where 仓库号in(select 仓库号from 仓库where 城市=’上海’));②delect [from] 订购单from 职工,仓库where 订购单.职工号=职工.职工号and 职工.仓库号=仓库.仓库号and城市=’上海’;注：第一个from可省略4）update 仓库set 面积=面积+100 where 城市=’北京’;5）update 职工set 工资=工资*1.05 where 工资<(select A VG(工资) from 职工);4、在第1题的数据库中建立一个存储过程，查询工资大于某个值且在某个城市工作的职工信息；并以工资大于8000元且在北京工作的职工为例执行该存储过程。

（10分）解：create or replace procedure pro(salary in number,city in number)asbeginselect * from 职工from 工资>salsry and 仓库号in(select 仓库号from 仓库where 城市=citu );end;--执行存储过程exec proc(salary=>8000,city=’北京’);5、在第1题的数据库中建立一个触发器，当删除仓库记录时同时删除该仓库的职工信息。

（10分）解：create or replace trigger triafter delete on 仓库for each rowbegindelete from 职工where 仓库号=::OLD.仓库号;end;6、在第1题的数据库中建立一个触发器，当插入一个职工记录时，检查该职工所在的仓库是否存在，若不存在则撤消所做的插入操作。

（10分）解：create or replace trigger 职工1after insert or update on 职工for each rowdeclaren number;beginselect count(*) into n from 仓库where 仓库号=:NEW仓库号if n=0 thendbms_output.put_line(‘职工所在仓库不存在’);rollback;end if;end;。