数据库实验多表查询参考答案

1、数据库原理实验教材实验答案实验二(1) 找出同一天进入公司服务的员工。 Select a.employeeNo,a.employeeName,a.hireDate,b.employeeNo,b.employeeNamefrom Employee a,Employee as bwhere a.employeeNo!=b.employeeNo and a.employeeName>b.employeeName and (a.hireDate=b.hireDate)参考解答：SELECT DISTINCT a.hireDate, a.employeeNo, a.employeeName - 消

2、除重复的元组FROM employee a, employee bWHERE a.employeeNo!=b.employeeNo -本人不跟本人连接AND a.hireDate=b.hireDateORDER BY a.hireDate, a.employeeNoselect a.hireDate, a.employeeNo, a.employeeName, b.employeeNo, b.employeeNamefrom employee a, employee bwhere a.employeeNo<b.employeeNo - 1. 本人不跟本人连接，2. 不同的两人只连接一次a

3、nd a.hireDate=b.hireDate(2) 查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务。 Select b.employeeName,b.sex,b.headShip,b.departmentfrom Employee a,Employee as bwhere a.employeeName='陈诗杰' and (a.department=b.department)(3) 在Employee表中查询薪水超过员工平均薪水的员工信息。 SELECT *FROM EmployeeWHERE salary > ( SELECT avg(salary)

4、avgsalary FROM Employee)(4) 查找有销售记录的客户编号、名称和订单总额。 select a.customerNo,a.customerName,b.orderNo,sum(quantity*price)from Customer a,OrderMaster b,OrderDetail cwhere a.customerNo=b.customerNo and b.orderNo=c.orderNogroup by a.customerNo,a.customerName,b.orderNo参考解答： select a.customerNo,a.customerName,s

5、um(orderSum)from Customer a,OrderMaster bwhere a.customerNo=b.customerNogroup by a.customerNo,a.customerName select a.customerNo,a.customerName,b.orderNo,orderSumfrom Customer a,OrderMaster bwhere a.customerNo=b.customerNoorder by a.customerNo,b.orderNo(5) 查询没有订购商品的客户编号和客户名称。 SELECT a.customerNo,cus

6、tomerNameFROM Customer aWHERE a.customerNo NOT IN (SELECT customerNo FROM OrderMaster )(6) 使用子查询查找32M DRAM的销售情况，要求显示相应的销售员的姓名、性别，销售日期、销售数量和金额，其中性别用“男”、“女”表示。 SELECT employeeName,case sex when 'F' then '女' when 'M' then '男' end sex, orderDate,quantity,quantity*price 金额

7、FROM Employee a,OrderMaster b,OrderDetail cWHERE a.employeeNo=b.salerNo AND b.orderNo=c.orderNo AND c.ProductNo IN ( SELECT ProductNo FROM Product WHERE ProductName='32M DRAM')参考解答： SELECT employeeName,case sex when 'F' then '女' when 'M' then '男' end sex, orde

8、rDate,quantity,quantity*price 金额FROM Employee a,OrderMaster b,OrderDetail cWHERE a.employeeNo=b.salerNo AND b.orderNo=c.orderNo AND c.ProductNo IN ( SELECT ProductNo FROM Product WHERE ProductName='32M DRAM')(7) 查询OrderMaster表中订单金额最高的订单号及订单金额。 select orderNo,orderSumfrom OrderMasterwhere ord

9、erSum=(select max(orderSum) from OrderMaster)(8) 在订单主表中查询订单金额大于“E2005002业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。 SELECT *FROM OrderMasterWHERE orderSum>any (SELECT orderSum FROM OrderMaster WHERE salerNo='E2005002' AND orderDate='20080109' )(9) 查询单价高于400元的商品编号、商品名称、订货数量和订货单价。 SELECT a.p

10、roductNo,productName,quantity,priceFROM Product a, OrderDetail bWHERE ductNo=ductNo AND price>400ORDER BY productName(10) 分别使用左外连接、右外连接、完整外部连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价，并分析比较检索的结果。 左外连接命令： SELECT ductNo,productName,quantity,priceFROM Product a LEFT OUTER JOIN OrderDetail b ON a

