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小记录