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

实验四 多表查询

1 实验目的与要求

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

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

2 实验内容

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

实验脚本：

Select

a.employeeNo,a.employeeName,a.hireDate,b.employeeNo,b.employeeName,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'

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

答：当同表中的某个元组变量与表中的另外的元组变量相关时，可以使用自表连接，从

而得到一些特殊的数据或者是说带特定条件的数据。自表连接一般是用来判断并筛选单一表中的一些数据。

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