数据库原理实验4多表查询

实验四 多表查询

1 实验目的与要求

(1) 熟练掌握SQL语句的使用。

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

2 实验内容

(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关键字有何异同点？它们可以互换吗？给出实例说明。 ④ 分析哪几种情况需要使用自表连接。

实验脚本如下：

(1) 找出同一天进入公司服务的员工。

select hireDate48 ,employeeName48 from Employee48 a where exists (

select * from Employee48 b

where a.hireDate48=b.hireDate48 and a.employeeNo48 !=b.employeeNo48 )

group by hireDate48 ,employeeName48

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

select a.employeeName48 ,a.department48 ,a.headShip48,

case a.sex48 when 'F' then '女' when 'M' then '男' end sex48

from Employee48

where employeeName48 ='陈诗杰' ) b

from Employee48 a,( select *

where a.department48 =b.department48

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

select *

from employee48 a,(select avg(salary48) 平均工资 from employee48 ) b where a.salary48>b.平均工资

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

SELECT a.CustomerNo48 ,a.CustomerName48 ,sum(quantity48 *price48 ) 订单总额

FROM Customer48 a,OrderMaster48 b,OrderDetail48 c

WHERE a.CustomerNo48 =b.CustomerNo48 and b.orderNo48 =c.orderNo48 GROUP BY a.CustomerNo48 ,a.CustomerName48

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

select a.customerNo48,customerName48 from customer48 a

where a.customerNo48 not in (

select b.customerNo48 from orderMaster48 b )

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

select employeeName48 ,

case sex48 when 'F' then '女'

when 'M' then '男'

end 性别, orderDate48 ,quantity48 ,sum(quantity48 *price48 )金额 from Employee48 a,OrderMaster48 b,OrderDetail48 c

where employeeNo48 =salerNo48 and b.orderNo48 =c.orderNo48 and ProductNo48 in

(select ProductNo48 from Product48 where ProductName48 ='32M DRAM')

group by employeeName48 ,sex48 , orderDate48 ,quantity48

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

select orderNo48,orderSum48 from orderMaster48

where orderSum48=(select max(orderSum48) max

from orderMaster48)

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

select *

from orderMaster48

where orderSum48>(select max(orderSum48) max1

from orderMaster48

wheresalerNo48='E2005002'and orderDate48='2008.1.9')

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

select b.ProductName48 ,a.ProductNo48 ,a.total48,a.price48 from (

select sum(quantity48 ) as total48,ProductNo48 ,price48 from OrderDetail48

group by ProductNo48 ,price48

having price48 >400) as a , Product48 as b where a.ProductNo48 =b.ProductNo48

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

左外连接:

select ProductName48 ,a.ProductNo48 ,quantity48,price48 from orderDetail48 a left join product48 b

on a.ProductNo48 =b.ProductNo48 and price48>'400'

右外连接:

select ProductName48 ,a.ProductNo48 ,quantity48,price48 from orderDetail48 a right join product48 b

on a.ProductNo48 =b.ProductNo48 and price48>'400'

完整外部连接:

select ProductName48 ,a.ProductNo48 ,quantity48,price48

本文来源：https://www.bwwdw.com/article/pu0d.html