Examine the structure of the EMPLOYEES table.
Name Null? Type
-—————- —– —— ——
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.
You want to display the last names and manager IDs of employees who work for the same manager as the employee whose EMPLOYEE_ID is 123.
Which query provides the correct output?
A. SELECT e.last_name, m.manager_id
FROM employees e RIGHT OUTER JOIN employees m on (e.manager_id = m.employee_id)
AND e.employee_id = 123;
B. SELECT e.last_name, m.manager_id
FROM employees e RIGHT OUTER JOIN employees m on (e.employee_id = m.manager_id)
WHERE e.employee_id = 123;
C. SELECT e.last_name, e.manager_id
FROM employees e RIGHT OUTER JOIN employees m on (e.employee_id = m.employee_id)
WHERE e.employee_id = 123;
D. SELECT m.last_name, e.manager_id
FROM employees e LEFT OUTER JOIN employees m on (e.manager_id = m.manager_id)
WHERE e.employee_id = 123;
|
Download Printable PDF. VALID exam to help you PASS. |
 |
Answer is D.
Tested in SQL VM Ware with the Oracle SQL Developer data. You can fill in both tables yourself if you don’t have data.
To proof it, you can solve it by breaking the syntax in smaller blocks such as:
select * from employees where employee_id = 123; — found out the manager_id is 100
select * from employees where manager_id = 100; — got the answer which is the same as the answer from option D.
Option B will give you the same answer multiple times.
D
D
alias m – doesn’t mean managers here, it means employees who has the same manager as employee 123
D because “who work for the same manager as the employee whose EMPLOYEE_ID is 123” mean that e.manager_id = m.manager_id, imho
F
D
it is not D because the relationship is between EMPLOYEE_ID and MANAGER_ID.
the correct is B
D
shouldn’t b & c have the same results?..
as e.manager_id = m.manager_id
i think displaying either e.manager_id and m.manager_id should be the same?
Why D?
D
D
Correct D
A
D