测试题

的数据库，由下面三个表 组成：
employee（empid, empname, age, address, sal）
department（deptno, dname, mgr）
work（empid, deptno, worktime）

用SQL语句求同时在三个部门工作的雇员姓名

用SQL语句求购买了供应商"宝洁"产品的所有顾客
select * from customer where customerid in (select distinct customerid from purchase, product where purchase.productid=product.productid and provider='宝洁');

现有一个商店的数据库，记录顾客及其购物情况，由 下面三个表组成：
Product(productid, productname, unitprice, category, provider) Customer(customerid, name，location)
Purcase(customerid, productid, quantity)
CREATE OR REPLACE TRIGGER tri_batchkc AFTER DELETE OR UPDATE ON batchkc FOR EACH ROW DECLARE v_num NUMBER; BEGIN IF DELETING THEN UPDATE kc SET num=num-:old.bnum WHERE id=:old.id; END IF; SELECT num INTO v_num FROM kc WHERE id=:old.id; IF v_num=0 THEN DELETE FROM kc WHERE id=:old.id; END IF; IF UPDATING THEN UPDATE kc SET num=num-(:old.bnum-:new.bnum) WHERE id=:old.id; END IF; END; /
select empname from employee where empid in ( select empid from (select empid, count(*) c from work group by empid) where c=3 )
CREATE OR REPLACE PROCEDURE pro_plane(v_route VARCHAR2, v_num NUMBER) AS CURSOR cur_ticket(v_p_route) IS SELECT num FROM ticket WHERE route = v_p_route FOR UPDATE; BEGIN OPEN cur_ticket(v_route); IF cur_ticket.num < v_num THEN DBMS_OUTPUT.PUT_LINE('Not Enough Ticket Available!'); ELSE UPDATE ticket SET num=num-v_num WHERE CURRENT OF cur_ticket; END IF; CLOSE cur_ticket; COMMIT; END; /
ORACLE测试题
软件学院
赵明
B
B
B
B
ACD
C
C
D

医院药品都实行效期管理，效期早的药品先使用，请 设计一个程序来按效期使用药品。要求:
使用触发器来维护两个表之间的库存的一致性 对药品出库时,能正确选择批次

药品库存表kc create table kc( id number(2) primary key, --药品id ypname varchar2(20),--药品名称 num number(5) --数量 ); insert into kc values (1,'青霉素',3000); insert into kc values (2,'双黄连',4000); 药品批次库存表batchkc create table batchkc( id number(2) references kc(id), --药品id validdate date, --到期时间 bnum number(5), --数量 primary key (id,validdate) ); insert into batchkc values (1,'01-5月-04',2500); insert into batchkc values (1,'01-12月-04',500); insert into batchkc values (2,'12-1月-04',1000); insert into batchkc values (2,'07-6月-04',1000); insert into batchkc values (2,'13-8月-04',1000); insert into batchkc values (2,'13-12月-05',1000);
出库青霉素2700支
1,'01-5月-04',2500
1,'01-12月-04',200
create or replace procedure deliver(v_name VARCHAR2, v_num NUMBER) as v_kc NUMBER; v_left NUMBER; v_p_id NUMBER; cursor cur_batchkc(v_id NUMBER) is select bnum from batchkc where id=v_id order by validdate for update; begin select num, id into v_kc, v_p_id from kc where ypname=v_name; if v_kc >= v_num then v_left := v_num; for iter in cur_batchkc(v_p_id) loop if iter.bnum > v_left then update batchkc set bnum=bnum-v_left where current of cur_batchkc; EXIT; elsif iter.bnum = v_num then delete from batchkc where current of cur_batchkc; EXIT; else delete from batchkc where current of cur_batchkc; v_left := v_left - iter.bnum; end if; end loop; else DBMS_OUTPUT.PUT_LINE('Not Enough in Inventory!'); end if; end; /

“北京到香港”的飞机票目前还剩3张，假设现在有 4个订票点都同时要订“北京到香港”的机票，那么 必然至少有一个订票点不能订到票，请设计一个订票 程序来保证机票的正确订出。
订票表的结构 create table ticket( id number(3) primary key, --唯一id route varchar2(20) not null, --航线名称 num number(3) --票的余数 ); insert into ticket values (1,'北京到香港',3);