?? 綜合練習2.txt
字號:
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;
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -