Quiz: Inner versus Outer Joins

Test: Quiz: Inner versus Outer Joins
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;




Correct Correct


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);




Correct Correct


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



Correct Correct


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


Page 1 of 1 Summary

( Vrei sa traduci ceva?Want to translate something? http://translate.google.ro/# )