Oracle Exam 1Z0-071 Topic 14 Question 89 Discussion

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

Question #: 89
Topic #: 14

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 Ezekiel at Apr 14, 2024, 06:17 AM

Limited Time Offer

25%

Off

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

Contribute your Thoughts:

Submit Cancel

Alise

6 months ago

I don't think option C is correct. It doesn't specify that the manager works in a different department.

upvoted 0 times

...

Maryanne

6 months ago

But what about option C? It also looks like it could work.

upvoted 0 times

...

Cristy

6 months ago

I agree. A and B seem to be the most logical choices.

upvoted 0 times

...

Laura

6 months ago

I think the correct queries are A and B.

upvoted 0 times

...

Kattie

6 months ago

I think query C, D, and E are incorrect because they are using different types of joins which may not give the desired result.

upvoted 0 times

...

Franklyn

7 months ago

I believe query B is also correct as it uses a subquery with NOT EXISTS to achieve the same result.

upvoted 0 times

...

Janey

7 months ago

I agree with you. Query A uses a NOT IN clause to check for employees whose manager works in a different department.

upvoted 0 times

...

Pamella

7 months ago

I think the correct queries should be A and B.

upvoted 0 times

...

Stephanie

8 months ago

I'm not a big fan of the NOT IN approach in option A. It just feels a bit clunky compared to the other options.

upvoted 0 times

...

King

8 months ago

Haha, option D with the RIGHT JOIN? Really? Who even uses that these days? That feels like a trap answer.

upvoted 0 times

...

Lynelle

8 months ago

I'm leaning towards option B or C. The NOT EXISTS and LEFT JOIN seem like they might be the most effective way to handle this requirement.

upvoted 0 times

Luke

7 months ago

User 2

upvoted 0 times

...

Brandon

7 months ago

User 1

upvoted 0 times

...

Jina

8 months ago

Okay, let's think this through step-by-step. We need to find employees whose managers work in a different department, so the key is finding the right join condition.

upvoted 0 times

...

Crista

8 months ago

Ugh, I hate these types of questions where you have to analyze multiple queries and figure out the most efficient one. It's like they're trying to trip us up.

upvoted 0 times

...

Anastacia

8 months ago

Hmm, this question seems a bit tricky. I'm not entirely sure which query will return the desired result, but I'll give it my best shot.

upvoted 0 times

...