ocp-047-49 233

49. View the Exhibit and examine the description of EMPLOYEES and DEPARTMENTS tables. 
You want to display the EMPLOYEE_ID, LAST_NAME, and SALARY for the employees who get the maximum salary in their respective departments.
你想显示employees 表中在各自部门里得到最高工资的人的EMPLOYEE_ID, LAST_NAME, SALARY


The following SQL statement was written:


WITH


SELECT employee_id, last_name, salary 
FROM employees 
WHERE (department_id, salary) = ANY (SELECT * FROM dept_max)
 
dept_max as (
  SELECT d.department_id, max(salary) 
  FROM departments d JOIN employees j
  ON (d.department_id = j.department_id)
  GROUP BY d.department_id
  );


with结构错误
使用在前声明在后，无法运行会报错
应该写成
WITH　dept_max as (
   SELECT d.department_id, max(salary) 
   FROM departments d JOIN employees j
   ON (d.department_id = j.department_id)
   GROUP BY d.department_id
   )
SELECT employee_id, last_name, salary 
FROM employees 
WHERE (department_id, salary) = ANY (SELECT * FROM dept_max);
 
Which statement is true regarding the execution and the output of this statement?


A. The statement would execute and give the desired results.
with语句结构错误，先是用再声明是无法成功执行的


B. The statement would not execute because the = ANY comparison operator is used instead of =. 
语句确实不会成功执行，但是并不是因为使用了=any代替了=，并且(department_id, salary) =(SELECT * FROM dept_max)肯定是错误的，因为返回值不一定唯一


C. The statement would not execute because the main query block uses the query name before it is even created. (right)


D. The statement would not execute because the comma is missing between the main query block and the query name.
语句确实不会成功执行，但是并不是因为主语句块与query name间少了个逗号，而是因为先使用再定义产生错误