Oracle Exam 1Z0-071 Topic 2 Question 98 Discussion

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

Question #: 98
Topic #: 2

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 Ahmad at Jul 13, 2024, 02:55 AM

Limited Time Offer

25%

Off

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

Contribute your Thoughts:

Submit Cancel

Paulene

4 months ago

I hope the exam doesn't have any 'knock-knock' jokes hidden in the questions.

upvoted 0 times

...

Stanford

4 months ago

This question is clearly designed to weed out the 'managers' who don't know their JOINs.

upvoted 0 times

...

Georgeanna

4 months ago

Option D seems a bit convoluted. Why the RIGHT JOIN and the IS NOT NULL check?

upvoted 0 times

Scot

3 months ago

C) Option E also looks like a good choice for this scenario.

upvoted 0 times

...

Theron

3 months ago

B) I agree, Option D does seem unnecessarily complicated.

upvoted 0 times

...

Laurel

4 months ago

A) Option A is more straightforward and easier to understand.

upvoted 0 times

...

Gladys

4 months ago

Option E is my pick. The JOIN with the additional condition seems clear and concise.

upvoted 0 times

...

Carline

4 months ago

I see your point, Galen, but I still think A and B are the best options.

upvoted 0 times

...

Verdell

5 months ago

I'm leaning towards Option C. The LEFT JOIN seems like a straightforward way to get the desired result.

upvoted 0 times

Antonio

3 months ago

True, Option A is another valid approach to find employees in different departments.

upvoted 0 times

...

Dalene

3 months ago

I think Option A could also work. Using NOT IN can be effective.

upvoted 0 times

...

Claribel

4 months ago

I agree, the LEFT JOIN is a clear way to compare departments.

upvoted 0 times

...

Benedict

4 months ago

Option C seems like a good choice. The LEFT JOIN should work well.

upvoted 0 times

...

Galen

5 months ago

I'm not sure, I think option C could also be a valid choice.

upvoted 0 times

...

Shalon

5 months ago

Option B looks good, but I'm not sure if the NOT EXISTS clause is the most efficient way to handle this.

upvoted 0 times

Thaddeus

4 months ago

I prefer Option C, it uses a LEFT JOIN to compare departments of employees and managers.

upvoted 0 times

...

Ling

4 months ago

I think Option A is also a good choice, it uses a subquery to check for different departments.

upvoted 0 times

...

Edward

4 months ago

Option B is efficient because it uses the NOT EXISTS clause.

upvoted 0 times

...

Chau

5 months ago

I agree with Carline, those options seem to be the most logical.

upvoted 0 times

...

Carline

5 months ago

I think option A and option B are correct.

upvoted 0 times

...