实验二 参考答案.doc

实验二 多表查询
实验目的与要求
（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的数据
employeeNo
employeeName
sex
birthday
address
telephone
hireDate
department
headShip
salary
E2005001
喻自强
M
1965-04-15
南京市青海路18号
13817605008
2005-02-06
财务科
科长
5800.80
E2005002
张小梅
F
1973-11-01
上海市北京路8号
13607405016
2005-03-28
业务科
职员
2400.00
E2005003
张小娟
F
1973-03-06
上海市南京路66号
13707305025
2005-03-28
业务科
职员
2600.00
E2005004
张露
F
1967-01-05
南昌市八一大道130号
15907205134
2005-03-28
业务科
科长
4100.00
E2005005
张小东
M
1973-09-03
南昌市阳明路99号
15607105243
2005-03-28
业务科
职员
1800.00
E2006001
张辉
M
1965-11-01
南昌市青山路100号
13607705352
2006-03-28
办公室
主任
4000.00
E2006002
韩梅
F
1973-12-11
上海市浦东大道6号
13807805461
2006-11-20
业务科
职员
2600.00
E2006003
刘风
F
1973-05-21
江西财经大学5栋1-101室
15907805578
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
表7 客户表Customer的数据
customerNo
customerName
address
telephone
zip
C20050001
统一股份有限公司
天津市
022-3566021
220012
C20050002
兴隆股份有限公司
天津市
022-3562452
220301
C20050003
上海生物研究室
北京市
010-2121000
108001
C20050004
五一商厦
上海市
021-4532187
210100
C20060001
大地商城
北京市
010-1165152
100803
C20060002
联合股份有限公司
上海市
021-4568451
210100
C20070001
南昌市电脑研制中心
南昌市
0791-4412152
330046
C20070002
世界技术开发公司
上海市
021-4564512
210230
C20070003
万事达股份有限公司
天津市
022-4533141
220400
C20080001
红度股份有限公司
北京市
010-5421585
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
when'F'then'女'
when'M'then'男'
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 orderSum>all(select orderSum from Ordermaster where salerNo='E2005002' and orderDate='2008/1/9')
（9） 查询单价高于400元的商品编号、商品名称、订货数量和订货单价。
select a.productno,productClass,quantity,price
from Product a,OrderDetail b
where a.productNo=b.productNo and price>400

（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,c.productName,quantity,price,orderSum,
ISNULL(CONVERT(char(10),orderdate,120),'') 日期
from Employee a,OrderMaster b,Product c,OrderDetail d
where a.employeeNo=b.salerNo and b.orderNo=d.orderNo and c.productNo=d.productNo
order by salerNo

（12） 使用连接查询来统计32M DRAM的销售情况，要求显示相应的销售员的姓名、销售日期、销售数量和金额。
（13）
（13）查询公司男业务员所接且订单金额超过2000元的订单号和订单金额。
select b.orderNo,SUM(quantity*price) ordersum
from Employee a,OrderMaster b,OrderDetail c
where a.employeeNo=b.salerNo and b.orderNo=c.orderNo and sex='M'
group by b.orderNo
having SUM(quantity*price)>'2000'

（14）查询每种商品的总销售数量及总销售金额，要求显示出商品的编号、商品名称、总数量及金额，并按商品号从小到大排列。
select a.productNo,a.productName,SUM(quantity)
totalquantity,SUM(quantity*price) totalsum
from Product a,OrderDetail b
where a.productNo=b.productNo
group by a.productNo,a.productName
order by a.productNo

实验要求：
（1）结合相关知识，操作规范正确。
（2）认真书写实验报告，如实填写各项实验内容，尤其是执行信息。
（3）完成任务后，继续验证其它与数据库和表创建有关的任务(书或资料上的)。