2022年实验五数据查询——复杂查询资料 .pdf

《2022年实验五数据查询——复杂查询资料 .pdf》由会员分享，可在线阅读，更多相关《2022年实验五数据查询——复杂查询资料 .pdf（11页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库原理实验学号： 123012008xxx 姓名： xxx 班级（ x）小班日期： 2010 年月日1 实验五数据查询复杂查询一、实验目的1掌握 SQLServer查询语句的基本语法2熟练使用 SQL 的 Select语句对多表进行查询3熟练掌握并运用SQLServer所提供的函数4熟练使用 SQL 语句进行复杂的连接操作二、实验环境（实验的软件、硬件环境）硬件： PC机 软件： SQL2000三、实验指导说明请复习相关的查询知识点并完成如下内容。四、实验内容1在订单数据库orderDB 中，完成如下的查询：（1）用子查询查询员工“张小娟”所做的订单信息。（2）查询没有订购商品的且在北京地

2、区的客户编号，客户名称和邮政编码，并按邮政编码降序排序。（3）查询订购了“ 32M DRAM ”商品的订单编号，订货数量和订货单价。（4）查询与员工编号“ E2008005”在同一个部门的员工编号，姓名，性别，所属部门。（5）查询既订购了 P20050001商品，又订购了 P20070002商品的客户编号，订单编号和订单金额（6）查询没有订购“ 52 倍速光驱”或“ 17寸显示器”的客户编号，客户名称。（7）查询订单金额最高的订单编号，客户姓名，销售员名称和相应的订单金额。（8）查询订购了“ 52 倍速光驱”商品的订购数量，订购平均价和订购总金额。名师资料总结 - - -精品资料欢迎下载 -

3、- - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 11 页 - - - - - - - - - 数据库原理实验学号： 123012008xxx 姓名： xxx 班级（ x）小班日期： 2010 年月日2 （9）查询订购了“52 倍速光驱” 商品且订货数量界于24 之间的订单编号，订货数量和订货金额。（10）在订单主表中查询每个业务员的订单数量（11） 统计在业务科工作且在1973年或 1967 年出生的员工人数和平均工资。（12）在订单明细表中统计每种商品的销售数量和金额，并按销售金额的升序排序输出。（13）统计客户号

4、为“ C20050001”的客户的订单数，订货总额和平均订货金额（14）统计每个客户的订单数，订货总额和平均订货金额。（15）查询订单中至少包含3 种（含 3 种）以上商品的订单编号及订购次数，且订购的商品数量在3 件（含 3 件）以上。（16）查找订购了“ 32M DRAM ”的商品的客户编号，客户名称，订货总数量和订货总金额。（17）查询每个客户订购的商品编号， 商品所属类别， 商品数量及订货金额，结果显示客户名称， 商品所属类别， 商品数量及订货金额， 并按客户编号升序和按订货金额的降序排序输出。（18）按商品类别查询每类商品的订货平均单价在280元（含 280元）以上的订货总数量，订货

5、平均单价和订货总金额。（19）查找至少有 2 次销售的业务员名称和销售日期。（20）查询销售金额最大的客户名称和总货款额（21）查找销售总额小于5000元的销售员编号，姓名和销售额（22）查找至少订购了3 种商品的客户编号，客户名称，商品编号，商品名称，数量和金额。（23）查找同时订购了商品为“ P20070002 ”和商品编号为“ P20070001 ”的商品的客户编号，客户姓名，商品编号，商品名称和销售数量，按客户编号排序输出。（24）计算每一商品每月的销售金额总和，并将结果首先按销售月份然后按订货金额降序排序输出。（25）查询订购了“键盘”商品的客户姓名，订货数量和订货日期名师资料总结

6、- - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 11 页 - - - - - - - - - 数据库原理实验学号： 123012008xxx 姓名： xxx 班级（ x）小班日期： 2010 年月日3 （26）查询每月订购“键盘”商品的客户名称。（27）查询至少销售了5 种商品的销售员编号，姓名，商品名称，数量及相应的单价，并按销售员编号排序输出。（28）查询没有订购商品的客户编号和客户名称。（29）查询至少包含了“世界技术开发公司”所订购的商品的客户编号，客户名称，商品编号，商品名称

7、，数量和金额。五、实验步骤请完成实验内容，并写出具体的实验步骤（1）用子查询查询员工 “ 张小娟 ” 所做的订单信息 。selectorderMaster.* from employee ,orderMaster where orderMaster .SaleNo=employeeNo andemployeeNamein (selectemployee.employeeNamefrom employee whereemployeeName =张小娟) （2）查询没有订购商品的且在北京地区的客户编号，客户名称和邮政编码，并按邮政编码降序排序。selectdistinct customer.Cus

