1、实验五 数据查询复杂查询实验五 数据查询复杂查询一、实验目的 1掌握SQLServer查询语句的基本语法2熟练使用SQL的Select语句对多表进行查询3熟练掌握并运用SQLServer所提供的函数4熟练使用SQL语句进行复杂的连接操作 二、实验环境(实验的软件、硬件环境)硬件:PC机 软件:SQL2000三、实验指导说明 请复习相关的查询知识点并完成如下内容。四、实验内容1在订单数据库orderDB中,完成如下的查询:(1)用子查询查询员工“张小娟”所做的订单信息。(2)查询没有订购商品的且在北京地区的客户编号,客户名称和邮政编码,并按邮政编码降序排序。(3)查询订购了“32M DRAM”商
2、品的订单编号,订货数量和订货单价。(4)查询与员工编号“E2008005”在同一个部门的员工编号,姓名,性别,所属部门。(5)查询既订购了P2*商品,又订购了P2*商品的客户编号,订单编号和订单金额(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)查找同时订购了商品为“P2*”和商品编号为“P2*”的商品的客户编号,客户姓名,商品编号,商品名称和销售数量,按客户编号排序输出。(24)计算每一商品每月的销售金额总和,并将结果首先按销售月份然后按订货金额降序排序输出。(25)查询订购了
5、“键盘”商品的客户姓名,订货数量和订货日期(26)查询每月订购“键盘”商品的客户名称。(27)查询至少销售了5种商品的销售员编号,姓名,商品名称,数量及相应的单价,并按销售员编号排序输出。(28)查询没有订购商品的客户编号和客户名称。(29)查询至少包含了“世界技术开发公司”所订购的商品的客户编号,客户名称,商品编号,商品名称,数量和金额。五、实验步骤请完成实验内容,并写出具体的实验步骤(1)用子查询查询员工“张小娟”所做的订单信息。select orderMaster.* from employee ,orderMaster where orderMaster .SaleNo =employ
6、eeNo 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 orderMaster .customerN
7、o 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 DRAM(4)查询与员工编号“E2008005”在同一个部门的员工编号
8、,姓名,性别,所属部门。select employee .employeeNo ,employee .employeeName ,case employee.Sex when Mthen 男when Fthen 女end ,employee .Department from employee where employee.Department =(select employee .Department from employee where employee .employeeNo =E2008005)(5)查询既订购了P2*商品,又订购了P2*商品的客户编号,订单编号和订单金额select
9、customer .customerno,ordermaster .orderno ,ordermaster .ordersum from ordermaster ,customer,orderdetail where customer.customerno =ordermaster .customerno and ordermaster .orderno =orderdetail .orderno and orderdetail .productno=P2007002intersectselect customer .customerno,ordermaster .orderno ,orde
10、rmaster .ordersum from ordermaster ,customer,orderdetail where customer.customerno =ordermaster .customerno and ordermaster .orderno =orderdetail .orderno and orderdetail .productno=P2005001(6)查询没有订购“52倍速光驱”或“17寸显示器”的客户编号,客户名称。select distinct customer .customerno ,customer .customername from custome
11、r ,orderdetail ,ordermaster,product where orderdetail .orderno =ordermaster .orderno and ordermaster .customerno =customer .customerno and orderdetail .productno =product .productno and product .productname not in (52倍速光驱,17寸显示器) (7)查询订单金额最高的订单编号,客户姓名,销售员名称和相应的订单金额。select ordermaster .orderno ,custo
12、mer .customername ,employee .employeename ,ordermaster .ordersum from employee ,ordermaster,customer where employee .employeeno =ordermaster .saleno and customer .customerno =ordermaster .customerno and ordermaster .ordersum =(select MAX (ordermaster .ordersum )from ordermaster)(8)查询订购了“52倍速光驱”商品的订购
13、数量,订购平均价和订购总金额。select SUM (orderdetail .qty )as订购数量,AVG (ordermaster.ordersum )as订购平均价,SUM (ordermaster .ordersum )as订购总金额 from orderdetail ,ordermaster ,product where orderdetail .orderno =ordermaster .orderno and orderdetail .productno =product .productno and product .productname =52倍速光驱(9)查询订购了“5
14、2倍速光驱”商品且订货数量界于4之间的订单编号,订货数量和订货金额。select orderdetail.orderno ,orderdetail .qty ,ordermaster .ordersum from ordermaster,orderdetail ,product whereorderdetail .orderno =ordermaster .orderno and product .productno =orderdetail.productno and product .productname =52倍速光驱and orderdetail .qty between 2 and
15、 4(10)在订单主表中查询每个业务员的订单数量select ordermaster.saleno ,COUNT (*)as 订单数量from ordermaster group by saleno (11)统计在业务科工作且在年或年出生的员工人数和平均工资。select COUNT (*)as 员工人数,AVG (employee .salary )as 平均工资from employee(12)在订单明细表中统计每种商品的销售数量和金额,并按销售金额的升序排序输出。select orderdetail .productno ,SUM(orderdetail .qty )as 销售数量,SU
16、M(orderdetail .price *orderdetail .qty )as 金额from orderdetail 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 (ordermast
17、er .ordersum )as 平均订单金额 from 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 (orderdetai
18、l .orderno )as 订单数,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)查询订单中至少包含种(含种)以上商品的订单编号及订购次数,且订购的商品
19、数量在件(含件)以上。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 订货总数量,SUM(ordermaster .ordersum)
20、as 订货总金额from product,orderdetail,customer,ordermaster where customer .customerno =ordermaster .customerno and ordermaster .orderno=orderdetail .orderno and product.productno =orderdetail .productno and product .productname =32M DRAMgroup by customer.customerno ,customername(17)查询每个客户订购的商品编号,商品所属类别,商
21、品数量及订货金额,结果显示客户名称,商品所属类别,商品数量及订货金额,并按客户编号升序和按订货金额的降序排序输出。select customername,product.productno,product.productclass,orderdetail .qty ,orderdetail .price *orderdetail .qty from orderdetail ,product ,customer ,ordermaster where customer.customerno =ordermaster .customerno and ordermaster .orderno =ord
22、erdetail .orderno and orderdetail .productno =product .productno order by customer.customerno asc,orderdetail .price *orderdetail .qty desc(18)按商品类别查询每类商品的订货平均单价在元(含元)以上的订货总数量,订货平均单价和订货总金额。select COUNT(qty)as订货总数量,AVG (price )as订货评价单价,SUM(ordersum )as订货总金额 from product, ordermaster a,orderdetail b w
23、here a.orderno =b.orderno and product .productno =b.productno group by product.productclass having AVG (price )=280(19)查找至少有次销售的业务员名称和销售日期。select employee .employeename ,ordermaster .orderdate from employee ,ordermaster where employee .employeeno =ordermaster.saleno and employeeno in (select saleno
24、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.ordersum) as b from ordermaster group by customerno )awhere customer.customerno =orde
25、rmaster.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 customername where customer .customerno =b.customerno group by customername (21)查找销售总额小于元的销售员编
26、号,姓名和销售额select employee.employeeno,employeename,a from employee,(select saleno,SUM(ordersum) a from ordermaster group by saleno )b where b.saleno =employeeno and a=3 ) order by customerno(23)查找同时订购了商品为“P2*”和商品编号为“P2*”的商品的客户编号,客户姓名,商品编号, 商品名称和销售数量,按客户编号排序输出。select customer.customerno,customername,pro
27、duct.productno ,productname,orderdetail.qty from customer,product,orderdetail,ordermaster where customer.customerno=ordermaster .customerno and ordermaster.orderno=orderdetail.orderno and orderdetail .productno =product.productno and customer.customerno in(select customer.customerno from customer,pr
28、oduct,orderdetail,ordermaster where customer.customerno=ordermaster .customerno and ordermaster.orderno=orderdetail.orderno and orderdetail .productno =product.productno and product.productno =P2007002intersectselect customer.customerno from customer,product,orderdetail,ordermaster where customer.cu
29、stomerno=ordermaster .customerno and ordermaster.orderno=orderdetail.orderno and orderdetail .productno =product.productno and product.productno =P2007001) order by customer.customerno (24)计算每一商品每月的销售金额总和,并将结果首先按销售月份然后按订货金额降序排序输出。select ordermaster.customerno ,ordermaster .orderdate,sum(ordersum) as 销售金额总和 from ordermaster group by ordermaster .customerno,ordermaster.orderdate order by ordermaster .orderdate,销售金额总和 desc(25)查询订购了“键盘”商品的客户姓名,订货数量和订货日期select customer.customername,orderdetail.qty,ordermaster .order
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1