Q.33 Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL
LAST_NAM
E
VARCHAR2(30) NOT NULL
FIRST_NAM
E
VARCHAR2(30)
DEPT_ID NUMBER(2)
JOB_CAT VARCHAR2(30)
SALARY NUMBER(8,2)
Which statement shows the department ID, minimum salary, and maximum salary paid in that
department, only of the minimum salary is less then 5000 and the maximum salary is more than
1Z0-007 35
21certify.com
15000?
A. SELECT dept_id, MIN(salary(, MAX(salary)
FROM employees
WHERE MIN(salary) < 5000 AND MAX(salary) > 15000;
B. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
WHERE MIN(salary) < 5000 AND MAX(salary) > 15000
GROUP BY dept_id;
C. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;
D. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
GROUP BY dept_id
HAVING MIN(salary) < 5000 AND MAX(salary) < 15000;
E. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
GROUP BY dept_id, salary
HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;
Answer: E Explanation:
This SELECT statement shows correct result.
Incorrect Answers
A: To provide correct data statement needs also GROUP BY clause.
B: This statement will not provide correct results.
C: HAVING clause can be used only in conjunction with GROUP BY clause.
D: You need grouping by salary also, not only by department. Also condition MAX(salary) < 15000 is
incorrect.
------解决方法--------------------------------------------------------
正确答案应该是E。
------解决方法--------------------------------------------------------
A,B,C是语法上都不对了,D是条件不对,
按照排除法只能选E
条件是选出最少工资小于5000并且最多工资大于15000的部门
但不知道如果sal不放在group by 后面行不行?如果让我自己写,我不会把salary放在group by 后面
------解决方法--------------------------------------------------------
Sorry，写错了，应该是D。
------解决方法--------------------------------------------------------