8、tomerNo ,customer.customerName,customer.Zipfrom customer ,orderMaster wherecustomer . customerNonot in(selectorderMaster .customerNo from orderMaster )andcustomer.addr=北京市 order by customer .Zip desc （3）查询订购了 “ 32M DRAM ” 商品的订单编号 ，订货数量和订货单价。selectorderDetail .OrderNo ,orderDetail .Qty ,orderDetail .

9、PricefromorderDetail ,product where orderDetail.ProductNo=product .ProductNo andProductName =32M DRAM （4）查询与员工编号 “ E2008005” 在同一个部门的员工编号，姓名 ，性别 ，所属部门 。selectemployee.employeeNo ,employee.employeeName ,caseemployee.Sex名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页

10、，共 11 页 - - - - - - - - - 数据库原理实验学号： 123012008xxx 姓名： xxx 班级（ x）小班日期： 2010 年月日4 when Mthen 男when Fthen 女end, employee.Department from employeewhere employee.Department =(selectemployee.Department from employeewhereemployee.employeeNo =E2008005) （5）查询既订购了P20050001 商品，又订购了 P20070002 商品的客户编号，订单编号和订单金额s

11、electcustomer.customerno,ordermaster.orderno ,ordermaster.ordersum fromordermaster,customer,orderdetail where customer.customerno=ordermaster.customernoandordermaster.orderno =orderdetail .orderno andorderdetail .productno=P2007002 intersect selectcustomer.customerno,ordermaster.orderno ,ordermaster

12、.ordersum fromordermaster,customer,orderdetail where customer.customerno=ordermaster.customernoandordermaster.orderno =orderdetail .orderno andorderdetail .productno=P2005001 （6）查询没有订购 “ 52 倍速光驱 ” 或“ 17 寸显示器 ” 的客户编号 ，客户名称 。selectdistinct customer .customerno,customer .customernamefromcustomer ,order

13、detail ,ordermaster,productwhere orderdetail .orderno=ordermaster.orderno and ordermaster.customerno=customer .customernoandorderdetail .productno =product .productno andproduct .productname notin (52 倍速光驱 ,17 寸显示器 )（7）查询订单金额最高的订单编号，客户姓名 ，销售员名称和相应的订单金额 。selectordermaster.orderno ,customer.customerna

14、me,employee.employeename,ordermaster.ordersum from employee,ordermaster,customerwhereemployee.employeeno=ordermaster.salenoand customer.customerno=ordermaster.customernoandordermaster.ordersum =(selectMAX(ordermaster.ordersum)from ordermaster) （8）查询订购了 “ 52 倍速光驱 ” 商品的订购数量 ，订购平均价和订购总金额。名师资料总结 - - -精品

15、资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 11 页 - - - - - - - - - 数据库原理实验学号： 123012008xxx 姓名： xxx 班级（ x）小班日期： 2010 年月日5 selectSUM (orderdetail .qty )as订购数量 ,AVG (ordermaster .ordersum )as订购平均价 ,SUM (ordermaster .ordersum )as订购总金额 fromorderdetail ,ordermaster,product wher

16、e orderdetail .orderno=ordermaster.orderno and orderdetail .productno =product .productno andproduct .productname =52 倍速光驱 （9）查询订购了 “ 52 倍速光驱 ” 商品且订货数量界于4 之间的订单编号 ，订货数量和订货金额。selectorderdetail.orderno ,orderdetail .qty ,ordermaster.ordersum fromordermaster,orderdetail ,product where orderdetail .orde

17、rno =ordermaster.orderno andproduct .productno=orderdetail.productno andproduct .productname =52 倍速光驱 andorderdetail .qty between 2 and 4 （10）在订单主表中查询每个业务员的订单数量selectordermaster.saleno,COUNT (*) as订单数量 from ordermastergroup bysaleno（11）统计在业务科工作且在年或年出生的员工人数和平均工资。selectCOUNT (*) as员工人数 ,AVG (employee.

18、salary )as平均工资 fromemployee （12）在订单明细表中统计每种商品的销售数量和金额，并按销售金额的升序排序输出 。selectorderdetail .productno ,SUM(orderdetail .qty )as销售数量,SUM(orderdetail .price *orderdetail .qty )as金额from orderdetail group byorderdetail .productno order by SUM(orderdetail .price *orderdetail .qty ) （13）统计客户号为 “ C20050001” 的客

