OCP-1Z0-051 补充题库 第15题 子查询和join

一、原题

The COMMISSION column shows the monthly commission earned by the employee.

Which two tasks would require sub queries or joins in order to be performed in a single step?
(Choose two.)
A. listing the employees who earn the same amount of commission as employee 3
B. finding the total commission earned by the employees in department 10
C. finding the number of employees who earn a commission that is higher than the average commission of the company
D. listing the departments whose average commission is more that 600
E. listing the employees who do not earn commission and who are working for department 20 in
descending order of the employee ID
F. listing the employees whose annual commission is more than 6000

答案: A,C

二、题目翻译

COMMISSION列显示了每个员工的月提成，具体如下:
下面哪两个任务需要子查询或者连接查询（join）?(选择2个)
A.显示和员工3提成相同的员工的信息。
B.查出部门10的总提成。
C.查出高于公司员工平均提成的员工数。
D.显示平均提成大于600的员工信息。
E.显示没有提成的员工和部门20的员工按降序排列。
F.显示年提成大于6000的人。

三、题目解析

A选项需要用到子查询，要找出和员工3一样提成的人，就需要先用子查询求出员工3的提成，然后才能查出和他提成一样的人。
B选项不需要，用where 部门=10，然后用sum汇总，就提出结果了，不需要用子查询，也不需要用join。
C选项需要用子查询，先要子查询查出公司员工的平均提成。
D选项不需要，只需要求出平均提成，然后和600比较就行，加个having就行了。
E选项不需要，where过滤一下就行。
F选项不需要，计算出年提成，where过滤一下就行了。