实验五-数据查询-复杂查询

数据库原理实验学号：123012008xxx姓名：xxx班级（x）小班日期：2010年月日PAGEPAGE6实验五数据查询——复杂查询一、实验目的1．掌握SQLServer查询语句的基本语法2．熟练使用SQL的Select语句对多表进行查询3．熟练掌握并运用SQLServer所提供的函数4．熟练使用SQL语句进行复杂的连接操作二、实验环境（实验的软件、硬件环境）硬件：PC机软件：SQL2000三、实验指导说明 请复习相关的查询知识点并完成如下内容。四、实验内容1．在订单数据库orderDB中，完成如下的查询：（1）用子查询查询员工“张小娟”所做的订单信息。（2）查询没有订购商品的且在北京地区的客户编号，客户名称和邮政编码，并按邮政编码降序排序。（3）查询订购了“32MDRAM”商品的订单编号，订货数量和订货单价。（4）查询与员工编号“E2008005”在同一个部门的员工编号，姓名，性别，所属部门。（5）查询既订购了P20050001商品，又订购了P20070002商品的客户编号，订单编号和订单金额（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）查找同时订购了商品为“P20070002”和商品编号为“P20070001”的商品的客户编号，客户姓名，商品编号，商品名称和销售数量，按客户编号排序输出。（24）计算每一商品每月的销售金额总和，并将结果首先按销售月份然后按订货金额降序排序输出。（25）查询订购了“键盘”商品的客户姓名，订货数量和订货日期金额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.ordernofromorderdetailgroupbyordernohavingCOUNT(orderno)>=3)groupbyorderno（16）查找订购了“32MDRAM”的商品的客户编号，客户名称，订货总数量和订货总金额。selectcustomer.customerno,customername,SUM(qty)as'订货总数量',SUM(ordermaster.ordersum)as'订货总金额'fromproduct,orderdetail,customer,ordermasterwherecustomer.customerno=ordermaster.customernoandordermaster.orderno=orderdetail.ordernoandductno=orderdetail.productnoandproduct.productname='32MDRAM'groupbycustomer.customerno,customername（17）查询每个客户订购的商品编号，商品所属类别，商品数量及订货金额，结果显示客户名称，商品所属类别，商品数量及订货金额，并按客户编号升序和按订货金额的降序排序输出。selectcustomername,ductno,ductclass,orderdetail.qty,orderdetail.price*orderdetail.qtyfromorderdetail,product,customer,ordermasterwherecustomer.customerno=ordermaster.customernoandordermaster.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=ductnogroupbyductclasshavingAVG(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)awherecustomer.customerno=ordermaster.customernoanda.customerno=customer.customernogroupbycustomernameselectcustomername,MAX(b.a)fromcustomer,(selectcustomerno,sum(ordersum)afromordermastergroupbycustomerno)bgroupbycustomernamewherecustomer.customerno=b.customernogroupbycustomername（21）查找销售总额小于元的销售员编号，姓名和销售额selectemployee.employeeno,employeename,afromemployee,(selectsaleno,SUM(ordersum)afromordermastergroupbysaleno)bwhereb.saleno=employeenoanda<5000（22）查找至少订购了种商品的客户编号，客户名称，商品编号，商品名称，数量和金额。selectcustomer.customerno,customer.customername,ductno,ductname,orderdetail.qty,orderdetail.price*orderdetail.qtyas'金额'fromcustomer,product,orderdetail,ordermasterwherecustomer.customerno=ordermaster.customernoandordermaster.orderno=orderdetail.ordernoandorderdetail.productno=ductnoandcustomer.customernoin(selectcustomer.customernofromcustomer,product,orderdetail,ordermasterwherecustomer.customerno=ordermaster.customernoandordermaster.orderno=orderdetail.ordernoandorderdetail.productno=ductnogroupbycustomer.customernohavingcount(customer.customerno)>=3)orderbycustomerno（23）查找同时订购了商品为“P20070002”和商品编号为“P20070001”的商品的客户编号，客户姓名，商品编号，商品名称和销售数量，按客户编号排序输出。selectcustomer.customerno,customername,ductno,productname,orderdetail.qtyfromcustomer,product,orderdetail,ordermasterwherecustomer.customerno=ordermaster.customernoandordermaster.orderno=orderdetail.ordernoandorderdetail.productno=ductnoandcustomer.customernoin(selectcustomer.customernofromcustomer,product,orderdetail,ordermasterwherecustomer.customerno=ordermaster.customernoandordermaster.orderno=orderdetail.ordernoandorderdetail.productno=ductnoandductno='P2007002'intersectselectcustomer.customernofromcustomer,product,orderdetail,ordermasterwherecustomer.customerno=ordermaster.customernoandordermaster.orderno=orderdetail.ordernoandorderdetail.productno=ductnoandductno='P2007001')orderbycustomer.customerno（24）计算每一商品每月的销售金额总和，并将结果首先按销售月份然后按订货金额降序排序输出。selectordermaster.customerno,ordermaster.orderdate,sum(ordersum)as'销售金额总和'fromordermastergroupbyordermaster.customerno,ordermaster.orderdateorderbyordermaster.orderdate,销售金额总和desc（25）查询订购了“键盘”商品的客户姓名，订货数量和订货日期selectcustomer.customername,orderdetail.qty,ordermaster.orderdatefromcustomer,orderdetail,ordermaster,productwherecustomer.customerno=ordermaster.customernoandorderdetail.orderno=ordermaster.ordernoductno=orderdetail.productnoandproductname='键盘'（26）查询没有订购“键盘”商品的客户名称。selectdistinctcustomernamefromcustomerwherecustomernamenotin(selectdistinctcustomernamefromcustomer,ordermaster,orderdetail,productwhereordermaster.customerno=customer.customernoandordermaster.orderno=orderdetail.ordernoandductno=ductnoandproductname='键盘')（27）查询至少销售了种商品的销售员编号，姓名，商品名称，数量及相应的单价，并按销售员编号排序输出。selectemployee.employeeno,employeename,productname,orderdetail.qty,orderdetail.pricefromemployee,product,orderdetail,ordermasterwhereemployee.employeeno=ordermaster.salenoandordermaster.orderno=orderdetail.ordernoandductno=product.productnoandemployee.employeenoin(selectemployee.employeenofromemployee,product,orderdetail,ordermasterwhereemployee.employeeno=ordermaster.salenoandordermaster.orderno=orderdetail.ordernoandductno=product.productnogroupbyemployee.employeenohavingCOUNT(employee.employeeno)>=5)orderbyemployeeno（28）查询没有订购商品的客户编号和客户名称。selecta.customernofromcustomerawherenotexists(select*fromordermasterwherea.customerno=ordermaster.customerno)（29）查询至少包含了“世界技术开发公司”所订购的商品的客户编号，客户名称，商品编号，商品名称，数量和金额。selectcustomer.customerno,customername,ductno,productname,orderdetail.qty,qty*pricefromcustomer,product,orderdetail,ordermasterwherecustomer.customerno=ordermaster.customernoandordermaster.orderno=orderdetail.ordernoandorderdetail.productno=ductnoandcustomer.customernamein(selectcustom