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

Jennifer

1 months ago

I bet the person who wrote this question spends their free time coming up with devious ways to confuse database admins. Must be a real SQL sadist!

upvoted 0 times

...

Marla

1 months ago

This question is making my head spin! I wish the database gods would just come down and tell me the right answer.

upvoted 0 times

Shay

3 days ago

Don't worry, I think the answer is B) SELECT FROM employees WHERE salary > AVG (salary) OVER (PARTITION BY department _ id);

upvoted 0 times

...

Audra

2 months 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

Delisa

14 days ago

User 3: Yeah, it's better to be safe than sorry when it comes to exam questions.

upvoted 0 times

...

Merlyn

15 days ago

User 2: I think so too. It's always good to double check before making a final decision.

upvoted 0 times

...

Willie

19 days ago

User 1: Option D does seem tricky, but it might be the correct answer.

upvoted 0 times

...

Dewitt

2 months ago

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

upvoted 0 times

...

Melvin

2 months 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

Alva

1 months ago

Yeah, I agree. Option A looks like the right choice. Let's focus on studying for the exam.

upvoted 0 times

...

Billye

2 months ago

Option A seems to be the correct one. Just need to compare salary to the average in the department.

upvoted 0 times

...

Shelton

2 months 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

Thad

1 months ago

Great, so the correct query would be WHERE salary > AVG (salary) OVER (PARTITION BY department_id).

upvoted 0 times

...

Lorean

2 months ago

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

upvoted 0 times

...

Samira

2 months ago

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

upvoted 0 times

...

Brent

2 months ago

I think the correct answer is B.

upvoted 0 times

...

Anglea

3 months 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:

Jennifer

Marla

Shay

Audra

Delisa

Merlyn

Willie

Dewitt

Melvin

Alva

Billye

Shelton

Shantell

Thad

Lorean

Samira

Brent

Anglea