OCP-1Z0-051-V9.02-109题

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); where不能跟表达式

B. SELECT student_name,SUM(subject1) 

FROM marks 

WHERE student_name LIKE 'R%';  少了group by

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;

Answer: CD

 

答案解析：

实验测试：

首先创建marks表

scott@TEST0924> create table marks

  2  (student_id varchar2(4) not null,

  3  student_name varchar(25),

  4  subject1 number(3),

  5  subject2 number(3),

  6  subject3 number(3)

  7  );

 

插入几条数据后查询如下：

scott@TEST0924> select * from marks;

 

STUD STUDENT_NAME                SUBJECT1   SUBJECT2   SUBJECT3

---- -------------------------                     ---------- ---------- ----------

1                        aa                                88         89         90

2                        bb                                85         85         80

3                        cc                                87         81         95

 

A答案：AGV分组函数不能用在where子句里

scott@TEST0924> SELECT student_name,subject1 

  2  FROM marks

  3  WHERE subject1 > AVG(subject1);

WHERE subject1 > AVG(subject1)

                 *

ERROR at line 3:

ORA-00934: group function is not allowed here

 

 

scott@TEST0924> SELECT student_name,subject1

  2  FROM marks

  3  WHERE subject1>(select AVG(subject1) from marks);

 

STUDENT_NAME                SUBJECT1

------------------------- ----------

aa                                88

cc                                87

 

B答案：缺少group by

scott@TEST0924> SELECT student_name,SUM(subject1)

  2  FROM marks

  3  WHERE student_name LIKE 'a%';

SELECT student_name,SUM(subject1)

       *

ERROR at line 1:

ORA-00937: not a single-group group function

 

 

scott@TEST0924> SELECT student_name,SUM(subject1)

  2  FROM marks

  3  WHERE student_name LIKE 'a%'

  4  group by student_name;

 

STUDENT_NAME              SUM(SUBJECT1)

------------------------- -------------

aa                                   88

 

C答案：因为测试数据里没有student_name没有为空的，所有无返回值。

cott@TEST0924> SELECT SUM(subject1+subject2+subject3)

  2  FROM marks

  3  WHERE student_name IS NULL;

 

SUM(SUBJECT1+SUBJECT2+SUBJECT3)

-------------------------------

 

故用is not null 一样的测试效果。

scott@TEST0924>

scott@TEST0924> SELECT SUM(subject1+subject2+subject3)

  2  FROM marks

  3  WHERE student_name IS not NULL;

 

SUM(SUBJECT1+SUBJECT2+SUBJECT3)

-------------------------------

                            780

D答案：

cott@TEST0924> SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)

  2  FROM marks

  3  WHERE subject1 > subject2;

 

SUM(DISTINCTNVL(SUBJECT1,0)) MAX(SUBJECT1)

---------------------------- -------------

                          87            87