11、.productNo=ductNoWHERE price>400 Select ductNo , ductName , b.quantity,b.priceFrom OrderDetail As b left JOIN Product As a ON (ductNo=ductNo) and price>400 这两个左外连接，第一个是先去掉单价小于等于400的商品，然后执行左外连接，该查询的结果表明商品表中所有单价高于400的商品全部被订购了 第二个左外连接是将那些单价小于等于400的商品用空值替代右外连接命令： SELECT a.

12、productNo,productName,quantity,priceFROM Product a RIGHT OUTER JOIN OrderDetail b ON ductNo=ductNoWHERE price>400 Select ductNo , ductName , b.quantity,b.priceFrom OrderDetail As b RIGHT JOIN Product As a ON (ductNo=ductNo) and price>400 全连接命令：SELECT ductNo,p

13、roductName,quantity,priceFROM Product a FULL OUTER JOIN OrderDetail b ON ductNo=ductNoWHERE price>400Select ductNo , ductName , b.quantity,b.priceFrom OrderDetail As b full JOIN Product As a ON (ductNo=ductNo) and price>400 右外连接和全外连接的分析同左外连接从上述结果可知：若表a和表b做外连接，且b表是

14、外码表，则a和b表左外连接可能会出现空值，但是右连接一定不会出现空值，全外连接与左外连接一样的结果。(11) 使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额，其中订货日期不要显示时间，日期格式为“yyyy-mm-dd”，按客户编号排序，同一客户再按订单金额降序排序输出。 SELECT a.customerNo,customerName,convert(char(10),orderDate,120) orderDate,orderSumFROM Customer a LEFT OUTER JOIN OrderMaster b ON a.customerNo=b.customerNo

15、ORDER BY a.customerNo,orderSum desc(12) 查找每个员工的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期，其中性别使用“男”和“女”表示，日期使用“yyyy-mm-dd”格式显示。 SELECT salerNo,employeeName, CASE sex WHEN 'M'THEN '男' WHEN 'F'THEN '女' END 性别, productName,quantity,price,orderSum, convert(char(10),orderDat

16、e,120) orderDateFROM Employee a,OrderMaster b,OrderDetail c,Product dWHERE a.employeeNo=b.salerNo AND b.orderNo=c.orderNo AND ductNo=ductNo(13) 查找16M DRAM的销售情况，要求显示相应的销售员的姓名、性别，销售日期、销售数量和金额，其中性别用“男”、“女”表示。 SELECT employeeName,CASE sex WHEN 'M' THEN'男' WHEN 'F' THEN&

17、#39;女' END sex, orderDate,quantity,quantity*price totalFROM Employee a,OrderMaster b,OrderDetail c,Product dWHERE a.employeeNo=b.salerNo AND b.orderNo=c.orderNo AND ductNo=ductNo AND productName='16M DRAM'(14) 找出公司男业务员所接且订单金额超过2000元的订单号及订单金额。 SELECT orderNo,orderSumFROM (SELECT

18、 orderNo,orderSum FROM OrderMaster a,Employee b WHERE a.salerNo=b.employeeNo AND b.sex='M') cWHERE ordersum>2000 或： SELECT orderNo,orderSumFROM OrderMaster a,Employee bWHERE a.salerNo=b.employeeNo AND b.sex='M' and ordersum>2000(15) 查询每种商品的总销售数量及总销售金额，要求显示出商品编号、商品名称、总数量及总金额，并按商

19、品号从小到大排列。SELECT ductNo 商品编号,productName 商品名称,qtySum 总数量,totalSum 总金额FROM Product a, (SELECT productNo,sum(quantity) qtySum,sum(quantity*price) totalSum FROM OrderDetail GROUP BY productNo)bWHERE ductNo=ductNoORDER BY ductNo (16) 实验问题： 连接操作类型有哪些？分析外连接在现实应用中的意义。答：连接运算包括内连接（等值连接，非等值连接，自然连接）外连接（左外连接，右外连接，全外连接），自表连接。外连接的意义：对于存在多对多的关系的两个关系，将关联不上的部分信息也能显示出来。在现实中对于数据库的数据初始化很有用。 查询表可以用在