（109）Examine the structure of the MARKS table:

name Null Type

STUDENT_ID NOT NULL VARCHAR2(4)

STUDENT_NAME VARCHAR2(25)

SUBJECT1 NUMBER(3)

SUBJECT2 NUMBER(3)

SUBJECT3 NUMBER(3)

Which two statements would execute successfully? (Choose two.)

A.

SELECT student_name,subject1 FROM marks

WHERE subject1 > AVG(subject1);

B.

SELECT student_name,SUM(subject1) FROM marks

WHERE student_name LIKE 'R%';

C.

SELECT SUM(subject1+subject2+subject3) FROM marks

WHERE student_name IS NULL;

D.

SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)

FROM marks WHERE subject1 > subject2;

答案：（C、D）

解析：

A（X），WHERE子句是对原始列的筛选，聚合函数并不是原始列中的内容，所以是不允许出现在该子句中的

B（X），出现在SELECT子句中的列，要么出现在聚合函数中，要么出现在GROUP BY子句中

ITPub博客

OCP 1Z0-051-109