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

1、实验五 数据查询复杂查询一、实验目的 1掌握SQLServer查询语句的基本语法2熟练使用SQL的Select语句对多表进行查询3熟练掌握并运用SQLServer所提供的函数4熟练使用SQL语句进行复杂的连接操作 二、实验环境（实验的软件、硬件环境）硬件：PC机 软件：SQL2000三、实验指导说明请复习相关的查询知识点并完成如下内容。四、实验内容1在订单数据库orderDB中，完成如下的查询：（1）用子查询查询员工“张小娟”所做的订单信息。（2）查询没有订购商品的且在北京地区的客户编号，客户名称和邮政编码，并按邮政编码降序排序。（3）查询订购了“32M DRAM”商品的订单编号，订货数量和订

2、货单价。（4）查询与员工编号“E2008005”在同一个部门的员工编号，姓名，性别，所属部门。（5）查询既订购了P20050001商品，又订购了P20070002商品的客户编号，订单编号和订单金额（6）查询没有订购“52倍速光驱”或“17寸显示器”的客户编号，客户名称。（7）查询订单金额最高的订单编号，客户姓名，销售员名称和相应的订单金额。（8）查询订购了“52倍速光驱”商品的订购数量，订购平均价和订购总金额。（9）查询订购了“52倍速光驱”商品且订货数量界于24之间的订单编号，订货数量和订货金额。（10）在订单主表中查询每个业务员的订单数量（11）统计在业务科工作且在1973年或1967年出

3、生的员工人数和平均工资。（12）在订单明细表中统计每种商品的销售数量和金额，并按销售金额的升序排序输出。（13）统计客户号为“C20050001”的客户的订单数，订货总额和平均订货金额（14）统计每个客户的订单数，订货总额和平均订货金额。（15）查询订单中至少包含3种（含3种）以上商品的订单编号及订购次数，且订购的商品数量在3件（含3件）以上。（16）查找订购了“32M DRAM”的商品的客户编号，客户名称，订货总数量和订货总金额。（17）查询每个客户订购的商品编号，商品所属类别，商品数量及订货金额，结果显示客户名称，商品所属类别，商品数量及订货金额，并按客户编号升序和按订货金额的降序排序输出

4、。（18）按商品类别查询每类商品的订货平均单价在280元（含280元）以上的订货总数量，订货平均单价和订货总金额。（19）查找至少有2次销售的业务员名称和销售日期。（20）查询销售金额最大的客户名称和总货款额（21）查找销售总额小于5000元的销售员编号，姓名和销售额（22）查找至少订购了3种商品的客户编号，客户名称，商品编号，商品名称，数量和金额。（23）查找同时订购了商品为“P20070002”和商品编号为“P20070001”的商品的客户编号，客户姓名，商品编号，商品名称和销售数量，按客户编号排序输出。（24）计算每一商品每月的销售金额总和，并将结果首先按销售月份然后按订货金额降序排序输

5、出。（25）查询订购了“键盘”商品的客户姓名，订货数量和订货日期（26）查询每月订购“键盘”商品的客户名称。（27）查询至少销售了5种商品的销售员编号，姓名，商品名称，数量及相应的单价，并按销售员编号排序输出。（28）查询没有订购商品的客户编号和客户名称。（29）查询至少包含了“世界技术开发公司”所订购的商品的客户编号，客户名称，商品编号，商品名称，数量和金额。五、实验步骤请完成实验内容，并写出具体的实验步骤（1）用子查询查询员工“张小娟”所做的订单信息。select orderMaster.* from employee ,orderMaster where orderMaster .Sal

6、eNo =employeeNo and employeeName in(select employee.employeeName from employee where employeeName ='张小娟')（2）查询没有订购商品的且在北京地区的客户编号，客户名称和邮政编码，并按邮政编码降序排序。select distinct customer.CustomerNo ,customer.customerName ,customer.Zip from customer ,orderMaster where customer .customerNo not in(select o

7、rderMaster .customerNo from orderMaster )and customer.addr='北京市'order by customer .Zip desc（3）查询订购了“32M DRAM”商品的订单编号，订货数量和订货单价。select orderDetail .OrderNo ,orderDetail .Qty ,orderDetail .Price from orderDetail ,product where orderDetail.ProductNo =product .ProductNo and ProductName ='32M

8、 DRAM'（4）查询与员工编号“E2008005”在同一个部门的员工编号，姓名，性别，所属部门。select employee .employeeNo ,employee .employeeName ,case employee.Sex when 'M'then '男'when 'F'then '女'end ,employee .Department from employee where employee.Department =(select employee .Department from employee wh

9、ere employee .employeeNo ='E2008005')（5）查询既订购了P20050001商品，又订购了P20070002商品的客户编号，订单编号和订单金额select customer .customerno,ordermaster .orderno ,ordermaster .ordersum from ordermaster ,customer,orderdetail where customer.customerno =ordermaster .customerno and ordermaster .orderno =orderdetail .ord

10、erno and orderdetail .productno='P2007002'intersectselect customer .customerno,ordermaster .orderno ,ordermaster .ordersum from ordermaster ,customer,orderdetail where customer.customerno =ordermaster .customerno and ordermaster .orderno =orderdetail .orderno and orderdetail .productno='

11、P2005001'（6）查询没有订购“52倍速光驱”或“17寸显示器”的客户编号，客户名称。select distinct customer .customerno ,customer .customername from customer ,orderdetail ,ordermaster,product where orderdetail .orderno =ordermaster .orderno and ordermaster .customerno =customer .customerno and orderdetail .productno =product .produ

12、ctno and product .productname not in ('52倍速光驱','17寸显示器') （7）查询订单金额最高的订单编号，客户姓名，销售员名称和相应的订单金额。select ordermaster .orderno ,customer .customername ,employee .employeename ,ordermaster .ordersum from employee ,ordermaster,customer where employee .employeeno =ordermaster .saleno and cust

13、omer .customerno =ordermaster .customerno and ordermaster .ordersum =(select MAX (ordermaster .ordersum )from ordermaster)（8）查询订购了“52倍速光驱”商品的订购数量，订购平均价和订购总金额。select SUM (orderdetail .qty )as'订购数量',AVG (ordermaster.ordersum )as'订购平均价',SUM (ordermaster .ordersum )as'订购总金额' from

14、 orderdetail ,ordermaster ,product where orderdetail .orderno =ordermaster .orderno and orderdetail .productno =product .productno and product .productname ='52倍速光驱'（9）查询订购了“52倍速光驱”商品且订货数量界于4之间的订单编号，订货数量和订货金额。select orderdetail.orderno ,orderdetail .qty ,ordermaster .ordersum from ordermaste

15、r,orderdetail ,product whereorderdetail .orderno =ordermaster .orderno and product .productno =ductno and product .productname ='52倍速光驱'and orderdetail .qty between 2 and 4（10）在订单主表中查询每个业务员的订单数量select ordermaster.saleno ,COUNT (*)as '订单数量'from ordermaster group by sale

16、no （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 orderde

17、tail group by orderdetail .productno order by SUM(orderdetail .price *orderdetail .qty )（13）统计客户号为“C20050001”的客户的订单数，订货总额和平均订货金额select customer .customerno ,COUNT (orderdetail .orderno )as '订单数',SUM (ordermaster .ordersum )as '订货总额',AVG (ordermaster .ordersum )as '平均订单金额' fro

18、m orderdetail ,ordermaster ,customer where orderdetail .orderno =ordermaster .orderno and ordermaster.customerno =customer .customerno and customer .customerno ='C2005001' group by customer .customerno （14）统计每个客户的订单数，订货总额和平均订货金额。select customer .customerno ,COUNT (orderdetail .orderno )as &#

19、39;订单数',SUM (ordermaster .ordersum )as '订货总额',AVG (ordermaster .ordersum )as '平均订单金额' from orderdetail ,ordermaster ,customer where orderdetail .orderno =ordermaster .orderno and ordermaster.customerno =customer .customerno group by customer .customerno （15）查询订单中至少包含种（含种）以上商品的订单编

20、号及订购次数，且订购的商品数量在件（含件）以上。select orderno ,count(*)from orderdetail where orderno in(select orderdetail.orderno from orderdetail group by orderno having COUNT(orderno)>=3) group by orderno （16）查找订购了“32M DRAM”的商品的客户编号，客户名称，订货总数量和订货总金额。select customer.customerno,customername,SUM(qty)as '订货总数量'

21、,SUM(ordermaster .ordersum)as '订货总金额'from product,orderdetail,customer,ordermaster where customer .customerno =ordermaster .customerno and ordermaster .orderno=orderdetail .orderno and ductno =orderdetail .productno and product .productname ='32M DRAM'group by customer.cus

22、tomerno ,customername（17）查询每个客户订购的商品编号，商品所属类别，商品数量及订货金额，结果显示客户名称，商品所属类别，商品数量及订货金额，并按客户编号升序和按订货金额的降序排序输出。select customername,ductno,ductclass,orderdetail .qty ,orderdetail .price *orderdetail .qty from orderdetail ,product ,customer ,ordermaster where customer.customerno =orderm

23、aster .customerno and ordermaster .orderno =orderdetail .orderno and orderdetail .productno =product .productno order by customer.customerno asc,orderdetail .price *orderdetail .qty desc（18）按商品类别查询每类商品的订货平均单价在元（含元）以上的订货总数量，订货平均单价和订货总金额。select COUNT(qty)as'订货总数量',AVG (price )as'订货评价单价'

24、;,SUM(ordersum )as'订货总金额' from product, ordermaster a,orderdetail b where a.orderno =b.orderno and product .productno =ductno group by ductclass having AVG (price )>=280（19）查找至少有次销售的业务员名称和销售日期。select employee .employeename ,ordermaster .orderdate from employee ,ordermaster

25、 where employee .employeeno =ordermaster.saleno and employeeno in (select saleno from ordermaster group by saleno having COUNT (*)>=2) order by employeename （20）查询销售金额最大的客户名称和总货款额select customer.customername,max(a.b) from customer,ordermaster,(select top 1 ordermaster .customerno,SUM(ordermaster.

26、ordersum) as 'b' from ordermaster group by customerno )awhere customer.customerno =ordermaster.customerno and a.customerno =customer .customerno group by customername select customername,MAX(b.a) from customer,(select customerno,sum(ordersum )a from ordermaster group by customerno)b group by

27、 customername where customer .customerno =b.customerno group by customername （21）查找销售总额小于元的销售员编号，姓名和销售额select employee.employeeno,employeename,a from employee,(select saleno,SUM(ordersum) a from ordermaster group by saleno )b where b.saleno =employeeno and a<5000（22）查找至少订购了种商品的客户编号，客户名称，商品编号，商品名称

28、，数量和金额。select customer.customerno,customer.customername,ductno,ductname,orderdetail.qty,orderdetail.price*orderdetail.qty as'金额' from customer,product,orderdetail,ordermaster where customer.customerno=ordermaster .customerno and ordermaster.orderno =orderdetail.orderno

29、and orderdetail .productno =ductno and customer.customerno in (select customer.customerno from customer,product,orderdetail,ordermaster where customer.customerno=ordermaster .customerno and ordermaster.orderno =orderdetail.orderno and orderdetail .productno =ductno group by cus

30、tomer.customerno having count(customer.customerno)>=3 ) order by customerno（23）查找同时订购了商品为“P20070002”和商品编号为“P20070001”的商品的客户编号，客户姓名，商品编号， 商品名称和销售数量，按客户编号排序输出。select customer.customerno,customername,ductno ,productname,orderdetail.qty from customer,product,orderdetail,ordermaster where c

31、ustomer.customerno=ordermaster .customerno and ordermaster.orderno=orderdetail.orderno and orderdetail .productno =ductno and customer.customerno in(select customer.customerno from customer,product,orderdetail,ordermaster where customer.customerno=ordermaster .customerno and ordermaster.o

32、rderno=orderdetail.orderno and orderdetail .productno =ductno and ductno ='P2007002'intersectselect customer.customerno from customer,product,orderdetail,ordermaster where customer.customerno=ordermaster .customerno and ordermaster.orderno=orderdetail.orderno and orderd

33、etail .productno =ductno and ductno ='P2007001') order by customer.customerno （24）计算每一商品每月的销售金额总和，并将结果首先按销售月份然后按订货金额降序排序输出。select ordermaster.customerno ,ordermaster .orderdate,sum(ordersum) as '销售金额总和' from ordermaster group by ordermaster .customerno,ordermast

34、er.orderdate order by ordermaster .orderdate,销售金额总和 desc（25）查询订购了“键盘”商品的客户姓名，订货数量和订货日期select customer.customername,orderdetail.qty,ordermaster .orderdate from customer,orderdetail,ordermaster,product where customer.customerno=ordermaster .customerno and orderdetail .orderno =ordermaster .orderno and

35、ductno =orderdetail .productno and productname='键盘'（26）查询没有订购“键盘”商品的客户名称。select distinct customername from customer where customername not in(select distinct customername from customer,ordermaster,orderdetail,product whereordermaster .customerno =customer.customerno and ordermaste

36、r .orderno =orderdetail .orderno and ductno =ductno and productname='键盘')（27）查询至少销售了种商品的销售员编号，姓名，商品名称，数量及相应的单价，并按销售员编号排序输出。select employee.employeeno,employeename,productname,orderdetail.qty,orderdetail .price from employee,product,orderdetail,ordermaster where empl

37、oyee.employeeno =ordermaster.saleno and ordermaster.orderno =orderdetail.orderno and ductno =product .productno and employee.employeeno in(select employee.employeeno from employee,product,orderdetail,ordermaster where employee.employeeno =ordermaster.saleno and ordermaster.orderno =or

38、derdetail.orderno and ductno =product .productno group by employee .employeeno having COUNT(employee.employeeno)>=5)order by employeeno （28）查询没有订购商品的客户编号和客户名称。select a.customerno from customer a where not exists(select *from ordermaster where a.customerno =ordermaster.customerno)（29）查询至少包含了“世界技术开发公司”