大型数据库Oracle

金陵科技学院实验报告

ISBN

-------------------------------------------------- TITLE

--------------------------------------------------

AUTHOR PUBDATE PUBLISHER_ID

-------------------------------------------------- -------------- ------------ COST RETAIL CATEGORY

---------- ---------- -------------------------------------------------- 978-7-121-18619-8 文化基础

王澜 01-1月 -10 2 35 30 管理

(13)没有儿童或者烹饪类的书

SQL> select * from BOOKS where category='儿童'OR category='烹饪'; 未选定行

(14)没有第二个字母是A第四个字母是N的书名的书 SQL> select * from BOOKS where title LIKE '_A_N'; 未选定行

(15)没有电子工业出版社在2012年出版的书

SQL> select title from BOOKS,PUBLISHERS where PUBLISHERS.name='电子工业出版社'AND (pubdate>=to_date('2012-1-1','yyyy-mm-dd') AND pubdate<=to_date('2012-12-31',' yyyy-mm-dd')) AND category='计算机'; 未选定行 (16)

SQL> select title,name,contact,phone from BOOKS,PUBLISHERS where PUBLISHERS.publ isher_id=BOOKS.publisher_id;

TITLE

--------------------------------------------------

NAME CONTACT -------------------------------------------------- ---------- PHONE

-------------------------------------------------- 文化基础

机械工业出版社 孙翔 89673456

Oracle

电子工业出版社 张芳 56231234

31

金陵科技学院实验报告

TITLE

--------------------------------------------------

NAME CONTACT -------------------------------------------------- ---------- PHONE

-------------------------------------------------- (17) (18) (19)

SQL> select ISBN,title from CUSTOMERS,BOOKS,ORDERS where name='王牧'AND CUSTOMERS.customer_id=ORDERS.cutomer_id ;

ISBN

-------------------------------------------------- TITLE

-------------------------------------------------- 978-7-121-18619-8 文化基础

978-7-122-18619-8 Oracle (20) SQL> select name from PROMOTION,ORDERITEM,BOOKS where(ORDERITEM.ISBN=BOOKS.ISBN

AND title='Oracle' AND retail*quantity>=minretail AND retail*quantity<=maxretail );

NAME

-------------------- 签字笔

(21)没有张扬这个人

SQL> select author from BOOKS,CUSTOMERS,ORDERS,ORDERITEM where name='张扬'AND OR

DERS.cutomer_id=CUSTOMERS.customer_id AND ORDERS.order_id=ORDERITEM.order_id AND

ORDERITEM.ISBN=BOOKS.ISBN;

未选定行 (22) SQL> select count(order_id) from CUSTOMERS,ORDERS where ORDERS.cutomer_id=CUSTOM

32

金陵科技学院实验报告

ERS.customer_id GROUP BY CUSTOMERS.customer_id;

COUNT(ORDER_ID) ---------------

1 1 (23)

SQL> select * from BOOKS,(select category,min(retail) minretail from BOOKS GROUP BY category) b where BOOKS.category=b.category AND retail =minretail;

ISBN

-------------------------------------------------- TITLE

--------------------------------------------------

AUTHOR PUBLISHER_ID

-------------------------------------------------- -------------- ------------ COST RETAIL CATEGORY

---------- ---------- --------------------------------------------------

CATEGORY MINRETAIL -------------------------------------------------- ---------- 978-7-122-18619-8 Oracle

孙风栋 01-2月 -11

ISBN

-------------------------------------------------- TITLE

--------------------------------------------------

AUTHOR PUBLISHER_ID

-------------------------------------------------- -------------- ------------ COST RETAIL CATEGORY

---------- ---------- --------------------------------------------------

CATEGORY MINRETAIL -------------------------------------------------- ---------- 40 32 计算机

计算机 32

ISBN

-------------------------------------------------- TITLE

--------------------------------------------------

33

PUBDATE 1 PUBDATE 金陵科技学院实验报告

AUTHOR PUBDATE PUBLISHER_ID

-------------------------------------------------- -------------- ------------ COST RETAIL CATEGORY

---------- ---------- --------------------------------------------------

CATEGORY MINRETAIL -------------------------------------------------- ---------- 978-7-121-18619-8 文化基础

王澜 01-1月 -10 2

ISBN

-------------------------------------------------- TITLE

--------------------------------------------------

AUTHOR PUBDATE PUBLISHER_ID

-------------------------------------------------- -------------- ------------ COST RETAIL CATEGORY

---------- ---------- --------------------------------------------------

CATEGORY MINRETAIL -------------------------------------------------- ---------- 35 28 管理

管理 28 (24)

SQL> select avg(retail) avg,min(retail) min,max(retail) max from BOOKS GROUP BY publisher_id;

AVG MIN MAX ---------- ---------- ----------

32 32 32 28 28 28 (25)

SQL> select quantity,quantity*retail money from ORDERS,ORDERITEM,BOOKS where OR DERS.order_id=ORDERITEM.order_id AND ORDERITEM.ISBN=BOOKS.ISBN;

QUANTITY MONEY ---------- ----------

5 150 20 640 (26)

SQL> with iteminfo as

2 (select order_id,sum(quantity) sum from ORDERITEM group by order_id)

3 select iteminfo.order_id,cutomer_id from ORDERS,iteminfo where orders.order

34