【Oracle实验 实验5 SQL语句应用】

实验5  SQL语句应用

1 实验目的

1. 掌握数据的插入、修改和删除操作。
2. 掌握不同类型的数据查询操作。

2 实验要求

1. 利用SQL语句对图书销售系统表进行插入、修改、删除和查询操作。

3 实验步骤

（1）以bs用户登录BOOKSALES数据库，将表1至表6中的数据插入到数据库的相应表中。 

        CUSTOMERS:

        insert into customers values(seq_customers.nextval,'王牧','83823422','Wangmu@snia.com',' 北京','110010');

        insert into customers values(seq_customers.nextval,'李青','83824566','Liqing@snia.com','大连','116023');

        PUBLISHERS:

        insert into publishers values(1,'电子工业出版社','张芳','56231234');

        insert into publishers values(2,'机械工业出版社','孙翔','89673456');

        ORDERS:

        insert into orders values(SEQ_ORDERS.nextval,1,to_date(TO_CHAR('2019-02-01'),'YYYY-MM-DD'),to_dat e(TO_CHAR('2019-02-05'),'YYYY-MM-DD'),'大连','116023');

        insert into orders values(SEQ_ORDERS.nextval,2,to_date(TO_CHAR('2019-03-01'),'YYYY-MM-DD'),to_dat e(TO_CHAR('2019-03-10'),'YYYY-MM-DD'),'大连','116023');

        ORDERITEM:

        insert into orderitem values(1000,1,'978-7-121-18619-8',5);

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

        UPDATE BOOKS

        SET RETRIL=30 WHERE ISBN='978-7-121-18619-8';

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

        UPDATE ORDERS

        SET SHIPDATE=to_date(TO_CHAR('2013-02-02'),'YYYY-MM-DD')

        WHERE ORDER_ID=1000;

（4）已知当前有一个图书表OLDPUBLISHERS，其表中数据如下：

publisher_id	name	contact	phone
1	电子工业出版社	李明	0531-86362790
2	机械工业出版社	孙浩	13786901456
3	人民邮电出版社	张春	3449876
4	传智播客	吴瑞	997990

请将表中的数据与PUBLISHERS中的数据进行合并。

        创建OLDPUBLISHERS:

        Create table OLDPUBLISHERS(

        publisher_id number(2) primary key,

        name VARCHAR2(50),

        contact char(10),

        phone VARCHAR2(50)

        )

        Tablespace BOOKTBS1;

        合并：

        Merge into publishers p using oldpublishers o

        on (p.publisher_id=o.publisher_id)

        When matched then update set

        p.name = o.name,p.contact = o.contact,p.phone= o.phone

        when not matched then insert

        Values (o.publisher_id,o.name,o.contact,o.phone);

        Select * From publishers;

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

        select title

        from books

        where category is not null;

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

        select title,pubdate "PublicationDate"

        from books;

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

        select customer_id,address

        from customers;

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

        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）查询图书名称、出版社名称、出版社联系人的名称和电话号码。

        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('2022-05-05','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 name

        from promotion

        Where(

        select quantity*books.retril

        from customers,books,orders,orderitem

        where

        customers.customer_id=orders.customer_id and orders.order_id=orderitem.order_id and

        orderitem.isbn=books.isbn and books.title='Oracle'

        ) >promotion.minretail;

（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,count(orders.order_id) as orders_count

        from customers,orders

        where customers.customer_id=orders.customer_id

        group by customers.customer_id,customers.name;

        图书数量：

        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 *

        from books b1

        where b1.retril<(select avg(b2.retril)

        From books b2

        where b1.category=b2.category

        );

        价格低于所有种类图书平均价格的图书：

        select *

        from books

        where books.retril<(select avg(books.retril)

        From books

        )；

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

        select publishers.name,avg(books.cost),min(books.cost),max(books.cost)

        from books,publishers

        where books.publisher_id=publishers.publisher_id

        group by name;

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

        select customers.name,sum(quantity),sum(quantity*books.retril)

        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）查询比1000号订单中图书数量多的其他订单信息。

        select order_id,sum(quantity)

        from orderitem

         group by order_id

        having sum(quantity) > (select sum(quantity)

        from orderitem g