综合练习2.txt

orale培训教材包括了所有的sql说明和实例

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;