内容发布更新时间 : 2024/10/29 7:24:29星期一 下面是文章的全部内容请认真阅读。
ORACLE数据库过程测试1
1、 用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 where E.职工号=O.经手人 and 供应商号=’S3’;
④join内连接
select 职工号,仓库号 from 职工 as E join 订购单 as O on 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 B where 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 null and 经手人 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 AVG(面积) as 平均面积 from 仓库;
17)检索出向S4供应商发出订购单的那些仓库的平均面积。 select AVG(面积) as 平均面积 from 仓库 where 仓库号 in (select 仓库号 from 职工 where 职工号 in
(select 经手人 from 订购单 where 供应商号=’S4’));
18)检索出每个仓库中工资多于1220元的职工个数。
select 仓库号,count(*) as 职工个数 from 职工 where 工资>1220 group 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 AVG(工资) 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 AVG(工资) from 职工);
4、 在第1题的数据库中建立一个存储过程，查询工资大于某个值且在某个城市工作的职工
信息；并以工资大于8000元且在北京工作的职工为例执行该存储过程。（10分） 解：create or replace procedure pro
(salary in number,city in number) as begin
select * from 职工 from 工资>salsry and 仓库号 in (select 仓库号 from 仓库 where 城市=city ); end;
--执行存储过程
exec proc(salary=>8000,city=’北京’);
5、 在第1题的数据库中建立一个触发器，当删除仓库记录时同时删除该仓库的职工信息。
（10分）
解：create or replace trigger tri
after delete on 仓库 for each row begin
delete from 职工 where 仓库号=::OLD.仓库号; end;
6、 在第1题的数据库中建立一个触发器，当插入一个职工记录时，检查该职工所在的仓库
是否存在，若不存在则撤消所做的插入操作。（10分） 解：create or replace trigger 职工1
after insert or update on 职工 for each row declare
n number; begin
select count(*) into n from 仓库 where 仓库号=:NEW仓库号 if n=0 then
dbms_output.put_line(‘职工所在仓库不存在’); rollback; end if; end;