Click the Exhibit button to examine the structure of the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables.
Two new departments are added to your company as shown:
DEPARTMENT_ID DEPARTMENT_NAME MGR_ID LOCATION_ID
9998 Engineering 123
9999 Administrative Boston
You need to list the names of employees, the department IDs, the department names, and the
cities where the departments are, even if there are no employees in the departments and even if
the departments are not yet assigned to a location. You need to join the EMPLOYEES,
DEPARTMENTS, and LOCATIONS tables to retrieve this information.
Which statement do you execute to retrieve this information?
A.
SELECT last_name, department_id,
department_name, city
FROM departments d
NATURAL JOIN employees e
NATURAL JOIN locations l;
B.
SELECT e.last_name, d.department_id,
d.department_name, l.city
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id = e.department_id
LEFT OUTER JOIN locations l
ON d.location_id = l.location_id;
C.
SELECT e.last_name, d.department_id,
d.department_name, l.city
FROM departments d
FULL OUTER JOIN employees e
ON d.department_id = e.department_id
FULL OUTER JOIN locations l
ON d.location_id = l.location_id;
D.
SELECT e.last_name, d.department_id,
d.department_name, l.city
FROM departments d
RIGHT OUTER JOIN employees e
ON d.department_id = e.department_id
RIGHT OUTER JOIN locations l
ON d.location_id = l.location_id;