实验五数据查询复杂查询

实验五 数据查询复杂查询一、实验目的1.掌握SQLServer查询语句的基本语法2熟练使用SQL 的 Select 语句对多表进行查询3.熟练掌握并运用SQLServer所提供的函数4熟练使用SQL 语句进行复杂的连接操作二、实验环境（实验的软件、硬件环境）硬件：PC机 软件：SQL2000三、 实验指导说明请复习相关的查询知识点并完成如下内容。四、实验内容1 在订单数据库orderDB 中，完成如下的查询：（ 1）用子查询查询员工“张小娟”所做的订单信息。（ 2）查询没有订购商品的且在北京地区的客户编号，客户名称和邮政编码，并按邮政编码降序排序。（ 3）查询订购了“32M DRAM ”商品的订单编号，订货数量和订货单价。（4）查询与员工编号” E2008005”在同一个部门的员工编号，姓名，性别， 所属部门。（ 5）查询既订购了P 商品，又订购了 P 商品的客户编号，订单编号和订单金额（6）查询没有订购“52 倍速光驱”或“ 17寸显示器”的客户编号，客户名称。（ 7）查询订单金额最高的订单编号，客户姓名，销售员名称和相应的订单金额。8）查询订购了“52 倍速光驱”商品的订购数量，订购平均价和订购总金额。（ 9） 查询订购了 “ 52 倍速光驱” 商品且订货数量界于 24 之间的订单编号，订货数量和订货金额。（ 10）在订单主表中查询每个业务员的订单数量（ 11） 统计在业务科工作且在1973 年或 1967 年出生的员工人数和平均工资。（ 12）在订单明细表中统计每种商品的销售数量和金额，并按销售金额的升序排序输出。（ 13）统计客户号为“ C”的客户的订单数，订货总额和平均订货金额（ 14）统计每个客户的订单数，订货总额和平均订货金额。（ 15） 查询订单中至少包含3 种（含 3 种） 以上商品的订单编号及订购次数，且订购的商品数量在3 件（含 3 件）以上。（ 16）查找订购了“32M DRAM ”的商品的客户编号，客户名称，订货总数量和订货总金额。（ 17） 查询每个客户订购的商品编号， 商品所属类别， 商品数量及订货金额，结果显示客户名称， 商品所属类别， 商品数量及订货金额， 并按客户编号升序和 按订货金额的降序排序输出。（ 18）按商品类别查询每类商品的订货平均单价在280元（含 280元）以上的订货总数量，订货平均单价和订货总金额。（ 19）查找至少有2 次销售的业务员名称和销售日期。（ 20）查询销售金额最大的客户名称和总货款额（ 21）查找销售总额小于5000元的销售员编号，姓名和销售额（ 22）查找至少订购了3 种商品的客户编号，客户名称，商品编号，商品名称，数量和金额。（ 23）查找同时订贝了商品为“ P”和商品编号为“ P”的商品的客户编号， 客户姓名，商品编号，商品名称和销售数量，按客户编号排序输出。（ 24）计算每一商品每月的销售金额总和，并将结果首先按销售月份然后按订货金额降序排序输出。（ 25）查询订购了“键盘”商品的客户姓名，订货数量和订货日期（ 26）查询每月订购“键盘”商品的客户名称。（ 27）查询至少销售了5 种商品的销售员编号，姓名，商品名称，数量及相应的单价，并按销售员编号排序输出。（ 28）查询没有订购商品的客户编号和客户名称。（ 29）查询至少包含了“世界技术开发公司”所订购的商品的客户编号，客 户名称，商品编号，商品名称，数量和金额。五、实验步骤请完成实验内容，并写出具体的实验步骤( 1) 用子查询查询员工 “张小娟 ” 所做的订单信息 。select orderMaster.* from employee ,orderMaster where orderMaster .SaleNo =employeeNo and employeeName in(select from employeewhere employeeName=张小娟)( 2) 查询没有订购商品的且在北京地区的客户编号， 客户名称和邮政编码 ， 并按邮政编码降序排序。select distinct , , from customer ,orderMaster where customer .customerNo not in(selectorderMaster .customerNo from orderMaster)and =北京市 order by customer.Zip desc( 3) 查询订购了“32M DRAM ”商品的订单编号， 订货数量和订货单价。select orderDetail .OrderNo ,orderDetail .Qty ,orderDetail .Price from orderDetail ,product where=product .ProductNo and ProductName =32M DRAM(4)查询与员工编号 E2008005”在同一个部门的员工编号，姓名，性别，所属部门 。select employee .employeeNo ,employee .employeeName ,case when Mthen男when Fthen 女end ,employee .Department from employee where =(selectemployee .Department from employee whereemployee .employeeNo =E2008005)( 5) 查询既订购了 P 商品 ， 又订购了 P 商品的客户编号， 订单编号和订单金额select customer.customerno,ordermaster.orderno ,ordermaster.ordersum from ordermaster,customer,orderdetail where =ordermaster.customerno and ordermaster.orderno =orderdetail .orderno andorderdetail .productno=P2007002intersectselect customer.customerno,ordermaster.orderno ,ordermaster.ordersum from ordermaster,customer,orderdetail where =ordermaster.customerno and ordermaster.orderno =orderdetail .orderno andorderdetail .productno=P2005001( 6) 查询没有订购“52倍速光驱”或 “17寸显示器”的客户编号， 客户名称 。select distinct customer .customerno ,customer .customernamefrom customer,orderdetail ,ordermaster,product where orderdetail .orderno =ordermaster.orderno and ordermaster.customerno=customer.customerno and orderdetail .productno =product .productno and product .productname notin （52倍速光驱 ,17 寸显示器 ）( 7) 查询订单金额最高的订单编号， 客户姓名 ， 销售员名称和相应的订单金额 。selectordermaster.orderno ,customer.customername,employee .employeename,ordermaster.ordersum from employee ,ordermaster,customer whereemployee .employeeno=ordermaster.salenoand customer.customerno =ordermaster.customerno and ordermaster.ordersum =（select MAX（ordermaster.ordersum ）from ordermaster）( 8) 查询订购了“52倍速光驱 ”商品的订购数量， 订购平均价和订购总金额。select SUM （orderdetail .qty ）aS订购数量,AVG ）aS订购平均价,SUM（ordermaster.ordersum）as订购总金额from orderdetail ,ordermaster,productwhere orderdetail .orderno =ordermaster.orderno and orderdetail .productno=product .productno and product .productname=52 倍速光驱 ( 9) 查询订购了“52倍速光驱”商品且订货数量界于 4 之间的订单编号，订货数量和订货金额。select ,orderdetail .qty ,ordermaster.ordersum fromordermaster,orderdetail ,product whereorderdetail .orderno =ordermaster.orderno and product .productno = and product .productname =52 倍速光驱 and orderdetail .qty between 2 and 4( 10) 在订单主表中查询每个业务员的订单数量select ,COUNT (*)as订单数量from ordermastergroup by saleno( 11) 统计在业务科工作且在年或年出生的员工人数和平均工资。select COUNT (*) as员工人数,AVG (employee .salary )as平均工资from employee( 12) 在订单明细表中统计每种商品的销售数量和金额， 并按销售金额的升序排序输出 。select orderdetail .productno ,SUM (orderdetail .qty )as销售数量,SUM (orderdetail .price *orderdetail .qty )as 金额 from orderdetail group by orderdetail .productno order by SUM(orderdetail .price * orderdetail .qty )( 13) 统计客户号为“C” 的客户的订单数， 订货总额和平均订货金额select customer.customerno ,COUNT (orderdetail .orderno )as订单数,SUM(ordermaster.ordersum )as订货总额,AVG (ordermaster.ordersum)as平均订单金额 from orderdetail ,ordermaster,customerwhere orderdetail .orderno=ordermaster.orderno and =customer .customerno andcustomer.customerno =C2005001 group by customer.customerno订货总额和平均订货金额14) 统计每个客户的订单数select customer.customerno ,COUNT (orderdetail .orderno )as订单数,SUM(ordermaster.ordersum )as订货总额,AVG (ordermaster.ordersum)as平均 订单金额 from orderdetail ,ordermaster,customerwhere orderdetail .orderno =ordermaster.orderno and =customer .customerno group by customer.customerno( 15) 查询订单中至少包含种( 含种 ) 以上商品的订单编号及订购次数，且订购的商品数量在件( 含件 ) 以上 。select orderno ,count(*) from orderdetail where orderno in( selectfrom orderdetail group by orderno having COUNT (orderno)=3) group by orderno( 16) 查找订购了“32M DRAM ”的商品的客户编号， 客户名称 ， 订货总数量和订货总金额。select ,customernameSUM (qty)as 订货总数量,SUM (ordermaster .ordersum)as订货总金额from product,orderdetail,customer,ordermasterwhere customer .customerno =ordermaster.customernoandordermaster.orderno=orderdetail .orderno and =orderdetail .productno and product .productname =32M DRAM group by ,customername( 17) 查询每个客户订购的商品编号， 商品所属类别 ， 商品数量及订货金额 ， 结果显示客户名称， 商品所属类别 ， 商品数量及订货金额 ， 并按客户编号升序和按订货金额的降序排序输出 。select customername,orderdetail .qty ,orderdetail .price *orderdetail .qty from orderdetail ,product ,customer ,ordermaster where =ordermaster.customernoandordermaster.orderno =orderdetail .orderno and orderdetail .productno =product .productno order by asc,orderdetail . price *orderdetail .qty desc( 18) 按商品类别查询每类商品的订货平均单价在元 ( 含元 ) 以上的订货总数量 ， 订货平均单价和订货总金额。select COUNT (qty)aS订货总数量,AVG (price )aS订货评价单价,SUM (ordersum )aS订货总金额from product, ordermastera, orderdetail b where = and product .productno = group by having AVG (price )=280( 19) 查找至少有次销售的业务员名称和销售日期 。select employee .employeename,ordermaster .orderdate from employee , ordermasterwhere employee.employeeno =ordermaster. salenoand employeeno in (selectsalenofrom ordermaster group by saleno having COUNT (*)= 2) order by employeename( 20) 查询销售金额最大的客户名称和总货款额select ,max from customer,ordermaster,(select top 1 ordermaster.customerno,SUM asb from ordermaster group by customerno)a where = and =customer .customerno group by customernameselectcustomername, MAX from customer,(selectcustomerno,sum(ordersum)a from ordermaster group by customerno)b group by customername where customer .customerno = group by customername( 21) 查找销售总额小于元的销售员编号， 姓名和销售额select ,employeename,a from employee,(select saleno,SUM (ordersum) a from ordermastergroup by saleno)bwhere =employeeno and a=3 ) order by customerno(23)查找同时订购了商品为P”和商品编号为P”的商品的客户编号,客户姓名 ， 商品编号 ，商品名称和销售数量， 按客户编号排序输出 。select,customername, ,productname, from customer,product, orderdetail,ordermasterwhere =ordermaster.customernoand =and orderdetail .productno = and in(select from customer,product,orderdetail,ordermasterwhere =ordermaster.customernoand =and orderdetail .productno = and =P2007002intersectselect from customer,product,orderdetail,ordermasterwhere =ordermaster.customernoand =and orderdetail .productno = and =P2007001) order by( 24) 计算每一商品每月的销售金额总和 ， 并将结果首先按销售月份然后按订货金额降序排序输出 。select ,ordermaster.orderdate,sum(ordersum) as销售金额总和fromordermastergroup by ordermaster.customerno, order by ordermaster.orderdate,销售金额总和 desc( 25) 查询订购了 “键盘 ”商品的客户姓名 ， 订货数量和订货日期select ,ordermaster.orderdatefrom customer,orderdetail ,ordermaster, product where =ordermaster.customernoandorderdetail .orderno =ordermaster.orderno and=orderdetail .productno and productname=键盘( 26) 查询没有订购 “键盘 ”商品的客户名称。select distinct customernamefrom customer where customernamenot in( select distinct customernamefrom customer,ordermaster,orderdetail,product whereordermaster.customerno= andordermaster.orderno =orderdetail .orderno and=and productname=键盘)( 27) 查询至少销售了种商品的销售员编号， 姓名 ， 商品名称 ， 数量及相应的单价 ， 并按销售员编号排序输出 。select,employeename,productname,orderdetail .price from employee,product, orderdetail,ordermasterwhere = and = and =product .productno and in(select from employee,product, orderdetail,ordermasterwhere = and = and =product .productno group by employee .employeeno having COUNT=5)order by employeeno( 28) 查询没有订购商品的客户编号和客户名称。select from customer a where not exists( select * from ordermaster where =( 29) 查询至少包含了 “世界技术开发公司 ”所订购的商品的客户编号， 客户名称 ， 商品编号 ， 商品名称 ， 数量和金额。select ,customername, ,productname,orderdetail .qty ,qty*price from customer,product ,orderdetail ,ordermasterwhere =ordermaster.customerno andordermaster.orderno=orderdetail .orderno and orderdetail .productno = and customer.customername in(select customernamefrom customer where not exists(select*from (selectcustomername,orderdetail .productno from customer custoemrb,orderdetail ,ordermaster,product productbwhere =世界技术开发公司and custoemrb .customerno=ordermaster.customernoandordermaster.orderno =orderdetail .orderno and orderdetail .productno =a where not exists (select*from (select customername,orderdetail .productno from customer custoemrb,orderdetail ,ordermaster,product productb where custoemrb .customerno=ordermaster.customernoand ordermaster.orderno =orderdetail .orderno and orderdetail .productno = b where = and = )六、思考题：1 存在量词与集合运算in 、连接运算和全程量词之间的关系如何？他们可以互相替换吗？给出你的理由2 . Where子句和having子句都是用于指定查询条件的，请区别它们的异同?用实例说明。3在分组聚集操作中，为什么在查询列中，除了聚集函数运算外，其它表达式必须包含在group by 子句中？七、总结 （ 实验过程的体会、心得和实验教与学之间还需改进的内容）