oracle语句借书,Oracle SQL题目及其解答（借书卡、图书、借书记录）

题目来源于：http://blog.csdn.net/lifetragedy/article/details/10305735

/*下面是一个基于图书系统的15道SQL问答，供大家参考

问题描述：

本题用到下面三个关系表：

T_CARD 借书卡。 CNO 卡号，NAME 姓名，CLASS 班级

T_BOOKS 图书。 BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数

T_BORROW 借书记录。 CNO 借书卡号，BNO 书号，RDATE 还书日期

备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。

要求实现如下15个处理：

*/

--1． 写出建立T_BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束。

/*

create table T_CARD

(

cno number(10) primary key,

name varchar2(100),

class varchar2(100)

);

create table T_BOOKS

(

bno number(10) primary key,

name varchar2(100),

author varchar2(100),

price number(10,2),

quantity number(10)

);

create table T_BORROW

(

cno number(10),

bno number(10),

rdate date

);

alter table T_BORROW

add constraint borrow_pk primary key (cno, bno);

alter table T_BORROW

add constraint boorow_fk_cno foreign key (cno) references T_CARD(cno);

alter table T_BORROW

add constraint boorow_fk_bno foreign key (bno) references T_BOOKS(bno);

insert into T_CARD VALUES(1, 'bryant', 'class_1');

insert into T_CARD VALUES(2, 'foxus', 'class_1');

insert into T_CARD VALUES(3, 'ennel', 'class_2');

insert into T_CARD VALUES(4, 'keliy', 'class_3');

insert into T_CARD VALUES(5, 'cinal', 'class_2');

insert into T_CARD VALUES(6, 'oopp', 'class_3');

INSERT INTO T_BOOKS VALUES(1, 'Chinese Book', 'Mr.mao', 35.8, 20);

INSERT INTO T_BOOKS VALUES(2, 'Math Book', 'Mr.xiao', 55.4, 20);

INSERT INTO T_BOOKS VALUES(3, 'English Book', 'Mr.li', 22.6, 20);

INSERT INTO T_BOOKS VALUES(4, 'Computer Book', 'Mr.yang', 78.8, 15);

INSERT INTO T_BOOKS VALUES(5, 'Music Book', 'Mr.wang', 25.3, 15);

INSERT INTO T_BOOKS VALUES(6, 'History Book', 'Mr.mao', 40.8, 12);

INSERT INTO T_BOOKS VALUES(7, 'Physics Book', 'Mr.tang', 46.6, 10);

INSERT INTO T_BOOKS VALUES(8, 'Chemistry Book', 'Mr.zou', 33.9, 10);

INSERT INTO T_BOOKS VALUES(9, 'Biology Book', 'Mr.tu', 23, 10);

INSERT INTO T_BOOKS VALUES(10, 'Political Book', 'Mr.ke', 36.2, 10);

insert into T_BORROW values(1, 1, to_date('2015-5-21', 'yyyy-MM-dd'));

insert into T_BORROW values(2, 1, to_date('2015-5-21', 'yyyy-MM-dd'));

insert into T_BORROW values(3, 3, to_date('2015-5-28', 'yyyy-MM-dd'));

insert into T_BORROW values(4, 8, to_date('2015-6-21', 'yyyy-MM-dd'));

insert into T_BORROW values(5, 4, to_date('2015-5-11', 'yyyy-MM-dd'));

insert into T_BORROW values(6, 10, to_date('2015-5-31', 'yyyy-MM-dd'));

insert into T_BORROW values(1, 9, to_date('2015-6-10', 'yyyy-MM-dd'));

insert into T_BORROW values(2, 3, to_date('2015-7-2', 'yyyy-MM-dd'));

insert into T_BORROW values(3, 5, to_date('2015-6-5', 'yyyy-MM-dd'));

insert into T_BORROW values(4, 6, to_date('2015-6-2', 'yyyy-MM-dd'));

insert into T_BORROW values(5, 9, to_date('2015-5-22', 'yyyy-MM-dd'));

insert into T_BORROW values(6, 1, to_date('2015-6-1', 'yyyy-MM-dd'));

insert into T_BORROW values(1, 3, to_date('2015-6-1', 'yyyy-MM-dd'));

insert into T_BORROW values(2, 4, to_date('2015-6-15', 'yyyy-MM-dd'));

insert into T_BORROW values(3, 8, to_date('2015-5-15', 'yyyy-MM-dd'));

insert into T_BORROW values(4, 10, to_date('2015-6-22', 'yyyy-MM-dd'));

insert into T_BORROW values(5, 7, to_date('2015-6-13', 'yyyy-MM-dd'));

insert into T_BORROW values(6, 6, to_date('2015-5-18', 'yyyy-MM-dd'));

insert into T_BORROW values(1, 8, to_date('2015-5-19', 'yyyy-MM-dd'));

insert into T_BORROW values(2, 9, to_date('2015-5-20', 'yyyy-MM-dd'));

insert into T_BORROW values(3, 6, to_date('2015-6-15', 'yyyy-MM-dd'));

insert into T_BORROW values(4, 2, to_date('2015-6-15', 'yyyy-MM-dd'));

insert into T_BORROW values(5, 2, to_date('2015-6-15', 'yyyy-MM-dd'));

调整库存数

update T_BOOKS b set b.quantity=b.quantity-(select count(*) from T_BORROW where bno=b.bno);

*/

