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,publisherswhere books.publisher_id=publishers.publisher_id;

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

select order_id,customers.name,orderdate,shipdate,shipaddress,shipcodefrom customers,orders where customers.customer_id=orders.customer_id andshipdate<=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_idand orders.order_id=orderitem.order_id andorderitem.isbn=books.isbn and books.category=\'计算机\';

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

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

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

select customer_id,name from promotion join(select customer_id,sum(quantity*retail) totalfrom orders join orderitem on orders.order_id=orderitem.order_id join books on orderitem.isbn=books.isbnwhere customer_id in(select customer_id from orders where order_id in(select order_id fromorderitem 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,customerswhere books.isbn=orderitem.isbnand 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 fromcustomers,orders,books,orderitem wherecustomers.customer_id=orders.customer_id andorders.order_id=orderitem.order_id and orderitem.isbn=books.isbn;

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

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

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

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

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

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

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

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

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

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

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

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

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

selectcustomers.customer_id,customers.name,customers.phone,customers.email,customers.address,customers.codefrom customers,orders,orderitem where customers.customer_id=orders.customer_id andorders.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小记录