实验五数据查询复杂查询.docx
《实验五数据查询复杂查询.docx》由会员分享,可在线阅读,更多相关《实验五数据查询复杂查询.docx(11页珍藏版)》请在冰豆网上搜索。
实验五数据查询复杂查询
实验五数据查询——复杂查询
一、实验目的
1.掌握SQLServer查询语句的根本语法
2.熟练使用SQL的Select语句对多表进行查询
3.熟练掌握并运用SQLServer所提供的函数
4.熟练使用SQL语句进行复杂的连接操作
二、实验环境〔实验的软件、硬件环境〕
硬件:
PC机软件:
SQL2000
三、实验指导说明
请复习相关的查询知识点并完成如下内容。
四、实验内容
1.在订单数据库orderDB中,完成如下的查询:
〔1〕用子查询查询员工“张小娟〞所做的订单信息。
〔2〕查询没有订购商品的且在北京地区的客户编号,客户名称和邮政编码,并按邮政编码降序排序。
〔3〕查询订购了“32MDRAM〞商品的订单编号,订货数量和订货单价。
〔4〕查询与员工编号“E2021005〞在同一个部门的员工编号,姓名,性别,所属部门。
〔5〕查询既订购了P2*******商品,又订购了P2*******商品的客户编号,订单编号和订单金额
〔6〕查询没有订购“52倍速光驱〞或“17寸显示器〞的客户编号,客户名称。
〔7〕查询订单金额最高的订单编号,客户姓名,销售员名称和相应的订单金额。
〔8〕查询订购了“52倍速光驱〞商品的订购数量,订购平均价和订购总金额。
〔9〕查询订购了“52倍速光驱〞商品且订货数量界于2~4之间的订单编号,订货数量和订货金额。
〔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〕查找同时订购了商品为“P2*******〞和商品编号为“P2*******〞的商品的客户编号,客户姓名,商品编号,商品名称和销售数量,按客户编号排序输出。
〔24〕计算每一商品每月的销售金额总和,并将结果首先按销售月份然后按订货金额降序排序输出。
〔25〕查询订购了“键盘〞商品的客户姓名,订货数量和订货日期
〔26〕查询每月订购“键盘〞商品的客户名称。
〔27〕查询至少销售了5种商品的销售员编号,姓名,商品名称,数量与相应的单价,并按销售员编号排序输出。
〔28〕查询没有订购商品的客户编号和客户名称。
〔29〕查询至少包含了“世界技术开发公司〞所订购的商品的客户编号,客户名称,商品编号,商品名称,数量和金额。
五、实验步骤
请完成实验内容,并写出具体的实验步骤
〔1〕用子查询查询员工“张小娟〞所做的订单信息。
selectorderMaster.*fromemployee,orderMasterwhereorderMaster.SaleNo=employeeNoandemployeeNamein
(selectemployee.employeeNamefromemployeewhereemployeeName='张小娟')
〔2〕查询没有订购商品的且在北京地区的客户编号,客户名称和邮政编码,并按邮政编码降序排序。
selectdistinctcustomer.CustomerNo,customer.customerName,customer.Zipfromcustomer,orderMasterwherecustomer.
customerNonotin(selectorderMaster.customerNofromorderMaster)andcustomer.addr='北京市'orderbycustomer.Zipdesc
〔3〕查询订购了“32MDRAM〞商品的订单编号,订货数量和订货单价。
selectorderDetail.OrderNo,orderDetail.Qty,orderDetail.PricefromorderDetail,productwhereorderDetail.ProductNo
=product.ProductNoandProductName='32MDRAM'
〔4〕查询与员工编号“E2021005〞在同一个部门的员工编号,姓名,性别,所属部门。
selectemployee.employeeNo,employee.employeeName,caseemployee.Sexwhen'M'then'男'when'F'then'女'end,
employee.Departmentfromemployeewhereemployee.Department=(selectemployee.Departmentfromemployeewhere
employee.employeeNo='E2021005')
〔5〕查询既订购了P2*******商品,又订购了P2*******商品的客户编号,订单编号和订单金额
selectcustomer.customerno,ordermaster.orderno,ordermaster.ordersumfromordermaster,customer,orderdetailwherecustomer.customerno=ordermaster.customernoandordermaster.orderno=orderdetail.ordernoandorderdetail.productno='P2007002'
intersect
selectcustomer.customerno,ordermaster.orderno,ordermaster.ordersumfromordermaster,customer,orderdetailwherecustomer.customerno=ordermaster.customernoandordermaster.orderno=orderdetail.ordernoandorderdetail.productno='P2005001'
〔6〕查询没有订购“52倍速光驱〞或“17寸显示器〞的客户编号,客户名称。
selectdistinctcustomer.customerno,customer.customernamefromcustomer,orderdetail,ordermaster,productwhereorderdetail.orderno=ordermaster.ordernoandordermaster.customerno=customer.customernoandorderdetail.productno=product.productnoandproduct.productnamenotin('52倍速光驱','17寸显示器')
〔7〕查询订单金额最高的订单编号,客户姓名,销售员名称和相应的订单金额。
selectordermaster.orderno,customer.customername,employee.employeename,ordermaster.ordersumfromemployee,ordermaster,customerwhereemployee.employeeno=ordermaster.salenoandcustomer.customerno=ordermaster.customernoandordermaster.ordersum=(selectMAX(ordermaster.ordersum)fromordermaster)
〔8〕查询订购了“52倍速光驱〞商品的订购数量,订购平均价和订购总金额。
selectSUM(orderdetail.qty)as'订购数量',AVG(ordermaster.ordersum)as'订购平均价',SUM(ordermaster.ordersum)as'订购总金额'fromorderdetail,ordermaster,productwhereorderdetail.orderno=ordermaster.ordernoandorderdetail.productno=product.productnoandproduct.productname='52倍速光驱'
〔9〕查询订购了“52倍速光驱〞商品且订货数量界于~4之间的订单编号,订货数量和订货金额。
selectorderdetail.orderno,orderdetail.qty,ordermaster.ordersumfromordermaster,orderdetail,productwhere
orderdetail.orderno=ordermaster.ordernoandproduct.productno=orderdetail.productnoandproduct.productname='52倍速光驱'andorderdetail.qtybetween2and4
〔10〕在订单主表中查询每个业务员的订单数量
selectordermaster.saleno,COUNT(*)as'订单数量'fromordermastergroupbysaleno
〔11〕统计在业务科工作且在年或年出生的员工人数和平均工资。
selectCOUNT(*)as'员工人数',AVG(employee.salary)as'平均工资'fromemployee
〔12〕在订单明细表中统计每种商品的销售数量和金额,并按销售金额的升序排序输出。
selectorderdetail.productno,SUM(orderdetail.qty)as'销售数量',SUM(orderdetail.price*orderdetail.qty)as'金额'fromorderdetailgroupbyorderdetail.productnoorderbySUM(orderdetail.price*orderdetail.qty)
〔13〕统计客户号为“C20050001〞的客户的订单数,订货总额和平均订货金额
selectcustomer.customerno,COUNT(orderdetail.orderno)as'订单数',SUM(ordermaster.ordersum)as'订货总额',AVG(ordermaster.ordersum)as'平均订单金额'fromorderdetail,ordermaster,customerwhereorderdetail.orderno=ordermaster.ordernoandordermaster.customerno=customer.customernoandcustomer.customerno='C2005001'groupbycustomer.customerno
〔14〕统计每个客户的订单数,订货总额和平均订货金额。
selectcustomer.customerno,COUNT(orderdetail.orderno)as'订单数',SUM(ordermaster.ordersum)as'订货总额',AVG(ordermaster.ordersum)as'平均订单金额'fromorderdetail,ordermaster,customerwhereorderdetail.orderno=ordermaster.ordernoandordermaster.customerno=customer.customernogroupbycustomer.customerno
〔15〕查询订单中至少包含种〔含种〕以上商品的订单编号与订购次数,且订购的商品数量在件〔含件〕以上。
selectorderno,count(*)fromorderdetailwhereordernoin(selectorderdetail.orderno
fromorderdetailgroupbyordernohavingCOUNT(orderno)>=3)groupbyorderno
〔16〕查找订购了“32MDRAM〞的商品的客户编号,客户名称,订货总数量和订货总金额。
selectcustomer.customerno,customername,SUM(qty)as'订货总数量',SUM(ordermaster.ordersum)as'订货总金额'
fromproduct,orderdetail,customer,ordermasterwherecustomer.customerno=ordermaster.customernoandordermaster.orderno
=orderdetail.ordernoandproduct.productno=orderdetail.productnoandproduct.productname='32MDRAM'
groupbycustomer.customerno,customername
〔17〕查询每个客户订购的商品编号,商品所属类别,商品数量与订货金额,结果显示客户名称,商品所属类别,商品数量与
订货金额,并按客户编号升序和按订货金额的降序排序输出。
selectcustomername,product.productno,product.productclass,orderdetail.qty,orderdetail.price*orderdetail.qty
fromorderdetail,product,customer,ordermasterwherecustomer.customerno=ordermaster.customernoand
ordermaster.orderno=orderdetail.ordernoandorderdetail.productno=product.productnoorderbycustomer.customernoasc
orderdetail.price*orderdetail.qtydesc
〔18〕按商品类别查询每类商品的订货平均单价在元〔含元〕以上的订货总数量,订货平均单价和订货总金额。
selectCOUNT(qty)as'订货总数量',AVG(price)as'订货评价单价',SUM(ordersum)as'订货总金额'fromproduct,ordermastera,
orderdetailbwherea.orderno=b.ordernoandproduct.productno=b.productnogroupbyproduct.productclasshavingAVG(price)>=280
〔19〕查找至少有次销售的业务员名称和销售日期。
selectemployee.employeename,ordermaster.orderdatefromemployee,ordermasterwhereemployee.employeeno=ordermaster.
salenoandemployeenoin(selectsalenofromordermastergroupbysalenohavingCOUNT(*)>=2)orderbyemployeename
〔20〕查询销售金额最大的客户名称和总货款额
selectcustomer.customername,max(a.b)fromcustomer,ordermaster,(selecttop1ordermaster.customerno,SUM(ordermaster.ordersum)as'b'fromordermastergroupbycustomerno)a
wherecustomer.customerno=ordermaster.customernoanda.customerno=customer.customernogroupbycustomername
selectcustomername,MAX(b.a)fromcustomer,(selectcustomerno,sum(ordersum)afromordermastergroupbycustomerno)bgroupbycustomername
wherecustomer.customerno=b.customernogroupbycustomername
〔21〕查找销售总额小于元的销售员编号,姓名和销售额
selectemployee.employeeno,employeename,afromemployee,(selectsaleno,SUM(ordersum)afromordermastergroupbysaleno)b
whereb.saleno=employeenoanda<5000
〔22〕查找至少订购了种商品的客户编号,客户名称,商品编号,商品名称,数量和金额。
selectcustomer.customerno,customer.customername,product.productno,product.productname,orderdetail.qty,
orderdetail.price*orderdetail.qtyas'金额'fromcustomer,product,orderdetail,ordermasterwherecustomer.customerno=
ordermaster.customernoandordermaster.orderno=orderdetail.ordernoandorderdetail.productno=product.productno
andcustomer.customernoin(selectcustomer.customernofromcustomer,product,orderdetail,ordermasterwherecustomer.customerno=
ordermaster.customernoandordermaster.orderno=orderdetail.ordernoandorderdetail.productno=product.productno
groupbycustomer.customernohavingcount(customer.customerno)>=3)orderbycustomerno
〔23〕查找同时订购了商品为“P2*******〞和商品编号为“P2*******〞的商品的客户编号,客户姓名,商品编号,
商品名称和销售数量,按客户编号排序输出。
selectcustomer.customerno,customername,product.productno,productname,orderdetail.qtyfromcustomer,product,
orderdetail,ordermasterwherecustomer.customerno=ordermaster.customernoandordermaster.orderno=orderdetail.orderno
andorderdetail.productno=product.productnoandcustomer.customernoin
(selectcustomer.customernofromcustomer,product,
orderdetail,ordermasterwherecustomer.customerno=ordermaster.customernoandordermaster.orderno=orderdetail.orderno
andorderdetail.productno=product.productnoandproduct.productno='P2007002'
intersect
selectcustomer.customernofromcustomer,product,
orderdetail,ordermasterwherecustomer.customerno=ordermaster.customernoandordermaster.orderno=orderdetail.orderno
andorderdetail.productno=product.productnoandproduct.productno='P2007001')orderbycustomer.customerno
〔24〕计算每一商品每月的销售金额总和,并将结果首先按销售月份然后按订货金额降序排序输出。
selectordermaster.customerno,ordermaster.orderdate,sum(ordersum)as'销售金额总和'fromordermaster
groupbyordermaster.customerno,ordermaster.orderdateorderbyordermaster.orderdate,销售金额总和desc
〔25〕查询订购了“键盘〞商品的客户姓名,订货数量和订货日期
selectcustomer.customername,orderdetail.qty,ordermaster.orderd