实验五数据查询

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

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

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

4、类别，商品数量及订货金额，并按客户编号升序和按订货金额的降序排序输出。(18) 按商品类别查询每类商品的订货平均单价在 280 元(含 280 元)以上的订货总数量，订货平均单价和订货总金额。(19) 查找至少有 2 次销售的业务员名称和销售日期。(20) 查询销售金额最大的客户名称和总货款额(21) 查找销售总额小于 5000 元的销售员编号，姓名和销售额(22) 查找至少订购了 3 种商品的客户编号，客户名称，商品编 号，商品名称，数量和金额。(23) 查找同时订购了商品为“ P”和商品编号为“ P'的商品的 客户编号，客户姓名，商品编号，商品名称和销售数量，按客户编号 排序输出。

5、(24) 计算每一商品每月的销售金额总和，并将结果首先按销售 月份然后按订货金额降序排序输出。(25) 查询订购了“键盘”商品的客户姓名，订货数量和订货日 期(26) 查询每月订购“键盘”商品的客户名称。(27) 查询至少销售了 5 种商品的销售员编号，姓名，商品名称， 数量及相应的单价，并按销售员编号排序输出。(28) 查询没有订购商品的客户编号和客户名称。(29) 查询至少包含了“世界技术开发公司”所订购的商品的客 户编号，客户名称，商品编号，商品名称，数量和金额。五、实验步骤请完成实验内容，并写出具体的实验步骤(1)用子查询查询员工 “张小娟 ”所做的订单信息 。select order

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

7、ter . customerNo from orderMaster )and =' 北京市 ' order by customer .Zip desc(3) 查询订购了 “32MDRAM商品的订单编号，订货数量和订货单 价。selectorderDetail . OrderNo , orderDetail . Qty , orderDetail . Price from orderDetail , product where=product . ProductNo and ProductName ='32M DRAM'(4) 查询与员工编号“E2008005&#

8、39;在同一个部门的员工编号，姓名,性别 ，所属部门 。select employee . employeeNo , employee . employeeName , case when 'M' then ' 男 ' when 'F' then ' 女 ' end , employee . Department from employee where=( selectemployee . Department from employee where employee . employeeNo ='E2008005'

9、; )(5) 查询既订购了 P商品，又订购了 P商品的客户编号，订单编号 和订单金额selectcustomer . customerno , ordermaster . orderno , ordermaster .ord ersum from ordermaster , customer , orderdetail where =ordermaster . customerno and ordermaster . orderno =orderdetail . orderno and orderdetail . productno ='P2007002' intersect

10、selectcustomer . customerno , ordermaster . orderno , ordermaster .ord ersum from ordermaster , customer , orderdetail where =ordermaster . customerno and ordermaster . orderno =orderdetail . orderno and orderdetail . productno ='P2005001'(6) 查询没有订购 “ 52倍速光驱 ”或“17寸显示器”的客户编号 ， 客户名称。select dis

11、tinct customer . customerno , customer . customernameorderdetail. orderno=ordermaster. ordernoandordermaster. customerno=customer. customernoandorderdetail. productno=product. productnoandfrom customer , orderdetail, ordermaster , productwhereproduct . productname not in（'52 倍速光驱 ' , '17

12、 寸显示器 ' ）(7) 查询订单金额最高的订单编号 的订单金额 。selectordermaster . orderno , customer loyeename , ordermaster employee , ordermaster , customer =ordermaster. saleno=ordermaster . customerno MAX( ordermaster，客户姓名 ，销售员名称和相应and and ordermaster . ordersum ) from ordermaster ). ordersumfromwhere employee. employe

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

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

15、orderno=ordermaster . orderno andproduct . productno = and product . productname ='52 倍速光驱 ' and orderdetail . qty between 2 and 4(10) 在订单主表中查询每个业务员的订单数量select , COUNT(*) as ' 订 单 数 量' from ordermaster group by saleno(11) 统计在业务科工作且在年或年出生的员工人数和平均工资 。 select COUNT(*) as ' 员工人数 '

16、; , 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 or

17、der by SUM( orderdetail . price * orderdetail .qty )(13) 统计客户号为“C的客户的订单数，订货总额和平均订货金select customer . customerno , COUNT( orderdetail. orderno ) as' 订 单 数 ' , SUM( ordermaster . ordersum ) as ' 订 货总 额 ' ,AVG( ordermaster. ordersum) as' 平 均 订 单 金 额 'fromorderdetail, ordermaster

