实验报告五

实验五多表查询
1.找出同一天进入公司工作的员工
select distinct a.employeeNo,a.employeeName,a.hireDate
from Employee a,Employee b
where a.employeeNo!=b.employeeNo and a.hireDate=b.hireDate
2.查找与“陈诗杰”在同一个单位工作的员工姓名，性别，部门和职务
select a.employeeName,a.sex,a.department,a.headShip
from Employee a,Employee b
where a.department=b.department and b.employeeName='陈诗杰'
3.在employee表中查询薪水超过员工平均薪水的员工信息
select*
from Employee a
where a.salary>(select avg(b.salary)
from Employee b)
4.查找有销售记录的客户编号，名称和订单总额
select a.customerNo,a.customerName,b.orderNo,sum(quantity*price) orderSum
from Customer a,OrderMaster b,OrderDetail c
where a.customerNo=b.customerNo and b.orderNo=c.orderNo
group by a.customerNo,a.customerName,b.orderNo
5.查询没有订购商品的客户编号和客户名称
6.使用子查询查找32M DRAM的销售情况，要求显示相应的销售员的姓名，性别，销售日期，销售数量和经济呢，其中性别用“男”和“女”表示
select employeeName,case sex
when'M'then'男'
when'F'then'女'
end as sex,b.orderDate,c.quantity 销售数量,c.quantity*c.price 金额
from Employee a,OrderMaster b,OrderDetail c
where a.employeeNo=b.salerNo and b.orderNo=c.orderNo and c.productNo in(select f.productNo
from OrderMaster d,OrderDetail e,Product f
where d.orderNo=e.orderNo and productName='32M DRAM')
7.查询OrderMaster表中订单金额最高的订单号及订单金额
select orderNo,sum(quantity*price) orderSum
from OrderDetail
group by orderNo
having sum(quantity*price)=(select max(orderSum)
from(select orderNo,sum(quantity*price) orderSum
from OrderDetail
group by orderNo)b)
8.在订单主表中查询订单金额大于“E2005002业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。

select*
from OrderMaster
where orderSum>any(select orderSum from OrderMaster where salerNo='E2005002'and orderDate='2008-1-9')
9.查询单价高于400元的商品编号，商品名称，订货数量和订货单价。

select a.productNo,a.productName,sum(b.quantity)订货数量,b.price
from Product a,OrderDetail b
where a.productPrice>400 and b.productNo=a.productNo
group by a.productNo,a.productName,b.price
10.分别使用左外连接，右外连接，完整外部连接查询单价高于400元的商品编号，商品名称，订货数量和订货单价，并分析比较检索的结果。

左外连接：
select a.productNo,a.productName,sum(b.quantity)订货数量,b.price
from Product a left outer join OrderDetail b on a.productPrice>400 and b.productNo=a.productNo group by a.productNo,a.productName,b.price
右外连接：
select a.productNo,a.productName,sum(b.quantity)订货数量,b.price
from Product a right outer join OrderDetail b on a.productPrice>400 and b.productNo=a.productNo
group by a.productNo,a.productName,b.price
全外连接：
select a.productNo,a.productName,sum(b.quantity)订货数量,b.price
from Product a full outer join OrderDetail b on a.productPrice>400 and b.productNo=a.productNo group by a.productNo,a.productName,b.price
11.使用左外连接查找每个客户的客户编号，名称，订货日期，订单金额，其中订货日期不显示时间，日期格式为yyyy-mm-dd，按客户编号排序，同一客户再按订单金额降序排序输出。

select a.customerno 客户编号,customername 客户名称,convert(char(10),orderdate,120)销售日期,ordersum 销售金额
from ordermaster a left outer join customer b on(a.customerno=b.customerno)
order by a.customerno,ordersum desc
12.查找每个员工的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期，其中性别使用“男”和“女”表示，日期使用yyyy-mm-dd格式显示。

select a.employeeNo,a.employeeName,case sex
when'F'then'女'
when'M'then'男'
End sex,b.productName,d.quantity,d.price,d.quantity*d.price 金额,orderDate=convert(char(10),orderDate,120)
from Employee a,Product b,OrderMaster c,OrderDetail d
where a.employeeNo=c.salerNo and b.productNo=d.productNo and c.orderNo=d.orderNo
13.查询32M DRAM的销售情况，要求显示相应的销售员的姓名，性别，销售日期，销售数量和金额，其中性别用“男”，“女”表示。

select a.employeeName,case sex
when'F'then'女'
when'M'then'男'
end as sex,b.orderDate,c.quantity,c.price*c.quantity 金额
from Employee a,OrderMaster b,OrderDetail c,Product d
where a.employeeNo=b.salerNo and b.orderNo=c.orderNo and c.productNo=d.productNo and d.productName='32M DRAM'
14.找出公司男业务员所接且订单金额超过2000的订单号及订单金
额。

select b.orderNo,b.orderSum
from Employee a,OrderMaster b
where a.employeeNo=b.salerNo and sex='M'and b.orderSum>2000
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
16.实验问题：
①有哪些连接操作类型？分析外连接在现实应用的意义。

有等值连接，非等值连接，自然连接，自表链接，外连接（左外联接，右外连接，全外连接）等等；
实践中需要把不满足连接条件的元祖检索出来，外连接则可以实现这样的查询
②查询表可以用在什么地方？使用查询要注意什么？
可以放在from子句，where子句后面，使用查询表需要注意查询条件，并且在必要时为查询表设置一个元组变量名，用该名表示查询表。

③分析SQL语句中IN和OR关键字的异同点。

它们可以互换吗？给出实例说明。

相同点：都是在where子句中使用的查询条件
不同点：in是用于查询属性值在某个集合内的元组，后面是集合，而or是复合条件查询，逻辑或运算
④分析哪几种情况需要使用自表连接。

当同表中的某个元组变量与表中的另外的元组变量相关时，可以使用自表连接，从而得到一些特殊的数据或者是说带特定条件的数据。

自表连接一般是用来判断并筛选单一表中的一些数据。