section 3

Test: Quiz: Cross joins and Natural Joins

-----------------------------------------------------------------------------------------
Cross joins and Natural Joins
1. What happens when you create a Cartesian product?
All rows from one table are joined to all rows of another table (*)
No rows are returned as you entered wrong join-criteria
The table is joined to itself, one column to the next column, exhausting all possibilities
All rows that do not match in the WHERE clause are displayed

2. A NATURAL JOIN is based on:
Columns with the same name and datatype (*)
Columns with the same name
Columns with the same datatype and width
Tables with the same structure

3. The ___________ join is the ANSI-standard syntax used to generate a Cartesian product.
NATURAL
ALL
FULL
CROSS (*)

4. The join column must be included in the select statement when you use the NATURAL JOIN clause. True or False?
True
False (*)

-----------------------------------------------------------------------------------------

Test: Quiz: Join Clauses
Join Clauses

1. The following is a valid SQL statement.
SELECT e.employee_id, e.last_name, d.location_id, department_id
FROM employees e JOIN departments d
USING (department_id) ;        True or False?

True (*)
False

2. You can do nonequi-joins with ANSI-Syntax. True or False?
True (*)
False

3. The keywords JOIN _____________ should be used to join tables with the same column names but different datatypes.
NATURAL ON
ON
WHEN
USING (*)

4. The primary advantage of using JOIN ON is:  
The join happens automatically based on matching column names and data types
It will display rows that do not meet the join condition
It easily produces a Cartesian product between the tables in the statement
It permits columns that don?t have matching data types to be joined
It permits columns with different names to be joined (*)

5. Table aliases MUST be used with columns referenced in the JOIN USING clause. True or False?
True
False (*)

-----------------------------------------------------------------------------------------

Test: Quiz: Inner versus Outer Joins
Inner versus Outer Joins

1. 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)

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

2. 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?


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;


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

Inner Join
Outer Join (*)
Equijoin
Optimal Join

4. What is another name for a simple join or an inner join?
Nonequijoin
Equijoin (*)
Self Join
Outer Join

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

employees (*)
department
both
Neither. the LEFT OUTER JOIN limits the value to the matching department id's.


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

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

7. Which syntax would be used to retrieve all rows in both the EMPLOYEES and DEPARTMENTS tables, even when there is no match?  
FULL OUTER JOIN (*)
LEFT OUTER JOIN AND RIGHT OUTER JOIN
FULL INNER JOIN
Use any equijoin syntax

-----------------------------------------------------------------------------------------

Test: Quiz: Self Joins and Hierarchical Queries
Self Joins and Hierarchical Queries

1. Hierarchical queries can walk both Top-Down and Bottom-Up. True or False?
True (*)
False

2. Hierarchical queries MUST use the LEVEL pseudo column. True or False?
True
False (*)

3. Which of the following database design concepts is implemented with a self join?
Non-Transferability
Recursive Relationship (*)
Supertype
Arc

4. Which of the following database design concepts do you need in your tables to write Hierarchical queries?
Non-Transferability
Recursive Relationship (*)
Supertype
Arc

5. Which SELECT statement implements a self join?

SELECT e.employee_id, m.manager_id
FROM employees e NATURAL JOIN employees m;

SELECT e.employee_id, m.manager_id
FROM employees e, employees m
WHERE m.employee_id = e.manager_id; (*)

SELECT e.employee_id, m.manager_id
FROM employees e, manager m
WHERE e.employee_id = m.manager_id;

SELECT e.employee_id, m.manager_id
FROM employees e, departments m
WHERE e.employee_id = m.manager_id;


6. Which select statement will return the last name and hire data of an employee and his/ her manager for employees that started in the company
before their managers?

SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
FROM employees w , employees m
WHERE w.manager_id = m.employee_id AND w.hire_date < m.hire_date (*)

SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
FROM employees w , employees m
WHERE w.manager_id != m.employee_id AND w.hire_date < m.hire_date

SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
FROM employees w , employees w
WHERE w.manager_id = w.employee_id
AND w.hire_date < w.hire_date

SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
FROM employees w , employees m
WHERE w.manager_id = m.employee_id AND w.hire_date > m.hire_date