实验五复杂查询答案4汇总.docx
《实验五复杂查询答案4汇总.docx》由会员分享，可在线阅读，更多相关《实验五复杂查询答案4汇总.docx（16页珍藏版）》请在冰豆网上搜索。
实验五复杂查询答案4汇总
-------------------------------------------------------------------------------------------------------
（1）用子查询查询员工“张小娟”所做的订单信息。
（难易程度：
易）
select*fromordermaster
wheresalernoin
（
selectemployeenofromemployeewhereemployeename='张小娟'
）
------------------------------------------------------------------------------------------------
（2）查询没有订购商品的且在北京地区的客户编号，客户名称和邮政编码，并按邮政编码降序排序。
（难易程度：
易）
selectcustomerno,customername,zip
fromcustomerc
whereaddress='北京市'andnotexists
（select*fromordermasterowherec.customerno=o.customerno）
orderbyzipdesc
方法二：
selectCustomerNo,CustomerName,Zip
fromCustomer
whereAddress='北京市'andCustomerNonotin（selectCustomerNo
fromOrderMaster）
orderbyZipdesc
-----------------------------------------------------------------------------------------------------------------
（3）查询订购了“32MDRAM”商品的订单编号，订货数量和订货单价。
（难易程度：
易）
selectorderno,quantity,pricefromorderdetailwhereproductnoin
（
selectproductnofromproductwhereproductname='32MDRAM'
）
-------------------------------------------------------------------------------------------------------------------
（4）查询与员工编号“E2008005”在同一个部门的员工编号，姓名，性别，所属部门。
（难易程度：
易）
selectemployeeno,employeename,sex,departmentfromemployee
wheredepartmentin
（
selectdepartmentfromemployeewhereemployeeno='E2008005'
）
andemployeeno!
='E2008005'
-------------------------------------------------------------------------------------------------------------------
（5）查询既订购了P2*******商品，又订购了P2*******商品的客户编号，订单编号和订单金额。
（难易程度：
中）
（为了验证查询结果，改为：
‘P2005001’和‘P2005002’）
方法一：
selectcustomerno,orderno,ordersumfromordermaster
whereordernoin
（
selectordernofromorderdetailwhereproductno='P2005001'
）
andordernoin
（
selectordernofromorderdetailwhereproductno='P2005002'
）
方法二：
可以使用表的自身连接。
Selectcustomerno,orderno,ordersum
fromordermaster
whereordernoin
（
selecta.ordernofromorderdetaila,orderdetailb
wherea.productno='P2005001'andb.productno='P2005002'anda.orderno=b.orderno
）
---------------------------------------------------------------------------------------------------
（6）查询没有（订购“52倍速光驱”或“17寸显示器”）的客户编号，客户名称。
（为了能验证查询结果，52倍速光驱改为32MDRAM）（难易程度：
难）
（此题要注意题意的理解。
）
selectCustomerNo,CustomerNamefromCustomer
whereCustomerNoNotin
（
selectCustomerNofromOrderMaster
whereOrderNoin
（
selectdistinctOrderNo
fromOrderDetail
whereProductNoin
（
selectProductNo
fromProduct
whereProductNamein（'32MDRAM','17寸显示器'）
）
）
）
用等价的exists谓词如下：
selectCustomerNo,CustomerNamefromCustomerc
wherenotexists
（
select*fromOrderMastero
whereo.CustomerNo=c.CustomerNoandOrderNoin
（
selectOrderNofromOrderDetail
whereProductNoin
（
selectProductNofromProduct
whereProductNamein（'32MDRAM','17寸显示器'）
）
）
）
----------------------------------------------------------------------------------------------
（7）查询订单金额最高的订单编号，客户姓名，销售员名称和相应的订单金额。
（难易程度：
中）
selectorderno,customername,employeename,ordersum
fromordermastero,customerc,employeee
whereo.customerno=c.customernoando.salerno=e.employeeno
andordersum=（selectmax（ordersum）fromordermaster）
或者
Selecttop1*
from
（
selectOrderNo,CustomerName,EmployeeName,Ordersum
fromOrderMaster,Customer,Employee
whereOrderMaster.SalerNo=Employee.EmployeeNo
andOrderMaster.CustomerNo=Customer.CustomerNo
）v
orderbyv.ordersumdesc
或者
selectv.OrderNo,CustomerName,EmployeeName,v.Ordersum
fromCustomer,Employee,
（selecttop1*fromordermaster
orderbyordersumdesc
）asv
wherev.SalerNo=Employee.EmployeeNoandv.CustomerNo=Customer.CustomerNo
----------------------------------------------------------------------------------------------
（8）查询订购了“52倍速光驱”商品的订购数量，订购平均价和订购总金额。
（难易程度：
易）
selectsum（quantity）,avg（price）,sum（quantity*price）
fromorderdetail
whereproductno=（selectproductnofromproductwhereproductname='52倍速光驱'）
-----------------------------------------------------------------------------------------
（9）查询订购了“52倍速光驱”商品且订货数量界于2~4之间的订单编号，订货数量和订货金额。
（难易程度：
易）
selectorderno,sum（quantity）,sum（quantity*price）订货金额
fromorderdetail
whereproductnoin
（selectproductnofromproductwhereproductname='52倍速光驱'）
andquantitybetween2and4
groupbyorderno
------------------------------------------------------------------------------
（10）在订单主表中查询每个业务员的订单数量（难易程度：
易）
selectsalerno,count（*）
fromordermaster
groupbysalerno
------------------------------------------------------
（11）统计在业务科工作且在1973年或1967年出生的员工人数和平均工资。
（难易程度：
易）
selectcount（*）员工个数,avg（salary）平均工资
fromemployee
wheredepartment='业务科'andyear（birthday）in（1973,1967）
----------------------------------------------------------------------------------------
（12）在订单明细表中统计每种商品的销售数量和金额，并按销售金额的升序排序输出。
（难易程度：
易）
selectproductno,sum（quantity）销售数量,sum（quantity*price）销售金额
fromorderdetail
groupbyproductno
orderbysum（quantity*price）asc
--------------------------------------------------------------------------------
（13）统计客户号为“C20050001”的客户的订单数，订货总额和平均订货金额
（难易程度：
易）
selectcount（*）,sum（ordersum）,avg（ordersum）
fromordermaster
wherecustomerno='C2005001'
-----------------------------------------------------------------
（14）统计每个客户的订单数，订货总额和平均订货金额。
（难易程度：
中）
selectcustomerno,count（orderno）,sum（ordersum）,avg（ordersum）
fromordermaster
groupbycustomerno
-----------------------------------------------------------------
（15）查询订单中至少包含3种（含3种）以上商品的订单编号及订购次数，且订购的每种商品数量在3件（含3件）以上。
（题意有歧义，可不做）
若（把订购次数理解为商品的种数）
selectorderno,count（*）
fromorderdetail
wherequantity>=3
groupbyorderno
havingcount（*）>=3
----------------------------------------------------------------------
（16）查找订购了“32MDRAM”的商品的客户编号，客户名称，订货总数量和订货总金额。
（难易程度：
中）
selectc.customerno,customername,sum（quantity）,sum（ordersum）
fromcustomerc,ordermasterom,orderdetailod
whereom.customerno=c.customerno
andom.orderno=od.orderno
andc.customernoin
（
selectcustomernofromordermaster
whereordernoin
（
selectordernofromorderdetail
whereproductnoin（selectproductnofromproductwhereproductname='32MDRAM'）
）
）
groupbyc.customerno,customername
------------------------------------------------------------------------------------------------------
（17）查询每个客户订购的商品编号，商品所属类别，商品数量及订货金额，结果显示客户名称，商品所属类别，商品数量及订货金额，并按客户编号升序和按订货金额的降序排序输出。
（难易程度：
中）
selectc.customerno,customername,productclass,sum（quantity）,sum（quantity*price）订货金额
fromcustomercjoin（ordermasteromjoinorderdetailodon（om.orderno=od.orderno）joinproductpon（p.productno=od.productno）
）on（om.customerno=c.customerno）
groupbyc.customerno,customername,productclass
orderbyc.customerno,sum（quantity*price）desc---------------------------------------------------------------------
（18）按商品类别查询每类商品的订货平均单价在280元（含280元）以上的订货总数量，订货平均单价和订货总金额。
（难易程度：
中）
selectproductclass,sum（quantity）,avg（price）,sum（quantity*price）
fromorderdetail,product
whereproduct.productno=orderdetail.productno
groupbyproductclass
havingavg（price）>=280
--------------------------------------------------------------------------------
（19）查找至少有2次销售的业务员名称和销售日期。
（难易程度：
中）
selectemployeename,orderdatefromordermasterom,employeee
wheree.employeeno=om.salernoandsalernoin
（
selectsalernofromordermaster
groupbysalerno
havingcount（salerno）>=2
）
orderbyemployeename
---------------------------------------------------------------------------------
（20）查询销售金额最大的客户名称和总货款额
（难易程度：
中）
selectcustomername,r.total总货款额
fromcustomer,（
selecttop1customerno,sum（ordersum）total
fromordermaster
groupbycustomerno
）asr
wherecustomer.customerno=r.customerno
----------------------------------------------------------------------------
（21）查找销售总额小于5000元的销售员编号，姓名和销售额
（难易程度：
中）
selectemployeeno,employeename,r.total销售额
fromemployee,（
selectsalerno,sum（ordersum）totalfromordermaster
groupbysalerno
havingsum（ordersum）<5000
）asr
whereemployee.employeeno=r.salerno
-----------------------------------------------------------------------------
（22）查找至少订购了3种商品的客户编号，客户名称，商品编号，商品名称，数量和金额。
（难易程度：
中）
selectCustomer.CustomerNo,CustomerName,Product.ProductNo,ProductName,Qty,Ordersum
fromCustomer,Product,OrderDetail,OrderMaster
whereCustomer.CustomerNo=OrderMaster.CustomerNo
andOrderMaster.OrderNo=OrderDetail.OrderNo
andProduct.ProductNo=OrderDetail.ProductNo
andOrderMaster.CustomerNoin
（selectCustomerNo
from（selectCustomerNo,ProductNo
fromOrderMaster,OrderDetail
whereOrderMaster.OrderNo=OrderDetail.OrderNo
）a
groupbya.CustomerNo
havingcount（distinctProductNo）>=3
）
-----------------------------------------------------------------------------------------------------------------
（23）查找同时订购了商品为“P2*******”和商品编号为“P2*******”的商品的客户编号，客户姓名，商品编号，商品名称和销售数量，按客户编号排序输出。
（难易程度：
难）
selectordermaster.customerno,CustomerName,Product.ProductNo,ProductName,Quantity
fromCustomer,Product,ordermaster,orderdetail
whereCustomer.customerno=ordermaster.Customerno
andordermaster.OrderNo=orderdetail.OrderNo
andproduct.productno=orderdetail.productno
andordermaster.OrderNoin
（selectOrderNo
fromOrderDetail
whereProductNo='P2007001'）
andordermaster.OrderNoin
（selectOrderNo
fromOrderDetail
whereProductNo='P2007002'）
---------------------------------------------------------------------------------------------------------------
（24）计算每一商品每月的销售金额总和，并将结果首先按销售月份然后按订货金额降序排序输出。
（难易程度：
中）
Selectproductno,month（Orderdate）salemonth,count（*）countproduct,sum（quantity*price）total
fromorderdetailasod,ordermasterasom
whereod.orderno=om.orderno
groupbyproductno,month（Orderd
升级会员 