Oracle数据库实验五SQL语句应用

Oracle数据库SQL语句应用

1. 以 bs 用户登录 BOOKSALES 数据库，将下列表（表 A-7 至表 A-12）中的数据插入 到数据库的相应表中

在插入第4第5个表的数据时可能会报违反完整约束条件 (BS.SYS_C0010865) - 未找到父项关键字的错误，最好先查询一下表一和表三。

insert into customers values (seq_customers.nextval,'王牧','83823422','Wangmu@sina. com','北京','110010');
insert into customers values (seq_customers.nextval,'李青','83824566','Liqing@sina. com','大连','116023');
insert into publishers values(1, '电子工业出版社', '张芳','56231234');
insert into publishers values(2, '机械工业出版社', '孙翔','89673456');
insert into books values('978-7-121-18619-8', '文化基础', '王澜',to_date('2010-1-1','yyyy-mm-dd'),2,28,35, '管理');
insert into books values('978-7-122-18619-8', 'Oracle', '孙风栋',to_date('2011-2-1','yyyy-mm-dd'),1,32,40, '计算机');
insert into orders values (SEQ_ORDERS.nextval,2,to_date('2013-2-1', 'yyyy-mm-dd'),to_date('2013-2-5','yyyy-mm-dd'), '大连','116023');
insert into orders values (SEQ_ORDERS.nextval,3,to_date('2013-3-1', 'yyyy-mm-dd'),to_date('2013-3-10','yyyy-mm-dd'), '大连','116023');
insert into orderitem values(1005,1,'978-7-121-18619-8',5);
insert into orderitem values(1005,2,'978-7-122-18619-8',20);
insert into orderitem values(1006,1,'978-7-121-18619-8',15);
insert into promotion values(1,'签字笔',100,150);
insert into promotion values(2,'笔记本',151,300);
insert into promotion values(3,'保温杯',301,500);
insert into promotion values(4,'电饭煲',501,999);

（2）将 ISBN 为 978-7-121-18619-8 的图书的零售价格（retail）修改为 30。

update books set retail=30 where isbn='978-7-121-18619-8';

（3）将订单号为 1000 的订单的发货日期修改为“2013-2-2” 。

update orders set shipdate=to_date('2013-2-2','yyyy-mm-dd') where order_id=1005;

（4）查询 BOOKS 表中包含的所有图书列表。

Select * from books;

（5）列出 BOOKS 表中有图书类型非空的图书书名。

select title from books where category is not null;

（6）列出 BOOKS 表中每本书的书名和出版日期。对 pubdate 字段使用 PublicationDate 列 标题。

select title,pubdate "PublicationDate" from books; 

（7）列出 CUSTOMERS 表中每一个客户的客户号以及他们所在的地址。

select customer_id,address from customers;

（8）创建一个包含各个出版社的名称、联系人以及出版社电话号码的列表。其中，联系人 的列在显示的结果中重命名为 ContactPerson。

select name,contact "ContactPerson",phone from publishers;

（9）查询下达了订单的每一个客户的客户号。

select customer_id from orders where order_id is not null;

（10）查询 2013 年 3 月 1 日之后发货的订单。

select * from orders where shipdate > to_date('2013-3-1','yyyy-mm-dd');

（11）查询居住在北京或大连的客户，将结果按姓名的升序排列。

select * from customers where address='大连' or address='北京' order by name;

（12）列出姓“王”的作者编写的所有图书信息，并将结果按姓名降序排序。

select * from books where author like '王%' order by author;

（13）查询“儿童”类和“烹饪”类的所有图书。

select * from books where category='儿童' or category='烹饪';

（14）查询书名的第二个字母是“A”、第四个字母是“N”的图书信息。

select * from books where title like '_A_N%';

（15）查询电子工业出版社在 2012 年出版的所有“计算机”类图书的名称。

select title from books where publisher_id=(select publisher_id from publishers where name='电子工 业出版社')and extract(year from pubdate)='2012' and category='计算机';

