ORACLE数据库测试1及答案

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 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 工资>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 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)

as

begin

select * from 职工from 工资>salsry and 仓库号in

(select 仓库号from 仓库where 城市=citu );

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;