Exam 1z0-082 topic 1 question 56 discussion

C is correct answer

I think C

C is correct

C is correct, and by the way is also the only valid query on the question. A) ORA-00937: not a single-group group function B)ORA-00979: not a GROUP BY expression D)ORA-00937: not a single-group group function

A - false; returns error ORA-00937: not a single-group group function; first occurence of dept_id is marked as the cause of the error B - false; returns error ORA-00979: not a GROUP BY expression; join_date is marked as the cause of the error C - true; columns mentioned between SELECT and FROM keywords are also mentioned after GROUP BY clause D - false; same as answer A

I see only B as invalid query, probably the question is wrong and it should be: Which query is INvalid?

C is correct

A (FALSE). SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id; select DEPARTMENT_ID, AVG(MAX(SALARY)) from EMPLOYEES group by DEPARTMENT_ID; ERROR at line 1: ORA-00937: not a single-group group function B (FALSE). SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id; select DEPARTMENT_ID, HIRE_DATE,sum(salary) from EMPLOYEES group by DEPARTMENT_ID * ERROR at line 1: ORA-00979: not a GROUP BY expression C (TRUE). SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date; SQL> select DEPARTMENT_ID, HIRE_DATE,SUM(SALARY) from EMPLOYEES group by DEPARTMENT_ID , HIRE_DATE; DEPARTMENT_ID HIRE_DATE SUM(SALARY) ------------- --------- ----------- 60 25-JUN-05 4800 D (FALSE). SELECT dept_id, MAX(AVG(salary)) FROM employees GROUP BY dept_id; SELECT DEPARTMENT_ID , MAX(AVG(salary)) FROM employees GROUP BY DEPARTMENT_ID * ERROR at line 1: ORA-00937: not a single-group group function

C is correct answer B is wrong answer, because not working! But why not A not ?

C, by testing on OE DB sample.

A is wrong. When using nested aggregate functions you are not allowed to display any additional column(s) other than the nested aggregate function. B is wrong. Missing the join_date column in the GROUP BY clause. The rule states: "Any column in the select list that's not part of an aggregate function MUST BE in the group by clause". C is correct. D is wrong. Same issue as with option A.

C for sure

B is correct

C is correct

I created the same table with same data type: Option A and B gave errors while C and D gave the same output.

C is the correct answer: SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date; All options with non-groupable functions need to be added in GROUP BY.