实验四-多表查询-实验报告

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

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

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

实验脚本：
Select
a.employeeNo,a.employeeName,a.hireDate,
b.employeeNo,b.empl
oyeeName,b.hireDate
from Employee a,Employee as b
where a.employeeNo!=b.employeeNo and
a.employeeName>
b.employeeName
and(a.hireDate=b.hireDate)
实验结果：
(2)查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务。

实验脚本：
select employeeName,sex,department,headShip
from Employee
where department in(
select department
from Employee
where employeeName='陈诗杰')
实验结果：
(3)在Employee表中查询薪水超过员工平均薪水的员工信息。

实验脚本：
select*
from Employee
where salary>(
select avg(salary)
from Employee)
实验结果：
(4)查找有销售记录的客户编号、名称和订单总额。

实验脚本：
select a.customerno 客户编号,b.customerName 客户名
称,sum(a.ordersum)订单总额
from OrderMaster a,Customer b,OrderDetail c
where a.customerno=b.customerno and a.orderNo=c.orderNo and c.quantity>0
group by a.customerNo,b.customerName
实验结果：
(5)查询没有订购商品的客户编号和客户名称。

实验脚本：
select customerno 客户编号,customerName 客户名称
from Customer
where customerno not in(select customerno
from OrderMaster)
实验结果：
(6)使用子查询查找32M DRAM的销售情况，要求显示相应的销售员的姓名、性别，
销售日期、销售数量和金额，其中性别用“男”、“女”表示。

实验脚本：
select employeeName 姓名,orderdate 销售日期,quantity 销售金
额,(price*quantity)金额,
case a.sex
when'F'then'女'
when'M'then'男'
end as性别
from Employee a,OrderMaster b,OrderDetail c,Product d
where a.employeeNo=b.salerNo and b.orderNo=c.orderNo and
d.productNo=c.productNo
and productName='32M DRAM'
实验结果：
(7)查询OrderMaster表中订单金额最高的订单号及订单金额。

实验脚本：
select orderNo 订单号,orderSum 订单金额
from OrderMaster
where orderSum=(select max(orderSum)from OrderMaster) 实验结果
(8)在订单主表中查询订单金额大于“E2005002业务员在2008-1-9这天所接的任一张
订单的金额”的所有订单信息。

实验脚本：
select*
from OrderMaster
where orderSum>(select max(orderSum)
from ordermaster
where salerNo='E2005002'and orderDate='2008-1-9') 实验结果：
(9)查询单价高于400元的商品编号、商品名称、订货数量和订货单价。

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

左外连接：
实验脚本：
select a.productNo,productName,quantity,price
from OrderDetail as a left join Product as b
on(a.productNo=b.productNo and price>400)
实验结果：
右外连接：
实验脚本：
select a.productNo,productName,quantity,price
from OrderDetail as a right join Product as b
on(a.productNo=b.productNo and price>400) 实验结果：
完全外部连接：
实验脚本：
select a.productNo,productName,quantity,price
from OrderDetail as a full join Product as b
on(a.productNo=b.productNo and price>400)
order by a.productNo
实验结果：
(11)使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额，其中订货日
期不要显示时间，日期格式为“yyyy-mm-dd”，按客户编号排序，同一客户再按订单金额降序排序输出。

实验脚本：
select a.customerNo 客户编号,customerName 名称,orderSum 订单
金额,
isnull(convert(char(10),ordersum,120),' ')订单金额,
isnull(convert(char(10),orderDate,120),' ')出生日期
from Customer as a left join OrderMaster as b
on(a.customerNo=b.customerNo)
order by a.customerNo,ordersum desc
实验结果：
(12)查找每个员工的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、
单价、金额和销售日期，其中性别使用“男”和“女”表示，日期使用“yyyy-mm-dd”
格式显示。

实验脚本：
select salerno 销售员编号,employeename 姓名,productname 商品名
称,quantity 数量,price 单价,quantity*price 金额,
case sex
when'F'then'女'
when'M'then'男'
end as性别,
isnull(convert(char(10),orderDate,120),' ')日期
from ordermaster a,employee b,product c,orderdetail d
where a.salerno=b.employeeno and c.productno=d.productno and
a.orderno=d.orderno
实验结果：
(13)查找16M DRAM的销售情况，要求显示相应的销售员的姓名、性别，销售日期、
销售数量和金额，其中性别用“男”、“女”表示。

实验脚本：
select employeename 姓名,quantity 数量,quantity*price 金额,
case sex
when'F'then'女'
when'M'then'男'
end as性别,
isnull(convert(char(10),orderDate,120),' ')日期
from employee a,ordermaster b,orderdetail c,product d
where a.employeeno=b.salerno and b.orderno=c.orderno and
d.productno=c.productno
and d.productno=(select productno
from product
where productname='16M DRAM')
实验结果：
(14)找出公司男业务员所接且订单金额超过2000元的订单号及订单金额。

实验脚本：
select orderno 订单号,ordersum 订单金额
from ordermaster
where ordersum>2000 and salerno in(select employeeno
from employee
where sex='M') 实验结果：
(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子句之后，必须为查询表设置一个元组变量名，用该名表示查询表。

查询表是执行过程中产生的表，执行完后其表自动丢失。

③分析SQL语句中的IN和OR关键字有何异同点？它们可以互换吗？给出实例说明。

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

例：select employeeName,sex,department,headShip
from Employee
where department in(
select department
from Employee
where employeeName='陈诗杰')
和select*
from Employee
where(headShip='职员'or headShip='科长')and sex='F'
④分析哪几种情况需要使用自表连接。

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

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