实验五数据查询.doc

实验五数据查询——复杂查询一. 实验目的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倍速光驱”商品且订货数量界于2〜4之间的订单编号，订货数量和订货 金额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=^ 长 <]\ 娟')(2) 查询没有订购商品的且在北京地区的客户编号，客户名称和邮政编码，并按邮政编码降序排序。

selectdistinctcustomer.CustomerNo,customer.customerName,customer.Zipfromcustomei\orderMa steivdierecu stonier.ciisTomeiNoiiotuKselectojrderMastei.ciistomeFNofromoiderMasteQaiidciistome 匸 addr=‘北京市 'orderbycustomerZipdesc(3) 查询订购了-32MDRAM-商品的订单编号，订货数量和订货单价selectojrdeiDetail.OrderNoQTderDetail.Qiy.oTderDetaiLP 门 cefiomordeiDetaiLpTodiictwheieoideT Detail.ProductNo=product.ProductNoandProductName=132MDRAlVr(4) 查询与员工编号“E2008005”在同一个部门的员工编号，姓名，性别，所属部门tM,then,®'when'F'tlien'女'end,employee.Depairmentfromemployeewhereemployee.Depaftment=(selectemployee.Depaftmentfio memployeewliereemployee.employeeNo-E2008005,)(5) 查询既订购了 P20050001商品，又订购了 P20070002商品的客户编号，订单编号和订单金额selectciistoinei.ciistomemo.ojrdemiastei.ojrde 门 lo.oTdeimaste 匸 oideisiimfromoTde 门 naste 匚 customer; ofderdetailwliefeeustomei\customenio=ofdennasteEcustomenioandordermasteEordeiiio=ordefdet ail.ordenioandoiderdetail.pioductno=,P2007002,mtersectP20050011(6) 查询没有订购“52倍速光驱”或T7寸显示器”的客户编号，客户名称。

selectdistinctcustomei\customenioxustomei\customernamefLomcustomei;ofderdetaiLofdermastei\ product^diereorderdetail.ordenio=ofdermastei\ofdenioandordermaster.customenio=customef.customenioandofderdetail.productno=product.productnoandproduct.productnamenotin(,52 倍速光 9区T17寸显示器)(7) 查询订单金额最高的订单编号，客户姓名，销售员名称和相应的订单金额selectojrdennasteEordejriJLO.ciistoiner.ciisTome 门 lame.employee.einployeename.oTdeimaste 匸 ordeisu mfromeinployee.ojrdennastecciistoinefwheieeinployee.employeeiiOhOJLde 门 nastei.saleiioaiidciisto mei.ciistoine 门 io=ojL・deimastef・ci】stomeinoaiidojL・deimastef・OJL・deisiim=(selectMAX(OTdei*masteF・Oj：d ersum)fromordennaster)(8) 查询订购了“52倍速光驱”裔品的订购数量，订购平均价和订购总金额。

selectSUM(orderdetail.qty)as,订购数量\AVG(ordennastei\ordersum)as,订购平均价；SUM(OTdermastejLOTdersuni)as‘ 订购总金额£・omoi・deTdetaiLojrdemiastei;pjrodiictwhei・eojL・deKle{ail・OKlemo=ordennasTei・・OTdeinoaiidojrdeTdetai=,52 倍速光驱,(9) 查询订购了“52倍速光驱”商品且订货数量界于、4之间的订单编号，订货数量和订货 金额selectojrdeTdeTaiLojrdewLO.OTdeideuuLqty.ojrde 门 nastei.ojLdeisiiinfroinoKieimastecoTderdelaiLpiodii ctwhereofderdetail.orderiio=ordennaster.ordenioandpiodiict.productno=ofdefdetail.pioductnoandproduct. pfoductname=,52 倍速光 ^,andordefdetail.qtybetweeii2and4(10) 在订单主表中查询每个业务员的订单数量selectoidermastei\saleno.COUNT(*)astiJ^g$W,fLomordermastefgroupbysaleno(11) 统计在业务科工作且在年或年出生的员工人数和平均工资。

selectCOUNT(*)as'员工人数,AVG( employee, salaiyjas'平均工资坨・oineinployee(12) 在订单明细表中统计每种商品的销售数量和金额，并按销售金额的升序排序输出selectoidefdetail.productno.SUM(ofdefdetail.qty)as,销售数量精心整理\SUM(orderdetail.price*orderdetail.qty)asl 金额,fiomorderdetailgroupbyorderdetail.productnoofderbySUM(orderdetail.price*orderdetail.qty)(13) 统计客户号为“C20050001”的客户的订单数，订货总额和平均订货金额selectcustomer.customenio.COUNT(ofdefdetail.orderno)as,n^gJ:SUM(oj?demiasteF・oi:dei・siim)as'订货总额\AVG(ofdermastei\ofdersum)as,平均订单金额'fromoideTdetaiLojrdemiastmiisTomerwhefeoKieKlegil.oKiemohOKleimastetordejLnoimdojrdemia stef.customenio=customer.customenioandcustomer.customenio=,C2005001,groupbycustomer.cus tomemo(14) 统计每个客户的订单数，订货总额和平均订货金额。

selectcustomer.customenio.COUNT(ofdefdetail.orderno)as,n^gJ:SUM(oj?demiasteF・oi:dei・siim)as'订货总额\AVG(ofdermastei\ofdersum)as,平均订单金额(15) 查询订单中至少包含种(含种)以上。