Examice

Exam 1z0-071 All QuestionsBrowse all questions from this exam

Go to Exam

Question 124

Examine the description of the EMPLOYEES table:

Which two queries will execute successfully? (Choose two.)

SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id HAVING hire_date > '01-JAN-19';

SELECT dept_id, SUM(salary) FROM employees WHERE hire_date > '01-JAN-19' GROUP BY dept_id;

SELECT dept_id, MAX(SUM(salary)) FROM employees GROUP BY dept_id;

SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id, salary;

SELECT AVG(MAX(salary)) FROM employees GROUP BY salary;

Correct Answer: B

The query 'SELECT dept_id, SUM(salary) FROM employees WHERE hire_date > '01-JAN-19' GROUP BY dept_id;' will execute successfully. The query satisfies the SQL query rules where a GROUP BY clause is used with a valid aggregate function (SUM) without any nested aggregate functions. The other proposed queries will not execute successfully due to improper use of nested aggregate functions or incorrect usage of GROUP BY clauses.

Discussion

CyberPOption: B

I think answer B is the only correct answer because In SQL, a group function operates on a set of rows and returns a single value for each group. Group functions include AVG, COUNT, MAX, MIN, and SUM. These functions cannot be nested within one another. For example, in query A, the AVG function is nested within the MAX function which is not allowed. Similarly, in query C, D and E, the MAX function is nested within the AVG function which is also not allowed. What do you think?

lucemqyOptions: BE

BE is the correct answer

ShrimathiOptions: BE

BE tested

WingLOptions: BE

Tested. A. not a GROUP BY expression C.D boths are not a single-group group function

zouveOptions: BE

BE tested

MooonLight

C works.

zouve

ORA-00937: not a single-group group function