19、户的订单数 ，订货总额和平均订货名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 11 页 - - - - - - - - - 数据库原理实验学号： 123012008xxx 姓名： xxx 班级（ x）小班日期： 2010 年月日6 金额selectcustomer.customerno,COUNT (orderdetail .orderno )as订单数 ,SUM(ordermaster.ordersum)as订货总额 ,AVG (ordermaster.ordersu

20、m)as平均订单金额 from orderdetail ,ordermaster,customer where orderdetail .orderno=ordermaster.orderno and ordermaster .customerno=customer .customernoandcustomer .customerno=C2005001 group by customer.customerno（14）统计每个客户的订单数，订货总额和平均订货金额。selectcustomer.customerno,COUNT (orderdetail .orderno )as订单数 ,SUM(o

21、rdermaster.ordersum)as订货总额 ,AVG (ordermaster.ordersum)as平均订单金额 from orderdetail ,ordermaster,customer where orderdetail .orderno=ordermaster.orderno and ordermaster .customerno=customer .customernogroup by customer .customerno（15）查询订单中至少包含种（含种 ）以上商品的订单编号及订购次数，且订购的商品数量在件（含件 ）以上 。selectorderno ,count(

22、*) from orderdetail where orderno in(selectorderdetail.ordernofrom orderdetail group by orderno having COUNT (orderno)=3) group byorderno（16）查找订购了 “ 32M DRAM ” 的商品的客户编号，客户名称 ，订货总数量和订货总金额 。selectcustomer.customerno,customername,SUM(qty)as订货总数量,SUM(ordermaster.ordersum)as订货总金额 from product,orderdetail

23、,customer,ordermasterwhere customer .customerno=ordermaster.customernoandordermaster.orderno =orderdetail .orderno andproduct.productno =orderdetail .productno andproduct .productname =32M DRAM 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 11 页 - - - - - - - -

24、 - 数据库原理实验学号： 123012008xxx 姓名： xxx 班级（ x）小班日期： 2010 年月日7 group by customer.customerno,customername （17）查询每个客户订购的商品编号，商品所属类别 ，商品数量及订货金额，结果显示客户名称，商品所属类别 ，商品数量及订货金额 ，并按客户编号升序和按订货金额的降序排序输出。selectcustomername,product.productno,product.productclass,orderdetail .qty ,orderdetail .price *orderdetail .qtyfro

25、m orderdetail ,product ,customer ,ordermasterwhere customer.customerno=ordermaster.customernoandordermaster.orderno =orderdetail .orderno andorderdetail .productno=product .productno order by customer.customernoasc ,orderdetail .price *orderdetail .qty desc （18）按商品类别查询每类商品的订货平均单价在元（含元 ）以上的订货总数量 ，订货平

26、均单价和订货总金额。selectCOUNT (qty)as订货总数量 ,AVG (price )as订货评价单价,SUM(ordersum)as订货总金额 from product, ordermastera, orderdetail b where a.orderno =b.orderno andproduct .productno =b.productnogroup by product.productclass having AVG (price )=280 （19）查找至少有次销售的业务员名称和销售日期。selectemployee.employeename,ordermaster.o

27、rderdatefromemployee,ordermasterwhere employee.employeeno=ordermaster . salenoand employeenoin (selectsalenofrom ordermastergroup by salenohaving COUNT (*)= 2) order by employeename（20）查询销售金额最大的客户名称和总货款额selectcustomer.customername,max(a.b) from customer,ordermaster,(selecttop1 ordermaster.customerno

28、,SUM(ordermaster.ordersum) asb from ordermastergroup by customerno)a where customer.customerno=ordermaster.customernoand a.customerno=customer .customernogroup by customername名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 11 页 - - - - - - - - - 数据库原理实验学号： 12301

29、2008xxx 姓名： xxx 班级（ x）小班日期： 2010 年月日8 selectcustomername ,MAX (b.a) from customer,(selectcustomerno,sum(ordersum )a from ordermastergroup by customerno)b groupby customernamewhere customer .customerno=b.customernogroup by customername（21）查找销售总额小于元的销售员编号，姓名和销售额selectemployee.employeeno,employeename,a

30、from employee,(selectsaleno,SUM(ordersum) a from ordermastergroup by saleno)bwhere b.saleno=employeenoand a=3 ) orderby customerno （23）查找同时订购了商品为“ P20070002” 和商品编号为 “ P20070001” 的商品的客户编号 ，客户姓名 ，商品编号 ，商品名称和销售数量，按客户编号排序输出。selectcustomer.customerno,customername ,product.productno ,productname,orderdeta

