实验二 参考答案

实验二 多表查询 实验目的与要求 （1）了解查询的概念和方法； （2）掌握多表连接的方法； （3）掌握嵌套查询的基本方法； （4）掌握 SELECT 语句在多表查询中的应用。 实验内容： 1、利用企业管理器在数据库 OrderDB 中建立数据表，包括员工表，客户 表，商品表，订单主表，订单明细表。这 5 张表的结构和数据如下： 表 1 员工表 Employee 的结构 属性含义 属性名 数据类型 员工编号 employeeNo char(8) 员工姓名 employeeName varchar(10) 性别 sex char(1) 出生日期 birthday datetime 住址 address varchar(50) 电话 telephone varchar(20) 雇佣日期 hiredate datetime 所属部门 department varchar(30) 职务 headship varchar(10) 薪水 salary numeric(8,2) 表 2 客户表 Customer 的结构 属性含义 属性名 数据类型 客户号 customerNo char(9) 客户名称 customerName varchar(40) 客户住址 address varchar(40) 客户电话 telephone varchar(20) 邮政编码 zip char(6) 表 3 商品基本信息表 Product 的结构 属性含义 属性名 数据类型 商品编号 productNo char(9) 商品名称 productName varchar(40) 商品类别 productClass varchar(20) 商品定价 productPrice numeric(7,2) 表 4 订单主表 OrderMaster 的结构 属性含义 属性名 数据类型 订单编号 orderNo char(12) 客户号 customerNo char(9) 员工编号 employeeNo char(8) 订单日期 orderDate datetime 订单金额 orderSum numeric(9,2) 发票号码 invoiceNo char(10) 表 5 订单明细表 OrderDetail 的结构 属性含义 属性名 数据类型 订单编号 orderNo char(12) 商品编号 productNo char(9) 销售数量 quantity int 成交单价 price numeric(7,2) 表 6 员工表 Employee 的数据 表 7 客户表 Customer 的数据 customerNo customerName address telephone zip C20050001 统一股份有限公司 天津市220012 C20050002 兴隆股份有限公司 天津市220301 C20050003 上海生物研究室 北京市108001 C20050004 五一商厦 上海市210100 C20060001 大地商城 北京市100803 C20060002 联合股份有限公司 上海市210100 C20070001 南昌市电脑研制中心 南昌市330046 C20070002 世界技术开发公司 上海市210230 employee No emplo yeeNa me sex birthday address telephone hireDate departm ent headS hip salary E2005001 喻自 强 M 1965-04-15 南京市 青海路 18 号 13817605 008 2005-02- 06 财务科 科长 5800. 80 E2005002 张小 梅 F 1973-11-01 上海市 北京路 8 号 13607405 016 2005-03- 28 业务科 职员 2400. 00 E2005003 张小 娟 F 1973-03-06 上海市 南京路 66 号 13707305 025 2005-03- 28 业务科 职员 2600. 00 E2005004 张露 F 1967-01-05 南昌市 八一大 道 130 号 15907205 134 2005-03- 28 业务科 科长 4100. 00 E2005005 张小 东 M 1973-09-03 南昌市 阳明路 99 号 15607105 243 2005-03- 28 业务科 职员 1800. 00 E2006001 张辉 M 1965-11-01 南昌市 青山路 100 号 13607705 352 2006-03- 28 办公室 主任 4000. 00 E2006002 韩梅 F 1973-12-11 上海市 浦东大 道 6 号 13807805 461 2006-11- 20 业务科 职员 2600. 00 E2006003 刘风 F 1973-05-21 江西财 经大学 5 栋 1- 101 室 15907805 578 2006-11- 20 业务科 职员 2500. 00 E2007001 吴浮 萍 M 1973-09-12 南昌市 高新开 发区 12 号 null 2007-06- 28 业务科 职员 2500. 00 E2007002 高代 鹏 M 1973-01-02 南昌市 高新开 发区 56 号 null 2007-11- 28 办公室 文员 2000. 00 E2008001 陈诗 杰 M 1968-01-06 江西财 经大学 12 栋 3- 304 室 null 2008-01- 06 财务科 出纳 3200. 00 E2008002 张良 M 1972-02-16 上海市 福州路 135 号 null 2008-02- 12 业务科 职员 2700. 00 E2008003 黄梅 莹 F 1972-05-15 上海市 九江路 88 号 null 2008-02- 12 业务科 职员 3100. 00 E2008004 李虹 冰 F 1972-10-13 南昌市 中山路 1 号 null 2008-02- 12 业务科 职员 3400. 00 E2008005 张小 梅 F 1970-11-06 深圳市 阳关大 道 10 号 null 2008-11- 18 财务科 会计 5000. 00 C20070003 万事达股份有限公司 天津市220400 C20080001 红度股份有限公司 北京市100800 表 8 商品基本信息表 Product 的数据 productNo productName productClass productPrice P20050001 32M DRAM 内存 80.70 P20050002 17 寸显示器 显示器 700.00 P20050003 120GB 硬盘 存储器 300.00 P20050004 3.5 寸软驱 设备 35.00 P20050005 键盘 设备 100.60 P20060001 VGA 显示卡 显示器 200.60 P20060002 网卡 设备 66.00 P20060003 Pentium100CPU 处理器 1200.00 P20070001 1G DDR 内存 256.00 P20070002 52 倍速光驱 设备 200.00 P20070003 计算机字典 图书 100.00 P20070004 9600bits/s 调制调解 设备 320.00 P20080001 Pentium 主板 主板 890.00 P20080002 硕泰克 SL-K8AN-RL 主板 主板 1100.00 P20080003 龙基 777FT 纯平显示器 显示器 900.00 表 9 订单明细表 OrderDetail 的数据 orderNo productNo quantity price 200801090001 P20050001 5 80.70 200801090001 P20050002 3 700.00 200801090001 P20050003 2 300.00 200801090002 P20060002 5 66.00 200801090002 P20080001 2 890.00 200801090002 P20080003 2 900.00 200801090003 P20050001 5 80.70 200801090003 P20060001 3 200.60 200802190001 P20060003 4 1200.00 200802190001 P20070001 2 256.00 200802190001 P20070002 5 200.00 200802190002 P20050003 2 300.00 200802190002 P20050005 3 100.60 200803010001 P20050001 8 80.70 200803010001 P20070001 4 256.00 200803020001 P20050001 2 80.70 200803020001 P20050002 1 700.00 200803020001 P20070003 3 100.00 200803090001 P20050003 4 300.00 200803090001 P20050004 5 35.00 200803090001 P20070001 2 256.00 200803090001 P20070002 5 200.00 200803090001 P20070004 3 320.00 200805090001 P20060003 8 1200.00 200805090001 P20070001 1 256.00 200805090001 P20070002 2 200.00 200805090001 P20070003 5 100.00 200806120001 P20050002 1 700.00 200806120001 P20050003 3 300.00 200806120001 P20050004 2 35.00 200806120001 P20050005 3 100.60 200901010001 P20050002 2 700.00 200901010001 P20050004 11 35.00 200901010001 P20050005 9 100.60 200901010001 P20080002 2 1100.00 201206070001 P20050003 10 300.00 201206070001 P20060002 5 66.00 201206070001 P20060003 4 1200.00 201206070002 P20060002 6 66.00 201206070002 P20070001 2 256.00 201206070002 P20070002 2 200.00 201206070002 P20080001 3 890.00 表 10 订单主表 OrderMaster 的数据 orderNo customerNo employeeNo orderDate orderSum invoiceNo 200801090001 C20050001 E2005002 2008-01-09 3103.50 I000000001 200801090002 C20050004 E2005003 2008-01-09 3910.00 I000000002 200801090003 C20080001 E2005002 2008-01-09 1005.30 I000000003 200802190001 C20050001 E2005003 2008-02-19 6312.00 I000000004 200802190002 C20070002 E2008002 2008-02-19 901.80 I000000005 200803010001 C20070002 E2007001 2008-03-01 1669.60 I000000006 200803020001 C20050004 E2008003 2008-03-02 1161.40 I000000007 200803090001 C20070003 E2008004 2008-03-09 3847.00 I000000008 200805090001 C20060002 E2008002 2008-05-09 10756.00 I000000009 200806120001 C20050001 E2005002 2008-06-12 1971.80 I000000010 200901010001 C20050001 E2005002 2009-01-01 4890.40 I000000011 201206070001 C20050004 E2005003 2012-06-07 8130.00 I000000012 201206070002 C20070002 E2008002 2012-06-07 3978.00 I000000013 2、根据上面的数据库，完成以下查询操作： （1）查询同一天进入公司工作的员工。 select hiredate,employeeName From Employee a Where exists (select * from Employee b where a.hiredate=b.hiredate and a.employeeno!=b.employeeno) group by hiredate,employeeName （2）查询与“陈诗杰”在同一部门工作的员工姓名、性别、部门和职务。 select a.employeeName,a.department,a.headShip,a.sex From Employee a,Employee b where a.department=b.department and b.employeeName=陈诗杰 （3）查询薪水超过员工平均薪水的员工信息。 select * From Employee where salary(select AVG(salary)from Employee) （4）查询有销售记录的客户编号、名称和订单总额。 select a.customerno,customerName,SUM(orderSum) 总额 From Customer a,OrderMaster b where a.customerNo=b.customerNo group by a.customerNo,customerName （5）查询没有订购商品的客户编号和名称。 select customerno,customerName From Customer where customerNo not in(select customerNo from OrderMaster) （6）使用子查询来统计 32M DRAM 的销售情况，要求显示相应的销售员 的姓名、销售日期、销售数量和金额。 select employeeName,orderDate,quantity,orderSum, sex=case sex whenFthen女 whenMthen男 end from Employee a,OrderMaster b,OrderDetail c where employeeno=salerno and b.orderNo=c.orderNo and productNo in (select productNo from Product where productName=32M DRAM) （7）查询 OrderMaster 表中订单金额最高的订单号及订单金额。 select ordersum,orderno from OrderMaster where orderSum=(select MAX(orderSum)from OrderMaster) （8）查询订单金额大于“E2005002”业务员在 2008-1-9 这天所接的任一 张订单的金额的所有订单信息。 select * from OrderMaster where orderSumall(select orderSum from Ordermaster where salerNo=E2005002 and orderDate=2008/1/9) （9）查询单价高于 400 元的商品编号、商品名称、订货数量和订货单价。 select ductno,productClass,quantity,price from Product a,OrderDetail b where ductNo=ductNo and price400 （10）使用左外连接查找每个客户的客户编号、名称、订货日期、订货金 额，其中订货日期不显示时间，日期格式为 yyyy-mm-dd，按客户编号排序，同 一客户再按订单金额降序排序输出。 select a.customerNo,a.customerName, isnull (convert(char(10),ordersum,120),) ordersum, isnull (convert(char(10),orderdate,120),) orderdate from Customer a left join OrderMaster b on a.customerNo=b.customerNo order by customerNo,ordersum desc （11）查询每个员工的销售记录，要求显示销售员的编号、姓名、商品名 称、数量、单价、金额和销售日期（日期使用 yyyy-mm-dd 格式显示） 。 select salerNo,employeeName,ductName,quantity,price,orderSum, ISNULL(