See the Exhibit and examine the structure and data in the INVOICE table:
Which two SQL statements would executes successfully? (Choose two.)
A.
SELECT MAX(inv_date),MIN(cust_id) FROM invoice;
B.
SELECT MAX(AVG(SYSDATE – inv_date)) FROM invoice;
C.
SELECT (AVG(inv_date) FROM invoice;
D.
SELECT AVG(inv_date – SYSDATE),AVG(inv_amt) FROM invoice;
why c is incorrect？what about d？
A.
SELECT MAX(inv_date),MIN(cust_id) FROM invoice;
D.
SELECT AVG(inv_date – SYSDATE),AVG(inv_amt) FROM invoice;
About C:
select avg(hiredate) from emp;
ORA-00932: inconsistent datatypes: expected NUMBER got DATE
00932. 00000 – “inconsistent datatypes: expected %s got %s”
inv_date is a date so you cannot use AVG function.
(inv_date-SYSDATE) it is a number so AVG will work well:
select avg(hiredate-sysdate) from emp;
-11449.4771428571428571428571428571428571
SELECT MAX(AVG(SYSDATE – inv_date)) FROM invoice;
sysdate-inv date gives number value..
then it should give right answer.
why b is wrong?
U would get Ora-00978: nested group function without group by 0098.00000 – “nested group function without GROUP BY”
Because of MAX and AVG function that nested. You get error:
ORA-00978 – “nested group function without GROUP BY”
C. is wrong because there is a missing closing Parethese.
B. is wrong because the MAX() function requires a Group By clause so that there is more than one result to run the function against. The nested AVG() function will return only One result, and the MAX() function cannot run when there is only one result to run against. A group by clause will give more than one result to run against.
I took the time to Create this table using ‘ORACLE SQL DEVELOPER’
A. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;
Works fine.
B. SELECT MAX(AVG(SYSDATE – inv_date)) FROM invoice;
-an AVG Function inside a MAX function is called
“Nested Aggregates(group) Function”
* GROUP BY function is required.
* Group function can be nested up to two levels only
e.g. COUNT(SUM(AVG(X)))
return the error ‘ORA-00935’ group function is too deeply
C. SELECT (AVG(inv_date) FROM invoice;
-There are two problems with this query
1. Missing the right parenthesis.
2. The (inv_date) return the error ‘ORA-00932’
inconsistent datatypes: expected NUMBER got DATE.
* if we used (SYSDATE – inv_date) instead it will work fine.
D. SELECT AVG(inv_date–SYSDATE),AVG(inv_amt) FROM invoice;
-Is right; however it
Returns the “ORA-00911” unless I change (inv_date–SYSDATE) to (SYSDATE-inv_date)
than it will work fine.
Of course, because you pasted “–” instead of “-“, different unicode character.