OCP 1Z0 051 107

107. View the Exhibit and examine the structure and data in the INVOICE table. 
Which two SQL statements would execute successfully?   (Choose two.)  

A. SELECT AVG(inv_date ) 
FROM invoice; 
B. SELECT MAX(inv_date),MIN(cust_id) 
FROM invoice; 
C. SELECT MAX(AVG(SYSDATE - inv_date)) 
FROM invoice; 
D. SELECT AVG( inv_date - SYSDATE), AVG(inv_amt) 
FROM invoice; 

A  日期不能求平均，只能对number取平均

SQL> SELECT AVG(inv_date) FROM invoice;
SELECT AVG(inv_date) FROM invoice
ORA-00932: inconsistent datatypes: expected NUMBER got DATE

C 聚合函数嵌套需要有grup by 子句

SQL> SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;
SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice
ORA-00978: nested group function without GROUP BY

SQL> SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice group by INV_NO;
MAX(AVG(SYSDATE-INV_DATE))
--------------------------
          2290.70414351852
1 row selected

这种嵌套相当于

SQL> SELECT MAX(av)
  2    FROM (SELECT AVG(SYSDATE - inv_date) AS av FROM invoice GROUP BY inv_no);
   MAX(AV)
----------
2290.70479
1 row selected

B与D没问题

SQL> SELECT MAX(inv_date), MIN(cust_id) FROM invoice;
MAX(INV_DATE) MIN(CUST_ID)
------------- ------------
2008-3-15     1
1 row selected
Executed in 0.032 seconds

SQL> SELECT AVG(inv_date - SYSDATE), AVG(inv_amt) FROM invoice;
AVG(INV_DATE-SYSDATE) AVG(INV_AMT)
--------------------- ------------
        -2276.2065625   177991.275
1 row selected

注：使用了结构一样的表，但数据是另外的，所以结果会与本题所述数据不一样

Answer: BD