Oracle Exam 1Z0-071 Topic 3 Question 96 Discussion

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

Question #: 96
Topic #: 3

Examine the description of the EMPLOYEES table:

Which two queries return rows for employees whose manager works in a different department?

ASELECT emp. *
FROM employees emp
WHERE manager_ id NOT IN (
SELECT mgr.employee_ id
FROM employees mgr
WHERE emp. department_ id < > mgr.department_ id
);

BSELECT emp.*
FROM employees emp
WHERE NOT EXISTS (
SELECT NULL
FROM employees mgr
WHERE emp.manager id = mgr.employee_ id
AND emp.department_id<>mgr.department_id
);

CSELECT emp.*
FROM employees emp
LEFT JOIN employees mgr
ON emp.manager_ id = mgr.employee_ id
AND emp. department id < > mgr. department_ id;

DSELECT emp. *
FROM employees emp
RIGHT JOIN employees mgr
ON emp.manager_ id = mgr. employee id
AND emp. department id <> mgr.department_ id
WHERE emp. employee_ id IS NOT NULL;

ESELECT emp. *
FROM employees emp
JOIN employees mgr
ON emp. manager_ id = mgr. employee_ id
AND emp. department_ id<> mgr.department_ id;

Show Suggested Answer

Suggested Answer: D, E

by Pilar at Jun 28, 2024, 12:14 PM

Limited Time Offer

25%

Off

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

Contribute your Thoughts:

Submit Cancel

Maynard

4 months ago

Option D looks like it's trying too hard. Let's keep it simple, folks - B or C for the win!

upvoted 0 times

Chanel

4 months ago

Let's go with either B or C, they both seem like the best options.

upvoted 0 times

...

Matthew

4 months ago

Yeah, option D does seem a bit complicated compared to B and C.

upvoted 0 times

...

Helaine

4 months ago

I think option C also makes sense, it's a good choice.

upvoted 0 times

...

Sylvie

4 months ago

I agree, option B is straightforward and easy to understand.

upvoted 0 times

...

Natalie

5 months ago

Hmm, I'm torn between B and C. But hey, at least this question isn't about calculating the meaning of life, right?

upvoted 0 times

Pearlie

3 months ago

Yeah, B looks like the best option here.

upvoted 0 times

...

Ulysses

3 months ago

I agree, B seems like the right choice.

upvoted 0 times

...

Caitlin

3 months ago

I think B is the correct answer.

upvoted 0 times

...

Melissa

3 months ago

Yeah, B looks like the best option here.

upvoted 0 times

...

Evangelina

3 months ago

I agree, B seems like the correct choice.

upvoted 0 times

...

Bette

3 months ago

I think B is the right answer.

upvoted 0 times

...

Lenna

5 months ago

I see your point, but I still think C and E are the right choices.

upvoted 0 times

...

Leigha

5 months ago

But A and B make more sense logically, don't you think?

upvoted 0 times

...

James

5 months ago

Option E seems the most comprehensive, using a regular JOIN with the additional condition to filter the results. Gotta love those SQL ninjas!

upvoted 0 times

Lynelle

4 months ago

Yeah, SQL ninjas always find the most efficient ways to query the database.

upvoted 0 times

...

Alaine

4 months ago

I agree, option E looks like the best choice for this scenario.

upvoted 0 times

...

Robt

5 months ago

I'd go with option C. The LEFT JOIN makes it easy to identify employees whose managers are in a different department.

upvoted 0 times

Deandrea

4 months ago

Option C and A both seem like good options. It's important to understand how each query is working.

upvoted 0 times

...

Ethan

5 months ago

I agree, option A is a valid choice as well. It's always good to consider different approaches.

upvoted 0 times

...

Yan

5 months ago

I think option A could also work. It uses a subquery to check for managers in different departments.

upvoted 0 times

...

Magnolia

5 months ago

Option C is a good choice. It uses LEFT JOIN to find employees with managers in different departments.

upvoted 0 times

...

Lenna

5 months ago

I disagree, I believe it's C and E.

upvoted 0 times

...

Leigha

5 months ago

I think the correct answers are A and B.

upvoted 0 times

...

Bethanie

6 months ago

Option B looks like the most straightforward way to get the desired result. I like how it uses the NOT EXISTS clause to exclude employees with managers in the same department.

upvoted 0 times

Micah

5 months ago

Yeah, using the NOT EXISTS clause makes it clear and concise.

upvoted 0 times

...

Ruthann

5 months ago

I agree, option B seems like the most efficient choice.

upvoted 0 times

...