之前做题,看到后面题目不知答案为何意,于是到处寻找,终于在oracle 9i:The complete reference中找到了一节解释这个事情,还一并处理了其它几个细节,遂记录之。
细节1:
组函数忽略null,例如count(column), 则计算column所以非空的行数。但count(*)并不适用,count(*)计算的是表中所有的行数,包括字段全为null的行。现在想来也是,全为null的行也是可以查到rowid的,所以也不能说是全null。
细节2:
count()是有选项的,默认是all,distinct选项则是之计算不同的值的数量。这大概就是后面题目的正确解释了。
Q: 33 Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
Which statement produces the number of different departments that have employees with last name
Smith?
A. SELECT COUNT(*)
FROM employees
WHERE last_name='Smith';
B. SELECT COUNT(dept_id)
FROM employees
WHERE last_name='Smith';
C. SELECT DISTINCT(COUNT(dept_id))
FROM employees
WHERE last_name='Smith';
D. SELECT COUNT(DISTINCT dept_id)
FROM employees
WHERE last_name='Smith';
E. SELECT UNIQUE(dept_id)
FROM employees
WHERE last_name='Smith';
感谢newkid的解答
:End
细节1:
组函数忽略null,例如count(column), 则计算column所以非空的行数。但count(*)并不适用,count(*)计算的是表中所有的行数,包括字段全为null的行。现在想来也是,全为null的行也是可以查到rowid的,所以也不能说是全null。
细节2:
count()是有选项的,默认是all,distinct选项则是之计算不同的值的数量。这大概就是后面题目的正确解释了。
Q: 33 Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
Which statement produces the number of different departments that have employees with last name
Smith?
A. SELECT COUNT(*)
FROM employees
WHERE last_name='Smith';
B. SELECT COUNT(dept_id)
FROM employees
WHERE last_name='Smith';
C. SELECT DISTINCT(COUNT(dept_id))
FROM employees
WHERE last_name='Smith';
D. SELECT COUNT(DISTINCT dept_id)
FROM employees
WHERE last_name='Smith';
E. SELECT UNIQUE(dept_id)
FROM employees
WHERE last_name='Smith';
感谢newkid的解答
:End
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21402791/viewspace-623431/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21402791/viewspace-623431/