OCP-1Z0-051 第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;

答案：CD

二、題目翻譯

檢視MARKS表的結構：

哪兩個語句可以執行成功？（選擇兩個）

三、題目解析

A選項不正确，組函數不能用于WHERE子句中，如果要用，就要放在having子句中。

B選項不正确，student_name是正常列顯示，sum是聚合函數，需要使用group by子句，否則報錯。