Oracle Exam 1Z0-071 Topic 11 Question 69 Discussion

Actual exam question for Oracle's 1Z0-071 exam

Question #: 69
Topic #: 11

Examine the description of the ENPLYEES table:

Which two queries return all rows for employees whose salary is greater than the average salary in their department?

ASELECT ''
FROM employees
WHERE salary > ANY
SELECT AVG (salary)
EROM employees
GROUP BY department_ id);

BSELECT
FROM employees
WHERE salary > AVG (salary) OVER (PARTITION BY department _ id);

CSELECT''
FROM employees e1
WHERE salary >!
SELECT AVG (salary)
FROM employees e2
WHERE e1. Department _id = e2, department_ id

DSELECT.
FROM
SELECT e.', AVG (salary) OVER (PARTITION BY department id) avg_ sal
FROM employees e
WHERE salary > avg_ sal;

ESELECT''
FROM employees
WHERE salary >
( SELECT AVG
(salary) FROM
employees
GROUP BY department _ id

Show Suggested Answer

Suggested Answer: D, E

by Eun at Jan 10, 2023, 01:20 AM

Limited Time Offer

25%

Off

Get Premium 1Z0-071 Questions as Interactive Web-Based Practice Test or PDF

Contribute your Thoughts:

Submit Cancel

Audra

4 days ago

Option D looks interesting, but I'm not sure if it's really the correct answer. I wonder if the exam writers are trying to trick us with that one.

upvoted 0 times

...

Dewitt

20 days ago

I'm not sure about the correct answers. Can you explain why B and E are the right choices?

upvoted 0 times

...

Melvin

20 days ago

Hmm, I'm not sure about this one. All the options look pretty confusing to me. I guess I'll just have to study harder for the next exam.

upvoted 0 times

...

Shelton

23 days ago

I agree with you, Anglea. Those queries seem to be the ones that return all rows for employees with salary greater than the average in their department.

upvoted 0 times

...

The correct answer is B. The OVER (PARTITION BY department_id) clause in the query allows you to calculate the average salary for each department and then compare the employee's salary to that average.

upvoted 0 times

Lorean

3 days ago

That makes sense, it allows you to compare the employee's salary to the average in their department.

upvoted 0 times

...

Samira

5 days ago

I agree, the OVER (PARTITION BY department_id) is used to calculate the average salary for each department.

upvoted 0 times

...

Brent

8 days ago

I think the correct answer is B.

upvoted 0 times

...

Anglea

27 days ago

I think the correct answers are B and E.

upvoted 0 times

...

Oracle Exam 1Z0-071 Topic 11 Question 69 Discussion

Contribute your Thoughts:

Audra

Dewitt

Melvin

Shelton

Shantell

Lorean

Samira

Brent

Anglea