Click the Exhibit button and examine the data in the EMPLOYEES table.
Which three subqueries work? (Choose three.)EMPLOYEES 部分数据如下:
--lastname--departmentId--salary
A 10 3000
B 20 4000
C 20 3500
D 30 3700
E 10 9000A. SELECT *
FROM employees
where salary > (SELECT MIN(salary)
FROM employees
GROUP BY department_id);B. SELECT *
FROM employees
WHERE salary = (SELECT AVG(salary)
FROM employees
GROUP BY department_id);C. SELECT distinct department_id
FROM employees
WHERE salary > ANY (SELECT AVG(salary)
FROM employees
GROUP BY department_id);D. SELECT department_id
FROM employees
WHERE salary > ALL (SELECT AVG(salary)
FROM employees
GROUP BY department_id);E. SELECT last_name
FROM employees
WHERE salary > ANY (SELECT MAX(salary)
FROM employees
GROUP BY department_id);F. SELECT department_id
FROM employees
WHERE salary > ALL (SELECT AVG(salary)
FROM employees
GROUP BY AVG(SALARY));Answer: C, D, E为什么答案是C、D、F呢?我觉得是A、D、F才对啊?大家指点下,谢谢了!
Which three subqueries work? (Choose three.)EMPLOYEES 部分数据如下:
--lastname--departmentId--salary
A 10 3000
B 20 4000
C 20 3500
D 30 3700
E 10 9000A. SELECT *
FROM employees
where salary > (SELECT MIN(salary)
FROM employees
GROUP BY department_id);B. SELECT *
FROM employees
WHERE salary = (SELECT AVG(salary)
FROM employees
GROUP BY department_id);C. SELECT distinct department_id
FROM employees
WHERE salary > ANY (SELECT AVG(salary)
FROM employees
GROUP BY department_id);D. SELECT department_id
FROM employees
WHERE salary > ALL (SELECT AVG(salary)
FROM employees
GROUP BY department_id);E. SELECT last_name
FROM employees
WHERE salary > ANY (SELECT MAX(salary)
FROM employees
GROUP BY department_id);F. SELECT department_id
FROM employees
WHERE salary > ALL (SELECT AVG(salary)
FROM employees
GROUP BY AVG(SALARY));Answer: C, D, E为什么答案是C、D、F呢?我觉得是A、D、F才对啊?大家指点下,谢谢了!
FROM employees
WHERE salary > ANY (SELECT AVG(salary)
FROM employees
GROUP BY department_id);D. SELECT department_id
FROM employees
WHERE salary > ALL (SELECT AVG(salary)
FROM employees
GROUP BY department_id);
这两个你能执行吗?
FROM employees
where salary > (SELECT MIN(salary)
FROM employees
GROUP BY department_id); > 只能大于一条记录。而子查询中可能返回多条,所以不对。>any() 等同于 > or >
>all() 等于于 > and > F这个选项不对吧
关注!
FROM employees
where salary > (SELECT MIN(salary)
FROM employees
GROUP BY department_id); A,语法错误。B,语法没有错,但是90%以上的概率是查不到数据,因为=avg吗。C,正确,有结果出来。D,正确,有结果出来。E,正确,有结果出来。F,语法错误如下:
SQL>
SELECT *
FROM employees
WHERE salary > ALL (SELECT AVG(salary)
FROM employees
GROUP BY AVG(SALARY))ORA-00934: group function is not allowed here所以正确答案是C、D、E
F的语法错误是:avg等聚合函数取值不能做为group by分组字段来用。
>any
的用法我倒是不知道,第一次看到,学习下
阿别说的对
其实你有测试环境去测试下就知道了