OCP 1Z0 051 109

来源：互联网发布：现在淘宝开店免费吗编辑：程序博客网时间：2024/04/29 13:06

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;

聚集函数不能出现在where中
当使用了聚集函数时，没有使用聚集函数的列，一定要放在group by子句后面

SQL> SELECT student_name, subject1 FROM marks WHERE subject1 > AVG(subject1);SELECT student_name, subject1 FROM marks WHERE subject1 > AVG(subject1)ORA-00934: group function is not allowed hereSQL> /*应为*/  2  SELECT student_name, subject1  3    FROM marks  4   WHERE subject1 > (SELECT AVG(subject1) FROM marks);STUDENT_NAME              SUBJECT1------------------------- --------Executed in 0 seconds

SQL> SELECT student_name, SUM(subject1) FROM marks WHERE student_name LIKE 'R%';SELECT student_name, SUM(subject1) FROM marks WHERE student_name LIKE 'R%'ORA-00937: not a single-group group functionSQL> /*应为*/  2  SELECT student_name, SUM(subject1) FROM marks WHERE student_name LIKE 'R%' GROUP BY student_name;STUDENT_NAME              SUM(SUBJECT1)------------------------- -------------Executed in 0.031 seconds

SQL> SELECT SUM(subject1+subject2+subject3)  2  FROM marks  3  WHERE student_name IS NULL;SUM(SUBJECT1+SUBJECT2+SUBJECT3------------------------------1 row selectedSQL> SQL> SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)  2  FROM marks  3  WHERE subject1 > subject2;SUM(DISTINCTNVL(SUBJECT1,0)) MAX(SUBJECT1)---------------------------- -------------1 row selected

Answer: CD

0 0