-- 2． 找出借书超过3本的读者,输出借书卡号及所借图书册数。

select cno, count(*) cs from t_borrow br group by cno having count(*) > 3;

--3． 查询借阅了"Chinese Book"一书的读者，输出姓名及班级。

select c.cno,c.name cname, c.class classname from t_card c, t_books b, t_borrow bo where c.cno=bo.cno and b.bno=bo.bno and b.name='Chinese Book';

--4． 查询过期未还图书，输出借阅者(卡号)、书号及还书日期。

select cno, bno, rdate from t_borrow where rdate

--5． 查询书名包括"Chinese"关键词的图书，输出书号、书名、作者。

select bno, name, author from t_books where name like '%Chinese%';

--6． 查询现有图书中价格最高的图书，输出书名及作者。

select name, author from t_books where price =(select max(price) from t_books);

--7． 查询当前借了"Chinese Book"但没有借"English Book"的读者，输出其借书卡号，并按卡号降序排序输出。

select cno from t_borrow br2 where exists

(select cno from t_borrow br, t_books b where b.name='Chinese Book' and br.bno=b.bno and br.cno=br2.cno)

and not exists

(select cno from t_borrow br, t_books b where b.name='English Book' and br.bno=b.bno and br.cno=br2.cno)

group by cno order by cno desc;

select cno from t_borrow br, t_books b where b.name='Chinese Book' and br.bno=b.bno

intersect

select cno from t_borrow where cno not in (select cno from t_borrow br, t_books b where b.name='English Book' and br.bno=b.bno) group by cno;

select br.cno from t_borrow br, t_books bk where br.bno=bk.bno and bk.name='Chinese Book' and not exists

(select * from t_borrow br1, t_books bk1 where br1.bno=bk1.bno and bk1.name='English Book' and br.cno=br1.cno)

order by br.cno desc;

-- 8． 将"class_1"班同学所借图书的还期都延长一周。

update t_borrow br set br.rdate = br.rdate+7 where exists (select cno from t_card where class='class_1' and cno=br.cno);

-- 9． 从T_BOOKS表中删除当前无人借阅的图书记录。

delete t_books b where not exists (select bno from t_borrow where bno=b.bno);

-- 10．如果经常按书名查询图书信息，请建立合适的索引。

create index index_t_books_name on t_books(name);

--11．在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是"Computer Book"，

--就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)。

create table T_BORROW_SAVE(cno number(10),bno number(10), rdate date);

create or replace trigger trg_borrow

after insert

on t_borrow

for each row

begin

if :new.bno=4 then

insert into t_borrow_save (cno, bno, rdate)

values(:new.cno, :new.bno, :new.rdate);

end if;

end;

drop trigger trg_borrow;

insert into t_borrow values(3, 4, sysdate);

select * from t_borrow where cno=3;

select * from t_borrow_save ;

CREATE OR REPLACE TRIGGER tr_del_emp

BEFORE DELETE --指定触发时机为删除操作前触发

ON scott.emp

FOR EACH ROW --说明创建的是行级触发器

BEGIN

--将修改前数据插入到日志记录表 del_emp ,以供监督使用。

INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )

VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );

END;

DELETE emp WHERE empno=7788;

DROP TABLE emp_his;

DROP TRIGGER del_emp;

-- 12．建立一个视图，显示"力01"班学生的借书信息(只要求显示姓名和书名)。

create view view_t_borrow as

select c.name cname, b.name bname from t_borrow br, t_card c, t_books b where br.cno=c.cno and br.bno=b.bno and c.class='class_1';

select * from view_t_borrow;

--13. 查询当前同时借有"Computer Book"和"Math Book"两本书的读者，输出其借书卡号，并按卡号升序排序输出。

select cno from t_borrow br, t_books b where br.bno=b.bno and b.name='Computer Book'

intersect

select cno from t_borrow br, t_books b where br.bno=b.bno and b.name='Math Book' order by cno;

SELECT a.CNO FROM T_BORROW a, T_BOOKS b WHERE a.BNO = b.BNO

AND b.NAME IN ('Computer Book', 'Math Book')

GROUP BY a.CNO

HAVING COUNT(*) = 2

ORDER BY a.CNO DESC

--14．假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句。

alter table t_books

add constraint pk_t_books primary key (bno);

/*15．对CARD表做如下修改：

a. 将NAME最大列宽增加到10个字符(假定原为6个字符)。

b. 为该表增加1列NAME(系名)，可变长，最大20个字符。

*/

alter table T_card

modify name varchar2(50);

alter table T_card

add name1 varchar2(20);

alter table t_Card

drop column name1;