31、il.qty from customer,product, orderdetail,ordermasterwhere customer.customerno=ordermaster.customernoandordermaster .orderno=orderdetail.ordernoandorderdetail .productno =product.productno andcustomer.customernoin (selectcustomer.customernofrom customer,product, 名师资料总结 - - -精品资料欢迎下载 - - - - - - - -

32、- - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 11 页 - - - - - - - - - 数据库原理实验学号： 123012008xxx 姓名： xxx 班级（ x）小班日期： 2010 年月日9 orderdetail,ordermasterwhere customer.customerno=ordermaster.customernoandordermaster .orderno=orderdetail.ordernoandorderdetail .productno =product.productno and product.pro

33、ductno=P2007002 intersect selectcustomer.customernofrom customer,product, orderdetail,ordermasterwhere customer.customerno=ordermaster.customernoandordermaster .orderno=orderdetail.ordernoandorderdetail .productno =product.productno andproduct.productno=P2007001) order by customer.customerno（24）计算每一

34、商品每月的销售金额总和，并将结果首先按销售月份然后按订货金额降序排序输出。selectordermaster.customerno,ordermaster.orderdate,sum(ordersum) as销售金额总和 from ordermastergroup by ordermaster.customerno,ordermaster.orderdateorder byordermaster.orderdate,销售金额总和desc （25）查询订购了 “ 键盘 ” 商品的客户姓名 ，订货数量和订货日期selectcustomer.customername,orderdetail.qty,

35、ordermaster.orderdatefromcustomer,orderdetail,ordermaster, product where customer.customerno=ordermaster.customernoandorderdetail .orderno =ordermaster.orderno and product.productno =orderdetail .productno andproductname=键盘 （26）查询没有订购 “ 键盘 ” 商品的客户名称 。selectdistinct customernamefrom customer where cu

36、stomernamenot in( selectdistinct customernamefrom customer,ordermaster ,orderdetail,productwhere ordermaster.customerno=customer.customernoandordermaster.orderno=orderdetail .orderno andproduct.productno=orderdetail.productno andproductname=键盘) （27）查询至少销售了种商品的销售员编号，姓名，商品名称 ，数量及相名师资料总结 - - -精品资料欢迎下载

37、- - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 11 页 - - - - - - - - - 数据库原理实验学号： 123012008xxx 姓名： xxx 班级（ x）小班日期： 2010 年月日10 应的单价 ，并按销售员编号排序输出。selectemployee.employeeno,employeename,productname,orderdetail.qty,orderdetail .price from employee,product, orderdetail,ordermasterwhere e

38、mployee.employeeno=ordermaster .salenoandordermaster .orderno =orderdetail.ordernoandorderdetail.productno =product .productno and employee.employeenoin (selectemployee.employeeno from employee,product, orderdetail,ordermasterwhere employee.employeeno=ordermaster .salenoandordermaster .orderno =orde

39、rdetail.ordernoandorderdetail.productno =product .productno group byemployee.employeenohaving COUNT (employee.employeeno)=5) order by employeeno（28）查询没有订购商品的客户编号和客户名称。selecta.customernofrom customer awhere not exists( select*from ordermasterwhere a.customerno=ordermaster.customerno) （29）查询至少包含了 “ 世界

40、技术开发公司 ” 所订购的商品的客户编号，客户名称 ，商品编号 ，商品名称 ，数量和金额 。selectcustomer.customerno,customername ,product.productno ,productname,orderdetail .qty ,qty*price fromcustomer,product ,orderdetail ,ordermasterwhere customer.customerno=ordermaster.customernoandordermaster.orderno=orderdetail .orderno andorderdetail .p

41、roductno =product.productno andcustomer .customernamein ( selectcustomernamefrom customerwherenot exists (select*from (selectcustomername ,orderdetail .productno from customercustoemrb,orderdetail ,ordermaster,product productbwhere custoemrb.customername=世界技术开发公司 andcustoemrb.customerno=ordermaster.

42、customernoandordermaster.orderno =orderdetail .orderno andorderdetail .productno=productb.productno)a wherenot exists (select*from (select customername ,orderdetail .productno fromcustomer custoemrb,orderdetail ,ordermaster,product productbwherecustoemrb .customerno=ordermaster.customernoand名师资料总结 -

43、 - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 11 页 - - - - - - - - - 数据库原理实验学号： 123012008xxx 姓名： xxx 班级（ x）小班日期： 2010 年月日11 ordermaster.orderno =orderdetail .orderno andorderdetail .productno=productb.productno) b where customer.customername=b.customernameandb.produc

44、tno=a.productno ) 六、思考题：1存在量词与集合运算in、连接运算和全程量词之间的关系如何？他们可以互相替换吗？给出你的理由2 Where子句和 having 子句都是用于指定查询条件的， 请区别它们的异同？用实例说明。3在分组聚集操作中，为什么在查询列中，除了聚集函数运算外，其它表达式必须包含在 group by 子句中？七、总结 （实验过程的体会、心得和实验教与学之间还需改进的内容）名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 11 页 - - - - - - - - -