Exam 1z0-082 All QuestionsBrowse all questions from this exam
Go to Exam
Question 56

Examine the description of the EMPLOYEES table:

Which query is valid?

    Correct Answer: C

    The valid query is the one that adheres to the requirements of SQL grouping. In the SQL SELECT statement, any column in the SELECT clause that is not contained within an aggregate function must be included in the GROUP BY clause. Option C correctly includes 'dept_id' and 'join_date' in both the SELECT and GROUP BY clauses. Options A and D are invalid because they incorrectly use nested aggregate functions, and Option B is invalid because it does not include 'join_date' in the GROUP BY clause.

Discussion
you1234Option: C

C is correct answer

danitoOption: C

I think C

Ekos

i agree

nautil2Option: C

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

FelipeCOption: C

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

Franky_TOption: C

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.

8hyehye8Option: C

C is correct

guimaleoOption: C

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

auwiaOption: B

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

fthusaOption: C

C is correct

OCP19cOption: C

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

mamadu

A and D are wrong simply because we cannot use nested group functions while grouping by any specific column, we should use the whole table as one group (by not using a group by clause) otherwise the query would not make sense.

algerianphoenixOption: C

C, by testing on OE DB sample.

Sankar1406Option: C

C for sure

sagartakeOption: B

B is correct

algerianphoenix

C, in B there is a missing column in the group by clause.

ryuahOption: C

C is correct

TaushOption: C

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

GuhborgesOption: C

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.