实验五 SQL语句应用.docx
《实验五 SQL语句应用.docx》由会员分享,可在线阅读,更多相关《实验五 SQL语句应用.docx(15页珍藏版)》请在冰点文库上搜索。
实验五SQL语句应用
实验5SQL语句应用
1实验目的
(1)掌握数据的插入、修改和删除操作。
(2)掌握不同类型的数据查询操作。
2实验要求
(1)利用SQL语句对图书销售系统表进行插入、修改、删除和查询操作。
3实验步骤
(1)以bs用户登录BOOKSALES数据库,将表1至表6中的数据插入到数据库的相应表中。
表1CUSTOMERS
表2PUBLISHERS
表3BOOKS
表4ORDERS
ORDERITEM
表5
表6PROMOTION
(2)将ISBN为978-7-121-18619-8的图书的零售价格(retail)修改为30。
Updatebookssetretail=30whereisbn=’978-7-121-18619-8’;
(3)将订单号为1000的订单的发货日期修改为“2013-2-2”。
Updateorderssetshipdate=to_date('2013-02-02','yyyy-mm-dd')whereorder_id=1000;
(4)已知当前有一个图书表OLDPUBLISHERS,其表中数据如下:
publisher_id
name
contact
phone
1
电子工业出版社
李明
0
2
机械工业出版社
孙浩
3
人民邮电出版社
张春
4
传智播客
吴瑞
997990
请将表中的数据与PUBLISHERS中的数据进行合并。
创建表:
CREATETABLEOLDPUBLISHERS(
2publisher_idCHAR(10)PRIMARYKEY,
3nameVARCHAR2(25),
4contactVARCHAR2(25),
5phoneCHAR(25))
6TABLESPACEUSERS;
插入数据:
INSERTINTOOLDPUBLISHERSVALUES(2,'机械工业出版社','孙浩',);
INSERTINTOOLDPUBLISHERSVALUES(3,'人民邮电出版社','张春',3449876);
INSERTINTOOLDPUBLISHERSVALUES(4,'传智播客','吴瑞',997990);
合并:
MERGEINTOPUBLISHERSPUSINGOLDPUBLISHERSOON(P.publisher_id=O.publisher_id)
WHENMATCHEDTHENUPDATESET
P.name=O.name,P.contact=O.contact,P.phone=O.phone
WHENNOTMATCHEDTHENINSERT
VALUES(O.publisher_id,O.name,O.contact,O.phone);
(6)列出BOOKS表中每本书的书名和出版日期,对pubdate字段使用PublicationDate列标题。
(7)列出CUSTOMERS表中每一个客户的客户号及他们所在的地址。
(8)创建一个包含各个出版社的名称、联系人及出版社电话号码的列表。
其中,联系人的列在显示的结果中重命名为ContactPerson
CREATEtableqwerasselectname,contact"ContactPerson",phonefrompublisher;或
CREATEtableqwerasselectname,contactasContactPerson,phonefrompublisher;
(9)查询下达了订单的每一个客户的客户号。
(10)查询2013年3月1日之后发货的订单。
(11)查询居住在北京或大连的客户,将结果按姓名的升序排列。
(12)列出姓“王”的作者编写的所有图书信息,并将结果按姓名降序排序。
Select*fromBOOKSWHEREauthorLIKE‘王%’orderbytitledesc;
(13)查询“儿童”类和“烹饪”类的所有图书。
(14)查询书名的第二个字母是“A”、第四个字母是“N”的图书
SELECT*FROMBOOKSWHERETITLELIKE'_A%'ANDTITLELIKE'___N%';
(15)查询电子工业出版社在2012年出版的所有“计算机”类图书的名称。
方法1:
错误:
SELECT*FROMBOOKS,PUBLISHERSWHEREPUBLISHERS.publisher_id=BOOKS.publisher_idandPUBLISHERS.name='电子工业出版社'andBOOKS.pubdate=to_date(like'2012-__-__','yyyy-mm-dd')andbooks.category='计算机';
正确:
SELECT*FROMBOOKS,PUBLISHERSWHEREPUBLISHERS.publisher_id=BOOKS.publisher_idandPUBLISHERS.name='电子工业出版社'andto_char(books.pubdate,'yyyy-mm-dd')like'2012%'andbooks.category='计算机';
方法2:
(16)查询图书名称、出版社名称、出版社联系人的名称和电话号码。
(17)查询当前还没有发货的订单信息及下达订单的用户名,查询结果按下达订单日期排序。
selectORDERS.order_id,CUSTOMERS.name,ORDERS.orderdatefromORDERS,CUSTOMERSWHEREORDERS.customer_id=CUSTOMERS.customer_idandORDERS.shipdate<=selectto_char(sysdate,'yyyy-mm-dd')fromdualorderbyorderdate;
(18)查询已经购买了“计算机”类图书的所有人的客户号和姓名。
selectCUSTOMERS.customer_id,CUSTOMERS.namefromBOOKS,CUSTOMERS,ORDERS,ORDERITEMWHEREBOOKS.ISBN=ORDERITEM.ISBNANDORDERITEM.ORDER_ID=ORDERS.ORDER_IDANDORDERS.CUSTOMER_ID=CUSTOMERS.CUSTOMER_IDANDBOOKS.CATEGORY='计算机';
(19)查询“王牧”购买的图书的ISBN以及书名。
SQL>SELECTBOOKS.ISBN,BOOKS.TITLEFROMBOOKS,CUSTOMERS,ORDERS,ORDERITEMWHERECUSTOMERS.CUSTOMER_ID=ORDERS.CUSTOMER_IDANDORDERS.ORDER_ID=ORDERITEM.ORDER_IDANDORDERITEM.ISBN=BOOKS.ISBNANDCUSTOMERS.NAME='王牧';
(20)查询订购图书“Oracle数据库基础”的客户将收到什么样的礼品。
Selectcustomer_id,namefrom
Promotionjoin
(selectcustomer_id,sum(quantity*retail)total
Fromorderjoinorderitemonorder.order_id=orderitem.order_id
Joinbookonorderitem.isbn=book.isbn
Wherecustomer_idin(selectcustomer_idfromorderswhereorder_idin(selectorder_idfromorderitemwhereisbn=’Oracle数据库基础’))
Groupbycustomer_id)detail
Ontotalbetweenminretailandmaxretail;
(21)确定客户“张扬”订购的图书的作者。
(22)查询CUSTOMERS表中的每一个客户所下达的订单数量。
selectCUSTOMERS.customer_id,CUSTOMERS.name,BOOKS.TITLE,ORDERITEM.QUANTITYFROMCUSTOMERS,ORDERS,BOOKS,ORDERITEMWHERECUSTOMERS.CUSTOMER_ID=ORDERS.CUSTOMER_IDANDORDERS.ORDER_ID=ORDERITEM.ORDER_IDANDORDERITEM.ISBN=BOOKS.ISBN;
(23)查询价格低于同一种类图书平均价格的图书的信息。
(24)查询每个出版社出版图书的平均价格、最高价格、最低价格。
(25)统计每个客户购买图书的数量及总价钱。
(26)查询比1号订单中图书数量多的其他订单信息。
(27)查询所有客户及其订购图书的信息。
(28)查询没有订购任何图书的客户信息。
(29)查询订购金额最高的客户信息。
(30)查询名为“赵敏”的客户订购图书的订单信息、订单明细。
5、总结
本次实验与大二学习的数据库知识相关,因此比较轻松,但还是遇到了不少问题,比如时间格式的“to_date”和“to_char”混淆不清,还有穿插聚集运算的夺标查询比较吃力,至今未能有效解决。