实验五数据查询

实验五数据查询复杂查询一. 实验目的1. 掌握SQLServe查询语句的基本语法2. 熟练使用SQL的Select语句对多表进行查询3. 熟练掌握并运用SQLServer所提供的函数4. 熟练使用SQL语句进行复杂的连接操作二、实验环境(实验的软件.硬件环境)硬件：PC机软件:SQL2000三、实验指导说明请复习相关的查询知识点并完成如下内容。四、实验内容1.在订单数据库oiderDB中，完成如下的查询：(1) 用子查询查询员工“张小娟”所做的订单信息。(2) 查询没有订购商品的且在北京地区的客户编号，客户名称和邮政编码，并按邮政编码降 序排序。(3) 查询订购了 “32MDRAM”商品的订单编号，订货数量和订货单价。(4) 查询与员工编号“E2008005”在同一个部门的员工编号，姓名，性别，所属部门。(5) 查询既订购了 P20050001商品，又订购了 P20070002商品的客户编号，订单编号和订单 金额(6) 查询没有订购“52倍速光驱”或“17寸显示器”的客户编号，客户名称。(7) 查询订单金额最高的订单编号，客户姓名，销售员名称和相应的订单金额。(8) 查询订购了 “52倍速光驱”商品的订购数量，订购平均价和订购总金额。(9) 查询订购了 “52倍速光驱”商品且订货数量界于24之间的订单编号，订货数量和订货 金额0(10) 在订单主表中查询每个业务员的订单数量(11) 统计在业务科工作且在1973年或1967年出生的员工人数和平均工资。(12) 在订单明细表中统计每种商品的销售数量和金额，并按销售金额的升序排序输出。(13) 统计客户号为“C20050001”的客户的订单数，订货总额和平均订货金额(14) 统计每个客户的订单数，订货总额和平均订货金额。(15) 查询订单中至少包含3种(含3种)以上商品的订单编号及订购次数，且订购的商品数量在3件(含3件)以上。(16) 查找订购了 “32MDRAM”的商品的客户编号，客户名称，订货总数量和订货总金额。(17) 查询每个客户订购的商品编号，商品所属类别，商品数量及订货金额，结果显示客户名称，商品所属类别，商品数量及订货金额，并按客户编号升序和按订货金额的降序排序输出。(18) 按商品类别查询每类商品的订货平均单价在280元(含280元)以上的订货总数量，订 货平均单价和订货总金额。(19) 查找至少有2次销售的业务员名称和销售日期。(20) 查询销售金额最大的客户名称和总货款额(21) 查找销售总额小于5000元的销售员编号，姓名和销售额(22) 查找至少订购了 3种商品的客户编号，客户名称，商品编号，商品名称，数量和金额。(23) 查找同时订购了商品为“P20070002”和商品编号为“P20070001”的商品的客户编号，客户姓名，商品编号，商品名称和销售数量，按客户编号排序输出。(24) 计算每一商品每月的销售金额总和，并将结果首先按销售月份然后按订货金额降序排序 输出。(25) 查询订购了 “键盘”商品的客户姓名，订货数量和订货日期(26) 查询每月订购“键盘”商品的客户名称。(27) 查询至少销售了 5种商品的销售员编号，姓名，商品名称，数量及相应的单价，并按销售员编号排序输出。(28) 查询没有订购商品的客户编号和客户名称。(29) 查询至少包含了“世界技术开发公司”所订购的商品的客户编号，客户名称，商品编号,商品名称，数量和金额。五. 实验步骤请完成实验内容，并写出具体的实验步骤(1) 用子查询查询员工“张小娟所做的订单信息。 selectorderMaster?fiomemployee,orderMastei-whereorderMaster.SaleNo=employeeNoandemplo veeNamemJ(selectemployee.employeeNamefLomemployeewhereemployeeName= 长 =3)groupbyorderno(16) 查找订购了32MDRAM”的商品的客户编号，客户名称，订货总数量和订货总金额。selectciistomer.customenioxustomeniame.SUM(qty)asiniS/WSUM(ordennaster.ordersum)as,订货总金额,fromprodiic 匸 ordeideaiLciisi:omei;OTdeimasWiwhereciistoineiciisToinejriLO=ojrdermasteiciisi:ome 门 1 oandordermaster.oiderno=orderdetail.ordenioaiidproduct.productiio=orderdetail.productnoandproduct.productname-32M DRAM*groupbycu stonier, cu stomernoxustomemame(17) 查询每个客户订购的商品编号，商品所属类别，商品数量及订货金额，结果显示客户名称，商品所属类别，商品数量及订货金额，并按客户编号升序和按订货金额的降序排序输出。selectcustomemame,product.productno.product.productclass,orderdetail.qty,orderdetail.price*ord erdetail.qtv fioinordeTdei:aiLpjLodiict;ciisi:omeTQTdeimasWiwhefeciistoineiciisToinejriio=ordermasteiciisK)mem oandoidemiaster.oidenio=orderdetail.ofdenioandorderdetail.productno=product.pioductnoorderbycust omer.customemoasc.orderdetail.pnce*ordefdetail.qtydesc(18) 按商品类别查询每类商品的订货平均单价在元(含元)以上的订货总数量，订货平均单价和订货总金额。selectCOUNT(qty)as订货总数 MAVG(pnce)as,iJ 货评价单价,SUM(ordersum)as订货总金额omproduct.oidermastera,ofderdetailbwherea.ordenio=b.ordenioandpfoduct.pfoductno=b.prodiictnogroupbyproduct.pfodiic tclasshavingAVG(pnce)=2 80(19) 查找至少有次销售的业务员名称和销售日期。selectemployee.employeename.ofdennaster.oiderdatefromemployee.ordeniiasterwhereemployee. employeeno=ordermastef.salenoandemployeenoin(selectsalenofiwiordeniiasteigroupbysalenohavingCOUNT(*)=2)order byemployeename(20) 查询销售金额最大的客户名称和总货款额selectcustomeEcustomeniameanax(a.b)fiomciistomeiofdermastei(selecttoplordermaster.custom eino.SUM(OKle 门 nastetoTdeisimJasiyfLomoTdejrmasteEggiipbyciisKmie 门 io)awherecustomeEciistomenio=ordermaster.customenioanda.ciistomerno=customer.customeniogrou pbycustomeniameselecteustomeniame,MAX(b.a)fiomcustomei;(selectcustomenio,sum(ordersum)afi-omordermaster groupbycu stomemo)bgroupbycu stomemamewherecustomer. cu stomemo=b. cu stomernogroupbycu stomemame(21) 查找销售总额力于元的销售员编号，姓名和销售额selecteiiiployee.employeeno,employeename,afLomemployee.(selectsaleiio.SUM(ofdersum)afLom ordemiastergroupbysaleno)bwhereb.saleno=employeenoanda=3)orderbycustomenio(23) 查找同时订购了商品为“P20070002”和商品编号为“P20070001”的商品的客户编号，客户姓名，商品编号，商品名称和销售数量，按客户编号排序输出。selectcustomer.customenioxustomeniame.pfoduct.pfoductno.productnameordefdetail.qtyfiomcu stomer.product,ojLdejrdetaiLoTdeimasteiwhereciistoineEciistoinejriiOhOKlerinasteT.ciistomeELioaiidoKieimastei. order no=orderdeta ll.oidernoandorderdetail.pfoductno=pfoduct.productnoandcustomer.customernoin (selectcustomer.customeniofromcustomeiproduct.ofderdetaiLofdermasterwhereciistomeECustomerno=oidermasteicustomenioandofdermastei order no=orderdeta ll.oidernoandorderdetail.productno=pioduct.productnoandproduct.productno=lP2007002,mtersectselectcustomer.customeniofLomcustomeEproduct, ofderdetaiLofdermasterwhereciistomeEcustomerno=oidermasteicustomenioandofdermastei order no=orderdeta ll.oidernoandofderdetail.pioductno=pfoduct.pfoductnoandpfoduct.productno=,P200700r)orderbycustomer. customemo(24) 计算每一商品每月的销售金额总和，并将结果首先按销售月份然后按订货金额降序排序输出。selectojrdemiasteiciisk)inemoQKlei:masi:eiOTdeKlaesi】m(oideFsi】m)as* 肖售金额总和fromoidermasteFggiipbyordemiastei.ciislomemo.oTdemiasTei.oTdeKlateoTdeTbyojrdemiasteiojrdeTdae* 肖售金额总 和 desc(25) 查询订购了“键盘”商品的客户姓名，订货数量和订货日期selectciistomei.ciistomemameQrdejLdelaiLqty.oKleifmastei.oKleKlatefroinciistomeaoKlejrdeTaiLoKle rmastei;product4ierecustomeEcustomenio=oidermasteEcustomenioaiidofdeidetail.ofderno=ordermaster. ordenioandproduct.productno=orderdetail.productnoaiidproductiiame=lfi,(26) 查询没有订购“键盘”商品的客户名称。selectdistinctcustomeniamefiomcustomeivTherecustomeniamenotin(selecidishnctciistoine 门 laineflomciistoine 匚 ojLdermasteT.ordejrdeTaiLpjLodiicrwheTe ofdennaster.ciistomenio=ciistomef.customenioandofdennaster.ordenio=ofderdetail.ordernoandpr oduct.productno=ofderdetail.productnoandproductname=,S,)(27) 查询至少销售了种商品的销售员编号，姓名，商品名称，数量及相应的单价，并按销售员编号排序输出。selectemployee.employeeno,employeename,productname,orderdetail.qty,orderdetail.pricefromem ployee,product,ordefdetaiLofdermasterwhereemployee.employeeno=ordermaster.salenoandofdennaster.ofdenio= orderdetail.orderno(selectemployee.employeenofiomemployee.product. ordefdetaiLofdermasterwhereemployee.employeeno=ordermaster.salenoandofdennaster.ofdenio= orderdetail.ordernoandofderdetail.pioductno=prodiict.pfoductnogroupbyemployee.employeenohavingCOUNT(empl oyee.employeeno)=5) orderbyemployeeno(28) 查询没有订购商品的客户编号和客户名称。selecta.ciistomernofromcustomerawherenotexists( selecfromojLdeimasterwheTea.ciistoine 门 io=oi:demiaserciisromenio)(29) 查询至少包含了“世界技术开发公司”所订购的商品的客户编号，客户名称，商品编号，商品名称，数量和金额Oselectcustomer.customenioxustomeniame.pfoduct.pfoductno.productname.ordeidetail.qty.qty*pn cefromordemiaster.ordenio=orderdetail.ordenioandorderdetail.productno=product.pioductiioandcustomer.customernamein selectcustomeniamefromcustomefwherenotexists(select*fLom(selectcustomeniame.ofdeidetail.productnofiomcustomercustoenHb.ofdeidetaiLofder masteiproductproductbwheTeciistoemrb.ciistoineniame世界技术开发公司,andcustoenub.customenio=ordermaster.customernoandofdennaster.oidenio=ordefdetail.ofdenioandoidefdetail.pfoductno=pfoductb.productno)awhere notexists(select*from(selectcustomeniame.ofderdetail.pfoductnofiomcustomercustoenub.ofdefde taiLordermasteiproductproductbwherecustoenKb.customerno=ordermaster.customenioandoidermasteiofderno=oiderdetail.ordeni oandorderdetail.productno=productb.productno)bwherecustomer.customeiiiame=b.customeniameandb.productno=a.pfoductno)六、思考题：1. 存在量词与集合运算m、连接运算和全程量词之间的关系如何？他们可以互相替换吗？给 出你的理由2. Wheie子句和having子句都是用于指定查询条件的，请区别它们的异同？用实例说明。3. 在分组聚集操作中，为什么在查询列中，除了聚集函数运算外，其它表达式必须包含在gioupbv子句中?七、总结（实验过程的体会、心得和实验教与学之间还需改进的內容）