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;
總結
以上是生活随笔為你收集整理的oracle语句借书,Oracle SQL题目及其解答（借书卡、图书、借书记录）的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： php对字符串进行编码,PHP如何使用c
- 下一篇： linux共享文件权限设置,linux