数据库系统原理与设计 实验二答案

实验四多表查询
1 实验目的与要求
(1) 熟练掌握SQL语句的使用。

(2) 熟练使用SQL语句进行连接操作。

2 实验内容
1）实验题目：
(1) 找出同一天进入公司服务的员工。

(2) 查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务。

(3) 在Employee表中查询薪水超过员工平均薪水的员工信息。

(4) 查找有销售记录的客户编号、名称和订单总额。

(5) 查询没有订购商品的客户编号和客户名称。

(6) 使用子查询查找32M DRAM的销售情况，要求显示相应的销售员的姓名、性别，销售日期、销售数量和金额，其中性别用“男”、“女”表示。

(7) 查询OrderMaster表中订单金额最高的订单号及订单金额。

(8) 在订单主表中查询订单金额大于“E2005002业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。

(9) 查询单价高于400元的商品编号、商品名称、订货数量和订货单价。

(10) 分别使用左外连接、右外连接、完整外部连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价，并分析比较检索的结果。

(11) 使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额，其中订货日期不要显示时间，日期格式为“yyyy-mm-dd”，按客户编号排序，同一客户再按订单金额降序排序输出。

(12) 查找每个员工的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期，其中性别使用“男”和“女”表示，日期使用“yyyy-mm-dd”格式显示。

(13) 查找16M DRAM的销售情况，要求显示相应的销售员的姓名、性别，销售日期、销售数量和金额，其中性别用“男”、“女”表示。

(14) 找出公司男业务员所接且订单金额超过2000元的订单号及订单金额。

(15) 查询每种商品的总销售数量及总销售金额，要求显示出商品编号、商品名称、总数量及总金额，并按商品号从小到大排列。

(16) 实验问题：
①连接操作类型有哪些？分析外连接在现实应用中的意义。

②查询表可以用在什么地方？使用查询表要注意哪些地方？
③分析SQL语句中的IN和OR关键字有何异同点？它们可以互换吗？给出实例说明。

④分析哪几种情况需要使用自表连接。

2）实验脚本：
（１）
Select
a.employeeNo,a.employeeName,a.hireDate,
b.employeeNo,b.employeeName,b.
hireDate
from Employee a,Employee b
where a.employeeNo!=b.employeeNo and a.employeeName>b.employeeName and (a.hireDate=b.hireDate)
（２）
select employeeName,sex,department,headShip
from Employee
where department in(select department from Employee where employeeName='陈诗杰')
（３）
select*
from Employee
where salary>(select avg(salary)from Employee)
（４）
update OrderMaster set orderSum=sum2
from OrderMaster a,(select orderNo,sum(quantity*price) sum2 from OrderDetail group by orderNo) b
where a.orderNo=b.orderNo
select a.customerNo,a.customerName,b.orderSum
from Customer a,OrderMaster b
where a.customerNo in(select customerNo from OrderMaster)and a.customerNo=b.customerNo
（５）
select customerNo,customerName
from Customer
where customerNo not in(select customerNo from OrderMaster)
（６）
select a.employeeName,case a.sex when'F'then'女'when'M'then'男' end sex,b.orderDate,c.quantity,c.price
from Employee a,OrderMaster b,OrderDetail c
where c.productNo in(select productNo from Product where productName='32M DRAM')and a.employeeNo=b.salerNo and b.orderNo=c.orderNo
（７）
select orderNo,orderSum
from OrderMaster
where orderSum=(select max(orderSum)from OrderMaster)
（８）
select*
from OrderMaster
where orderSum>any(select orderSum from OrderMaster where salerNo='E2005002'and orderDate='2008-01-09')
（９）
select b.productNo,a.productName,b.quantity,b.price
from Product a,OrderDetail b
where b.price>'400'and a.productNo=b.productNo
（１０）左外连接：
select b.productNo,a.productName,b.quantity,b.price
from Product a left join OrderDetail b
on( b.price>'400')and a.productNo=b.productNo
右外连接：select b.productNo,a.productName,b.quantity,b.price
from Product a right join OrderDetail b
on( b.price>'400')and a.productNo=b.productNo
全外连接：
select b.productNo,a.productName,b.quantity,b.price
from Product a full join OrderDetail b
on( b.price>'400')and a.productNo=b.productNo
（11）
select a.customerNo,a.customerName,convert(char(10),b.orderDate,120) orderDate,b.orderSum
from Customer a left join OrderMaster b on a.customerNo=b.customerNo order by customerNo,orderSum desc
（12）
select a.employeeNo,a.employeeName,case a.sex when'M'then'男'when'F' then'女'end
sex,d.productName,c.quantity,c.price,b.orderSum,convert(char(10),b.or derDate,120) orderDate
from Employee a,OrderMaster b,OrderDetail c,Product d
where a.employeeNo=b.salerNo and b.orderNo=c.orderNo and c.productNo=d.productNo
（13）
select a.employeeName,case a.sex when'M'then'男'when'F'then'女' end sex,b.orderDate,c.quantity,b.orderSum
from Employee a,OrderMaster b,OrderDetail c
where c.productNo=(select productNo from Product where productName='16M DRAM')and a.employeeNo=b.salerNo and b.orderNo=c.orderNo
（14）
select a.orderNo,a.orderSum
from OrderMaster a,Employee b
where orderSum>'2000'and b.employeeNo=a.salerNo and b.sex='F'
（15）
select a.productNo,productName,sum(quantity)总销售数
量,sum(quantity*price)总销售金额
from Product a,OrderDetail b
where a.productNo=b.productNo
group by a.productNo,productName
order by a.productNo
3）实验结果：
（１）
（２）
（３）
（４）
（５）
（６）
（７）
（８）
（９）
（１０）左外连接：
右外连接：
全外连接：
（11）
（12）
（13）
（14）
（15）
（16）
①数据库中有等值连接和非等值连接、自然连接、自表连接、外连接这三种连接类型。

对于一般的连接，只有满足条件的元组才会被检索出来，有时候我们现实生活中是需要把不满足条件的元组也输出的。

所以外连接很多时候更能解决实际问题。

②查询表可以用在嵌套查询和连接查询里面，建立查询表时要注意给表命名。

③ IN和OR不能替换，IN后面可以用于嵌套查询中，而OR不可以。

④需要自表连接的情况有：同一个表的元组间同一属性进行比较；通过一个元组查找同一张表中的另一个或几个元组的信息。

3 实验小结及知识点归纳小结
这次多表查询实验主要的知识点有，简单表的连接、嵌套查询、多表分组运算等，其中简单表的连接里面又有自表连接，外连接等。

嵌套查询里面主要是用IN来实现，多表分组运算是把嵌套查询和聚合查询结合起来进行操作。

在这次实验中，我觉得自己对聚合查询还是没有彻底弄懂，特别是GROUP BY这个语句，通过和同学的讨论知道了它的用法。

对于其他的语句或是查询方法我还是比较清楚的。