《数据库实验多表查询参考答案.docx》由会员分享，可在线阅读，更多相关《数据库实验多表查询参考答案.docx（10页珍藏版）》请在三一文库上搜索。
1、数据库实验多表查询参考答案 数据库原理试验教材试验答案试验二(1) 找出同一天进入公司服务的员工。Select a.employeeNo,a.employeeName,a.hireDate, b.employeeNo,b.employeeName from Employee a,Employee as b where a.employeeNo!=b.employeeNo and a.employeeNameb.employeeName and (a.hireDate=b.hireDate) 参考解答： SELECT DISTINCT a.hireDate, a.employeeNo, a.em
2、ployeeName FROM employee a, employee b WHERE a.employeeNo!=b.employeeNo AND a.hireDate=b.hireDate ORDER BY a.hireDate, a.employeeNo -本人不跟本人连接 - 消退重复的元组 select a.hireDate, a.employeeNo, a.employeeName, b.employeeNo, b.employeeName from employee a, employee b where a.employeeNob.employeeNo and a.hireD
3、ate=b.hireDate - 1. 本人不跟本人连接，2. 不同的两人只连接一次 (2) 查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务。Select b.employeeName,b.sex,b.headShip,b.department from Employee a,Employee as b where a.employeeName='陈诗杰' and (a.department=b.department) (3) 在 Employee 表中查询薪水超过员工平均薪水的员工信息。SELECT * FROM Employee WHERE salary (
4、SELECT avg(salary) avgsalary FROM Employee) (4) 查找有销售记录的客户编号、名称和订单总额。select a.customerNo,a.customerName,b.orderNo,sum(quantity*price) 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 参考解答：select a.custo
5、merNo,a.customerName,sum(orderSum) from Customer a,OrderMaster b where a.customerNo=b.customerNo group by a.customerNo,a.customerName select a.customerNo,a.customerName,b.orderNo,orderSum from Customer a,OrderMaster b where a.customerNo=b.customerNo order by a.customerNo,b.orderNo (5) 查询没有订购商品的客户编号和
6、客户名称。SELECT a.customerNo,customerName FROM Customer a WHERE a.customerNo NOT IN (SELECT customerNo FROM OrderMaster ) (6) 用法子查询查找 32M DRAM 的销售状况，要求显示相应的销售员的姓名、性别，销 售日期、销售数量和金额，其中性别用“男”“女”表示。 、SELECT employeeName,case sex when 'F' then '女' when 'M' then '男' end sex, or
7、derDate,quantity,quantity*price 金额 FROM Employee a,OrderMaster b,OrderDetail c WHERE 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
8、' then '男' end sex, orderDate,quantity,quantity*price 金额 FROM Employee a,OrderMaster b,OrderDetail c WHERE a.employeeNo=b.salerNo AND b.orderNo=c.orderNo AND c.ProductNo IN ( SELECT ProductNo FROM Product WHERE ProductName='32M DRAM') (7) 查询 OrderMaster 表中订单金额最高的订单号及订单金额。select o
9、rderNo,orderSum from OrderMaster where orderSum=(select max(orderSum) from OrderMaster) (8) 在订单主表中查询订单金额大于“E2021002 业务员在 2021-1-9 这天所接的任一张 订单的金额”的全部订单信息。SELECT * FROM OrderMaster WHERE orderSumany (SELECT orderSum FROM OrderMaster WHERE salerNo='E2021002' AND orderDate='20210109' ) (
10、9) 查询单价高于 400 元的商品编号、商品名称、订货数量和订货单价。SELECT a.productNo,productName,quantity,price FROM Product a, OrderDetail b WHERE a.productNo=b.productNo AND price400 ORDER BY productName (10) 分别用法左外连接、右外连接、完整外部连接查询单价高于 400 元的商品编号、商 品名称、订货数量和订货单价，并分析比较检索的结果。 左外连接指令：SELECT a.productNo,productName,quantity,price
11、FROM Product a LEFT OUTER JOIN OrderDetail b ON a.productNo=b.productNo WHERE price400 Select a.productNo , a.productName , b.quantity,b.price From OrderDetail As b left JOIN ON (a.productNo=b.productNo) Product As a and price400 这两个左外连接，第一个是先去掉单价小于等于 400 的商品，然后执行左外连接， 该查询的结果表明商品表中全部单价高于 400 的商品全部被订
12、购了 其次个左外连接是将那些单价小于等于 400 的商品用空值替代 右外连接指令：SELECT a.productNo,productName,quantity,price FROM Product a RIGHT OUTER JOIN OrderDetail b ON a.productNo=b.productNo WHERE price400 Select a.productNo , a.productName , b.quantity,b.price From OrderDetail As b RIGHT JOIN Product As a ON (a.productNo=b.produ
13、ctNo) and price400 全连接指令：SELECT a.productNo,productName,quantity,price FROM Product a FULL OUTER JOIN OrderDetail b ON a.productNo=b.productNo WHERE price400 Select a.productNo , a.productName , b.quantity,b.price From OrderDetail As b full JOIN Product As a and price400 ON (a.productNo=b.productNo)
14、 右外连接和全外连接的分析同左外连接 从上述结果可知：若表 a 和表 b 做外连接，且 b 表是外码表，则 a 和 b 表左外连接可能 会消失空值，但是右连接肯定不会消失空值，全外连接与左外连接一样的结果。 (11) 用法左外连接查找每个客户的客户编号、名称、订货日期、订单金额，其中订货日 期不要显示时间，日期格式为“yyyy-mm-dd” ,按客户编号排序，同一客户再按订 单金额降序排序输出。SELECT a.customerNo,customerName, convert(char(10),orderDate,120) orderDate,orderSum FROM Customer a
15、LEFT OUTER JOIN OrderMaster b ON a.customerNo=b.customerNo ORDER BY a.customerNo,orderSum desc (12) 查找每个员工的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、 单价、 金额和销售日期， 其中性别用法 “男” “女” 和 表示， 日期用法 “yyyy-mm-dd” 格式显示。SELECT salerNo,employeeName, CASE sex WHEN 'M'THEN '男' WHEN 'F'THEN '女' E
16、ND 性别, productName,quantity,price,orderSum, convert(char(10),orderDate,120) orderDate FROM Employee a,OrderMas ter b,OrderDetail c,Product d WHERE a.employeeNo=b.salerNo AND b.orderNo=c.orderNo AND c.productNo=d.productNo (13) 查找 16M DRAM 的销售状况，要求显示相应的销售员的姓名、性别，销售日期、销 售数量和金额，其中性别用“男”“女”表示。 、SELECT e
17、mployeeName,CASE sex WHEN 'M' THEN'男' WHEN 'F' THEN'女' END sex, orderDate,quantity,quantity*price total 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 productName='16M DRAM
18、' (14) 找出公司男业务员所接且订单金额超过 2021 元的订单号及订单金额。SELECT orderNo,orderSum FROM (SELECT orderNo,orderSum FROM OrderMaster a,Employee b WHERE a.salerNo=b.employeeNo AND b.sex='M') c WHERE ordersum2021 或：SELECT orderNo,orderSum FROM OrderMaster a,Employee b and ordersum2021 WHERE a.salerNo=b.employe
19、eNo AND b.sex='M' (15) 查询每种商品的总销售数量及总销售金额，要求显示出商品编号、商品名称、总数 量及总金额，并按商品号从小到大排列。SELECT a.productNo 商品编号,productName 商品名称, qtySum 总数量,totalSum 总金额 FROM Product a, (SELECT productNo,sum(quantity) qtySum, sum(quantity*price) totalSum FROM OrderDetail GROUP BY productNo)b WHERE a.productNo=b.produ
20、ctNo ORDER BY a.productNo (16) 试验问题： 连接操作类型有哪些？分析外连接在现实应用中的意义。 答：连接运算包括内连接(等值连接，非等值连接，自然连接)外连接(左外连接，右 外连接，全外连接) ,自表连接。外连接的意义：对于存在多对多的关系的两个关系，将关 联不上的部分信息也能显示出来。在现实中对于数据库的数据初始化很有用。 查询表可以用在什么地方？用法查询表要留意哪些地方？ 答：查询表用在 from 子句之后，必需为查询表设置一个元组变量名，用该名表示查询 表。查询表是执行过程中产生的表，执行完后其表自动丢失。 子查询也可以认为是一个查询表，但是不需要为他指定元
21、组变量名。 分析 SQL 语句中的 IN 和 OR 关键字有何异同点？它们可以互换吗？给出实例说明。 答：in 可用于集合查询，也可用于嵌套子查询中。 or 则为规律查询，规律或。 当 in 用于集合查询时，in 和 or 之间可以相互转换；当 in 用于嵌套子查询时，则不 可以相互转换。 举例： 用 IN 来实现：SELECT studentNo,courseNo,score FROM Score WHERE courseNo IN(001,005,003) 用 OR 来实现：SELECT studentNo,courseNo FROM Score WHERE courseNo=001 OR courseNo=005 OR courseNo=003 但是当 IN 用于子查询时，此时 IN 不能和 OR 互换。 举例：SELECT studentName FROM Student WHERE Student.studentNo IN (SELECT Score.studentNo FROM Score) 此时只能用 IN。 分析哪几种状况需要 用法自表连接。 答：当同表中的某个元组变量与表中的另外的元组变量相关时，可以用法自表连接，从 而得到一些特别的数据或者是说带特定条件的数据。 自表连接一般是用来推断并筛选单一表 中的一些数据。