QUESTION NO: 4
See the Exhibit and examine the structure and data in the INVOICE table:
Exhibit:
Which two SQL statements would executes successfully? (Choose two.)
A. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;
B. SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;
C. SELECT AVG(inv_date) FROM invoice;
D. SELECT AVG(inv_date - SYSDATE),AVG(inv_amt) FROM invoice;
A D 没错,对所有数据聚合。date不能取平均值,但inv_date - sysdate的结果是number,所以可以用avg
B 同一级别,聚合函数不参嵌套
C date不能取平均值,只有数字可以取平均值
Answer: A,D
See the Exhibit and examine the structure and data in the INVOICE table:
Exhibit:
Which two SQL statements would executes successfully? (Choose two.)
A. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;
B. SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;
C. SELECT AVG(inv_date) FROM invoice;
D. SELECT AVG(inv_date - SYSDATE),AVG(inv_amt) FROM invoice;
A D 没错,对所有数据聚合。date不能取平均值,但inv_date - sysdate的结果是number,所以可以用avg
TEST > SELECT MAX(inv_date),MIN(cust_id) FROM invoice;
MAX(INV_DATE) MIN(CUST_ID)
------------- ------------
2007-10-01 A1Q
1 row selected
TEST > SELECT AVG(inv_date - SYSDATE),AVG(inv_amt) FROM invoice;
AVG(INV_DATE-SYSDATE) AVG(INV_AMT)
--------------------- ------------
-2578.36114583333 2000
1 row selected
B 同一级别,聚合函数不参嵌套
TEST > SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;
SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice
ORA-00978: nested group function without GROUP BY
TEST > SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice group by cust_id;
MAX(AVG(SYSDATE-INV_DATE))
--------------------------
2678.69530092593
1 row selected
C date不能取平均值,只有数字可以取平均值
TEST > SELECT AVG(inv_date) FROM invoice;
SELECT AVG(inv_date) FROM invoice
ORA-00932: inconsistent datatypes: expected NUMBER got DATE
Answer: A,D