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.
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.
解决方案 »
- ORA-01033: ORACLE initialization or shutdown in progress???
- 求一个Oracle的存储过程
- oralce安装问题,急! 倾尽所有积分!
- 求助!!PLS-00103错误,调了一下午,不知道哪错了就..
- 急急急急急急!!!!!!!!!!!!希望各位大哥赶快帮忙做一下,谢谢了 小弟急着用.!!!!!!!!!!!在线等待...
- 请问oracle 9i中 float类型 和number类型
- 奇怪的oracle错误报告。。。。。。。。。。。。。。。。。。。
- 妹妹有问题啊!快来帮忙!!
- 请问如果查看ORACLE创建表空间的语句,急,谢谢
- COL 命令格式化问题
- sql developer 连接本机Oracle服务器的问题
- 创建视图错误 00905!
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME 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
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条件是选出最少工资小于5000并且最多工资大于15000的部门
但不知道如果sal不放在group by 后面行不行?如果让我自己写,我不会把salary放在group by 后面
答案没有错啊
你可以分2步理解啊:
1,找的是最后一个名字为Smith的记录(涉及到LAST_NAME字段)。
2,这些记录必须是不同部门的(涉及到DEPT_ID字段)。D. SELECT COUNT(DISTINCT dept_id)
FROM employees
WHERE last_name='Smith';答案就是分2步走的啊,where条件实现第一步,然后COUNT(DISTINCT dept_id)实现第二步。当然我有另外的sql语句也可以实现,如下:
select count(*) from (select 1 from employees where last_name='Smith' group by dept_id) a;
只是效率估计没有题库上面的高吧,我没有比较过,自己估计的。Which statement produces the number of different departments that have employees with last name
Smith?
FROM employees
GROUP BY dept_id
HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;
看错哦了。你的题目跟我的不一样啊!把你的题目传给我吧,我的msn是[email protected]
个人感觉E是可以的,因为多然多了分组salary,但是可以得出结果。E. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
GROUP BY dept_id, salary
HAVING MIN(salary) < 5000 AND MAX(salary) > 15000; 大家说呢?
---------- ----------- -----------SQL> SELECT dept_id, MIN(salary), MAX(salary)FROM employees GROUP BY dept_id,salary HAVING MIN(salary) < 5000 AND MAX(salary) > 15000; DEPT_ID MIN(SALARY) MAX(SALARY)
---------- ----------- -----------SQL>可惜没有数据,不然可以试试结果啊!
2 FROM emp
3 GROUP BY deptno,sal
4 HAVING MIN(sal) < 1500 AND MAX(sal) > 2000;未选定行按相同语法在scott/tiger里面测试,没有结果,但里面有符合条件的记录;
应该是没有答案的;D是条件错了,E是group by 多了一个salary,
应该是印错了
E虽然多了个salary,但是没有打破部门分组,所以E也算是正确答案,只是效率比较低!
Version 8.0
的题库,itpub上面找的,
http://www.itpub.net/thread-110212-1-1.html
你那天考啊,我可能到月底了,你考完了给我说一下啊.谢谢!
SQL> SELECT deptno,MIN(sal), MAX(sal)
2 FROM emp
3 GROUP BY deptno,sal
4 HAVING MIN(sal) < 1500 AND MAX(sal) > 2000; 未选定行SQL> SELECT deptno,MIN(sal), MAX(sal)
2 FROM emp
3 GROUP BY deptno
4 HAVING MIN(sal) < 1500 AND MAX(sal) > 2000; DEPTNO MIN(SAL) MAX(SAL)
---------- ---------- ----------
10 1300 5000
20 800 3000
30 950 2850所以说E也不对,为什么呢?本来部门里存在符合条件的记录,但没有被筛选出来啊