OCP-1Z0-051-2015-57题

QUESTION NO: 57
Examine the structure of the MARKS table:

Exhibit:

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;
Answer: C,D

Explanation:

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

可改为

scott@TEST0924> SELECT student_name,subject1

  2  FROM marks

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

B答案：缺少group by

可改为

scott@TEST0924> SELECT student_name,SUM(subject1)

  2  FROM marks

  3  WHERE student_name LIKE 'R%'

  4   group by student_name;