实验五数据查询复杂查询.docx
《实验五数据查询复杂查询.docx》由会员分享，可在线阅读，更多相关《实验五数据查询复杂查询.docx（11页珍藏版）》请在冰点文库上搜索。
实验五数据查询复杂查询
Thefollowingtextisamendedon12November2020.
实验五数据查询复杂查询
实验五数据查询——复杂查询
一、实验目的
1．掌握SQLServer查询语句的基本语法
2．熟练使用SQL的Select语句对多表进行查询
3．熟练掌握并运用SQLServer所提供的函数
4．熟练使用SQL语句进行复杂的连接操作
二、实验环境（实验的软件、硬件环境）
硬件：
PC机软件：
SQL2000
三、实验指导说明
请复习相关的查询知识点并完成如下内容。
四、实验内容
1．在订单数据库orderDB中，完成如下的查询：
（1）用子查询查询员工“张小娟”所做的订单信息。
（2）查询没有订购商品的且在北京地区的客户编号，客户名称和邮政编码，并按邮政编码降序排序。
（3）查询订购了“32MDRAM”商品的订单编号，订货数量和订货单价。
（4）查询与员工编号“E2008005”在同一个部门的员工编号，姓名，性别，所属部门。
（5）查询既订购了P商品，又订购了P商品的客户编号，订单编号和订单金额
（6）查询没有订购“52倍速光驱”或“17寸显示器”的客户编号，客户名称。
（7）查询订单金额最高的订单编号，客户姓名，销售员名称和相应的订单金额。
（8）查询订购了“52倍速光驱”商品的订购数量，订购平均价和订购总金额。
（9）查询订购了“52倍速光驱”商品且订货数量界于2~4之间的订单编号，订货数量和订货金额。
（10）在订单主表中查询每个业务员的订单数量
（11）统计在业务科工作且在1973年或1967年出生的员工人数和平均工资。
（12）在订单明细表中统计每种商品的销售数量和金额，并按销售金额的升序排序输出。
（13）统计客户号为“C”的客户的订单数，订货总额和平均订货金额
（14）统计每个客户的订单数，订货总额和平均订货金额。
（15）查询订单中至少包含3种（含3种）以上商品的订单编号及订购次数，且订购的商品数量在3件（含3件）以上。
（16）查找订购了“32MDRAM”的商品的客户编号，客户名称，订货总数量和订货总金额。
（17）查询每个客户订购的商品编号，商品所属类别，商品数量及订货金额，结果显示客户名称，商品所属类别，商品数量及订货金额，并按客户编号升序和按订货金额的降序排序输出。
（18）按商品类别查询每类商品的订货平均单价在280元（含280元）以上的订货总数量，订货平均单价和订货总金额。
（19）查找至少有2次销售的业务员名称和销售日期。
（20）查询销售金额最大的客户名称和总货款额
（21）查找销售总额小于5000元的销售员编号，姓名和销售额
（22）查找至少订购了3种商品的客户编号，客户名称，商品编号，商品名称，数量和金额。
（23）查找同时订购了商品为“P”和商品编号为“P”的商品的客户编号，客户姓名，商品编号，商品名称和销售数量，按客户编号排序输出。
（24）计算每一商品每月的销售金额总和，并将结果首先按销售月份然后按订货金额降序排序输出。
（25）查询订购了“键盘”商品的客户姓名，订货数量和订货日期
（26）查询每月订购“键盘”商品的客户名称。
（27）查询至少销售了5种商品的销售员编号，姓名，商品名称，数量及相应的单价，并按销售员编号排序输出。
（28）查询没有订购商品的客户编号和客户名称。
（29）查询至少包含了“世界技术开发公司”所订购的商品的客户编号，客户名称，商品编号，商品名称，数量和金额。
五、实验步骤
请完成实验内容，并写出具体的实验步骤
（1）用子查询查询员工“张小娟”所做的订单信息。
selectorderMaster.*fromemployee,orderMasterwhereorderMaster.SaleNo=employeeNoandemployeeNamein
（selectfromemployeewhereemployeeName='张小娟'）
（2）查询没有订购商品的且在北京地区的客户编号，客户名称和邮政编码，并按邮政编码降序排序。
selectdistinct,,fromcustomer,orderMasterwherecustomer.
customerNonotin（selectorderMaster.customerNofromorderMaster）and='北京市'orderbycustomer.Zipdesc
（3）查询订购了“32MDRAM”商品的订单编号，订货数量和订货单价。
selectorderDetail.OrderNo,orderDetail.Qty,orderDetail.PricefromorderDetail,productwhere
=product.ProductNoandProductName='32MDRAM'
（4）查询与员工编号“E2008005”在同一个部门的员工编号，姓名，性别，所属部门。
selectemployee.employeeNo,employee.employeeName,casewhen'M'then'男'when'F'then'女'end,
employee.Departmentfromemployeewhere=（selectemployee.Departmentfromemployeewhere
employee.employeeNo='E2008005'）
（5）查询既订购了P商品，又订购了P商品的客户编号，订单编号和订单金额
selectcustomer.customerno,ordermaster.orderno,ordermaster.ordersumfromordermaster,customer,orderdetailwhere=ordermaster.customernoandordermaster.orderno=orderdetail.ordernoandorderdetail.productno='P2007002'
intersect
selectcustomer.customerno,ordermaster.orderno,ordermaster.ordersumfromordermaster,customer,orderdetailwhere=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）as'订购平均价',SUM（ordermaster.ordersum）as'订购总金额'fromorderdetail,ordermaster,productwhereorderdetail.orderno=ordermaster.ordernoandorderdetail.productno=product.productnoandproduct.productname='52倍速光驱'
（9）查询订购了“52倍速光驱”商品且订货数量界于~4之间的订单编号，订货数量和订货金额。
select,orderdetail.qty,ordermaster.ordersumfromordermaster,orderdetail,productwhere
orderdetail.orderno=ordermaster.ordernoandproduct.productno=andproduct.productname='52倍速光驱'andorderdetail.qtybetween2and4
（10）在订单主表中查询每个业务员的订单数量
select,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）统计客户号为“C”的客户的订单数，订货总额和平均订货金额
selectcustomer.customerno,COUNT（orderdetail.orderno）as'订单数',SUM（ordermaster.ordersum）as'订货总额',AVG（ordermaster.ordersum）as'平均订单金额'fromorderdetail,ordermaster,customerwhereorderdetail.orderno=ordermaster.ordernoand=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.ordernoand=customer.customernogroupbycustomer.customerno
（15）查询订单中至少包含种（含种）以上商品的订单编号及订购次数，且订购的商品数量在件（含件）以上。
selectorderno,count（*）fromorderdetailwhereordernoin（select
fromorderdetailgroupbyordernohavingCOUNT（orderno）>=3）groupbyorderno
（16）查找订购了“32MDRAM”的商品的客户编号，客户名称，订货总数量和订货总金额。
select,customername,SUM（qty）as'订货总数量',SUM（ordermaster.ordersum）as'订货总金额'
fromproduct,orderdetail,customer,ordermasterwherecustomer.customerno=ordermaster.customernoandordermaster.orderno
=orderdetail.ordernoand=orderdetail.productnoandproduct.productname='32MDRAM'
groupby,customername
（17）查询每个客户订购的商品编号，商品所属类别，商品数量及订货金额，结果显示客户名称，商品所属类别，商品数量及
订货金额，并按客户编号升序和按订货金额的降序排序输出。
selectcustomername,,,orderdetail.qty,orderdetail.price*orderdetail.qty
fromorderdetail,product,customer,ordermasterwhere=ordermaster.customernoand
ordermaster.orderno=orderdetail.ordernoandorderdetail.productno=product.productnoorderbyasc
orderdetail.price*orderdetail.qtydesc
（18）按商品类别查询每类商品的订货平均单价在元（含元）以上的订货总数量，订货平均单价和订货总金额。
selectCOUNT（qty）as'订货总数量',AVG（price）as'订货评价单价',SUM（ordersum）as'订货总金额'fromproduct,ordermastera,
orderdetailbwhere=andproduct.productno=groupbyhavingAVG（price）>=280
（19）查找至少有次销售的业务员名称和销售日期。
selectemployee.employeename,ordermaster.orderdatefromemployee,ordermasterwhereemployee.employeeno=ordermaster.
salenoandemployeenoin（selectsalenofromordermastergroupbysalenohavingCOUNT（*）>=2）orderbyemployeename
（20）查询销售金额最大的客户名称和总货款额
select,maxfromcustomer,ordermaster,（selecttop1ordermaster.customerno,SUMas'b'fromordermastergroupbycustomerno）a
where=and=customer.customernogroupbycustomername
selectcustomername,MAXfromcustomer,（selectcustomerno,sum（ordersum）afromordermastergroupbycustomerno）bgroupbycustomername
wherecustomer.customerno=groupbycustomername
（21）查找销售总额小于元的销售员编号，姓名和销售额
select,employeename,afromemployee,（selectsaleno,SUM（ordersum）afromordermastergroupbysaleno）b
where=employeenoanda<5000
（22）查找至少订购了种商品的客户编号，客户名称，商品编号，商品名称，数量和金额。
select,,,,,
*as'金额'fromcustomer,product,orderdetail,ordermasterwhere=
ordermaster.customernoand=andorderdetail.productno=
andin（selectfromcustomer,product,orderdetail,ordermasterwhere=
ordermaster.customernoand=andorderdetail.productno=
groupbyhavingcount>=3）orderbycustomerno
（23）查找同时订购了商品为“P”和商品编号为“P”的商品的客户编号，客户姓名，商品编号，
商品名称和销售数量，按客户编号排序输出。
select,customername,,productname,fromcustomer,product,
orderdetail,ordermasterwhere=ordermaster.customernoand=
andorderdetail.productno=andin
（selectfromcustomer,product,
orderdetail,ordermasterwhere=ordermaster.customernoand=
andorderdetail.productno=and='P2007002'
intersect
selectfromcustomer,product,
orderdetail,ordermasterwhere=ordermaster.customernoand=
andorderdetail.productno=and='P2007001'）orderby
（24）计算每一商品每月的销售金额总和，并将结果首先按销售月份然后按订货金额降序排序输出。
select,ordermaster.orderdate,sum（ordersum）as'销售金额总和'fromordermaster
groupbyordermaster.customerno,orderbyordermaster.orderdate,销售金额总和desc
（25）查询订购了“键盘”商品的客户姓名，订货数量和订货日期
select,,ordermaster.orderdatefromcustomer,orderdetail,ordermaster,
productwhere=ordermaster.customernoandorderdetail.orderno=ordermaster.ordernoand
=orderdetail.productnoandproductname='键盘'
（26）查询没有订购“键盘”商品的客户名称。
selectdistinctcustomernamefromcustomerwherecustomernamenotin（
selectdistinctcustomernamefromcustomer,ordermaster,orderdetail,productwhere
ordermaster.customerno=andordermaster.orderno=orderdetail.ordernoand
=andproductname='键盘'）
（27）查询至少销售了种商品的销售员编号，姓名，商品名称，数量及相应的单价，并按销售员编号排序输出。
select,employeename,productname,,orderdetail.pricefromemployee,product,
orderdetail,ordermasterwhere=and=
and=product.productnoandin
（selectfromemployee,product,
orderdetail,ordermasterwhere=and=
and=product.productnogroupbyemployee.employeenohavingCOUNT>=5）
orderbyemployeeno
（28）查询没有订购商品的客户编号和客户名称。
selectfromcustomerawherenotexists（
select*fromordermasterwhere=
（29）查询至少包含了“世界技术开发公司”所订购的商品的客户编号，客户名称，商品编号，商品名称，数量和金额。
select,customername,,productname,orderdetail.qty,qty*pricefrom
customer,product,orderdetail,ordermasterwhere=ordermaster.customernoandordermaster.orderno
=orderdetail.ordernoandorderdetail.productno=andcustomer.customernamein
（
selectcustomernamefromcustomerwhere
notexists
（select*from（selectcustomername,orderdetail.productnofromcustomercustoemrb,orderdetail,ordermaster,productproductb
where='世界技术开发公司'andcustoemrb.customerno=ordermaster.customernoand
升级会员 