18、, customer where orderdetail . orderno=ordermaster. ordernoand=customer . customernoandcustomer. customerno='C2005001' groupbycustomer . customerno(14) 统计每个客户的订单数 ，订货总额和平均订货金额 。select customer . customerno , COUNT( orderdetail . orderno ) as ' 订 单 数 ' , SUM( ordermaster . ordersum )

19、as ' 订 货总 额 ' ,AVG ( ordermaster . ordersum ) as ' 平 均 订 单 金 额 ' from orderdetail , ordermaster , customer where orderdetail . orderno =ordermaster . orderno and =customer . customerno group by customer . customerno(15) 查询订单中至少包含种 (含种 )以上商品的订单编号及订购次 数，且订购的商品数量在件 (含件)以上 。select ordern

20、o , count (*) from orderdetail where orderno in( selectfrom orderdetail group by orderno having COUN(Torderno )>= 3) group by orderno(16) 查找订购了 “32MDRAM的商品的客户编号，客户名称，订货 总数量和订货总金额 。select , customername, SUM(qty)as ' 订 货 总 数 量 ' , SUM( ordermaster . ordersum) as ' 订货总金额 'from produ

21、ct , orderdetail , customer , ordermasterwherecustomer . customerno =ordermaster . customerno and ordermaster . orderno=orderdetail . orderno and =orderdetail . productno and product . productname ='32M DRAM'group by , customername17）查询每个客户订购的商品编号 ，商品所属类别 ，商品数量及订 货金额 ，结果显示客户名称 ，商品所属类别 ，商品数量及

22、 订货金额 ，并按客户编号升序和按订货金额的降序排序输出select customername, orderdetail .qty , orderdetail . price* orderdetail . qtyfrom orderdetail , product , customer , ordermaster where=ordermaster . customerno and orderdetail . productno =product . productno order by asc , orderdetail . price *orderdetail .qty descorder

23、master. orderno =orderdetail. orderno and18）按商品类别查询每类商品的订货平均单价在元（含元）以上的 订货总数量 ， 订货平均单价和订货总金额 。select COUN(Tqty )as' 订货总数量 ' , AVG( price )as' 订货评价单价 ' , SUM( ordersum )as' 订货总金额 ' from product , ordermaster a, orderdetail b where = and product . productno = group by having AV

24、G( price )>=280(19) 查找至少有次销售的业务员名称和销售日期 。select employee . employeename , ordermaster . orderdate from employee , ordermaster where employee . employeeno =ordermaster .saleno and employeeno in ( select saleno from ordermaster group by saleno having COUNT(*)>= 2) order by employeename(20)查询销售金额

25、最大的客户名称和总货款额select ,max from customer , ordermaster ,( select top 1 ordermaster . customerno , SUMas 'b' from ordermaster group by customerno )awhere = and =customer . customerno group by customernameselect customername, MAX from customer ,( select customerno , sum( ordersum )a from ordermas

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

27、 品名称，数量和金额 。select ,* as' 金 额 ' from customer , product , orderdetail , ordermaster where =ordermaster . customerno and = and orderdetail . productno = and in ( select from customer , product , orderdetail , ordermaster where = ordermaster . customerno and = and orderdetail . productno = gro

28、up by having count >=3 ) order by customerno(23) 查找同时订购了商品为“P和商品编号为“P”的商品的客户 编号，客户姓名 ，商品编号 ，商品名称和销售数量 ，按客户编号排序输出 。 select , customername, , productname, from customer , product , orderdetail , ordermaster where =ordermaster . customerno and = and orderdetail . productno = and in( select from cust

29、omer , product ,orderdetail , ordermaster where =ordermaster . customerno and = and orderdetail . productno = and ='P2007002'intersectselect from customer , product ,orderdetail , ordermaster where =ordermaster . customerno and =and orderdetail . productno = and ='P2007001' ） order b

30、y（24）计算每一商品每月的销售金额总和 ，并将结果首先按销售月份 然后按订货金额降序排序输出 。select , ordermaster . orderdate , sum（ ordersum） as ' 销售金额总 和 ' from ordermastergroup by ordermaster . customerno , order by ordermaster . orderdate , 销售金额总和 desc（25）查询订购了 “键盘 ”商品的客户姓名 ，订货数量和订货日期 select , ordermaster . orderdate from customer

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

32、omcustomer , ordermaster , orderdetail , product where ordermaster. customerno = and ordermaster . orderno=orderdetail . orderno and= and productname=' 键盘 ' )(27) 查询至少销售了种商品的销售员编号 ，姓名，商品名称 ，数量 及相应的单价 ，并按销售员编号排序输出 。select , employeename, productname , orderdetail. price fromemployee, product

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