create table book (
isbn char(13),--ISBN號
name varchar2(20),
zuozhe varchar2(40), --考慮合著
price number(6,2),
publisher varchar2(30),
shuliang number(8,0),
publishdate date);

--插入相應圖書的紀錄
insert into book values
('7-5053-6548-7','ORACLE入門','Tom',30.5,'清華大學出版社',1000,'16-7月 -02');

insert into book values
('7-5053-6548-6','ORACLE寶典','PETER CHEN,趙德奎',60,'清華大學出版社',1000,'16-7月 -02');

insert into book values
('7-5053-6548-5','oracle實用技巧','JACK',60,'北京大學出版社',1000,'16-7月 -01');

insert into book values
('7-5053-6548-4','JAVA入門','趙德奎',30,'清華大學出版社',1000,'10-7月 -02');

insert into book values
('7-5053-6548-3','JAVA技巧','趙德奎',60,'清華大學出版社',0,'16-1月 -01');

insert into book values
('7-5053-6548-2','oracle大全','JACK',40,'北京大學出版社',1000,'16-7月 -01');
---


--瀏覽本系統所有圖書，及其售價，按書名顯示
select name , price from book order by name;

--查詢(按ISBN號)
select * from book where isbn='7-5053-6548-2';

--查詢所有關于ORACLE的圖書，按出版時間先后顯示

select * from book where upper(name) like '%ORACLE%' order by publishdate;
--刪除所有已缺貨的圖書
delete from book where shuliang <=0;

--《Oracle寶典》這本書賣出3本，更新其庫存數量
update book set shuliang = shuliang-3 where name ='ORACLE寶典';

--查詢最新出版的關于oracle的圖書中最便宜的
select isbn, name ,price from book order by publishdate desc , price asc;

--查詢趙德奎 老師在清華大學出版社出版的書
select * from book where zuozhe like '%趙德奎%' and publisher ='清華大學出版社';


------------------------------------練習2----------------------------------
--《Oracle寶典》這本書降價，8折銷售，更改其價格為實際售價
update book set price=round(price*0.8,2) where name ='ORACLE寶典';

--全場8折，顯示所有圖書實際售價
select name ,price 原價,round(price*0.8,2) 現價 from book;

--查詢近20天內出版的最新圖書及其庫存量，按庫存量由小到大顯示。
select name ,shuliang from book where 
publishdate between sysdate and (sysdate-20) order by price;
--未選定行.注意 between必須小值在前.
select name ,shuliang ,publishdate from book where 
publishdate between (sysdate-20) and sysdate order by shuliang asc;

--querybook.sql
set echo off;
set verify off;
accept days prompt '要查詢當前多少日內的圖書？';
select * from book where publishdate between (sysdate-&days) and sysdate;
set verify on;
set echo on;

----getbook.sql
set echo off;
set verify off;
accept bookname prompt '想定購哪本書，輸入書名: ';
accept benshu prompt '想定購幾本書?輸入數字: ';
update book set shuliang=shuliang-&benshu where name ='&bookname';
set verify on;
set echo on;

--book.sql
@querybook.sql;
@getbook.sql;

--建立定購表
create table booked( 
isbn char(13),
benshu number(3,0), 
uername varchar2(20));

--getbook.sql擴展
----getbook.sql
set echo off;
set verify off;
accept bookname prompt '想定購哪本書，輸入書名: ';
accept benshu prompt '想定購幾本書?輸入數字: ';
insert into booked (select isbn, &benshu,user from book where name = '&bookname');
update book set shuliang=shuliang-&benshu where name ='&bookname';
commit;
set verify on;
set echo on;