实验五复杂查询答案4汇总.docx
- 文档编号:23722840
- 上传时间:2023-05-20
- 格式:DOCX
- 页数:16
- 大小:67.11KB
实验五复杂查询答案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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 实验 复杂 查询 答案 汇总
![提示](https://static.bdocx.com/images/bang_tan.gif)