实验二 参考答案

数据库

实验二 多表查询

实验目的与要求

（1）了解查询的概念和方法； （2）掌握多表连接的方法；

（3）掌握嵌套查询的基本方法；

（4）掌握SELECT语句在多表查询中的应用。 实验内容：

1、利用企业管理器在数据库OrderDB中建立数据表，包括员工表，客户表，商品表，订单主表，订单明细表。这5张表的结构和数据如下：

数据库

销售数量 成交单价 employee No E2005001 E2005002 E2005003 E2005004 E2005005 E2006001 E2006002 E2006003 emplo yeeNa me 喻 自 强 张 小 梅 张 小 娟 张露 张 小 东 张辉 韩梅 刘风 sex M F F F M M F F birthday

quantity price address 南 京 市 青 海 路 18 号 上 海 市 北京路 8 号 上 海 市 南 京 路 66 号 南 昌 市 八 一 大 道 130 号 南 昌 市 阳 明 路 99 号 南 昌 市 青 山 路 100 号 上 海 市 浦 东 大 道6号 江 西 财 经大学 5 栋 1-101 室 南 昌 市 高 新 开 发 区 12 号 南 昌 市 高 新 开 发 区 56 号 江 西 财 经 大 学 12 栋 3-304 室 上 海 市 福 州 路 135 号 上 海 市 九 江 路 88 号 南 昌 市 中山路 1 号 深 圳 市 阳 关 大 道 10 号 telephone 13817605 008 13607405 016 13707305 025 15907205 134 15607105 243 13607705 352 13807805 461 15907805 578 null

int numeric(7,2) hireDate 2005-0206 2005-0328 2005-0328 2005-0328 2005-0328 2006-0328 2006-1120 2006-1120 2007-0628 2007-1128 2008-0106 2008-0212 2008-0212 2008-0212 2008-1118 departm ent 财务科 业务科 业务科 业务科 业务科 办公室 业务科 业务科 headS hip 科长 职员 职员 科长 职员 主任 职员 职员 salary 5800. 80 2400. 00 2600. 00 4100. 00 1800. 00 4000. 00 2600. 00 2500. 00 2500. 00 2000. 00 3200. 00 2700. 00 3100. 00

3400. 00 5000. 00

表 6 员工表 Employee 的数据

1965-04-15 1973-11-01 1973-03-06 1967-01-05 1973-09-03 1965-11-01 1973-12-11 1973-05-21

E2007001

吴 浮 萍 高 代 鹏 陈 诗 杰 张良 黄 梅 莹 李 虹 冰 张 小 梅

M

1973-09-12

业务科

职员

E2007002

M

1973-01-02

null

办公室

文员

E2008001

M

1968-01-06

null

财务科

出纳

E2008002 E2008003 E2008004 E2008005

M F F F

1972-02-16 1972-05-15 1972-10-13 1970-11-06

null null null null

业务科 业务科 业务科 财务科

职员 职员 职员 会计

customerNo C20050001 C20050002 C20050003 C20050004 C20060001 C20060002 C20070001 C20070002 C20070003

表 7 客户表 Customer 的数据 customerName address telephone 统一股份有限公司 兴隆股份有限公司 上海生物研究室 五一商厦 大地商城 联合股份有限公司 南昌市电脑研制中心 世界技术开发公司 万事达股份有限公司 天津市 天津市 北京市 上海市 北京市 上海市 南昌市 上海市 天津市 022-3566021 022-3562452 010-2121000 021-4532187 010-1165152 021-4568451 0791-4412152 021-4564512 022-4533141

zip 220012 220301 108001 210100 100803 210100 330046 210230 220400

数据库

C20080001

红度股份有限公司

北京市

010-5421585

100800

productNo P20050001 P20050002 P20050003 P20050004 P20050005 P20060001 P20060002 P20060003 P20070001 P20070002 P20070003 P20070004 P20080001 P20080002 P20080003

表 8 商品基本信息表 Product 的数据 productName productClass 32M DRAM 17 寸显示器 120GB 硬盘 3.5 寸软驱 键盘 VGA 显示卡 网卡 Pentium100CPU 1G DDR 52 倍速光驱 计算机字典 9600bits/s 调制调解 Pentium 主板 硕泰克 SL-K8AN-RL 主板 龙基 777FT 纯平显示器 内存 显示器 存储器 设备 设备 显示器 设备 处理器 内存 设备 图书 设备 主板 主板 显示器

productPrice 80.70 700.00 300.00 35.00 100.60 200.60 66.00 1200.00 256.00 200.00 100.00 320.00 890.00 1100.00 900.00

orderNo 200801090001 200801090001 200801090001 200801090002 200801090002 200801090002 200801090003 200801090003 200802190001 200802190001 200802190001 200802190002 200802190002 200803010001 200803010001 200803020001 200803020001 200803020001 200803090001 200803090001 200803090001 200803090001 200803090001 200805090001 200805090001 200805090001 200805090001 200806120001 200806120001 200806120001 200806120001 200901010001 200901010001 200901010001 200901010001 201206070001 201206070001 201206070001 201206070002 201206070002 201206070002 201206070002

表 9 订单明细表 OrderDetail 的数据 productNo quantity P20050001 P20050002 P20050003 P20060002 P20080001 P20080003 P20050001 P20060001 P20060003 P20070001 P20070002 P20050003 P20050005 P20050001 P20070001 P20050001 P20050002 P20070003 P20050003 P20050004 P20070001 P20070002 P20070004 P20060003 P20070001 P20070002 P20070003 P20050002 P20050003 P20050004 P20050005 P20050002 P20050004 P20050005 P20080002 P20050003 P20060002 P20060003 P20060002 P20070001 P20070002 P20080001 5

3 2 5 2 2 5 3 4 2 5 2 3 8 4 2 1 3 4 5 2 5 3 8 1 2 5 1 3 2 3 2 11 9 2 10 5 4 6 2 2 3

price 80.70 700.00 300.00 66.00 890.00 900.00 80.70 200.60 1200.00 256.00 200.00 300.00 100.60 80.70 256.00 80.70 700.00 100.00 300.00 35.00 256.00 200.00 320.00 1200.00 256.00 200.00 100.00 700.00 300.00 35.00 100.60 700.00 35.00 100.60 1100.00 300.00 66.00 1200.00 66.00 256.00 200.00 890.00

表 10 订单主表 OrderMaster 的数据 orderNo customerNo employeeNo orderDate orderSum invoiceNo

数据库

（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)