Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
Section 1
1. If you select rows from two tables (employees and departments) using an outer join, what will you get? Use the code below to arrive at your answer:
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);
Mark for Review
(1) Points
All employees that do not have a department_id assigned to them
All employees including those that do not have a department_id assigned to them (*)
No employees as the statement will fail
None of the above
Correct Correct
2. What is another name for a simple join or an inner join? Mark for Review
(1) Points
Nonequijoin
Equijoin (*)
Self Join
Outer Join
Correct Correct
3. EMPLOYEES Table:
Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
DEPARTMENT_ID NUMBER (4)
DEPARTMENTS Table:
Name Null? Type
DEPARTMENT_ID NOT NULL NUMBER 4
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER (6)
A query is needed to display each department and its manager name from the above tables. However, not all departments have a manager but we want departments returned in all cases. Which of the following SQL: 1999 syntax scripts will accomplish the task?
Mark for Review
(1) Points
SELECT d.department_id, e.first_name, e.last_name
FROM employees e
LEFT OUTER JOIN departments d
WHERE (e.department_id = d.department_id);
SELECT d.department_id, e.first_name, e.last_name
FROM employees e
RIGHT OUTER JOIN departments d ON (e.employee_id = d.manager_id);(*)
SELECT d.department_id, e.first_name, e.last_name
FROM employees e
FULL OUTER JOIN departments d ON (e.employee_id = d.manager_id);
SELECT d.department_id, e.first_name, e.last_name
FROM employees e, departments d
WHERE e.employee_id RIGHT OUTER JOIN d.manager_id;
4. Given the following descriptions of the employees and jobs tables, which of the following scripts will display each employee?s possible minimum and maximum salaries based on their job title?
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)
JOBS Table:
Name Null? Type
JOB_ID NOT NULL VARCHAR2 (10)
JOB_TITLE NOT NULL VARCHAR2 (35)
MIN_SALARY NUMBER (6)
MAX_SALARY NUMBER (6)
Mark for Review
(1) Points
SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
FROM employees e
NATURAL JOIN jobs j
USING (job_id);
SELECT first_name, last_name, job_id, min_salary, max_salary
FROM employees
NATURAL JOIN jobs;(*)
SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
FROM employees e
NATURAL JOIN jobs j;
SELECT first_name, last_name, job_id, min_salary, max_salary
FROM employees e
FULL JOIN jobs j (job_id);
SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
FROM employees e
NATURAL JOIN jobs j ON (e.job_title = j.job_title);
5. Which syntax would be used to retrieve all rows in both the EMPLOYEES and DEPARTMENTS tables, even when there is no match? Mark for Review
(1) Points
FULL OUTER JOIN (*)
LEFT OUTER JOIN AND RIGHT OUTER JOIN
FULL INNER JOIN
Use any equijoin syntax
6. The following statement is an example of what kind of join?
SELECT car.vehicle_id, driver.name
FROM car
LEFT OUTER JOIN driver ON (driver_id) ;
Mark for Review
(1) Points
Inner Join
Outer Join (*)
Equijoin
Optimal Join
Correct Correct
7. For which of the following tables will all the values be retrieved even if there is no match in the other?
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);
Mark for Review
(1) Points
employees (*)
department
both
Neither. the LEFT OUTER JOIN limits the value to the matching department id's.
Correct Correct
