OCP-1Z0-051-V9 02-107题

淡淡的烟草味﹌ 2022-03-18 06:41 149阅读 0赞

107. View the Exhibit and examine the structure and data in the INVOICE table.

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

![SouthEast][]

A. SELECT AVG(inv\_date ) 类型不一致，不能对date求平均值

FROM invoice;

B. SELECT MAX(inv\_date),MIN(cust\_id)

FROM invoice;

C. SELECT MAX(AVG(SYSDATE - inv\_date)) 缺少group by

FROM invoice;

D. SELECT AVG( inv\_date - SYSDATE), AVG(inv\_amt)

FROM invoice;

Answer: BD

答案解析：

参考：[http://blog.csdn.net/rlhua/article/details/12868639][http_blog.csdn.net_rlhua_article_details_12868639]

A，不能对date类型求平均数

B,MAX,MIN可以对数值和date求最大值和最小值

C,组合函数，需要分组，错误

D,AVG对数值求平均数，正确。

首先创建测试表：

sh@TEST0924> create table invoice

2 (inv\_no number(3) not null,

3 inv\_date date,

4 cust\_id varchar2(4),

5 inv\_amt number(8,2)

6 );

Table created.

插入数据后查询：

sh@TEST0924> select \* from invoice;

INV\_NO INV\_DATE CUST INV\_AMT

\---------- --------- ---- ----------

1 01-APR-07 A10 1000

2 01-OCT-07 B1R 2000

3 01-FEB-07 3000

开始测试：

A答案：AGV函数需要一个数值类型或者可以隐式转为数值类型的参数

sh@TEST0924> SELECT AVG(inv\_date ) FROM invoice;

SELECT AVG(inv\_date ) FROM invoice

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected NUMBER got DATE

AGV function takes as an argument any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type. The function returns the same data type as the numeric data type of the argument.

B答案：

sh@TEST0924> SELECT MAX(inv\_date),MIN(cust\_id)

2 FROM invoice;

MAX(INV\_D MIN(

\--------- ----

01-OCT-07 A10

C答案：缺少group 不要函数

sh@TEST0924> SELECT MAX(AVG(SYSDATE - inv\_date)) FROM invoice;

SELECT MAX(AVG(SYSDATE - inv\_date)) FROM invoice

ERROR at line 1:

ORA-00978: nested group function without GROUP BY

sh@TEST0924> SELECT MAX(AVG(SYSDATE - inv\_date)) FROM invoice

2 group by cust\_id;

MAX(AVG(SYSDATE-INV\_DATE))

\--------------------------

2427.62578

可以单独求平均值和最大值。

sh@TEST0924> SELECT AVG(SYSDATE - inv\_date)FROM invoice;

AVG(SYSDATE-INV\_DATE)

\---------------------

2327.29273

sh@TEST0924> SELECT MAX(SYSDATE - inv\_date) FROM invoice;

MAX(SYSDATE-INV\_DATE)

\---------------------

2427.62655

D答案：

sh@TEST0924> SELECT AVG( inv\_date - SYSDATE), AVG(inv\_amt) FROM invoice;

AVG(INV\_DATE-SYSDATE) AVG(INV\_AMT)

\--------------------- -----------

\-2327.2942 2000

再分享一下我老师大神的人工智能教程吧。零基础！通俗易懂！风趣幽默！还带黄段子！希望你也加入到我们人工智能的队伍中来！[https://blog.csdn.net/jiangjunshow][https_blog.csdn.net_jiangjunshow]

[SouthEast]: /images/20220318/13858f1294764259a4a9e7b75128b283.png
[http_blog.csdn.net_rlhua_article_details_12868639]: http://blog.csdn.net/rlhua/article/details/12868639
[https_blog.csdn.net_jiangjunshow]: https://blog.csdn.net/jiangjunshow/article/details/77338485