（16）查询图书名称、出版社名称、出版社联系人的名称、E-mail 和电话号码。

select title,name,contact,phone from books,publishers
where books.publisher_id=publishers.publisher_id;

（17）查询当前还没有发货的订单信息及下达订单的用户名，查询结果按下达订单日期排序。

select order_id,customers.name,orderdate,shipdate,shipaddress,shipcode
from customers,orders where customers.customer_id=orders.customer_id and
shipdate<=to_date('2013-3-1','yyyy-mm-dd') order by orderdate;

（18）查询已经购买了“计算机”类图书的所有人的客户号和姓名。

select customers.customer_id,customers.name from customers,orders,orderitem,books where customers.customer_id = orders.customer_id 
and orders.order_id=orderitem.order_id and
orderitem.isbn=books.isbn and books.category='计算机';

（19）查询“王牧”购买的图书的 ISBN 以及书名。

select books.isbn,books.title from books,customers,orders,orderitem where
customers.customer_id=orders.customer_id and orders.order_id=orderitem.order_id and
orderitem.isbn=books.isbn and customers.name='王牧';

（20）查询订购图书“Oracle”的客户将收到什么样的礼品。

select customer_id,name from promotion join
(select customer_id,sum(quantity*retail) total
from orders join orderitem on orders.order_id=orderitem.order_id join books on orderitem.isbn=books.isbn
where customer_id in(select customer_id from orders where order_id in(select order_id from
orderitem where isbn='978-7-122-18619-8'))group by customer_id)detail on total between minretail and maxretail;

（21）确定客户“张扬”订购的图书的作者。

select books.author from books,orderitem,orders,customers 
where books.isbn=orderitem.isbn
and orderitem.order_id=orders.order_id and orders.customer_id=customers.customer_id and customers.name='张扬';

（22）查询 CUSTOMERS 表中的每一个客户所下达的订单数量。

select customers.customer_id,customers.name,books.title,orderitem.quantity from
customers,orders,books,orderitem where
customers.customer_id=orders.customer_id and
orders.order_id=orderitem.order_id and orderitem.isbn=books.isbn;

（23）查询价格低于同一种类中其他图书的平均价格的图书的信息。

select isbn,title,retail,author,category from books where retail<(select avg(retail) from
books);

（24）查询每个出版社出版图书的平均价格、高价格、低价格。

select category,min(retail),max(retail),avg(retail) from books group by category;

（25）统计每个客户购买图书的数量及总价钱。

select customers.name,sum(quantity),sum(quantity*cost) from
customers,books,orders,orderitem where
customers.customer_id=orders.customer_id and orders.order_id=orderitem.order_id and
orderitem.isbn=books.isbn group by customers.name;

（26）查询比 1005号订单中图书数量多的其他订单信息。

select order_id,sum(quantity) from orderitem group by order_id
having sum(quantity) > (select sum(quantity) from orderitem group by order_id
having order_id=1005);

（27）查询所以客户及其订购图书的信息。

select * from customers,orders,books,orderitem where 
customers.customer_id=orders.customer_id and
orders.order_id=orderitem.order_id and orderitem.isbn=books.isbn;

（28）查询没有订购任何图书的客户信息。

select customers.customer_id from customers,orders,orderitem where 
customers.customer_id=orders.customer_id
and orders.order_id=orderitem.order_id and orders.order_id is null;

（29）查询订购金额高的客户信息。

select 
customers.customer_id,customers.name,customers.phone,customers.email,customers.address,customers.code
from customers,orders,orderitem where customers.customer_id=orders.customer_id and
orders.order_id=orderitem.order_id and orderitem.quantity=(select max(quantity) from orderitem);

（30）查询名为“赵敏”的客户订购图书的订单信息、订单明细。

select * from customers,orders where orders.customer_id = customers.customer_id and customers.name='赵敏';

MJE小记录