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<sysdate;

--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;