SQL Join Multiple Choice Questions



1)
You want to retrieve all employees, whether or not they have matching departments in the departments table. Which query would you use?

A) SELECT last_name, department_name
FROM employees e LEFT OUTER
JOIN departments d ON (e.department_id = d.department_id);

B) SELECT last_name, department_name
FROM employees e RIGHT OUTER
JOIN departments d ON (e.department_id = d.department_id);

C) SELECT last_name, department_name
FROM employees e FULL OUTER
JOIN departments d ON (e.department_id = d.department_id);

2)
Which two statements about views are true? (Choose two.)

A) A view can be created as read only.
B) A view can be created as a join on two or more tables.
C) A view cannot have an ORDER BY clause in the SELECT statement.
D) A view cannot be created with a GROUP BY clause in the SELECT statement.
E) A view must have aliases defined for the column names in the SELECT statement

3)
Evaluate this SQL statement:

SELECT employee_id, e.department_id, department_name, salary
FROM employees e, departments d
WHERE e.department_id = d.department_id;

Which SQL statement is equivalent to the above SQL statement?

A) SELECT employee_id, department_id, department_name, salary
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments);

B) SELECT employee_id, department_id, department_name, salary
FROM employees
NATURAL JOIN departments;

C) SELECT employee_id, d.department_id, department_name, salary
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

D) SELECT employee_id, department_id, department_name, salary
FROM employees
JOIN departments
USING (e.department_id, d.department_id);

4)
In which case would you use a FULL OUTER JOIN?

A) Both tables have NULL values.
B) You want all unmatched data from one table.
C) You want all matched data from both tables.
D) You want all unmatched data from both tables.
E) One of the tables has more data than the other.
F) You want all matched and unmatched data from only one table.

5)
Evaluate this SQL statement:

SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROM EMP e, DEPARTMENT d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;

In the statement, which capabilities of a SELECT statement are performed?

A) Selection, projection, join
B) Difference, projection, join
C) Selection, intersection, join
D) Intersection, projection, join
E) Difference, projection, product

6)
What is true about joining tables through an equijoin?

A) You can join a maximum of two tables through an equijoin.
B) You can join a maximum of two columns through an equijoin.
C) You specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.
D) To join two tables through an equijoin, the columns in the join condition must be primary key and foreign key columns.
E) You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions.

7)
Which three are true regarding the use of outer joins? (Choose three.)

A) You cannot use IN operator in a condition that involves an outerjoin.
B) You use (+) on both sides of the WHERE condition to perform an outerjoin.
C) You use (*) on both sides of the WHERE condition to perform an outerjoin.
D) You use an outerjoin to see only the rows that do not meet the join condition.
E) In the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outerjoin.
F) You cannot link a condition that is involved in an outerjoin to another condition by using the OR operator.

8)
In which three cases would you use the USING clause? (Choose three.)

A) You want to create a nonequijoin.
B) The tables to be joined have multiple NULL columns.
C) The tables to be joined have columns of the same name and different data types.
D) The tables to be joined have columns with the same name and compatible data types.
E) You want to use a NATURAL join, but you want to restrict the number of columns in the join condition.

9)
Examine the structure of the EMPLOYEES and DEPARTMENTS tables:

EMPLOYEES

EMPLOYEE_ID NUMBER
DEPARTMENT_ID NUMBER
MANAGER_ID NUMBER
LAST_NAME VARCHAR2(25)

DEPARTMENTS

DEPARTMENT_ID NUMBER
MANAGER_ID NUMBER
DEPARTMENT_NAME VARCHAR2(35)
LOCATION_ID NUMBER

You want to create a report displaying employee last names, department names, and locations. Which query should you use to create an equi-join?

A) SELECT last_name, department_name, location_id FROM employees , departments ;
B) SELECT employees.last_name, departments.department_name, departments.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;
C) SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE manager_id =manager_id;
D) SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;

10)
In which two cases would you use an outer join? (Choose two.)

A) The tables being joined have NOT NULL columns.
B) The tables being joined have only matched data.
C) The columns being joined have NULL values.
D) The tables being joined have only unmatched data.
E) The tables being joined have both matched and unmatched data.
F) Only when the tables have a primary key/foreign key relationship.

11)
A SELECT statement can be used to perform these three functions:

- Choose rows from a table.
- Choose columns from a table.
- Bring together data that is stored in different tables by creating a link between them.
Which set of keywords describes these capabilities?

A) difference, projection, join
B) selection, projection, join
C) selection, intersection, join
D) intersection, projection, join
E) difference, projection, product

1) A, 2) A, B, 3) C, 4) D, 5) A, 6) E, 7) D, E, F, 8) C,D,E, 9) D, 10) C, E, 11) B